I need help in finacial modeling

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

The construction takes $2$ years to complete. The set$-$up cost of the hotel is as follows:

Component

Land $680,000$

Building $3,000,000$

Furniture & Equipment $2,800,000$

Pre$-$operating expenses $($this is for the initial working capital$)360,000$

Total $=6,840,000$omr

The capital structure of the hotel company is as follows:

Equity $60\%$

Debt $40\%$

The debt is $10$ years payable yearly, and the effective cost of financing $($interest charge$)$ is $5\mathrm{.}6\%$

The weighted average cost of capital $($WACC$)$ is $11\%$

Please come up with the financial model for $10$ years with the following

Income Statement$/$Balance Sheet$/$Cashflow

Revenue Growth

Gross Profit Margin

Net Profit Margin

Terminal Value

NPV

IRR

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\mathrm{.}2\%$ on yearly basis to $108$ 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\mathrm{.}5\%)$

utilities $(2\%)$

property insurance $(1\%)$

hotel operator $(8\mathrm{.}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\mathrm{.}5\%$

Hotel Funding Assumptions