9. Projecting our Statement of Cashflows

Overview

Drawing from line-items in the income statement and balance sheet, in this lesson, we project the Statement of Cashflows for TrackerTime.

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 create a projection for the Statement of Cashflows.

  2. Projecting Operating Cashflows (00:09)

    The first step in the cashflow projection is to take net profit and add the adjustments for non-cash charges. In our model, the two non-cash charges are depreciation and amortization and deferred income taxes. 

    Next we add the adjustments for changes in operating assets and liabilities. For each current asset and current liability, the adjustment is found by subtracting the previous year’s value from the current year’s value. For current assets, we place a minus sign around the calculation, as an increase in a current asset leads to a decrease in cash. For current liabilities, we simply perform the calculation, as an increase in a liability leads to an increase in cash. After adding all the changes in operating assets and liabilities, we reach the net cashflows from operating activities.

  3. Projecting Investing Cashflows (02:42)

    To calculate the cashflows from investing activities, we first need to know about the proposed sale and purchase of PP&E in the coming years. Our capital expenditure schedule tells us the proposed acquisition of PP&E, while we can find out about plans to sell existing PP&E from the company’s executives. 

    Changes in long-term investments and other non-current assets will also produce cashflows from investing activities. The cashflow for each year from these items can be found by subtracting the previous year’s value from the current year’s value. We place a minus sign around the calculation as increases in these items reduces our cashflows. Adding cashflows from these items to cashflows from the sale and purchase of PP&E gives us our total cashflows from investing activities.

  4. Projecting Financing Cashflows (04:38)

    In our model, there will be a single financing cashflow, which will occur when the venture capital company purchases their new equity in the company. We simply enter the amount of this transaction in the appropriate year in our model. This is the only cashflow from financing activities in our model.

  5. Projecting Foreign Exchange Effects (05:27)

    Foreign exchange effects are placed at the bottom of the Statement of Cashflows. We have assumed these cashflows will be a percentage of revenue, so we project the figure by multiplying the percentage assumption by revenue.

    Finally, we can calculate the net change in cash and cash equivalents for each year by adding operating cashflows, investing cashflows, financing cashflows, and foreign exchange effects. This lets us calculate the opening and closing cash balance for each year in our projection.

Transcript

In the previous lesson we almost completed our balance sheet projections.

We still need to calculate net PP and E, cash and cash equivalents and the equity entries. To do this, we're going to do our statement of cashflow projections first and then link our statement of cashflow projections back to the balance sheet starting by entering net profit in this row.

And I can take net profit from the income statement.

Next I'll add depreciation and amortization, and of course deferred income taxes which is going to be in my balance sheet and I'll copy these values across for the remaining cells.

Now we need to focus on the changes in operating assets and liabilities. As you might remember, when we have an increase in current assets, this typically means a decrease in cash, so for trade and other receivables I'll write equals minus, open a bracket and then calculate the change.

And because this is a growing startup I'm expecting the change to be an increase each year.

And as you can see, it is.

The same applies to inventories so I can simply copy down and then copy across for the remaining cells.

For current liabilities it works the other way around. An increase in current liabilities means an increase in cash, so I can simply calculate the change.

So, for trade and other payables, it's I126 minus H126.

And again I'll copy across.

And of course I can also copy down for the other current liabilities.

And to calculate net cash from operating activities, I'll just sum these values together.

Now let's move on to cashflow from investing activities.

Having spoken to the TrackerTime CEO, she assures us there will be no sale of property, plant and equipment in the next five years, so I'll assume this to be zero.

However, we do know that there will be an increase in PP and E which we've seen earlier in our CapEx and depreciation entries.

So, I'll scroll up, and pick up the annual CapEx number. I must make sure that this is negative 'cause purchasing CapEx drains cash.

For other investing activities, again my assets will increase when my cash decreases, so this is going to be a negative number and we'll capture the change on the balance sheet.

And we'll capture the change between other long-term investments, and other non-current assets minus the same values for the previous year.

And again I'll copy across.

I'll use the sum function to calculate the net cash using investing activities, and lastly, I'll calculate cash flows from financing activities.

In my model I'm going to assume in the base case that Ventura invests 20 million in new equity and they'll make this investment in 2017.

However, I'm going to add this in as an assumption for now and link this number back to our assumption section in the model which I'll create in a later lesson but for now we can simply enter this number in the statement of cash flows.

I'll make sure this is entered as the net cash used in financing activities and then copy this figure across.

For foreign exchange effects, I know that this is an assumption based on revenue, so I'll take the assumption and multiply by revenue which is cell I77 and again I'll copy across for the remaining cells.

And now I can calculate my net decrease and increase in cash and cash equivalents, my beginning cash balance and my ending cash balance, so the net decrease or increase will be foreign exchange effects, net cash flow from financing activities, net cashflow from investing activities and net cashflow from operating activities.

Beginning cash will be the ending cash from last year and ending cash would be these two values added together and then I can copy across for the remaining cells.

Now let's link these values back to my balance sheet and I'll do this in the next lesson.