Chapter 8: Preparing a Cash Flow Statement

A wise man once said Turnover is vanity, profit is sanity, but cash is reality. An entity's profit and loss (P&L) account gives the result (profit or loss) of revenue activities during the year, and the balance sheet gives a snapshot of the assets, liabilities, and equity at the balance sheet date. However, both of these statements are affected by the accruals basis of accounting that seeks to ensure that all transactions that belong to the period under review are brought into the accounts for that period, irrespective of whether or not cash has changed hands. This means that you are likely to have turnover or sales or income that is not supported by cash inflow because some sales would have been made on credit and may would not yet have been paid for by the period end. This also affects purchases, inventory, and expenses that are either prepaid (such as rent and insurance) or accrued (such as electricity). Since the ability to settle...

Introduction to the cash flow statement

Following our agenda, the next stage is the preparation of the cash flow statement:

• Record the historical P&L and Balance Sheet.
• Calculate the historical growth drivers.
• Project the growth drivers for P&L and Balance Sheet.
• Build up projected P&L and Balance Sheet.
• Prepare asset and depreciation schedule.
• Prepare debt schedule.
• Prepare cash flow statement.
• Ratio analysis.
• DCF valuation.
• Other valuations.
• Scenario analysis.

You will recall that we created a visual check in Chapter 2, Steps for Building a Financial Model, to indicate when the balance sheets are in or out of balance. If they are out of balance, the check cells will be red, but the moment they fall in balance, the cells will turn green, as shown in the following diagram.

Figure 8.1 illustrates the position with the forecast years out of balance. The historical balance sheets will of course be in balance:

...

Cash flow from operations

Cash flow from operations is the cash flow from operating activities. If the accounts were prepared on a cash basis, it would simply be the turnover less all expenses.

However, the P&L account is different from the cash flow statement in that it does not wait for the cash implications of a transaction to be settled before the transaction is recognized. For example, if you make a sale of N100,000 and the customer has received the goods or services but has not yet paid, there is no cash movement. However, both you and the customer recognize that a sale has been made; indeed, ownership and custody of the goods have been transferred. So, the P&L account will record this as a credit sale, increasing turnover by 100,000, and to complete the double entry, a receivable is created under that customer's name to signify that they owe you 100,000.

This is the accrual basis of accounting that says that income should be recorded in the period in which...

Cash flow from investing activities

Cash flow from investing activities will include cash generated or utilized in the following:

• The sale or purchase of property, plant, and equipment (PPE): Purchase of PPE is straightforward and can be obtained from the additions in the fixed assets schedule. The sale of PPE would have been reflected in the accounts as profit or loss on the disposal of assets. This is arrived at by comparing the proceeds of the sale to the net book value (cost less accumulated depreciation) of the assets sold. The profit on disposal will be reversed from operating profit along with depreciation as not involving the movement of cash. The proceeds of sale is the cash element of the transaction and is calculated as profit on the sale of assets plus the net book value of assets sold.
• Sale or purchase of investments: Cash utilized in the purchase of investments will simply be an increase in investments after considering any sale. Cash generated from the sale...

Cash flow from financing activities

Cash flow from financing activities will include cash generated or utilized in the following activities:

• Increase/decrease in long term debt.
• Increase in share capital.
• Payment of interest on long-term debt. Again, to include this here, it must be excluded from the computation of profit for the year.

An illustration of cash flow from financing activities can be seen in Figure 8.5:

Figure 8.5 – Cash flow from financing activities

You will then aggregate the cash flows from the different sources to arrive at net cash inflow/outflow:

Figure 8.6 – Net cash flow and closing balance of cash

Prepare the cash flow statement, as usual, ending with a figure for the closing cash balance. Then, post this figure to the balance sheet as cash and cash equivalents for the projected years.

If the balance sheet now balances, this indicates that our model up to this stage is...

Balancing the balance sheet

The closing cash balance will be posted to the balance sheet as cash and cash equivalent under current assets. However, it is important to note that the balance could be negative, in which case it should be reflected as an overdraft under current liabilities. Since you don't know which it is going to be, especially as it may change as a result of subsequent modifications, you need to build your model in such a way that the cash balance is posted to cash and cash equivalents if it is positive, and to overdraft if it is negative.

Usually, when you need to model a situation that depends on a logical question (one that results in a true or false answer), the first thing that springs to mind is the IF statement. For example, say the cursor is in cell J35, cash and cash equivalents, and you wish to relate this to the calculated cash balance from the cash flow statement in cell J86, you would type = J86 and the cash balance would appear in cell J35 when...

Troubleshooting

The first step is to check the accuracy of your cash flow statement. Since the historical years will already have a cash or overdraft balance, you can check your cash flow and cash balances for those years against the balance sheet cash.

If the balances do not agree, you will need to check your cash flow entries again:

1. First of all, check your totals for any casting errors.
2. Next, determine what the difference is and divide by two. Look through your cash flow to see whether there is an amount equal to this figure. This test checks to see whether you have wrongly posted a figure as negative instead of positive, or vice versa.
3. Scan through the balance sheet and P&L account for an amount equal to the whole difference calculated in Step 1. This test checks to see whether you have omitted an amount from the cash flow.
4. Scan through the balance sheet and P&L account to see whether there are any accounts or balances that have not been accounted...

Circular references

Say you have data in cells A1 to A4 and you type = SUM(A1:A6) in cell A5. This will be flagged by Excel as a circular reference error because you have included the answer cell, A5, in your sum range.

In complex models, you may wish to deliberately create a circular reference for the following reasons.

In the general scheme of things, a company would invest any surplus cash to earn interest. On the other hand, when cash is in overdraft, it will incur interest. If we wanted to expand our model to include this scenario, we would need to extend our cash flow statement to include interest earned or charged on the cash balance. This interest is then subtracted from or added to the existing interest charge in the P&L account, which changes the PAT. Since the PAT is linked to the cash flow statement, this will also result in a change in the closing cash balance, which will affect the interest earned or charged on that balance and the cycle continues, creating...

Summary

In this chapter, you have learned about the importance of the cash flow statement. You have understood the different elements of the statement and how to calculate them. You also learned that cash flow from operations gives an indication of the company's ability to generate sufficient funds for its operations, and movement in working capital makes adjustments to convert accruals-based accounting to cash-based accounting.

We have seen how to extract cash flow from financing and investing activities and finally, we have seen how to arrive at the closing cash balance for the year and use this to balance out the balance sheet.

We have now concluded the three-statement model, and in the next chapter, we will perform some ratio analysis to gain further insight into the projected performance of the company.