3. Revenue Projections
Generating revenue projections is typically the first Excel task we complete when building a valuation model. This part of the model is where market knowledge is most important.
Components of revenue
- Revenue projections are typically created by multiplying existing revenue by a percentage change
- The components of our revenue model will be rental income, vacancy rate and collection losses
Creating revenue projections in Excel
- The current annual rent will be the basis for all our revenue model assumptions
- Rental price changes, vacancy rates and collection losses, are all based on % changes
- This is a very common approach for modelling revenue projections
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + C: Copy selected item(s)
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
In this lesson, we're going to focus on building the revenue projections in our model.
Our current diagram of the model shows the six factors that will influence our annual cash flows.
Let's focus in on revenue and ask ourselves what variables will determine our revenue projections in the future.
When making projections for an asset's revenues, we often take the current revenue, in this case rental income, and calculate future changes in revenue typically in the form of a percentage change.
So in our model, one input will be the current rental income and another will be the future growth and rental income based on a percentage figure.
Another factor that will affect revenue will be the vacancy rate.
Although our apartment may be occupied for 12 months of the year, this is not guaranteed.
And so it's prudent to allow a couple of weeks every year, where no tenant is in place.
This often happens when a lease comes to an end and a tenant moves out.
If we're being very conservative, we might also want to include collection losses which happen when a tenant doesn't pay its rent.
With these variables now identified, let's move to Excel and build our revenue projections.
Off camera, I've created a cover sheet that includes the client name, the problem statement, the start date and my name.
On the revenue projections sheet, I've included a legend on the top left hand corner, which indicates the various formats for the different types of data.
In the revenue projections panel, I've included a row that specifies the Year, a cell that indicates Current annual rent and three rows for our Rental price changes, Estimated vacancy rates and Collection losses.
To make our spreadsheet more readable, I'll now remove the grid lines by going to the View tab and unchecking this box.
Now let’s add some assumptions in our revenue projections.
The current annual rent for the apartment is $70,000, so I'll enter this number here.
After researching some similar apartments in the same building and surrounding area, we find out why Joe intuitively thinks that this apartment could be a good bargain.
The rent charged is actually a lot lower than the market value.
In fact, the current owners could be charging $80,000 a year.
This means Joe could increase rent by 14% next year and still be at the market rental price.
So for 2015, I'm going to increase rent by 14%.
From our research, we also realize that there's a shortage of supply in the sought after area.
So we'll assume increases in rent above inflation for the next four to five years, but gradually returning back to the level of inflation around 2021.
I'll enter these assumptions quickly off camera.
Now let's look at vacancy rates.
If we increase rent by 14% next year, it's likely that the current tenants will move out, and we'll need a few weeks to find a replacement.
I'll assume that finding a new tenant will take six weeks.
And so the vacancy rate will be equal to 6 divided by 52, which turns out to be 11.5%.
For the remaining years, I'll assume the vacancy rate is four weeks.
So this will be 4 divided by 52.
And I'll copy this across for the remaining cells.
Our last set of assumptions are collection losses.
These are typically quite low, particularly for more prestigious assets.
So I'm going to set my collection losses at 1% for each year.
And again I'll copy across for the remaining years.
With these assumptions now in place, we're ready to calculate our effective gross income on the asset, which I'll do in the next lesson.