Please write down the formula you use to calculate in excel. Thanks:)
P rpose : To incorporate the concepts related to the Income Statement and Ratio Analysis. Use the statement from Part 1 (Exhibit 1 below) and calculate the ratios listed below. 0 Calculate each ratio for both years. 0 Show the formula used to obtain the ratio. o Use one or two sentences to explain what information the ratio provides (what does it mean). Make sure to use complete sentences in your response. Hint: You may have already calculated some of these in your vertical analysis. o GOP percentage 0 The Cougar Hotel Mix of Sales o Total Occupancy Percentage o Paid Occupancy Percentage 0 Average Daily Rate (ADR) 0 Revenue Per Available Room (RevPAR) o Sales and Marketing Expense Per Sold Room Exhibit No. 1 The Cougar Hotel Summary Operating Statement For the Years Ending December 31, 20X7 and 20X8 20X7 20X8 Changes o $ % $ % Operating Revenue Rooms 9,697,631 10,344,459 Food and Beverage 3,531,758 3,690,474 Other Operating Department 627,746 667,330 Miscellaneous Income 258,354 273,747 Total Operating Revenue Departmental Expenses Rooms 2,459,313 2,600,029 Food and Beverage 2,484,599 2,569,361 Other Operating Departments 361,696 379,384 Total Departmental Expenses Total Departmental Profit Undistributed Operating Expenses Administrative and General 1,044,546 1,108,225 Information and Telecommunications Systems 211,732 224,640 Sales and Marketing 1,171,586 1,243,009 Property Operating and Maintenance 578,735 614,016 Utilities 423,465 449,280 Total Undistributed Expenses Gross Operating ProfitManagement Fees 423,465 449,280 Income Before Non-Operating Income and Expenses Non-Operating Income and Expenses Income (18,140) (9,087) Rent 315,522 325,430 Property and Other Taxes 488,125 520,233 Insurance 117,634 115,860 Other 154,463 200,483 Total Non-Operating Income and Expenses Earnings Before Interest, Taxes, Depreciation and Amortization Replacement Reserve 846,929 898,561 EBITDA less Replacement Reserve STATISTICS Rooms Sold 54,969 56,794 Rooms Occupied 56,319 58,444 Rooms in Hotel 200 200