10. Completing our 3 Statement Projections

Overview

In this lesson, we complete our Statement of Cashflows projection and ensure that our Balance Sheet projection is balanced!

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Lesson Goal (00:04)

    The goal of this lesson is to complete the projections for our three financial statements.

  2. Adding Cash and PP&E to the Balance Sheet (00:16)

    After completing the projected Statement of Cashflows, we can add cash to the Balance Sheet. We add the closing cash balance for each year to the Balance Sheet. For PP&E, we add the final amount of PP&E for each year to the Balance Sheet. The PP&E amount for a year is the PP&E amount for the previous year, plus the expenditure on new PP&E, minus the depreciation expense.

  3. Adding Equity to the Balance Sheet (01:01)

    The equity section of our Balance Sheet contains three items. We first consider Accumulated Earnings. The accumulated earnings for a year are the previous year’s accumulated earnings plus the net profit for the year.

    The second equity item is issued capital and share premium. This rises when the venture capitalist makes their investment. The amount of share capital increases by the amount of the investment. 

    The final equity item is reserves. The reserves for a year are the previous year’s reserves, plus the foreign exchange effects, which are found in the Statement of Cashflows.

    Adding all these items gives us the amount for Total Equity. We can then complete the Balance Sheet projection. Total Assets should equal Total Liabilities and Equity. We can create a Balance Sheet check that calculates the difference between these figures each year. This helps us identify at a glance if the Balance Sheet is balanced.

  4. Formatting the Model Correctly (03:44)

    After completing a financial projection model like this, it’s worth taking time to ensure the model is formatted properly, so it can be easily understood by other people. For example, in our model, we include a column that indicates the units used in each row of the model.

    In addition to model formatting, it’s a good idea to conduct some formula auditing to ensure that your formulas work correctly. Tools such as Trace Precedents, Trace Dependents, and Colorize can be to audit your formulas.

Transcript

In the previous lesson, we calculated our statement of cash flows projection and now we can calculate our balance sheet projection to complete our three statement financial model.

Let's start by linking cash and cash equivalents to the relevant value in my statement of cash flows.

And of course, I'll copy across for the remaining cells.

Next, we've property, plant and equipment. And this will equal to the previous amount plus the new amount minus depreciation.

And again, I'll copy across for the remaining cells.

Now I have my total assets calculated and I need to calculate liabilities and equity. As you can see, liabilities are complete, but equity is not.

Let's start with accumulated earnings, which is simply equal to the previous amount of earnings plus net income.

Next, I'll look at issued capital and share premium. This figure increases when we issue new shares to investors, and we've issued 20 million to Ventura in 2017.

So I'll take the previous figure and add 20 million.

As you can see, this is how a balance sheet balances. Twenty million in cash is added to current assets and 20 million in issued capital and share premium is added to liabilities and equity, thereby keeping our balance sheet balanced for every transaction. Reserves are simply equal to the previous reserves plus foreign exchange movements. And again, I can find these numbers in my statement of cash flows.

And I'll copy these numbers across for the remaining cells.

And of course, I'll calculate total equity also.

And total liabilities and equity by adding these two cells together.

Now let's do a balance sheet check where I'll take total assets and I'll subtract liabilities and equity.

And I'll copy across for the remaining cells.

And as you can see, our balance sheet balances. As you may have noticed, when we're using multiples, such as revenue multiples or EBTA multiples, we don't necessary need to calculate the full balance sheet and statement of cash flows projection. However, when we perform this analysis, and as you can see it doesn't take very long, there are some interesting insights about the company's performance that we can analyze. And I'm going to show you how to do this in the next lesson. But before that, we're going to do a small amount of clean-up to make sure our model is formatted correctly. For example, we can see that the number of years and statement of cash flows are not complete.

So to do this, I'll simply add one and copy across.

Next, we can see that a lot of the values that we included earlier on in our model in Column D, no longer apply for the financial statements. And I'd like to ensure that the correct unit for each row is included in Column D, and I do this off camera. In addition to checking your model formatting, it's also worth checking some of your model formulas. Even if your balance sheet balances, it doesn't necessarily mean that all of your formulas are working correctly. For example, if you make a mistake in one of your assumptions, it's likely that the mistake will flow into both sides of the balance sheet, thereby creating an error.

When checking your formulas, be sure to use some of the formula auditing tools such as Trace Precedence and Trace Dependence. I'll link to some of my lessons on these topics in the show notes below.