How to create a cash flow forecast in Excel.
This video will show you a cash flow forecasting example, from the data and assumptions that
you need to build it, to the structure of the cash flow statement, to the formulas in
the Excel spreadsheet, to the business decisions that need to be made to optimize cash flow.
Before we build our cash flow forecast in Excel, let’s ask ourselves why a business
would want to make a cash flow forecast.
The main “technical” reason for cash flow forecasting
is to identify cash shortages or surpluses.
The main business reason to create a cash flow forecast is a very fundamental one.
Can we keep operating?
Can we pay our employees and suppliers?
Because if we can’t, the employees may not come to work anymore, and the suppliers might
stop supplying us.
If we can keep operating, can we grow and invest through Capital Expenditures or acquisitions?
In case we have cash surpluses, can we pay down our loans,
and can we pay dividends to shareholders?
The cash flow forecast might be the most important spreadsheet you will ever build for your company!
Here’s a cash flow forecast spreadsheet that I have built based on real life experiences
of the cash flow dynamics of several companies.
As you can see from the header, this is a cash flow forecast on a monthly basis.
Some companies might prefer a higher frequency: weekly,
or if you are really running low on cash even daily.
In some situations, a company may look at a cash flow forecast on a less frequent basis:
quarterly or annually, that’s mostly to zoom out and look at the bigger picture.
This cash flow forecast spreadsheet is made up of just over 20 rows, and 14 columns
(12 months, plus row labels on the left, plus full year check totals on the right).
The first line in the cash flow forecast is the cash opening balance.
You can get the January cash opening balance either from the balance sheet, or from the
source document: the year-end bank statement.
The opening balance for each of the other months is an estimate, based on the prior
month cash balance and cash flows, and it’s equal to the ending balance of the prior month.
Let’s first go through the cash flow forecast for the month of January, and then we’ll
see how the other months are built up.
The cash flow statement is split into three sections: cash flows from operating activities,
cash flows from investing activities, and cash flows from financing activities.
Let’s focus on the operating activities first, using what is called the direct method
of cash flow forecasting and reporting.
Listed here are five very common line items: cash receipts from customers, cash paid to
employees, cash paid to suppliers, interest paid on loans, and income taxes paid.
In the cash flow statement, we focus on cash received and cash paid, which is different
from the income statement, where we focus on revenue and expenses.
Out of the five line items in this section of the cash flow forecast, one is a cash inflow,
and four are cash outflows.
Cash outflows are shown as negative numbers, between brackets.
You could add more line items if you want to forecast specific cash inflows and outflows
individually, but please remember the concept of materiality: if you are spending time forecasting
a $10 monthly spend on stationery on an individual line item level, you are clearly not getting
the point of cash flow forecasting!
The single most important line item in here is cash receipts from customers.
This is the main inflow of cash for most companies,
so you need to spend a good amount of attention to it, to get it right!
As you can see, I have linked this cell to the key assumptions section below the spreadsheet.
Involvement from marketing and sales, as well as the billing and collections staff, is very
important to make this forecast as accurate as possible.
One key assumption, you could also call this a key commitment from the marketing and sales
department, is the expected revenue for the year.
In our example, this is $800,000.
This revenue is not billed evenly over the months, but there is significant seasonality
between the quarters, as well as within the quarters.
The revenue seasonality estimate is based on historical patterns, what has occurred
in previous years.
Q1 is 20% of the total year number, with March delivering half of the Q1 revenue.
Q2 is 30% of the total year number, with June delivering two thirds of the Q2 revenue.
Q3 is 20% of the total year number, Q4 30%.
On the far right, we sum the percentages, to make sure that the total year adds up to 100%.
The next row calculates the dollar number of revenue by month, by multiplying the expected
revenue for the year by the % of annual revenue for that month.
To check, we sum the expected revenue for the twelve months at the right.
This should be the same as the expected annual revenue in cell B27.
So now we have the amounts to be invoiced by month, but that’s not necessarily the
same as the amount received in cash from customers in a month.
From the terms that the company sets in its contracts, and historical data on the actual
payment behavior of customers, we know that the Days Sales Outstanding (or DSO) for the
company is 30 days.
In other words: what is invoiced in January, gets paid in February, what gets invoiced
in February, gets paid in March, what gets invoiced in March, gets paid in April, etcetera.
A one month delay between invoicing and collection.
March, June, September and December are big revenue months.
With 30 days DSO, the collections (or cash receipts) occur one month later: April, July,
October and January are big collection months.
For January of this current year, we can estimate the collections based on the outstanding receivables
balance at the end of the previous year.
Just like the opening cash balance, you should input the actual number here to start from
the right place.
Full year collections are expected to be $740,000, this is what goes into our cash flow forecast.
Look, here’s the same $740,000 for the year, with the cells for each month linked to the
assumptions below.
The second most important row in the CFOA section is cash paid to employees.
These are cash outflows, hence the negative numbers (between brackets).
To estimate cash paid to employees accurately, the cooperation between finance and human
resources is very important.
Cash paid to employees is the second (and last) section for which I have built a detailed
list of assumptions in this Excel spreadsheet.
The first assumption is the headcount forecast.
The company starts the year with 8 people, plans to hire one additional person who starts
work in April, bringing headcount to 9 people, and then hire another person who starts work
in July, bringing headcount to 10 people.
The second assumption is the average salary per employee, on an “all in” basis: gross
salary, including social security charges and healthcare plan contributions by the employer.
For the first half of the year, we estimate the cost per employee at $4000 per month.
For the second half of the year, at $4200 per month,
after a 5% merit increase across the board in July.
Now we can calculate the salaries paid by month: headcount times average salary.
But these are just the regular salary payments.
We have two items to take into account.
The marketing and sales staff gets a bonus paid out in February, once the actual results
versus the sales targets for the prior year have been calculated.
All employees get an annual “vacation payment”, equal to one months’ salary, paid in May.
If you add up the regular salary payments, plus bonuses and vacation pay, you see that
total payments to employees has an uneven pattern, with a large peak in May.
It’s not quite as bumpy as the collections line,
but it could contribute to cash flow bottlenecks.
If we scroll back up from the assumptions section to the cash flow from operating activities
section, we link the cells with a minus: payments to employees are a cash outflow.
Next row is cash paid to suppliers.
This is evenly distributed over the months, and consists of things like office rental,
equipment rental, lease car payments, and advertising spending.
Interest paid on loans is also evenly distributed over the months.
Income taxed paid is unevenly distributed over the year: most of this is paid in January,
and a little bit in July.
This is another area, just like revenue vs collections, and employee costs vs payments
to employees, where the cash flow perspective and the income statement perspective
can differ significantly.
In the income statement, you focus on revenues earned and expenses incurred
on an accrual basis.
You record (or in financial terms “accrue”) income tax expense in the income statement
by month, depending on how much income is generated in that month.
In the cash flow statement, you focus purely on cash receipts and cash paid.
The subtotal for net cash provided by operating activities shows how dynamic the cash flow
for the company is.
Fairly big net cash inflows in the first month of each quarter (shown as a positive number),
as the collections for the prior month peak revenue occur.
Net cash outflows in many of the other months (shown as a negative number), as collections
are fairly low while either the ongoing or the one-time cash outflows related to operating
activities continue to occur.
On to the second section of the cash flow forecast: cash flows from investing activities.
Two line items in this section: Purchases of property and equipment (in short CapEx,
for Capital Expenditures), and payments made for acquisitions.
The company is planning two purchases of equipment (machines, servers, office refurbishment):
$30,000 each in March and September.
Currently, no acquisitions are planned, but that could change based on opportunities arising.
If an acquisition does happen, the company will probably need to take out a new loan,
or get a capital infusion from shareholders.
Both of these would be accounted for in the cash from financing activities section.
The third section of the cash flow forecast: cash flows from financing activities.
In this example, I am showing three line items: proceeds from issuance of debt (none planned),
repayments of debt ($10,000 planned in June and December), and dividends paid to shareholders
($15,000 planned in both April and October).
If the company would raise capital from shareholders, we could add a fourth line item in this section
of the cash flow forecast.
Up next is the cash ending balance.
Here’s the formula, which we can visualize in Excel by clicking on the Formulas tab and
then Trace Precedents.
The expected ending cash balance for January equals the opening cash balance plus or minus
the expected net cash from operating activities, minus the net cash used in investing activities,
plus or minus the net cash from financing activities.
The expected opening cash balance for February
is equal to the expected ending cash balance for January.
Remember to update the spreadsheet with actual numbers once each month has passed.
For two reasons: when you update the actual values the rest of the months are estimated
from a correct starting point, and you can improve your understanding of the actual cash
inflows and outflows versus the forecasted ones by comparing them.
On the cash ending balance, I have applied conditional formatting.
In Excel, you go to the Home tab, then Conditional Formatting, and add rules for Greater Than,
Less Than, and Between.
If you click “Manage Rules”, you see the rules I added.
Expected cash ending balances of $20,000 or more get a green format, this is assumed to
be a safe level of expected cash, although you could argue whether that is really the
case as $20,000 doesn’t even cover half of the monthly payroll
in the second half of the year.
Expected cash ending balances between $0 and $20,000 get a yellow format, this is a risky
level of expected cash, as you are close to an overdraft situation.
Expected cash ending balances below $0 get a red format,
this is where the company is in trouble.
So for the overall year, the company is expected to be fine.
We start in January with $30,000, and expect to end in December with $34,000.
Remember that these are forecasted values in an Excel model, the real world hardly ever
conforms itself to the models that you build.
The actual cash balances over the year could be off by at least a few thousand dollars
due to normal volatility, or off by hundreds of thousands or worse
in case of wild volatility like a pandemic.
Most of the months are expected to be “green”, the company in those months should have some
cash buffer to keep operating.
May is risky, June is in trouble.
This is where the real value of the cash flow forecast comes in: turn it into cash flow
scenario analysis, and cash flow planning!
As the management team of the company, we have to evaluate which actions we can take
in order to keep the company safe in each and every month.
Here are a few suggestions.
Let’s start with scenarios in the cash from operating activities section
of the cash flow forecast.
The revenue pattern by month.
Is it possible to create a more evenly distributed delivery and billing pattern during the quarters.
For example, instead of the 5/5/20 pattern in Q2, can we try to make it 5/10/15?
Same total quarter revenue, but a different split by month.
That would solve the June cash flow issue, but still puts us at risk in May.
It’s worth pursuing!
Alternatively, we could delay the hiring of employee #9 from April to July,
keeping headcount at 8 in all of Q2.
From a cash flow perspective this helps, but delaying the hiring of new employees could
hurt the company in its ability to operate, grow and generate revenue.
How about cash from investing activities?
We could move the CapEx spending from March to July.
This helps the company on short term cash flow, but just like a hiring delay it could
hurt the company in its ability to operate and grow.
Maybe we are better off moving cash outflows in the cash from financing activities section.
Can we negotiate with the bank to move the repayment of the debt by one month,
from June to July?
Or can we agree with the owners of the company
to postpone the April dividend payment to July?
That looks a lot better: July is expected to be a great month in terms of cash inflow
from operating activities, it would be good to match cash outflows
from financing activities to that.
My advice on cash flow planning: it’s best to be on the safe side, as the penalty for
being wrong one way (running out of cash) is not the same as the penalty for being wrong
the other way (having more cash than you expected, which is a good problem to have).