David has published a cash flow planning spreadsheet as a download in a free toolkit product. This can be used as a cash flow planning tool to create a cash flow projection or cash flow forecast for your business.
His presentation on cash flow planning and sharing his cash flow planning spreadsheet took take place at an online Zoom event and webinar hosted by creative and digital industries hub Baltic Creative CIC in Liverpool, UK. The event was organised in response to the Coronavirus COVID-19 crisis, to help businesses to manage their finances and plan cash flow during the pandemic and beyond.
The Cash Flow Planning spreadsheet toolkit is now available, which includes:
– A free, downloadable Cash Flow Planning spreadsheet with a template to use, with a sample cash flow forecast, plus notes.
– A short video of David’s online webinar presentation about cash flow planning and how to manage a cash flow projection.
– Notes about cash flow planning with advice, tips and tricks, and other relevant information.
The Excel workbook includes a template, an example cash flow forecast, plus detailed notes about how to set it up, use it and develop it further.
Video of David’s online presentation about cash flow planning and how to manage a cash flow projection.
Cash Flow Planning Spreadsheet
SETTING UP THE SPREADSHEET
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:
Please credit David Parrish when using or sharing this spreadsheet, adding a link to this website:
Wishing you every success!
David Parrish delivers interactive training workshops on financial management and other business subjects. He has devised and developed a range of training workshops which he delivers at locations worldwide and online. His workshops are specifically designed for owners and managers of digital and creative businesses.
Read about other events, webinars, interviews, resources and business advice, which are part of David’s response to the Coronavirus COVID-19 Crisis. The page includes videos of past events as well as notice of forthcoming events, plus some suggestions for future collaborations (more…)
If you like this page and want to say thanks, or encourage me to publish more free information for creative entrepreneurs, you can buy me a coffee!