Please illustrate a presentation based on the file that I've uploaded

profilejessezz
accreport.xlsx
Home>Business & Finance homework help>Accounting homework help>Please illustrate a presentation based on the file that I've uploaded

Sheet1

Description Main Reference Reference Monday Tuesday Wednesday Thursday Friday Saturday Sunday Total Remarks
Number of Days A 53 52 52 52 52 52 52
Number of Rooms B 100 100 100 100 100 100 100
Double Rooms C 70 70 70 70 70 70 70
Single Rooms D 30 30 30 30 30 30 30
Forcasted Occupancy For Double Rooms E 70% 70% 70% 70% 70% 70% 70%
Forcasted Occupancy For Single Rooms F 60% 60% 60% 60% 60% 60% 60%
Coffee Shop Seats G 50 50 50 50 50 50 50
Ratio of Breafast Taking Guests In Double Rooms H 20% 20% 20% 20% 20% 20% 20%
Ratio of Breafast Taking Guests In Single Rooms I 40% 40% 40% 40% 40% 40% 40%
Total Double Room Occupancy C * E 49 49 49 49 49 49 49
Total Single Room Occupancy D * F 18 18 18 18 18 18 18
Number of Breafast Taking Guests In Double Rooms H * (C *E) 9.8 9.8 9.8 9.8 9.8 9.8 9.8
Number of Breafast Taking Guests In Single Rooms I * (D * F) 7.2 7.2 7.2 7.2 7.2 7.2 7.2
Per Night Revenue From Double Room Occupancy Given In Question $100 $100 $100 $100 $100 $100 $100
Per Night Revenue From Single Room Occupancy Given In Question $80 $80 $80 $80 $80 $80 $80
Total Per Night Revenue From Double Room Occupancy 1 (C * E) * $100 $4,900 $4,900 $4,900 $4,900 $4,900 $4,900 $4,900 Room Revenue
Total Per Night Revenue From Sinlge Room Occupancy 2 (D * F) * $80 $1,440 $1,440 $1,440 $1,440 $1,440 $1,440 $1,440 Room Revenue
Per Day Breakfast Revenue From Double Room Guests (Coffee Shop) 3 H * (C *E) * $4.80 $47 $47 $47 $47 $47 $47 $47 Coffee Revenue
Per Day Breakfast Revenue From Single Room Guests (Coffee Shop) 4 I * (D * F) * $4.80 $35 $35 $35 $35 $35 $35 $35 Coffee Revenue
Grand Total of All Room Occupancy & Breakfast Revenues 5 (1+2+3+4) $6,422 $6,422 $6,422 $6,422 $6,422 $6,422 $6,422
Total Yearly Revenue (Room Occupancy & Breakfast) 6 A * (1+2+3+4) 340,345 333,923 333,923 333,923 333,923 333,923 333,923 2,343,884
Guests Turnover From Double Room (Lunch) 7 J 0.25 0.25 0.25 0.25 0.25 0.5 0.5 In question, it is given the average occupancy is 1.6. Therfore, each turnover from Lunch & Dinner has been apportioned
Guests Turnover From Single Room (Lunch) 8 K 1 1 1 1 1 0 1 In question, it is given the average occupancy is 1.6. Therfore, each turnover from Lunch & Dinner has been apportioned
Guests w.r.t Turnover of Double Room (Lunch) 9 (C * E) * J 12.25 12.25 12.25 12.25 12.25 24.5 24.5
Guest w.r.t Turnover of Single Room (Lunch) 10 (D * F) *K 18 18 18 18 18 0 18
Turnover Guests Per Day 11 9 + 10 30.25 30.25 30.25 30.25 30.25 24.5 42.5
Total Turnover Guest Days 12 A * 11 1603.25 1573 1573 1573 1573 1274 2210
Average Check Rate 13 Given In Question 6.00 6.00 6.00 6.00 6.00 6.50 7.00
Total Revenue (Lunch) 14 12 * 13 9,620 9,438 9,438 9,438 9,438 8,281 15,470 71,123
Guests Turnover From Double Room (Dinner) 15 L 0 0 0 0 0 0 0.5 In question, it is given the average occupancy is 1.6. Therfore, each turnover from Lunch & Dinner has been apportioned
Guests Turnover From Single Room (Dinner) 16 M 1 1 1 1 1 1 0.5 In question, it is given the average occupancy is 1.6. Therfore, each turnover from Lunch & Dinner has been apportioned
Guests w.r.t Turnover of Double Room (Dinner) 17 (C * E) * L 0 0 0 0 0 0 24.5
Guest w.r.t Turnover of Single Room (Dinner) 18 (D * F) *M 18 18 18 18 18 18 9
Turnover Guests Per Day 19 17 + 18 18 18 18 18 18 18 33.5
Total Turnover Guest Days 20 A * 19 954 936 936 936 936 936 1742
Average Check Rate 21 Given In Question 10.75 10.75 10.75 10.75 10.75 12.50 11.25
Total Revenue (Dinner) 22 20 * 21 10,256 10,062 10,062 10,062 10,062 11,700 19,598 81,801
Grand Total Revenue (Room Occupancy + Breakfast + Lunch + Dinner) 23 6 + 14 + 22 360,220 353,423 353,423 353,423 353,423 353,904 368,991 2,496,808
Food Cost @35% 24 {(3*A) +(4*A) + 14 + 22)} * 35% 8,470 8,310 8,310 8,310 8,310 8,478 13,759
Labor Cost @20% of Room Occupancy 25 {A * (1 + 2)} * 20% 67,204 65,936 65,936 65,936 65,936 65,936 65,936
Labor Cost @32% For Breakfast At Coffee Shop 26 {A * (3 + 4)} * 32% 1,384 1,358 1,358 1,358 1,358 1,358 1,358
Other Direct Expenses @10% of Room Occupancy 27 {A * (1 + 2)} * 10% 33,602 32,968 32,968 32,968 32,968 32,968 32,968
Other Direct Expenses @12% of Coffee Shop 28 {A * (3 + 4)} * 12% 519 509 509 509 509 509 509
Variable Undistributed Operating Expenses 29 23 * 10% 36,022 35,342 35,342 35,342 35,342 35,390 36,899
Total Expenses 30 24 + 25 + 26 + 27 + 28 + 29 147,201 144,423 144,423 144,423 144,423 144,640 151,429 1,020,963
Contribution Margin 31 23 - 30 213,019 209,000 209,000 209,000 209,000 209,264 217,562 1,475,844
Less: Fixed Expenses
Undistributed Operating Expenses 32 100,000
Property Tax 33 30,000
Depreciation 34 60,000
Interest Expense 35 50,000 Assumed that it is fixed as given in question
Operating Profit Before Tax 36 31 - (32 + 33 + 34 + 35) 1,235,844
Less: Tax @ 30% 37 370,753
Net Profit After Tax 38 36 - 37 865,091