Building financial models is an art. The only method to improve your craft is to develop a variety of financial models across a number of industries. Let's consider using a model to have an investment that isn't past the reach on most individuals - an investment property.
Before we jump into creating a business model, we ought to ask ourselves what drives the business that we are exploring. The answer will have significant implications for how we construct the model.
Who'll Utilize it?
Who will be using this model and what will they be using it for? A company could have a new product that they need to calculate an optimal price. Or perhaps an investor might want to map out a project to see what kind of investment return they might expect.
Depending on these scenarios, the end result of the items the model will calculate could be very different. Unless you know exactly what decision the consumer of your model needs to make, you might find yourself beginning again many times until you find an approach that uses the best inputs to find the appropriate outputs.
On to Real Estate
In our scenario, you want to discover what kind of financial return don't be surprised from an investment property given certain details about the investment. This information would come with variables like the cost, rate of appreciation, the cost where we are able to rent it out, the financial lending terms available fore the property, etc.
Our return on this investment will be driven by two primary factors: our rental income and also the appreciation from the property value. Therefore, we ought to begin by forecasting rental income and also the appreciation from the property in consideration.
After we have built out that area of the model, we can make use of the information we have calculated to figure out how we will finance the purchase of the property and what financial expenses we can expect to incur consequently.
Next we tackle the property management expenses. We will need to use the home value that people forecasted in order to be able to calculate property taxes, so it's essential that we build the model inside a certain order.
Using these projections in position, we can start to patch together the income statement and the balance sheet. Once we put these in position, we might spot items that we haven't yet calculated so we may have to go back and add them in the spots.
Finally, we are able to begin using these financials to project the money flow towards the investor and calculate our return on investment.
Installing the Model
We ought to also feel about how we want to lay it so we keep our workspace clean. In Excel, among the best methods to organize financial models would be to separate certain parts of the model on several worksheets.
We can give each tab a name that describes the information contained in it. This way, other people that use the model can better understand where data is calculated in the model and just how it flows.
Within our investment property model, let's use four tabs: property, financing, expenses and financials. Property, financing and expenses would be the tabs on which we input assumption making projections for our model. The financials tab is going to be our results page where we will display the creation of our model in a way that's easily understood.
Let's start with the property tab by renaming the tab "Property" and adding this title in cell A1 from the worksheet. By taking proper care of a few of these formatting issuing around the front-end, we'll have an easier time maintaining your model clean.
Next, let's setup our assumptions box. Several rows below the title, type "Assumptions" and make a vertical listing of the next inputs:
Initial Monthly Rent
Annual Rent Increase
In the cells to the right of each input label, we'll setup a port field by adding a realistic placeholder for every value. We will format all these values to be blue colored. This is a common modeling convention to indicate that these are input values. This formatting will make it more convenient for us and others to know how the model flows. Here are a few corresponding values to begin with:
The purchase price will be the price we predict to cover a specific property. The first monthly rent will be the price that we predict to rent the property. The occupancy rate will measure how good we keep your property rented out (95% occupancy will mean that there are only about 18 days the property will go un-rented between tenants each year).
Annual appreciation determines the speed that the worth of our property increases (or decreases) each year. Annual rent increase determines how much we will boost the rent each year. The broker fee measures what percentage of the sale price from the property we'll have to pay an agent whenever we sell the home.
The investment period is how long we will contain the property for before we sell it. Now that there exists a good set of property assumptions down, we can start to make calculations based on these assumptions.
A Note promptly Periods
There are lots of ways to begin forecasting out values across time. You can project financials monthly, quarterly, annually or some mixture of the three. For many models, you should consider forecasting the financials monthly throughout the first couple years.
By doing so, you permit people that use the model to determine a few of the cyclicality from the business (when there is any). Additionally, it allows you to spot certain problems with the business model that won't show up in annual projections (such as cash balance deficiencies). After the first couple of years, after that you can forecast the financials yearly.
For our purposes, annual projections will cut down on the complexness of the model. One for reds effect of this choice is the fact that when we begin amortizing mortgages later, we will wind up incurring more interest expense than we'd if we were making monthly principal payments (that is what goes on in reality).
Another modeling choice you may want to consider is whether or not to make use of actual date headings for your projection columns (12/31/2010, 12/31/2011,...). Doing this can sort out performing more complicated function later, but again, for our purposes, we will simply employ 1, 2, 3, etc. to determine out our years. In Excel, we can have fun with the formatting of these numbers a bit to read:
Year 12 months Two year 3 Year 4...
These numbers ought to be entered below our assumptions box using the newbie from at least column B. We will carry these values out to year ten. Projections made beyond ten years don't have much credibility so most financial models don't exceed ten years.