Create me a blance sheet in excel using the info below

Topic $\_=$ financial modeling using excel

Answer the following by creating an excel file with 5 sheets named. income statement Capex and dep Balance sheet Cash flow statement NPV and irr Hotel Revenue Assumptions Room revenues The ABC Hotel shall consist of 216 keys (no of rooms) The average room rate per night has been assumed at RO 44 during the first year. An increase Of 5% per annum has been assumed from the second year onwards The room occupancy level has been estimated at 45% during the first year. From the second year, an increase of 5% every year has been assumed till Year 6 (guidance note: occupancy level is the number of rooms used out of the total no of rooms available in the hotel). The room occupancy level has been capped at a maximum of 70%. Food and beverage revenues Food and beverage contribution is estimated to be 14% of room revenue Other revenues Revenue from communication, laundry, etc. has been assumed at 5% Of room revenues. Hotel Expenses Assumptions DIRECT expenses Rooms Expenses 8% of room revenues Food and beverage 35% of food and beverage revenues Other Expenses 55% of other revenues Indirect expenses The staff number is estimated to be 87 in year 1 , growing by 2.2% on yearly basis to 10B in year 10, and the average cost per staff per month is estimated at RO 528, with pay rise Of 3% every year. The Other operating cost is estimated as a percentage of total revenue as follows: property operation & maintenance (2.5%) utilities (2%) property insurance (1 %) hotel operator (8.5%) Tax has been calculated at the rate of 12% on the profits after the standard deduction Of RO 30,000. Other Assumptions Depreciation: Buildings: Straight-line depreciation over 50 years Furniture and Equipment: Straight-line depreciation over 7 year Working Capital: -Accounts Receivable Days: 30 days Inventory Days: 15 days o Accounts Payable Days: 45 days Terminal Growth Rate: 2.5% Hotel Funding Assumptions Include all formulas The construction takes 2 years to complete. The set-up cost of the hotel is as follows: Component Land 680,000 Building Furniture & Equipment 2,800,000 Pre-operating expenses (this is for the initial working capital) 360,000 Total = omr The capital structure of the hotel company is as follows: Equity 60% Debt The debt is 10 years payable yearly, and the effective cost Of financing (interest charge) is 5.6% The weighted average cost of capital (WACC) is 11%