[Solved] Business Finance – Accounting Formulas Assignment

Assignment Prompt & Instructions

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