Sign in or start a free trial to avail of this feature.
3. Revenue Projections for TrackerTime
TrackerTime sells two products, one to consumers and one to businesses. In this lesson, we learn how to project revenue for both products, over a 5year timeframe.
To explore more Kubicle data literacy subjects, please refer to our full library.

Lesson Goal (00:00)
The goal of this lesson is to create projections of future revenue for TrackerTime.

Structuring the Excel Model (00:04)
In a Leveraged Buyout model, we would start by outlining the assumptions surrounding the transaction. However, when dealing with a startup, the investment terms depend on the structure of the investment, which is not finalized until later in the process. As a result, our model begins with projections for the three main financial statements, then includes investment terms afterwards.

Understanding TrackerTime’s Products (00:36)
When a company has two separate products, it’s best to build separate customer and revenue projections for each product. For example, TrackerTime has a B2C (Business to Consumer) product and a B2B (Business to Business) product. In our model, we project the revenue from each product separately.
For each product, we have historic information on the number of customers each year, and the total revenue per year. We have separate information for the B2C product and the B2B product. We’ll use this information to project future revenue for each product.

Projecting Future Customer Numbers (01:09)
The first step is to create projections for future customer numbers. We can calculate historic growth rates in customer numbers, and use these as a basis to project future growth rates. The growth rate in customer numbers for the current year is calculated as the number of customers gained since the previous year divided by the number of customers in the previous year.
Once we calculate the customer growth rates for historic years, we can project customer growth rates for future years. We determine the projected growth rates based on the historic growth rates, and the business knowledge of the CEO. Startups often see very high growth rates in the early years, and these growth rates usually decline over time as the company becomes more established, so it generally makes sense to project that growth rates will decline over time.
After calculating growth rates, we can calculate the projected number of customers for each year. The number of customers for a year is the growth rate for that year multiplied by the number of customers from the previous year. In our model, we complete all these calculations separately for the B2C and B2B products.

Projecting Revenue per Customer (03:59)
The next step in our revenue projections is to determine the average revenue per customer. We can determine the historic revenue per customer by dividing the total revenue by the number of customers. By inspection we can then determine if average revenue per customer is growing over time or not.
Next we can create future projections of revenue per customer. Again, we do this by combining the historic values with the business knowledge of the CEO. For example, scheduled price increases in the coming years would increase revenue per customer. Again, we complete these calculations separately for the B2C and B2B product.

Projecting Total Revenue (06:36)
The final step in projecting future revenue is to multiply the projected number of customers for each future year by the projected revenue per customer for each year. As before, we calculate the projected revenue separately for B2B and B2C.
Unlike the previous models we've built, this model will start with revenue projections rather than the initial investment terms. The reason for this is quite simple. The final structure of the investment that we will cover later in the course will determine how we designed the investment terms section. So, for now I'm going to simply build out my three statement financial projections and include the investment terms later once negotiations between Ventura and TrackerTime have begun. We're going to start in this lesson with revenue projections. TrackerTime has two different products, a B2C product which is sold to consumers and a B2B product which is sold to other businesses or companies. In each case, we have the number of customers and the amount of revenue for each product. Our projections are going to be based on the growth rate for the number of customers in future years and the average annual revenue per customer for both products. Let's start by calculating the historical customer growth rates and the revenue per customer growth rates to benchmark our future projections. Let's start with the B2C customer growth rate. As we can see in 2013, the company had barely any B2C customers but that started to grow rapidly in 2014 through to 2016. To calculate the growth rate, I'll simply take this year's figure minus last year's figure and divide by last year's figure and I'll copy across for the next two years.
As the company gets bigger, I expect the growth rate to reduce over time and we can see that this has happened already. In 2017 I'm going to assume that the growth rate drops to 150% and each year it will drop by another 25% all the way down to a 50% growth rate in 2021.
This will tend to happen for almost all startups as the company gets bigger, it gets harder and harder to reach the same growth rate as the previous year. To fill in the actual number of B2C customers, I'll simply take the previous year and multiply it by one plus the growth rate and I'll copy across.
What this shows you is that in startups high growth rates can show huge increases in the number of customers and hence revenue in a relatively short period of time. Here I'm assuming that a business that has 2,430 customers has over 71,000 customers in fives years' times.
Obviously this is quite an aggressive assumption but based on the previous growth rates already hit by the company. Now let's move on to B2B customers.
Again, I'll use the same formula for growth rate and I can actually copy these formulas, alt E, S, F.
Having spoken to the CEO of TrackerTime, she tells me that B2B growth is expected to be 40% for 2017 and 2018.
However, in 2019 she expects the growth to drop to 30% and staying constant at this level until 2021.
Again, I'll calculate the actual B2B customer numbers by taking the previous year and multiplying by one plus the growth rate.
With the number of customers now projected, I now need to project the amount of annual revenue per customer for both B2C and B2B.
The annual revenue per customer can be calculated by taking the total revenue and dividing by the number of customers.
So, that this number makes a little more sense, I'm going to multiply by a million because the units of one million are too high for average annual revenue per customer.
And I'll copy across for the remaining cells.
As you can see, there's no growth rate in B2C pricing since the product was launched in 2013. When we speak to TrackerTime's CEO she tells me she plans to increase prices by 33% in 2017 and then another increase of 25% in 2019.
Outside of this there will be no price increases.
And again, to forecast the average annual revenue per customer, I'll simply take the previous value and multiply by one plus the growth rate.
Next up is the B2B average annual revenue per customer and again I'll take the revenue, multiply by a million to make sure the units are readable and then divide by the number of customers and I'll copy across for the remaining cells.
As you can see, the average annual revenue per customer is growing over time. When we speak to TrackerTime about this, the CEO explains that she can charge more money to larger companies and in actual fact she expects the average annual revenue per customer to continue to grow over time as TrackerTime begins to target bigger and bigger customers. The growth rate is forecasted to be 15% in 2017 and 2018 and then 10% each year after this. Again, we can forecast out this number by multiplying the previous year by one plus the growth rate and I'll copy across for the remaining cells.
With the average annual revenue per customer and the number of customers now forecasted, we can calculate revenue. So, for B2C it's going to be this number multiplied by the average annual price per customer and obviously I'll need to divide again by a million so that by units make sense and I'll copy across for the remaining cells.
For B2B I'll take the number of B2B customers multiply by the average annual revenue per customer and divide by a million.
And again copy across for the remaining cells.
Off camera I'm going to change the format to show one decimal place. In the next lesson we'll move onto our operating costs such as cost of sales, marketing, R and D and general and administrative.
Sign in or start a free trial to avail of this feature.