1	Start Excel. Open the downloaded file named Excel_Bu05_PS4_HotelReservations.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
2	You will need to import reservation data from the hotel in order to clean the data for further analysis. Establish a connection to the text file Excel_BU05_PS2_ReservationData.txt using Get & Transform.
3	Split the data into individual columns using the Tab character as a delimiter and use the first row of data as headings.
4	Change the data types of the CheckInDate and CheckOutDate fields to Date.
5	Clean any non-printable characters from the GuestName field. These may not be visible in the Power Query Editor, but will be visible in Excel if not removed.
6	Extract the first name, middle name, and last name from GuestName into three new columns named FirstName MiddleName and LastName in that order.
7	Close and Load the data into Cell A1 of the ReservationData worksheet.
8	On the RatesScenario worksheet, use the Scenario Manager to add a scenario named Most-likely scenario. Use cells C4:C7 as the Changing cells. The current values on the worksheet will be your Most Likely scenario values.
9	Add a new scenario named Best-case scenario using the same changing cells. In the Scenario Values dialog box, in row 1, type 410 and then in row 2, type 220. Click row 3, type 265 and then in row 4, type 130.
10	Add a new scenario named Worst-case scenario using the same changing cells. In the Scenario Values dialog box, in row 1, type 160 and then in row 2, type 105. Click row 3, type 160 and then in row 4, type 65.
11	Create a Scenario Summary report using E17 as your Result cells. Resize column B to 24. Delete the cell addresses in cells C6:C9, and C11. In cell B6, type the text Double (1 king bed) as a more appropriate label for the changing cell. In cell B7, type the text Triple (1 king, foldout couch) as a more appropriate label for the changing cell. In cell B8, type the text Quad (2 king beds) as a more appropriate label for the changing cell. In cell B9, type the text Grand Villa Suite as a more appropriate label for the changing cell. In cell B11, type the text Net Income as a more appropriate label for the result cell.
12	On the GrandVillaAnalysis worksheet, begin creating a weekly break-even analysis for the Grand Villa Suite by inserting a scroll bar inside the area of cells E4:E14.
13	In the Format Control dialog box of the scroll bar, type the following criteria: Current value: 1 Minimum value: 1 Maximum value: 20 Incremental change: Leave as the default value of 1 Page change: Leave as the default value of 10 Cell link: $D$4
14	Apply conditional formatting to cell D17 so numbers that are less than zero are displayed in red text, and then use the scroll bar to scroll until you find the break-even point.
15	In cells G4:I4, create cell references for the values that correspond to the headings in G3:I3. Format the range G4:I4 to hide the values you just referenced by right-clicking any of the selected cells, clicking Format Cells, and then using the ;;; Custom Category format.
16	Using the range F4:I14, create a one-variable data table. Use $D$4 as the Column input cell box. Format the cells in columns G:I of the data table as Currency. Adjust the column widths of columns G:I so that all data are visible.
17	Use conditional formatting to create Data Bars for the range I5:I14. Select Green Data Bar under Gradient Fill.
18	Select the range G5:H14 in the data table, and then create a 2-D line chart. Use the range F5:F14 as the Horizontal (Category) Axis Labels.
19	Delete the chart title and legend, and add a horizontal axis title that is below the axis. Type Rooms Booked in the Title text box, and then move your chart below the one-variable data table so that the top-left corner is in cell F16.
20	Close the workbook and then exit Excel. Submit the workbook as directed.

B D E F G H I K L M N o 0 R S 1 Q 2 3 Revenue Painted Paradise Resort and Spa Weekly Break-even Analysis for the Grand Villa Suite Booked Expenses Revenue Quantity sold Price per Night $1,293.00 Gross Revenue $1,293.00 Net Income 4 1 5 2 4 6 7 Expenses 8 Fixed Costs 6 8 9 10 12 Manager Salaries Utilities Equipment Depreciation Insurance $3,421.00 $580.00 $988.00 $363.00 $5,352.00 14 16 18 20 Marketing Expense 16% 10 11 12 13 Total Fixed Costs 14 Variable Costs 15 16 Total Expenses 17 Net Income 18 19 20 21 22 23 $206.88 $5,558.88 $4,265.88 24 Excel_BUO5_PS2_Reservation Data Reservation Data RatesScenario Grand VillaAnalysis Documentation B F G I 1 J K L M. N o o R 1 Painted Paradise Resort and Spa 2 Scenario Analysis for Monthly Hotel Room Pricing 3 Revenue Qty Price Extended Price 4. Double (1 king bed) 245 $318.00 $77,910.00 5 Triple (1 king bed, foldout couch) 167 $414.00 $69,138.00 6 Quad (2 king beds) 210 $541.00 $113,610.00 7 Grand Villa Suite 85 $1,293.00 $109,905.00 8 Gross Revenue $370,563.00 9 Expenses 10 Fixed Costs 11 Manager Salaries $9,283.00 12 Utilities $1,879.00 13 Equipment Depreciation $3,705.00 14 Insurance $1,013.00 15 Total Fixed Costs $15,880.00 16 Taxes $99,311.24 17 Net Income $255,371.76 18 19 20 21 22 23 24 Excel_BU05_PS2_ReservationData ReservationData RatesScenario GrandvillaAnalysis Documentation + B D E F G H I K L M N o 0 R S 1 Q 2 3 Revenue Painted Paradise Resort and Spa Weekly Break-even Analysis for the Grand Villa Suite Booked Expenses Revenue Quantity sold Price per Night $1,293.00 Gross Revenue $1,293.00 Net Income 4 1 5 2 4 6 7 Expenses 8 Fixed Costs 6 8 9 10 12 Manager Salaries Utilities Equipment Depreciation Insurance $3,421.00 $580.00 $988.00 $363.00 $5,352.00 14 16 18 20 Marketing Expense 16% 10 11 12 13 Total Fixed Costs 14 Variable Costs 15 16 Total Expenses 17 Net Income 18 19 20 21 22 23 $206.88 $5,558.88 $4,265.88 24 Excel_BUO5_PS2_Reservation Data Reservation Data RatesScenario Grand VillaAnalysis Documentation B F G I 1 J K L M. N o o R 1 Painted Paradise Resort and Spa 2 Scenario Analysis for Monthly Hotel Room Pricing 3 Revenue Qty Price Extended Price 4. Double (1 king bed) 245 $318.00 $77,910.00 5 Triple (1 king bed, foldout couch) 167 $414.00 $69,138.00 6 Quad (2 king beds) 210 $541.00 $113,610.00 7 Grand Villa Suite 85 $1,293.00 $109,905.00 8 Gross Revenue $370,563.00 9 Expenses 10 Fixed Costs 11 Manager Salaries $9,283.00 12 Utilities $1,879.00 13 Equipment Depreciation $3,705.00 14 Insurance $1,013.00 15 Total Fixed Costs $15,880.00 16 Taxes $99,311.24 17 Net Income $255,371.76 18 19 20 21 22 23 24 Excel_BU05_PS2_ReservationData ReservationData RatesScenario GrandvillaAnalysis Documentation +