Answer Happy

Project Description:
The hotel area has asked you to develop a spreadsheet model that
will assist in determining room rates and the break-even point for
specific types of rooms—such as the Grand Villa Suite. As part of
this analysis, you will need to import data on reservations made at
the hotel. Currently, William Mattingly is not certain whether the
pricing helps cover expenses such as housekeeping costs or whether
the price even generates a profit for the resort. He would also
like you to generate a Scenario summary report that will illustrate
the net income based on best-case, worst-case, and most-likely
scenarios. Mr. Mattingly gave you an Excel workbook with a partial
model already built and some data to use while building your model.
Upon completion, you will present your model to Mr. Mattingly
before the spreadsheet model is implemented.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Excel. Open the downloaded file named
e05_grader_h1_HotelFinancials.xlsx. Save the file with the
name e05_grader_h1_HotelFinancials_LastFirst,
using your last and first name. If necessary, enable the
content.
0
2
Import the table tblRoomTypes from the
e05HotelSales.accdb database. Place the imported data in
cell A3 on the SalesForecast worksheet as a Table
10
3
On the SalesForecast worksheet, in cell D3, type
Quantity, and enter 1 as the
quantity for each record that was imported.
4
4
In cell E3 type Extended Price. In the cell
range E4:E6, calculate the extended price for each room type by
multiplying the room rate by the quantity.
8
5
In cell F3, type Goal, and in cells F4 and F5,
type 3000. In cell F6, type
8000.
6
6
Use Goal Seek to find the appropriate quantities of each room
type in order for the extended price to meet the goals you typed in
cell range F4:F6.
9
7
Format the cell range D4:D6 as Number with 0 decimal
places.
Format the cell ranges C4:C6 and E4:F6 as Currency with 0 decimal
places.
Adjust the column widths so that all data are visible.
6
8
On the NewCustomers worksheet, in the range E2:E12, use a text
function to remove any nonprintable characters from the guest names
in column B of the data. Adjust the width of column E to fit the
text.
6
9
In cell F2, use the appropriate function to display the street
address from C2 in proper case. Copy the function down through cell
F11.
5
10
The data in the cell range D2:D11 contains the customer’s home
city and state. The last two characters in each cell contain the
state abbreviation. In cell G2, use the appropriate functions to
display the only city from D2. Be certain to remove extra spaces
from the city name. Copy the function down through cell G11.
10
11
On the Scenarios worksheet, use the Scenario Manager to add a
scenario named Most-likely scenario. Use the cell
range D5:D7 as the Changing cells. The current values on the
worksheet will be your Most-likely scenario values.
8
12
Add a new scenario named Best-case scenario
using the same changing cells. In the Scenario Values dialog box,
type 40 in row 1, type 65 in row
2, and type 80 in row 3.
8
13
Add a new scenario named Worst-case scenario
using the same changing cells. In the Scenario Values dialog box,
type 12 in row 1, type 24 in row
2, and type 50 in row 3.
8
14
Create a Scenario PivotTable Report using cells E8, C20, and C22
as your Result cells.
Complete the following tasks to format the report with appropriate
headings and formatting.
• In cell A1, type Room Reservations.
• In cell B3, type Gross Revenue.
• In cell C3, type Total Expenses.
• In cell D3, type Net Income.
• Format the gross revenue, total expenses, and net income data as
Currency, and AutoFit the widths of the columns as needed
12
15
Close the workbook and then exit Excel. Submit the workbook as
directed.
0
Total Points
100