dules Sales Report for Coastal
Resorts
Coastal Resorts manages a chain of hotels on four islands off the coast of British Columbia: Hornby Island,
Galiano Island, Saltspring Island, and Quadra Island. As the sales manager for the chain, you want to
attract more guests to the hotels in the spring months of April, May, and June in order to ensure that those
months are profitable. You need to determine the number of rooms to rent during these spring months in
order to show a profit. For this project, you need to Summarize Sales, Calculate Projected Sales, and
Create the Sales Report. The completed sales report appears as shown in FIGURE 5-17 on page 125.
Summarize Sales
You need to enter labels and values in an Excel worksheet and then calculate total sales.
STEPS
1. Start a new workbook in Excel, enter and enhance the labels and values so that the
worksheet appears as shown in FIGURE 5-11, then save the workbook as PR 5-Coastal
Resorts Sales Data to the location where you save the files for this book
2. Click cell B8, enter the formula to multiply the Average Cost per Room by the Total
Number of Rooms Rented, then copy the formula through cell E8, widening columns
as needed
As you complete the required calculations, refer to FIGURE 5-12 to verify your totals.
3. Click cell B13, enter the formula to multiply the Number of Rooms Available by the
Operating Cost per Room, then copy the formula through cell E13
Note that the value for the Operating Cost per Room is based on the cost over a three-month period.
4. Click cell B15, enter the formula to add the Total Operating costs to the Advertising
Costs, then copy the formula through cell E15
5. Calculate the Net Revenue in cell B17 as the Total Expenses subtracted from the Total
Room Rental Revenue, then copy the formula through cell E17, widening columns as
needed
The hotels on Hornby Island and Galiano Island lost money during the spring months of April, May, and
June. Only the Saltspring Island and Quadra Island hotels made a profit.
6. Select cells B7:F8, click the AutoSum button in the Editing group, then widen Column F
as needed to fit content
7. Select cells B13:F17, click the AutoSum button, then verify that $(8,400.00) appears in
cell F17 as shown in FIGURE 5-12
8. Select cells B7:E7, click the Data tab, click What-lf Analysis in the Forecast group, click
Scenario Manager, click Add, type 2017 Rentals, click OK, click OK, then click Close
You create a scenario to preserve the existing data because in the next lesson you will change the data in
order to calculate projected sales.
9. Apply borders and shading to the worksheet so that it appears as shown in FIGURE 5-12,
then save the workbook
uration 120
Intoaroti
Setup
F
с
B
24 pt, bold
1 Coastal Resorts
Wrap text in columns B to
E, then adjust column
widths as needed
2 Sales Summary for April, May, June, 2017
3
Galiano
19 14 pt
Hornby
Island
Saltspring
Island
Quadra
Island
Island
Totals
4 bold, italic
5 REVENUE
6 Average Cost per Room
7. Total Number of Rooms Rented
8 Total Room Rental Revenue
$
150.00 $
175.00 $
175.00
125.00 $
300
200
700
400
Apply
Accountit
Number
formattin
and bold
shown
20
30
50
30
$ 2,000.00 $ 2,000.00 $ 2,000.00 $ 2,000.00
10 EXPENSES
11 Number of Rooms Available
12 Operating Cost per Room
13 Total Operating Costs
14 Advertising Costs
15 Total Expenses
16
$ 1,200.00 $ 2,000.00 $ 3,000.00 $ 2,200.00
17 NET REVENUE
18
19
G
FIGURE 5-12: Worksheet complete with calculations and formatting
A
B
C 19 D
E
F
Coastal Resorts
Blue, Accent 5,
1
Darker 25%
Blue, Accent 5,
with White text
2
Lighter 80%; cells are
» April, May, June, 2017
3
merged and centered,
then left aligned
Hornby Galiano Saltspring Quadra
4
Island
Island
Island Island
Totals
5 REVENUE
6 Average Cost per Room
$ 150.00 $ 125.00 $ 175.00 $ 175.00
7 Total Number of Rooms Rented
200
300
700
400
1600
8 Total Room Rental Revenue $ 30,000.00 $ 37,500.00 $ 122,500.00 $ 70,000.00 $ 260,000.00
9
10 EXPENSES
11 Number of Rooms Available
20
30
50
30
12 Operating Cost per Room
$ 2,000.00 $ 2,000.00 $ 2,000.00 $ 2,000.00
13 Total Operating Costs
$ 40,000.00 $ 60,000.00 $ 100,000.00 $ 60,000.00 $260,000.00
14 Advertising Costs
$ 1,200.00 $ 2,000.00 $ 3,000.00 $ 2,200.00 $ 8,400.00
15 Total Expen: Gold, Accent 4, $ 41,200.00 $ 62,000.00 $ 103,000.00 $ 62,200.00 $ 268,400.00
16
Lighter 80%
17 INET REVENUE
$(11,200.00) $(24,500.00) $ 19,500.00 $ 7,800.00 $ (8,400,00)
18
Border lines
added to
ranges A4:F
A10:F15 an
A17:F17
19
dule 5
Sales Report (continued)
Calculate Projected Sales
You use the Goal Seek feature to determine how many rooms you should rent at the Hornby Island and
Galiano Island hotels to increase the net revenue from these hotels, then create a bar chart to compare
current and projected income from the rental of rooms at the Bowen Island and Galiano Island hotels.
STEPS
HINT
use Goal Seek to
rmine how
y rooms you
to rent at the
aby Island hotel
ncrease the net
enue to $10,000.
1. Click cell B17 (the net revenue for the Hornby Island hotel), click What-lf Analysis in
the Forecast group on the Data tab, click Goal Seek, click the text box next to “To
value:”, type 10000 (four zeroes), press [Tab], type B7 as shown in FIGURE 5-13, click OK,
then click OK again
The value needed, 341.3333333, appears in cell B7.
2. Click cell C17, click What-lf Analysis, click Goal Seek, enter 8000 as the To value and C7
as the cell to change, click OK, click OK, select B7:F7, click the Home tab, apply the
Comma style, then click the Decrease Decimal button twice
Over the three months, you need to rent 341 rooms at the Hornby Island hotel to make a profit of $10,000,
and
you need to rent 560 rooms at the Galiano Island hotel to make a profit of $8,000.
3. Click the Data tab, click What-If Analysis, click Scenario Manager, click Add, type
Projected Rentals, enter B7:07 as the changing cells, click OK, click OK, then click Close
4. Double-click the Sheet1 tab, type 2017 Rentals, click the Select All button in the
upper-left corner of the worksheet frame to select all the data, click the Home tab,
click the Copy button in the Clipboard group, add a new worksheet, click the Paste
button in the Clipboard group, then name the new sheet tab Projected Rentals
5. Show the 2017 Rentals worksheet, click the Data tab, click What-If Analysis, click
Scenario Manager, click 2017 Rentals, click Show, click Close, then click cell A1
The net revenue in cell F17 of the 2017 Rentals sheet is again $ (8,400.00).
6. Go to the Projected Rentals worksheet, click the Insert tab, click the Insert Column
or Bar Chart button in the Charts group, click Clustered Bar in the 2D Bar
section, click Select Data in the Data group, click the Chart data range Collapse
button click the 2017 Rentals tab, select cells B4:E4, press and hold [Ctrl],
select cells B8:E8, click the Chart data range Expand button then click OK
7. Move the chart down so that it starts at cell A20, resize it so that it extends to cell
G40, click Select Data in the Data group, click Edit under Legend Entries (Series), type
2017 Rentals as shown in FIGURE 5-14, click OK, click Add, type Projected Rentals, click
the Series values Collapse button select cells B8:E8 on the Projected Rentals tab,
click the Expand button , click OK, then click OK
8. Click Add Chart Element in the Chart Layouts group, point to Chart Title, click Above
Chart, type Comparison of Current and Projected Room Rentals, press [Enter), click
Add Chart Element, point to Legend, then click Bottom if necessary
9. Click any orange bar so only the orange bars are selected, click the Chart Tools Format
tab, change the Shape Fill color to Gold, Accent 4, Lighter 80%, click away from the
chart, then save the workbook
The chart is shown in FIGURE 5-15.
tegration 122
Integration Projects
FIGURE 5-14: Edit Series dialog
FIGURE 5-13: Goal Seek dialog box
box qol salsa
cole Fan Series 1019 atslu?
X
Goal Seek
?
х
Series name:
Set cell
B17
2017 Rentals
ES
Select Range
To value:
10000
By changing cell: B7
Series values:
= ‘2017 Rentals’!$B$8:$E$8
$30,000.00,…
OK
Cancel
OK
Cancel
FIGURE 5-15: Chart comparing current rentals with projected rentals
B OC 7.103901E
F
G
H
19
20
21
22
Comparison of Current and Projected Room Rentals
23
24
Quadra Island
25
26
27
28
Saltspring Igand
29
Integration 2016
30
31
Galiano Island
32
33
34
Hornby Band
mimim
$
$20,000.00
$40,000.00
$60,000.00
$80,000.00
$100,000.00 $120,000.00 $140,000.00
38
3
39
Projected Rentals
2017 Rentals
40
41
13
2017 Rentals
Projected Rentals
Using Goal Seek
You use Goal Seek when you know the result you want a for- many hours a month you need to work to make $1000. You set
mula to produce, but you don’t know what value you need to up a worksheet with Hours Worked, Hourly Rate, and Total. The
include in the formula to get the result. For example, suppose total cell must include a formula-in this case Hours Worked X
you want to make $1000 a month from a part-time job. You Hourly Rate. You use Goal Seek to set the Total to $1000 by
know you can make $15/hour, but you need to know how changing the Hours Worked. FIGURE 5-16 shows the result.
FIGURE 5-16: Goal
Seek Example adi
ni Mr
olegada tollo siupne
Nisa
The Set cell
Goal Seek dialog box
must contain
contains the cell to
a formula
set to a specific value
(cell B3) by changing
the value in cell B1.
3
-21az
Hours Worked
Hourly Rates
Total Payl $
15.CO
Goal Seek
?
Х
Sot ait
es
To yalur:
1100
5
6
7
Hours Worked
67
Hourly Rate $ 15.CO
Total Pay $1,000.00
Ey shanging at 811
Canal
6
10
11
12
13
14
15
16
17
ansion
powinno sielov s
sa tak
Goal Seek result: 67 hours
must be worked to make
$1,000 @ $15.00/hour)
10
19
2D
Integration Projects
Integration 123
Sales Report for Coastal Resorts
Prepared by Your Name
In April
, May, and June of 2017, both the Hornby Island and Galiano Island resorts lost
money. To increase revenue at the Hornby Island resort, we need to rent 341 rooms
instead of the current 200 rooms. To increase revenue at the Galiano Island resort, we
need to rent 560 rooms instead of the current 300 rooms. By doing so, we increase the
Hornby Island resort revenue to $10,000 and the Galiano Island resort revenue to $8,000.
Coastal Resorts
Sales Projections for April, May, June, 2018
Hornby
Island
Galiano
Island
Saltspring
Island
Quadra
Island
Totals
REVENUE
Average Cost per Room
Total Number of Rooms Rented
Total Room Rental Revenue
$ 150.00 $ 125.00 $ 150.00 $ 175.00
341
560
700
400
2,001
$ 51,200.00 $ 70,000.00 $ 105,000.00 $ 70,000.00 $296,200.00
EXPENSES
Number of Rooms Available
Operating Cost per Room
Total Operating Costs
Advertising Costs
Total Expenses
20
30
50
30
$ 2,000.00 $ 2,000.00 $ 2,000.00 $ 2,000.00
$ 40,000.00 $ 60,000.00 $ 100,000.00 $ 60,000.00 $ 260,000.00
$ 1,200.00 $ 2,000.00 $ 3,000.00 $ 2,200.00 $ 8,400.00
$ 41,200.00 $ 62,000.00 $ 103,000.00 $ 62,200.00 $268,400.00
NET REVENUE
$10,000.00 $ 8,000.00 $ 2,000.00 $ 7,800.00 $ 27,800.00
The bar chart shown below compares the current room rentals in April, May, and June
of 2017 with the projected room rentals in April, May, and June of 2018.
Comparison of Current and Projected Room Rentals
1
1
Quadra Island
Saltspring Island
Increase to 560 rooms
Galiano Island
Increase to 341 rooms
Hornby Island
$-
$20,000.00 $40,000.00 $60,000.00 $80,000.00 $100,000.00 $120,000.00 $140,000.00
Projected Rentals 2017 Rentals
tegration
dule 5
Sales Report (continued)
Create the Sales Report
1.
from the Excel workbook in a report you
You need to compile all the data from the Excel workbook in a report you create in Word. The completed
report is shown in FIGURE 5-17.
STEPS
1. Start Word, open a new blank document, type Sales Report for Coastal Resorts, press
[Enter], apply the Title style to the text, click the Design tab, select the Slice theme,
select the Casual Style Set, then save the document as PR 5-Coastal Resorts Sales
Report to the location where you save the files for this book
2. Click the Colors button in the Document Formatting group, scroll to and select the Red
Orange color set, type the subtitle, then type the first paragraph of text as shown in
the completed sales report in FIGURE 5-17
3. Switch to Excel, select cells A1:F17 in the Projected Rentals worksheet, click the Copy
button in the Clipboard group, switch to Word, verify that the insertion point is at the
end of the paragraph you just typed, press [Enter], click the Home tab, click the Paste
list arrow, click Paste Special, click the Paste link option button, click Microsoft Excel
Worksheet Object, then click OK
4. Right-click the pasted worksheet object in Word, click Picture, click the Size tab, set the
Width of the object to 6″, click OK, double-click below the worksheet object, type the
paragraph of text under the copied worksheet object as shown in FIGURE 5-17, then press
[Enter]
5. Double-click the pasted worksheet object, click cell A2, select Summary in the formula
bar, type Projections, select 2017, type 2018, then press [Enter]
6. Scroll to the chart, click the chart, click the Copy button, switch to Word, double-click
below the last paragraph of text, click the Paste list arrow, click the Use Destination
Theme & Link Data button 2, set the Height of the chart to 3.1″ and the Width to
6.7″, switch to Excel, change the cost of the Saltspring Island rooms (cell D6) to 150,
return to Word, scroll to verify that the Saltspring Island room cost was updated to
$150, then click anywhere in the text above the chart
By selecting the Use Destination Theme & Link Data option, you ensure that the chart uses the color
scheme and theme associated with the Word document, not the original Excel workbook.
7. Click the Insert tab, click Shapes in the Illustrations group, click the Rounded
Rectangular Callout shape in the Callouts section, draw a shape above the chart, click
the More button in the Shape Styles gallery, click Subtle Effect – Gold, Accent 2,
type Increase to 341 rooms, then resize and position the callout as shown in FIGURE 5-17
8. Click the border of the callout box, copy and paste the callout box, then enter the
required text and position the callout box next to Galiano Island as shown in FIGURE 5-17
9. Save and close the document, switch to Excel, add your name below the chart, save
and close the workbook, click No if prompted to save the copied objects, then submit
both files to your instructor
If you wish to make changes to the Word document linked to the Excel workbook, open the Excel work-
book first. Then, open the Word document and click Yes in response to the message that appears asking if
you wish to maintain the link between the document and the workbook.
TROUBLE
If the room rate was
not updated, right-
click the worksheet
object, then click
Update Link.
TROUBLE
You will need to drag
the yellow circle
handle to move the
tail to the left side of
the callout. You may
need to drag and
move several times
to get the callout to
look like you want.
MORE PRACTICE
For more practice
with the skills
presented in this
project, complete
Independent
Challenge 2.
Integration 124
Integration Projects
C
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.