Cash Flow Statement

The following list shows our agenda and the next stage is the preparation of the cash flow statement:

• Record the historical profit and loss and balance sheet
• Calculate the historical growth drivers
• Project the growth drivers for the profit and loss accounts and balance sheet
• Build up the projected profit and loss accounts and balance sheet
• Prepare the asset and depreciation schedule
• Prepare the debt schedule
• Prepare the cash flow statement
• Ratio analysis
• DCF valuation
• Other valuations
• Scenario analysis

At this stage, we have completed the profit and loss account and the only item left to complete the balance sheet, which is still out of balance, is cash. In this chapter, we will look at how to prepare the cash flow statement for our project.

In this chapter, we will cover the following topics:

• Introduction to the cash flow statement
• Items not involving...

Introduction to the cash flow statement

Profit and loss accounts are different from the cash flow statement in that they do 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 profit and loss account will record this as a credit sale, increasing turnover by N100,000, and to complete the double entry, a receivable is created under that customer's name to signify that they owe you N100,000. This is the accrual basis of accounting, which says that income should be recorded in the period in which it is earned, and expenses should be matched...

Items not involving the movement of cash

In arriving at PAT, a number of items not involving the movement of cash have been considered, which now have to be reversed in order to arrive at an accurate figure for cash flow, as shown here:

The obvious candidate for this is depreciation. The relevant cash flow occurs at the time the asset is purchased. However, we don't charge the total cost to the profit and loss account all at once; the correct accounting treatment is to allocate the original cost over the useful life of the asset.

This periodic allocation of cost is called depreciation and clearly does not involve the movement of cash. Since it has been deducted as an expense in arriving at our profit, we need to add it back to the PAT, as shown in the preceding screenshot. We also add back interest charged to PAT. Although this is cash flow, it is the cost of debt finance...

Net change in working capital

Let's look at the following screenshot on the net change in working capital:

This section converts our profit from the accrual basis into cash-based profit. In simple terms, taking our previous example, we have recorded a sale of N100,000, increasing our profit by that amount even though no cash was received. This section looks at the corresponding increase in accounts receivable of N100,000 and treats it as an outflow to be deducted before arriving at cash flow from operating activities, hence reversing the inflow recorded from the credit sale.

In summary, under this section, we add increases in working capital liabilities and subtract increases in working capital assets. An increase is manifested when we subtract the current year's figure from the previous year's, assuming that this year's figure is higher than that of the previous...

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...

Creating a quick cash flow statement

Now that you have learned how to create a cash flow statement and everything you need to do, let's go ahead and prepare a simple statement for a specific scenario. Let's assume that you are the founder of a web design company and you need to keep tabs on everything that you spend money on, because as a start-up, capital is vital to the development of the company. We will create a simple cash flow statement to track the first year of expenses, so that we can understand whether it will be profitable to proceed with the company later down the line:

1. We will begin by creating two cells at the top, with the financial year and the cash invested at the beginning of that year, as follows:

2. As we have learned in this chapter, cash flow will be divided into three main sections, the first of which is operational activities. So, let's...

Summary

In this chapter, we learned how to create our cash flow statement using various functions in Excel. We learned how to factor in various elements, such as items not involving cash movement, cash flow from various activities such as investment and finance, and so on. We also learned how to balance the sheet so that everything is accurate. We also understood how to troubleshoot any errors that might occur here. Finally, we created a sample cash flow statement for a specific scenario.

In the next chapter, we will look at various kinds of ratio analysis.