I need assistance in calculating some financial ratios so that i can perform further analysis. I have attached 3 "pages" the first two are financial statements/data that was provided as part of the case study.(disregard the variance column) the third page I created in the spreadsheet. I listed the ratios I wanted to find and the formulas I think are how your find them. I did those that I could and put answers in. Those that I couldn't I have left blank. Could someone please check my work to see if it is correct (if it is wrong, please correct/explain how to do it and help me with the blank ones?
Budget For 12 months ending 12/31/2021: Daily Rooms Annual Available Rooms Annual rooms Sold Actual 160 58,400 160 58,400 43,800 44,968 Occupancy % 77.0% 75.0% ADR RevPAR $185.00 $142.45 $170.00 $127.50 Variance Between Actual and Budgeted 8,319,080 2,473,240 359,744 179,872 11,331,936 7,446,000 0.12 2,190,000 0.13 306,600 0.17 162,064 0.11 10,104,664|0.12 2.271,104 1,895,741 196,421 4,363,267 6,968,669 2,114,964 (0.07) 1,470,804(0.29) 159,432 (0.23) 3,745,200(0.17) 6,359,464 0.10 Operating Revenue Rooms Food and beverage Other Operated Departments Miscellaneous Income Total Operating Revenue Departmental Expenses Rooms Food and beverage Other Operated Departments Total Departmental Expenses Total Departmental Profit Undistributed Operating Expenses Administrative & General Information and Telecommunication Systems Sales and Marketing Property Operation and Maintenance Utilities Total Undistributed Expenses Gross Operating Profit Management Fees Income Before Non-Operating Income and Expenses Non-Operating Income and Expenses Income Rent Property and Other Taxes Insurance Other Total Non-Operating Income and Expenses EBITDA Replacement Reserve EBITDA Less Replacement Reserve 963,786 178,483 1,082,772 416,454 368,860 3,010,354 3,958,315 428,347 3,529,968 826,663 (0.17) 142,879 (0.25) 949,135 (0.14) 295,962 (0.41) 459,257 0.20 2,673,897|(0.13) 3,685,567 0.07 306,174 (0.40) 3,379,392 0.04 13,518 469,788 453,698 151,317 128,720 1,217,040 2,312,928 594,927 1,718,001 (4,252) 4.18 382,644 (0.23) 373,953 (0.21) 127,548 (0.19) 127,548 (0.01) 1,007,441(0.21) 2,371,951 0.02 511,135 (0.16) 1,860,815 0.08 As of December 31: 2020 2021 Variance (S) Current Assets Cash 962,794 907,778 (55,016) Marketable Securities Notes Receivable Inventories Accounts Receivable Prepaid Expenses Total Current Assets 315,000 64,187 1,733,283 4,557,227 486,648 8,119,139 1,605,085 309,000 (6,000) 56,240|(7,947) 1.700,268|(33,015) 4,383,618 (173,609) 509,821 23,173 7,866,725 (252,414) 1,650,944 45,859 Investments Property and Equipment Land 8,023.286 8,023,2860 Building Furniture, Fixtures and Equipment Subtotal Less: Accumulated Depreciation Net Property and Equipment Other Assets 15,404,710 3,851,177 27,279,173 (6,418,629) 20,860,544 1,508,379 32,093,146 14,084,306|(1,320,404) 3,924,533 73.356 26,032,125(1.247,048) (6,204,675) 213,954 19,827,451(1,033,093) 1,465,282 (43,097) 30,810,402|(1,282,744) Total Assets Current Liabilities Accounts Payable Notes Payable Accrued Expenses Income Tax Payable Advance Deposits Total Current Liabilities Long-Term Debt, Net of Current Maturities Total Liabilities Owners Equity Total Liabilities and Owner's Equity 3,851,177 2,909,058 1,925,589 64,187 1,523,666 10,273,676 11,877,010 22,150,686 9,942,459 32,093,145 3,851,1770 2,992,173 83,115 1,852,233|(73,356) 56,851|(7,336) 1,364,043 (159,623) 10,116,478|(157,198) 10,858,980|(1,018,030) 20,975,459|(1,175,227) 9,834,943 (107,516) 30,810,402|(1,282,743) 2020 2021 Current Ratio = current assets/current liabilities Current Ratio 0.79 0.778 Asset Turnover Ratio - Total Rev/ Avg. Total Assets Asset Turnover Ratio 0.353 0.328 Profit Margin = net income/total Rev. Profit Margin Return on Assets = Profit Margin X Asset Turnover ROA Solvency Ratio - Total Assets/Total Liabilities Solvency Ratio 1.449 1.469 Debt Equity Ratio = Total liabilities/Total owners equity Debt Equity Ratio 2.228 2.133 Gross Operating Profit Gross Revenue - Gross Expenses GOP 3,958,315 3,685,567 GOP Margin = GOP/Total income X 100 GOP Margin 34.931 36.474 GOPPAR=GOP/total #rooms GOPPAR 67.779 63.109 IRR = Internal Rate of Return NPV - Net Present Value
