As promised, I’ve spent a good portion of my day preparing for you a model cash flow statement on Excel. This is a popular indirect method of showing cash flows from operating activities.
The model has 3 worksheets. You need to update highlighted cells in “Inputs” sheets with your own company data. The final cash flow statement will be produced automatically for you in “Result” sheet. Go on, download the Excel file Here and give it a go.
Here’s how your cash flows should flow:
Step 1) I’m starting with my profit before tax (PBT) of $24,600. Then, I’m adjusting the PBT by removing/reversing all non-cash income and expenses. Non-cash expenses are added-back to the profit, and non-cash income is removed from the profit as follows:
You will notice above that I’m also removing finance income and finance cost. This is because they don’t necessarily show how much interest was paid or received in terms of cash. Remember, finance costs may include all the premium unwinding and discounts and effective interest rate adjustments.
Step 2) Above, we have arrived at $61,100 cash inflow from operating activities before working capital changes. Changes in working capital normally includes changes in your inventories, receivables and payables. Next, I go through my balance sheet and identify all assets and liabilities which have occurred neither from investing, nor financing activities (ignore equity and cash and cash equivalents!).
I have identified below assets and liabilities, which you can call as my operating assets and liabilities, and hence form part of my working capital. I calculate the effect of changes in working capital on my cash.
Remember that increase in assets will decrease your cash and vice versa. Similarly, increase in your liabilities will increase your cash and vice versa. Think of it this way:
- Increase in inventories – You have spent cash to buy inventories.
- Increase in receivables – You have spent cash to extend further credit to your customers.
- Increase in payables – You have purchased more items for credit, hence you did not spend cash.
- Decrease in inventories – You have sold inventories, and received cash.
- Decrease in receivables – You have collected monies owed to you from debtors and received cash.
- Decrease in payables – You have paid to your suppliers and have spent cash.
Step 4) We are done with operating cash flows. That’s 1/3 of our work done! In step 4, we will go through our investing activities. You will need to put actual payments and proceeds from investing activities.
Remember, amount you are showing as disposal on your PP&E movement may not necessarily be the same as the amount you receive from this sale. There may be some gains and losses from the book price. In my company, even though I’ve disposed-off my PP&E worth $12k on the balance sheet, I have $14k cash proceeds. The difference can be traced to my P&L, where I have $2k gain on disposal of property.
Step 5) Financing activities. Similarly, I’m showing actual proceeds and repayments, gross:
Step 6) Good news! There’s no Step 6. Because you are done! Make sure your cash and cash equivalent balances tie back to your balance sheet at the beginning and end of the year. Net increase in cash and cash equivalent below is just sum of operating, investing and financing cash flows. My cash and cash equivalents on cash flow statement is net of bank overdrafts, which is seen on the liability side of my balance sheet.
Arrivederci,
JU
If you have any questions, or suggestions, please comment below. If you are seeking professional advice email me at advisory@finspect.uz
