Shopping Mall and Residential Apartment – A Financial Model and Valuation

So, a quick overview of the model, in the “Index” tab, the user can see the structure of the model and by clicking on any of the headlines, be redirected to the relevant tab. The “Index” tab also indicates the other tabs where data are to be entered and tabs that are formula driven. The data entry fields are “salmon” colored.

The “data” tab is the input for all the initial data, which are used in all other tabs for calculations and reports. The “data” tab has sub-tables for all the required inputs. The sub-tables of the “data” tab are summarized in the “index” tab with hyperlinks.

The “Area” tab has area input for the retail space and the Apartments. This tab has details on rental rates, occupancy, and expense of retail space and apartments.

The parking norms vary between cities across the World. The “Parking” tab has assumed parking norms for the City of Boise, Idaho, US. The user may use the norm of any other city/municipality.

The “Project Cost” tab is a formula-driven worksheet that calculates the components of the project cost based on area, and construction rate picked from the “Data” and “Area” tabs besides Interest During Construction and Preoperative expenses (picked from IDC and Pre tabs).

The “Debt” tab is a formula-driven tab calculating the debt repayment schedule based on leverage, debt term, and rate of interest information from the “data’ tab.

The tab “IDC” calculates the interest during construction based on funds requirements as per the project activity schedule. It is assumed in this model that first, the equity will be injected by the equity owners, and debt will be taken only once the entire equity has been used up. This tab dynamically calculates the period-wise equity and debt injection based on leverage and period-wise funds required for construction.

The “Apartment” tab calculates the period-wise revenue and expense from the Apartment part of the property based on the number of apartments, occupancy, and rental as input in the “Data” tab. The “Retail” tab calculates the period-wise revenue and expense from the retail part of the property based on the area of retail space, occupancy, and rental as input in the “Data” tab.

The “Income” tab is formula driven and calculates the profit and cash based on revenue, expenses, interest, and depreciation. The “BS” tab is formula driven and casts the balance sheet. The “Cash” tab calculates the cash flow.

The “Pre” tab is formula driven and calculates the pre-operative expenses, i.e., the expenses during the construction period, such as salary for the project team and other expenses (office rent, communication, travel).

The “Valuation” tab is formula driven and computes the enterprise value by two methods – 1) Discounted Cash Flow and 2) Economic Value-Added methods. The tab calculates the cost of equity based on risk-free return and market premium. The basic data is picked from the “Data” tab.

You must log in to submit a review.