Copy the original template to a new worksheet so that you can use the original again, if required. Also, so that you can share the original template. (Please feel free to share it. See copyright note below.)
If you wish, you can instead copy the “Unlocked” sheet to use as a template. This will allow further development of the spreadsheet since all cells are unlocked. However this leaves the formula cells unprotected from accidental changes.
The simple numbers inserted into the Template spreadsheet are there just to illustrate how the numbers are calculated. Delete them when you start using a copy of the template for your own business.
The Example tab shows figures for a fictional business I use in one of my training courses. I’ve also added in some random numbers. Don’t take the actual numbes seriously. But you can see how they forecast the cash balance at the end of each month.
Use the new copy of the template worksheet for your business, adding relevant categories of Receipts and Payments.
Add a real figure for your opening bank balance at the beginning of April. This number can be inserted into the pale orange cell.
You can add new rows to increase the number of Categories for Receipts and Payments.
You can also delete rows if there are too many.
Additional columns can be added to extend the duration of the cash flow projection beyond July 2021 on the “Unlocked” sheet.
Please note that some cells are locked and not accessible. These are the cells containing formulas that should not be changed. They are locked to prevent accidental changes to them.
If you want to access all cells, including formula cells, then use the “Unlocked” sheet as a template.
START USING THE SPREADSHEET TO FORECAST CASH FLOW OVER THE COMING MONTHS
Insert numbers into all the relevant categories, using the best information you have. Some will be confirmed numbers, others will be your best guess at the current time.
The spreadsheet is a working document that can be updated frequently, even daily if appropriate. Add new information about expected movement of cash in and out of the business, as soon as you have better information or a more informed estimate.
Put receipts and payments into the spreadsheet in the month they are most likely to occur. This might not be the same as when they should occur. (For example if a customer is likely to pay later than credit terms allow, put the receipt into a later month.)
As a general rule of thumb, always err on the side of caution. To highlight potential cash flow problems, anticipate that receipts may arrive late. But only plan for delayed payments if you really can delay them.
Update the spreadsheet once receipts and payments actually occur. Hopefully, any changes will be bonuses rather than unanticipated problems.
The beauty of using a spreadsheet like this for cash flow projections is that you can explore different scenarios. (“What if we delay a payment for a month?” “What if a client pays a percentage upfront?” “What happens if a customer pays late?”)
By exploring these different possibilities, we can see in advance where problems may occur, and make plans to deal with them.
Keep an eye on the ‘bottom line’, which in a cash flow forecast is the month-end bank balances for the months ahead. Hopefully we can keep this positive by planning ahead.
Develop the spreadsheet as required, adding new categories and new types of receipts and payments as they become relevant to your business.
Check that the cash flow forecast tallies with bank statements at the end of each month. If not, perhaps there is a type of receipt or payment that needs adding to the categories (for example a direct debit).
TERMINOLOGY AND FUN FACTS
The terms “Cash Flow Forecast” and “Cash Flow Projection” may be used interchangeably.
The term “Cash” refers to all receipts and payments, whether by cash (notes and coins), cheque or electronic transfer.
Do not use the terms “Income” and “Expenditure” in a cash flow forecast. In accounting terms, Income and Expenditure have special meanings, which is NOT about the movement of cash.
Income is achieved when you invoice for a completed job, even though you might not actually receive payment until months later.
Similarly, Expenditure happens when you commit to a payment, even though you might not actually pay it until much later.
A cash flow forecast more correctly uses terms such as “Receipts”, “Cash Inflows” or “Cash In” ; “Payments”, “Cash Outflows” or “Cash Out”.
Receipts for payment of invoices that include VAT should be included as a receipt, even though the VAT element is not counted as Income.
(For example an invoice to a client for £1,000 + £200 VAT = £1,200. When the £1,200 is received, all of it is included as a receipt, even though only £1,000 is Income. Other accounts will deal with this. The cash flow is simply the total receipt.)
(In a similar way, an invoice from a supplier for £500 + £100 VAT = £600. When the £600 is paid, all of it is included as a payment, even though only £600 is Expenditure. Other accounts will deal with this. The cash flow is simply the total payment.)
Depreciation is a valid item of Expenditure in business accounts, but is not a Payment. Therefore depreciation never appears in a cash flow forecast.
By understanding and using these terms correctly, you will add to your credibility when dealing with investors, bankers, accountants, advisers and others in business.
COPYRIGHT, CREDITS AND LINKS
This spreadsheet and its notes are Copyright © David Parrish 2020. However they may be used and shared free of charge provided they are not changed or sold. See Creative Commons Licence:
https://creativecommons.org/licenses/by-nc-nd/2.0/uk/
Please credit David Parrish when using or sharing this spreadsheet, adding a link to this website:
http://www.davidparrish.com/
Thank you!
Wishing you every success!
David Parrish