Financial model building is an art. The only way to improve as a financial modeller is to construct a range of financial models across multiple sectors. Let’s test out a model for an investment that is accessible to the majority of people: an investment property.
Before constructing a financial model, we should determine what drives the business we are investigating. The response will significantly affect how we create the model.
Who Will Employ It?
Who will use this model, and what will they do with it? A corporation may have a new product for which the ideal price must be determined. Or, an investor may choose to draw out a project to determine the expected return on investment.
Depending on these circumstances, the model’s calculated outcome may be vastly different. If you do not know precisely what decision the user of your model must make, you may have to start over multiple times until you find a strategy that leverages the correct inputs to determine the desired outputs.
Moving on to Real Estate
In this example, we wish to determine the financial return we may anticipate from an investment property, given the property’s characteristics. This data would contain variables such as the purchase price, the rate of appreciation, the rental price, the available financing terms for the property, etc.
Our return on this investment will be determined by two key factors: rental revenue and property value appreciation. Therefore, we should begin with rental revenue and appreciation projections for the property in question.
Once we have completed this component of the model, we can utilise the computed information to determine how we will finance the home purchase and what financial expenses we can anticipate as a result.
Next, we will discuss property management costs. In order to calculate property taxes, we will need to use the estimated property value, hence it is crucial that we construct the model in the correct sequence.
With these assumptions in place, we can assemble the income statement and balance sheet. As we arrange these, we may discover items that we haven’t yet computed, requiring us to go back and add them to the right locations.
Lastly, we can utilise these financial statements to predict the investor’s cash flow and determine our return on investment.
Establishing the Model
We should also consider how we will organise things to maintain a tidy workspace. One of the most effective ways to arrange financial models in Excel is to segregate elements of the model onto distinct spreadsheets.
We can give each tab a descriptive name based on the information it contains. Thus, other model users can comprehend where data is calculated and how it moves within the model.
Let’s use four tabs in our investment property model: property, finance, expenses, and financials. On the tabs for property, financing, and expenses, we will enter assumptions and create estimates for our model. The financials tab will serve as our results page, displaying the output of our model in an easily digestible format.
Predicting Earnings
Let’s begin with the property tab by renaming the tab “Property” and adding this title to worksheet cell A1. By handling some of these formatting issues on the front end, it will be easier to maintain a clean model.
Next, let’s build up our assumptions box. A few rows beneath the title, type “Assumptions” and list the following inputs vertically:
Purchase Cost
First-Monthly Rent
Vacancy Ratio
Annual Appreciation
Annual Increase in Rent
Broker Fee
Investment Time Frame
In the cells to the right of each input label, we will add a realistic placeholder for each value to create input fields. Each of these values will be formatted to be blue in colour. This is a standard modelling convention for indicating input values. This layout will facilitate our and others’ comprehension of the model’s flow. Here are some initial corresponding values:
$250,000.00
$1,550.00
95.00%
3.50%
1.00%
6.00%
4 years
The purchase price will be the amount we anticipate paying for a certain property. Initial monthly rent will be the price at which we anticipate renting the property. The occupancy rate will indicate how well we are able to rent out the property (95% occupancy indicates that the property will only be vacant for around 18 days per year).
Annual appreciation will establish the annual rate of increase (or decline) in the value of our property. Annual rent rise will determine how much the rent will be increased annually. The broker fee indicates the proportion of the sale price of the property that will be paid to a broker when the property is sold.
The investment period is the length of time we will keep the property before selling it. Now that we have a solid set of property assumptions, we can begin calculating based on them.
A Note on Periods of Time
There are numerous approaches to forecasting future values. You may make monthly, quarterly, or annual financial projections, or some combination of the three. For the majority of models, monthly financial projections should be included for the first couple of years.
By doing so, you enable model users to observe some of the cyclical nature of the firm (if there is any). Additionally, it enables you to identify flaws with the business model that may not appear in annual estimates (such as cash balance deficiencies). After the initial couple of years, annual financial projections can be made.
For our purposes, annual estimates will reduce the model’s complexity. A consequence of this decision is that when we start amortising mortgages later, we will suffer greater interest expense than if we made monthly principal payments (which is what happens in reality).
Consider whether to use actual date heads for your projection columns (12/31/2010, 12/31/2011, etc.). This is an additional modelling decision that you may wish to make. This can aid in the performance of more complex tasks in the future, but for our purposes we will just use 1, 2, 3, etc. to measure our years. In Excel, we may modify the format of these numbers to make them easier to read:
Year 1 Year 2 Year 3 Year 4…
These figures should be inserted below our assumptions box, beginning with the first year in at least column B. We will maintain these levels into the tenth year. Since projections made beyond ten years lack reliability, the majority of financial models do not extend beyond ten years. More details judi slot online
