Reference no: EM131232223

Analysis of Hotel Sales Discounts

Project Description:

The Painted Paradise Resort and Spa has 700 rooms. The Paradise Resort and Spa gives discounts to guests who meet certain conditions. First, the Paradise Club discount, a 9% discount applicable to all products and services at the resort, is offered to guests who opt into the resort's rewards program. Another 10% discount, good on hotel rooms and services, is offered to groups who book a large block of rooms. Finally, at the discretion of the resort management, rooms may be charged at a complimentary rate. The resort's management would like to get an idea of how much revenue is lost to discounts on Friday and Saturday nights, the busiest nights of the week.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Instructions

1 Start Excel. Open the downloaded file named e01_grader_h1_HotelDisc.xlsx. Save the file with the name e01_grader_h1_HotelDisc_YourOffice.

2 In the Documentation worksheet, enter 12/31/2015 in cells A6, A8, and A20.

Type your Your Office into cells B6, B8, and C20.
Type FormattingHotelDiscountsworksheet into cell C6.
Type HotelDiscounts into cell B20.
Type Analysisof weekend hotel room discounts by room type into cell D20.

3 In the Hotel Discounts worksheet, set the height of row 1 to 40. Insert the downloaded e01_grader_h1_PaintedPar.jpg image file. Turn on Snap to Grid. Position the image in row 1 and resize the image to span columns C:F and to be the height of row 1.

4 Apply the Heading 4 cell style to cells A2, A12, A24, and A41. Apply the Heading 3 cell style to cells A3:G3 and A7:G7.

5 Change the font color in cells A4:A5 and A8:A10 to Dark Blue, Text 2 and apply Bold. Format the range B8:G10 as Percent Style with one decimal place.

6 Apply Table Styles as follows:

Format cells A25:H31 as Table Style Medium 2 with My table has headers. Select the First Column and Last Column Table Style Options. Then Convert to Range the table you just formatted.

7 Apply number formatting as follows:

Format cells B26:G29, B34:G37, and B43:G45 as Number as follows: (1,234.10). Negative numbers should not be displayed in red, 2 decimal places and Use 1000 Separator (,) should be checked.

Format cells B30:H31, H26:H29, B38:H39, H34:H37, B46:H46, and H43:H45 as Accounting.

8 In cell B26 enter a formula to multiply the total number of One Double rooms rented on Friday by the Standard Rate for a One Double room. To enter the formula, on the HOME tab, in the Editing group, click AutoSum, and then select the cell range B14:B17. Edit the formula to multiply the SUM() function by the Standard Rate for a One Double room.

9 Calculate the discount total for the Paradise Club for One Double rooms for Friday night in cell B27. To enter the formula, multiply the number of Paradise Club One Double rooms rented on Friday by the Standard Rate for a One Double room by the Paradise Club discount. The result of the formula will be a negative number to reflect the discount total.

10 Calculate the Group discount total in cell B28. To do so, multiply the Standard Rate for a One Double room by the number of Group Discount rooms sold for Friday night by the Group Discount.

11 Calculate the Comp discount total in cell B29. Multiply the Standard Rate for a One Double room by the number of Comp Discount rooms sold for Friday night by the Comp Discount.

12 Select cell range B26:B29. Click and hold the fill handle and then expand the selection to include B26:G29.

13 Calculate the Gross Sales for One Double rooms for Saturday night in cell B34. Multiply the Standard Rate for each room type by the number of rooms rented of that type for Saturday night.

14 Calculate the Paradise Club discount total for One Double rooms for Saturday night in cell B35. To do so, multiply the number of Paradise Club rooms sold Saturday night by the Standard Rate by the Paradise Club Discount.

15 Calculate the group discount total for One Double rooms for Saturday night in cell B36. To do so, multiply the Standard Rate by the number of Group One Double rooms sold Saturday night by the Group Discount.

16 Calculate the Comp discount total for One Double rooms for Saturday night in cell B37. To do so, multiply the Standard Rate by the number of Comp One Double rooms sold Saturday night by the Comp Discount.

17 Select cell range B34:B37. Click and hold the fill handle and then expand the selection to include B34:G37.

Calculate the Paradise Club discounts for Friday and Saturday night for One Double rooms in cell B43. Add the Friday Paradise Club discounts to the Saturday Paradise Club discounts using appropriate cells references so the formula can be copied. Use the fill handle to copy cell B43 to cell range B44:B45, but do not fill formatting. Copy cell range B43:B45 to cell range C43:G45.

18 Calculate the Net Sales for each room type on each night, the total sales and discounts for all room types on each night, the Total Discounts for each room type for both nights, and the Total of each discount type for the weekend. Do this by using AutoSum Sum to calculate totals in cell ranges B30:G30, H26:H30, B38:G38, H34:H38, B46:G46 and H43:H46.

In cell B31, calculate Total Discounts for Friday for One Double rooms by subtracting Gross Sales from Net Sales. Copy the formula to cell range C31:H31.

19 Copy the formulas in cell range B31:H31 to cell range B39:H39.

20 Apply Bold to cell ranges B31:H31, B39:H39, and B46:G46. Apply AutoFit Column Width for columns A:H. Use the resizing handles to reposition the right side of the logo in row 1 between columns F and G.

21 On the PAGE LAYOUT tab, set Orientation to Landscape and set Width to 1 page.

22 In the Documentation worksheet, type Completed formatting and calculations in the Hotel Discounts worksheet in cell C8.

23 Save the workbook and then exit Excel. Submit the workbook as directed.

Attachment:- alqadheeb_e01_grader_h1_hoteldisc_0.xlsx