Fo be Assignment Instructions 2 Import the table tblRoom Types from the e05Hotel Sales.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 8 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. 5 In cell F3, type Goal, and in cells F4 and F5, type 3000. In cell F6, type 8000. 6 6 9 7 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. 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. Close Assignment Instructions 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 11 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 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 Assignment Instructions 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. 12 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 4 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 A1 X fx CustID B C 1 CustID Name 2 1 Gordon, Emily 5590 JUMAR 3 2 Burns, Christopher 4 3 Watkins, Emma 5 4 Ford, Richard 6 5 Duncan, Noel 7 6 Fowler, Steven 8 7 Romero, Anthony 9 8 Moody, Michael Address 6940 COOK INLET 9314 NW LOOP 5626 CINDYLEE 3870 MADELYNNE 8624 TOPAZ 2993 DEBBIE 7259 REED City&State D Lake Havasu City CO Sahuarita ND Tempe PA Aguila IL Rio Rico NJ Salome VT Wenden IA Marana VA 10 9 Martinez, Ella 11 10 Conner, Christopher 5828 ONDOLA 3865 NORTH POINT Thatcher AZ Cottonwood HI 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Ready SalesForecast Type here to search E F G FullName Street Address City NewCustomers Scenarios Documentation A1 X fx 1 2 Painted Paradise Resort & Spa B C Painted Paradise Resort & Spa Room Reservation Scenarios 3 Revenue 4 Room Types Number Reserved Extended Price 56 Quad (2 king beds) $ 492.00 25 $ 12,300.00 Double (2 queen beds) $ 269.00 50 $ 13,450.00 7 Triple (2 queen beds) $ 349.00 75 $ 26,175.00 8 Gross Revenue $ 51,925.00 9 Expenses 10 Fixed Costs 11 12 13 14 Total Fixed Costs 15 16 Variable Costs Utilities $ 535.00 Staff Salaries $ 5,532.00 Insurance $ 489.00 6,556.00 Unit Cost 17 Cleaning and Maintainance S 75.00 18 Total Variable Costs S 11,250.00 19 20 Total Expenses 17,806.00 21 22 Net Income 23 24 25 $ 34,119.00 nd Ready SalesForecast NewCustomers Scenarios Documentation Type here to search G Cut Ascending Selection Copy Descending Advanced View Paste Filter Format Painter Remove Sort Toggle Filter Clipboard Sort & Filter Views All Access Objects Search... Tables tblChargeDetails tblEmployees tblGuests tblReservations tblRoomCharges tblRoomTypes < < 2 New Tote ABC Save Spe Refresh All Delete Mor Records