A cash flow forecast can help your business (or even yourself personally) to predict and prepare for future financial difficulties. This is usually the main use, but companies also use it to plan different scenarios. For example: purchasing a building or employing more staff. Are they financially feasible? Let’s look at the options and the steps then do yours with our free cash flow forecast template!
Ways to do a cash flow forecast
Before we deep dive into using our cash flow forecast template, let’s look at the options you have for doing your forecast.
-Software
Thanks to the rapidly evolving fintech industry, there is now countless software out there to help you. Most of these will work with your cloud accounting software (eg. Xero, Quickbooks) as an add-on. Some other accounting software might even have it integrated. Usually, what we have found is that integrated solutions are quite limited. The company’s core focus is providing you with an accounting software and not a super amazing cash flow forecast tool. Therefore an add-on software tends to give you a lot more insight.
Some of the cash flow forecast add-on tools we recommend (if you use Xero) are:
Float App (www.floatapp.com)
Float is very user friendly and syncs with Xero, Quickbooks and FreeAgent also. However, if you have just started trading this could be a bit expensive solution.
Vistr (www.vistr.co)
Slightly less user friendly than Float is, but Vistr is a great FREE tool. Vistr only works with Xero, so if you happen to use another cloud accounting software this won’t work for you.
DryRun (www.dryrun.com)
DryRun is one of the most expensive solutions out there, but so far the only tool that connects with Pipedrive – if you use that.
They are fairly new and we have run into several issues with it while testing it. It could be a great software, but unfortunately, their customer support is far from top notch. The software is too complex to set it up without their customer support team (even though we have used several similar software before). We have been trying to set it up for a client for 4+ months now and we have to constantly chase them for a reply. Therefore if you have serious cash flow issues and need an immediate solution, go with something else. At the time we tested it in August 2020, there was also a huge bug with their currency exchange that can put you at financial risk. If your company doesn’t trade in multi currency, DryRun can still be a great solution. However, if you do, (at the time of testing) it exchanged foreign currency invoices based on the invoice’s issue date, rather than the current market rate. So if you have well overdue invoices or the exchange rate fluctuates, it will forecast you based on the wrong rate. Do give it a try if you like it. It might have been fixed by now. In that case, please let us know in the comments below!
Why is it better to use a software than doing it manually?
The great advantage of using a cash flow forecast tool is that it syncs with your cloud accounting software.
Why is this so great?
Wait till you do it manually! 😉 You will instantly know! Softwares takes all the outstanding sales invoices and purchase bills from your accounting software, so you can forecast when to receive money or pay bills. As it syncs, it means that once you have paid a bill and reconciled it (connected the payment to the outstanding invoice) in your accounting software, it will stop showing in the cash flow forecast as outstanding. The cash flow forecast tool will also remind you if something is forecasted to the past and you need to move the expected payment date into the future.
These are the things that are manually super tedious and time-consuming. As your business grows, cross-checking 100s of invoices and payments in Excel is far from fun. Then you find that your bank balance doesn’t agree and start again… headache on a headache!
If you are already sold on an add-on cash flow forecast tool, please don’t skip the manual section just yet!
-Manually
The other option to do the forecast is manually in Excel (see our cash flow forecast tool below).
As much as I even hate the thought of it, here is the deal: you don’t want to take driving lessons with a Ferrari. Ok, maybe you do… but try to stay with me. 🙂
You need to understand the ins and outs of cash flow forecasting to be able to use these tools well – or they could give you a false prediction and I’ll explain a bit below why.
So irrespective of your choice, go through the examples below so you understand the mechanics of it.
Disclaimer: I’ve saved several companies from bankruptcy and even people close to me from financial disasters. I know this method works – as long as you keep tight control over it, especially when times are hard.
How to do a cash flow forecast?
What do you need before you start?
In the below steps we will explain cash flow forecasting manually in Excel.
Before you start, you will need to:
-Download our cash flow forecast template here (if you don’t have one).
Collect:
-Opening bank balance (all of your bank balances!)
Tip: use your previous day’s closing balance to ensure you are not using the balance after the 1st transaction has been added / deducted on the day.
-Outstanding sales invoices
-Any other revenue you expect to get, even if you have not invoiced for it
-Outstanding purchase bills
-Any other expenses you will need to pay, even if you don’t have a bill for it (eg. recurring costs, loan repayments etc)
Once you have the above, follow the steps:
Step 1.) Basics: Enter all the information you collected into the cash flow forecast template
In the screenshot below, we have just entered all the figures we collected in the beginning. The first row on the top are the days of the month. So today is the 1st of the month. We have the bank balances (closing balance of the 30th) and money we expect to receive and pay. To start with, these are the payment due dates. NOT when the invoice was issued!
We can already see that if we pay the rent today, we have a problem. Our bank balance (Closing balance) will go into minus (1000). Brackets in finance mean minus.
Now we have to start forecasting. So first let’s add some formulas and copy it across all the rows.
I’ve highlighted the lines in light grey where I’m using a formula and added a comment in the first cell where it starts from and to explain what it is.
For the Opening bank balances, I’m copying the previous day’s closing bank balance. You might ask why I have two lines for bank balances (Opening bank balance & Total bank balance). This is because one (Opening bank balance) is the previous day’s closing based on our Excel. The other is based on the bank accounts and we constantly want to keep an eye on the two, to make sure they agree. If not, you have probably missed a payment from the bank account (welcome to the world of manual forecasting ;). To help you keep an eye on this, you can even create a line to show their difference. If that line is not zero, there is an issue.
Now that we have all the formulas in and copied to the future, to save our bank balance for tonight, we moved the rent payment (orange) to the 3rd. This leaves us 2 more days (today and tomorrow) with cash in the bank. We still have an issue on the 3rd.
To ensure we won’t have an issue on the 3rd again, we can do two things:
-Either move the rent payment or Supplier 1’s payment to the 4th when we have money coming in or
-Chase Customer 1 to pay their invoice sooner (no later than the 3rd – red cell). Below we chose this 2nd option and you can see our Closing balance is now positive.
Step 2.) Advanced: How about money that’s not your own money?
There are certain types of companies who collect money from their clients on behalf of their suppliers. This money is therefore not their own money and will have to be paid it.
Typical companies like this are:
-Property letting agencies (where the agency collects the rent from the tenant and has to pay it out to the landlord).
-Media / marketing agencies (where the agency collects the advertising budget from the customer and pays it to the platforms where the client is advertising – eg. Google Adwords, Facebook, etc).
As you can see, this is not an unusual scenario and there are several businesses who have to handle money, that is not theirs.
If you are running a company like this, you will need to have 2 additional sections on your cash flow forecast:
-Media income (or call it rent income etc)
-Media expense (rent expense) as below:
We left all the numbers the same, except, we assumed you collected 1500 as media income from Customer 2. So instead of them showing 2000 in cell D9 9 (green), we show 500 in cell D9 and the 1500 media income shows in cell D18 (blue). So basically we have just separated out the media from the total invoice.
You might ask: what do you do if you charge VAT (sales tax) on your invoices? Everything goes into the top section – even the VAT on the media income. You only enter the pure media income / costs at the bottom.
You will notice that now we have 2 totals:
-Core cash: this is our pure cash flow without media income / expenses. So this is our true cash flow.
-Closing balance: this is our total balance including media money.
As you can see above, on the 3rd, our core cash is now zero, but we still have money in the bank (closing balance).
However, on the 6th, we pay out the media income (becomes a media expense) and now both our core cash and closing balance are zero. As the core cash and the closing balance agrees, it means we don’t hold cash on anyone else’s behalf.
3 different outcome:
Core cash & closing balance – both negative: means you are in big trouble! You will need to try and move expenses into the future and chase client to pay their invoices so at least your closing balance is positive.
Core cash – negative, but closing balance – positive: this means even though your own cash flow (core cash) is in minus, you still have money in the bank due to the media income. In this case you are using other peoples’ money to pay for your bills. This is very not ideal (and in some industries if you are even regulated – and you are using money from an escrow account, it can get you into real big trouble). If you are not regulated, then it can help you temporarily, but really not ideal – you should not run a business like this.
Core cash & closing balance – both positive: happy days, you are all good!
Summary:
1.) Ways to do a cash flow forecast:
Software
- -Float App
- -Vistr
- -DryRun
- -Why is it better to use a software than doing it manually?
Manually
2.) How to do a cash flow forecast?
What do you need before you start? Download our cash flow forecast template here
Step 1) Basics
Step 2) Advanced
3 different scenarios
Next Step:
Read Next: 6 Ways to improve Amazon UK sales during COVID-19