Using your Forecast from the previous assignment please enter formulas to fill the following cells:
• Transient Rooms Revenue = Transient Occupied Rooms x Average Transient Rate.
• Group Rooms Revenue = Group Occupied Rooms x Average Group Rate.
• Total Occupied Rooms = Transient Occupied Rooms + Group Occupied Rooms.
• Occupancy % (this is a 150 rooms hotel) = Total Occupied rooms / 150.
• Food & Beverage Revenue = Food & Beverage Covers x Average Cover.
• Labor = Occupied Rooms x 1.44 (hotel’s average productivity) x $15 (average hourly wage).
• Taxes & Benefits = Labor x 43%.
• Rent = $50,000 / days in the month.
• Insert a row after Food & Beverage Revenue that calculates Total Revenue (Transient + Group + Food & Beverage).
• Insert a row after rent that calculates total expenses (Labor + Taxes & Benefits + Rent).
• Add a row at the end that calculates profit (Total Revenue – Total Expenses).
• Add a total column at the end after the 31st. Watch out for cells AG6, 9, & 14 They should be averages not sums! Cell AG12 should have the following formula =+AG11/(150*31).
Once you have entered all the formulas above please apply these additional format changes:
• Format all cells to match their number type. i.e. currency, percentage etc. (all cells should have 0 decimal places)
• The whole worksheet should be Arial font size 12 (except the title cell).
• Bold cells A4 & A17.
• Add an underline to rows 16 & 21.
• Add a double underline to row 22.
Formulas must use cell references, DO NOT add actual numbers!! You should not need a calculator to complete this assignment, the whole point is to make EXCEL do the work for you 🙂
Save your file as “Formulasfirstlastname”
Grading for this assignment will be:
2 points for each of the following:
• Transient Rooms Revenue formula is correct.
• Group Rooms Revenue formula is correct.
• Total Occupied Rooms formula is correct.
• Occupancy % formula is correct.
• Food & Beverage Revenue is correct.
• Labor formula is correct.
• Taxes & Benefits formula is correct.
• Rent formula is correct.
• Total Revenue formula is correct.
• Total Expenses formula is correct.
• Profit formula is correct.
• Total column is set up correctly.
• Cells are formatted correctly
• The whole worksheet is correct font and size.
• Cells A4 & A17 Bolded.
• Underline added to rows 16 & 21
• Double underline added to row 22.
6 points for catching the formulas pitfalls
