Sign in or start a free trial to avail of this feature.
11. Testing our Logic
Even if your balance sheet is balanced, it's critical to still test your model's logic, to ensure no mistakes have been made.
To explore more Kubicle data literacy subjects, please refer to our full library.
Lesson Goal (00:04)
The goal of this lesson is to adjust our financial model to incorporate customers who churn.
Testing the Logic of Our Model (00:09)
After creating a financial model, it’s important to check the underlying logic of the model with a subject matter expert, such as a company executive. The aim is to verify that the high-level thinking used to create the model is sensible. In our case, we want to check that the methods used to calculate total revenue and sales and marketing expenses are reasonable.
In our model, we identify a major issue by consulting with the CEO. Our model does not account for customer churn. Churn occurs where a customer cancels their subscription to a service and stops being a customer. Without incorporating churn in our model, we are assuming that all customers maintain their subscription indefinitely, which is not a realistic assumption.
Adding Churn to the Model (02:24)
The churn rate is generally forecast as a percentage of customers. In our model, we have separate projected churn rates for B2B and B2C customers each year. These projections are supplied by the company. These churn rates will change the balance of new and existing customers each year.
First, we need to adjust the number of existing customers that we are projecting for each future year. Previously, our model assumed that the number of existing customers in any year would equal the total number of customers from the previous year. We adjust this formula by multiplying by one minus the churn rate. As a result, the number of existing customers in each year reduces.
Next, we calculate the number of new customers each year. Previously, this was the difference between total customers in the current year and the previous year. We change this formula so that it is the difference between total customers in the current year and existing customers in the current year. The projected total number of customers does not change, so the number of new customers each year rises. We recalculate the number of new and existing customers for each year for both the B2B product and the B2C product.
The Impact of Adding Churn (04:47)
The overall projected number of customers for each year does not change as a result of adding churn. This is because projected growth rates have not changed. The number of existing customers each year is reduced, because not all customers from the previous year maintain their subscription. As a result, the number of new customers each year rises in order to maintain the same total number of customers.
The sales and marketing cost of acquiring a new customer is greater than the cost of retaining an existing one. As a result, incorporating churn into the model increases our overall sales and marketing costs. Consequently, our profits will reduce each year.
In the previous lesson, we wrapped up our three statement financial projection for Tracker Time. To ensure that we've captured the business logic correctly, we arrange a meeting with Tracker Time's CEO, Mary Hernandez, to talk through our model. When checking the logic of our model, we're not going through each formula to check for errors. Instead, we're going to take a look at the higher level thinking behind some of our more complex calculations, such as the way in which we calculate total revenue, operating costs, cash, et cetera. In this particular model, we wan to discuss with Mary the build up to total revenue and the calculation of sales and marketing costs because these two calculations contain the most complexity and the most assumptions about the business. Let's start off with total revenue, where we ask Mary to validate our approach on splitting revenue projections between the two products and our assumptions on the number of customers and revenue per customer.
Thankfully, after reviewing our calculations, Mary agrees with our logic broadly, although she explains that a new product may be released for enterprise or larger B2B customers in 2019 or 2020, and this will acquire an additional line item in the model. However, it's better to stay conservative and let's assume that this product is not released before the end of the projected period. Let's now move to sales and marketing costs. Here, we explain our logic of splitting customers into the new and existing categories and the different costs associated with each.
Immediately, Mary asks, where's the churn in your model? We are forecasting a B2C churn of 15% and a B2B churn of 6%, although that number should decrease over time. In case you haven't heard the term before, churn is when a customer decides to leave a product or a service.
Clearly, we have a problem in our model, as we don't have a churn assumption included. Instead, we move all new customers into existing customers the next year, so this automatically assumes that all new customers will always renew their subscription to Tracker Time and, needles to say, this isn't the case. So off camera, I'm going to add in two new lines for churn. In addition to adding in the new rows, I've also added in the churn projections that Mary has given us. Unfortunately, Tracker Time hasn't been calculating churn properly over the previous years, and so we don't actually have an assumption for this figure, so instead, I'll just put in 0%.
Let's now adjust our model to take account of churn. Churn can be calculated in two ways. It can be based off the total number of customers in that particular year, or it can be based off the previous year. In our model, I'm going to base it off the previous year because this is what Tracker Time's CEO has told me to do. So let's start by adjusting our figures, starting with B2B existing customers.
And so instead of linking directly to the total number of customers in the previous year, I'll change the formula. By pressing F2, I'm multiplying this figure by 1 - churn.
And now this will give me a smaller number. If I add these two numbers together I'll get 4,049, which is smaller than 4,231, which is my projection for total customers. So I also need to edit my new customers.
So this formula will now equal to I8 - I11, and I'll do a little bit of formatting to make sure my decimal places aren't showing.
Now, I copy across for the remaining cells.
And I'll do this, as well, for B2C.
So again, I'll jump into the formula and multiply by 1 - churn.
And I'll change the number of new customers so that it refers to the correct cell.
And I'll copy across.
What you'll notice is that the total number of customers isn't changing because this is our main projection. What's actually changing is the number of new customers and number of existing customers because more new customers are going to have to take the place of the churned customers, so our marketing costs are going to increase. When I compare my EBIT calculation before and after introducing churn, you can see that this has a significant impact on the figures.
While it's tempting to think that you have no errors in your financial model, when your balance sheet balances, it's always critical to check your logic with a subject matter expert, particularly, if it's a business model that you're not very familiar with. Otherwise, you risk creating embarrassing errors, as I have shown here.