Shopping Mall and Residential Apartment - A Financial Model and Valuation

This is a financial model for a composite facility including a Shopping Mall and Residential Apartments.

by Sumit Barua, CFA (ICFAI), PMP
Senior Project Management Advisor at Ministry of Citizen Services, Government of British Columbia, Victoria, Canada,Follow 11

2 reviews170 views|Start the discussion!|Bookmark download for free

Description
The multipurpose mall financial model presents the case of an investment and operations of a shopping mall and residential apartment. The model generates the financial statements, a summary, and a valuation. The financing options for the project include a standard long-term loan and equity funding from investors.

In the “project Info” tab, the user will enter the general information for the model such as project title, location, sponsor, timeline, and currency.


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 to be redirected to the relevant tab. The “Index” tab also indicates the other tabs where data are to be entered and tabs which 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 “index” tab with hyperlinks.


The “Area” tab has area input for the retail space and the Apartments. This tab has details on rental rate, 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 norm of any other city/municipality.


The “Project Cost” tab is a formula driven worksheet which calculates the components of the project cost based on area, construction rate picked from “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, rate of interest information from the “data’ tab.


The tab “IDC” calculates the interest during construction based on funds requirement as per 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 number of apartments, occupancy, 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 area of retail space, occupancy, 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.

This Best Practice includes
One Excel Sheet with multiple tabs.