11. Completing Our Financial Projections

Overview

We complete our financial projections in this lesson by connecting our cashflow statement to the cash figure in our balance sheet.

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 our cashflow projections and add the cashflow balance to the Balance Sheet.

  2. Cashflows from Investing Activities (00:16)

    The section on cashflows from investing activities contains three items. The first is cash received from selling PP&E. In our case, we assume all PP&E is used until the end of its life, so this is zero.

    Second is cash spent on new PP&E. When we acquire PP&E, this has a negative impact on cashflow. The relevant figure can be found in the depreciation schedule created as part of our assumptions.

    Third is other investing activities activities, which incorporates long-term investments and other non-current assets. These items are found in the Balance Sheet. We calculate the cashflow impact of these items by subtracting the previous year’s Balance Sheet amount from the current year’s amount. If these items increase, this has a negative impact on cashflow, so we place a minus sign around our calculation. We repeat this calculation for each year of our projection.

    The sum of these three values represents the net cash used in investing activities.

  3. Cashflows from Financing Activities (02:10)

    This section of the cashflow statement represents cash gained from issuing new shares or cash spent on buying back shares. In our case, MarkerCo is not expected to issue new shares or buy back existing shares, so we project a value of zero each year for this section.

  4. Completing the Cashflow Projection (02:31)

    The net change in cash and cash equivalents for a year can be determined by adding the net cash from operating activities, investing activities, and financing activities. The beginning cash for any year will be the ending cash for the previous year. The ending cash for a year will be the beginning cash for the year plus the net change in cash and cash equivalents. Using these formulas, we can calculate these three figures for each year of our projection.

  5. Completing the Financial Projections (03:07)

    After projecting the cashflow statement, we can add cash and cash equivalents to the Balance Sheet. We do this by linking to the closing cash balance in the projected Statement of Cashflows. 

    Finally, we also add PP&E to the Balance Sheet. The figure contained on the Balance Sheet is net PP&E. Net PP&E for a year is the previous year’s PP&E, plus expenditure on new PP&E, minus the total depreciation expense for the year. New PP&E expenditure and the depreciation expense can both be found in our PP&E schedule in the assumptions section.

Transcript

To wrap up our financial projections, we need to first calculate cash flows from investing activities and then cash flows from financing activities before linking the cash balance back to the Balance Sheet. Let's begin with cash flows from investing activities. I'm going to assume that we do not sell any PP and E in the coming years, using all the equipment until the end of its useful life. So I'll add zero to each of these cells.

However, for the acquisition of PP and E we do have an assumption. When we acquire PP and E, that means we'll have a negative cash flow. So I'll write equals minus and go to the assumption at the top of the page.

And this assumption for 7.4 million will be the same for the remaining years.

Next on the list are other investing activities. And these are long-term investments and other noncurrent assets. The difference between 2016 and 2015 values represent the cash spent on these investments. If these values go up, it means that cash will go down. So it's a negative value. So I'll write equals minus and create a sum formula.

And skipping up to the Balance Sheet, I'll take these two values.

And subtract the two adjacent values for 2015.

And I'll copy across for the remaining cells. Finally, I can calculate net cash used in investing activities, which will be the sum of these three values.

And finally we calculate cash flows from financing activities. Because the company will not issue any new equity or debt in the next 5 years, according to our projections, these values will all be zero.

And the subtotal will equal to zero as well. And I'll just copy across this formula. We can now calculate the net change in cash and cash equivalents by adding together the three components of our statement of cash flows.

The beginning cash is always the ending cash from the previous period and the ending cash will equal the beginning cash plus the change.

And we can copy these across for the remaining cells.

To wrap up our financial projections, I now need to transport the ending cash into the Balance Sheet. So let's scroll up to cash and cash equivalents and link to the ending cash balance.

And I'll copy across for the remaining cells.

The last line left to fill in is property, plant and equipment. And as you can see from the entry, it's net property, plant and equipment. And this means we take the current PP and E, add new PP and E and subtract depreciation.

So if I scroll up to my assumptions I'll add new PP and E and subtract total depreciation which in this column is H31.

Now I'll copy across for the remaining cells. Now that our financial projection is complete, let's make sure that our Balance Sheet balances. So in 2020, I have total assets of 212.7 and I have total liabilities and equity of 212.7 as well. So our Balance Sheet balances. In the next lesson I'll explain more about the dynamics behind our financial model and how to debug some errors that you may find.