Complete the income statement by finding revpar, room revenue, total revenue, total departmental expenses, total departmental profit, total undistributed operating expenses, gross operating profit, franchise fees, management fees, income before fixed charges, EBITDA, income before income tax, income tax, and net income for the period of 2014-2018. Also, conduct horizontal and vertical analysis for each year.

Can you also show all the calculations using the excel functions and clearly show your calculations for each answer. So i could see how you got the answers.

Number of rooms 196 2014-2015 2015-2016 2016-2017 2017-2018 2014 2015 2016 2017 2018 Difference % Change Difference % Change Difference % Change Difference % Change ADR un 300 $ 315 $ 340 $ 350 S 360 OCC 70%% 70% 70% 72% 72% RevPAR REVENUE Rooms Food & Beverage 1,980,450 $ 2,178,495 $ 2,396,345 $ 2,635,979 $ 2,899,577 Other Operated Departments 223,400 5 241,272 $ 260,574 $ 281,420 5 303,933 Miscellaneous Income 81,376 5 88,700 $ 97,570 $ 109,278 $ 119,113 TOTAL REVENUE DEPARTMENTAL EXPENSES Rooms 3,004,680 $ 3,154,914 $ 3,405,304 $ 3,605,616 $ 3,708,634 Food & Beverage 990,225 $ 1,089,248 $ 1,198,172 $ 1,317,989 $ 1,449,788 Other Operated Departments 89,360 S 96,509 $ 104,230 $ 112,568 $ 121,573 TOTAL DEPARTMENTAL EXPENSES TOTAL DEPARTMENTAL PROFITS UNDISTRIBUTED OPERATING EXPENSES Administrative & General 692,345 $ 731,321 $ 791,240 $ 842,190 $ 874,632 Marketing 519,259 $ 548,491 $ 593,430 $ 631,643 $ 655,974 Utility Costs 346,173 5 365,661 5 395,620 5 421,095 $ 437,316 Property Operation & Maintenance 259,629 5 274,246 $ 296,715 $ 315,821 $ 327,987 TOTAL UNDISTRIBUTED OPERATING EXPENSES GROSS OPERATING PROFIT Franchise Fees (Royalty) (5% of Total Revenue) Management Fees (6% of GOP) INCOME BEFORE FIXED CHARGES SELECTED FIXED CHARGES Property Taxes 346,173 5 365,661 5 395,620 $ 421,095 $ 437,316 Insurance 63,000 63,000 S 63,000 $ 63,000 $ 63,000 Reserve For Capital Replacement 228,139 S 240,453 $ 259,926 $ 276,157 5 285,798 EBITDA Depreciation 320,000 5 280,000 S 280,000 $ 280,000 $ 280,000 in un Interest Expense 105,000 $ 105,000 $ 68,000 $ 68,000 68,000 Income Before Income Tax Income Tax @ 30% Net IncomeHorizontal Analysis Vertical Analysis 2014-2015 2015-2016 2016-2017 2017-2018 2014 2015 2016 2017 2018 Difference % Change Difference %% Change Difference % Change Difference % Change