Cash Flow Forecasting

Forecasting the Impact of a Financial Decision

Cash Flow Forecasting - Forecasting the Impact of a Financial Decision

© iStockphoto
Maica

To keep your business healthy, you need to understand your cashflow.

As individuals, we can often "muddle through" if we run out of cash in a month – we quickly arrange a bank loan or overdraft, or put purchases on a credit card.

But the same doesn't apply for businesses, which often struggle – partly because the amounts of money involved are usually much larger, and partly because it's often difficult to raise a lot of money quickly.

Cash is the lifeblood of most businesses. It's the money that you have easy access to, and that you use to pay staff, tax authorities, and suppliers. If your business doesn't have enough cash to pay these people, it may fail within only a few days.

To keep your business healthy, you need to understand how much cash will be coming into it, and ensure that your outgoings are comfortably lower than these cash incomings. Cash flow forecasts (also known as projections) help you do this. They help you make informed decisions about the future of your business, and proactively arrange finance, where this is necessary.

In this article, we'll look at how to forecast cash flows using a spreadsheet.

Note:

This article is intended to help non-financial managers understand cash flow forecasts. Where you are relying on cash flow forecasts for business purposes, ensure that they are prepared by appropriately qualified finance professionals.

About the Tool

Cash moves in and out of a business in three ways:

  1. Operations (for example, by making sales or by purchasing supplies).
  2. Financing (for example, by taking out new loans or by repaying them).
  3. Investing (for example, by selling or purchasing assets).

Cash flow forecasts show how you can expect cash to move through your business in these ways.

With a cash flow forecast, you can:

  • Model a new business or project to check that it's viable.
  • Check that you will have enough cash to pay your staff and suppliers, and cover operating expenses.
  • Anticipate shortfalls in cash and either plan your operations accordingly or arrange finance to cover the shortfall.
  • Determine your borrowing needs and plan for capital investment.
  • Monitor money owing (receivables) and money owed (payables), and manage stock to make the best use of cash.
  • Plan investment strategies to ensure that you can earn the best-possible return on spare cash.

Note:

Cash flow forecasts record money flowing in and out of your business or project over a number of periods of time.

They will not show your reported income, as this is affected by wider factors such as depreciation (how the cost of an asset is allocated by accountants) and amortization (where large payments are spread over several periods of time).

To learn the basics of financial reporting, and to understand more about financial terminology, read our articles on Understanding Accounts: Basic Finance for Non-Financial Managers and Words Used In Financial Accounting.

Setting Up a Cash Flow Forecast

The steps below show how to set up and fill in a projection spreadsheet.

Free "Build a Positive Team" Toolkit

When you join the Mind Tools Club before midnight PST September 27

Find out more

We've based our example on a small, fictitious restaurant called "Dinner's On Us." We've imagined that the managers rent a one-story premises for the restaurant. They have invested $5,000 of their own capital in the business, and they have also received a $2,000 bank loan at the start of the calendar year to finance the purchase of new kitchen equipment.

In our example, at the start of the new calendar year the owners launched a new service, delivering lunch platters to local businesses for meetings and corporate events. They bought a van for these deliveries, with the aim of covering its costs within six months.

Follow the steps below to set up your own cash flow forecast spreadsheet, using our example as a guide.

1. Set Up Column Headings

Use the columns of your spreadsheet to show the time periods over which your forecast will run. Typically, you'll use a column for each month.

Enter the names of these months at the top of each column. (Leave the first column blank, as you will use this for the row labels – see step 2.)

It's good practice to use a title for your spreadsheet that includes the period for which you're forecasting, and to make a note of the currency you're using.

Our example runs for six months; however, most cash flow forecasts run over a longer period – often one, two or three years.

Dinner's On Us – Cash Flow Forecast January 1, 2013 to June 30, 2013
(All values in US$)
Item Jan Feb Mar Apr May Jun
             

2. Set Up Row Titles

Next, set up the row titles.

You will need to organize rows into two main sections:

  • Inflows.
  • Outflows.

Then insert subheadings to show inflows and outflows from the following:

  • Operations.
  • Financing.
  • Investing.
Dinner's On Us – Cash Flow Forecast January 1, 2013 to June 30, 2013
(All values in US$)
Item Jan Feb Mar Apr May Jun
Inflows            
Operations            
Financing            
Investing            
Outflows            
Operations            
Financing            
Investing            

3. Insert Sources of Income and Expenditure

Now, within the sections you've just created, create new rows showing the sources of income and expenditure.

It's a good idea to separate different revenue streams out in your spreadsheet. This gives you the flexibility you need to analyze each revenue stream separately, and to evaluate cash inflows and outflows accordingly. In this example, we have separated cash flows for eat-in meals from those generated by the lunch platters, but you could have any number of revenue streams as part of your business.

In our example, the details are as follows:

  • Inflows.
    • Sales of eat-in meals.
    • Sales of lunch platters.
  • Financing.
    • Loans.
    • Lines of credit.
  • Investing.
    • Investment income received.
  • Outflows.
    • Operations.
      • Ingredients.
      • Staff salaries.
      • Payroll taxes.
      • Publicity.
      • Equipment.
      • Maintenance.
  • Financing.
    • Loan repayments.
  • Investing.
    • Purchase of capital equipment.

Tip:

Think how deeply you need to analyze your costs, and how it's appropriate to group them. Do you need to include separate lines in your spreadsheet for costs due to different suppliers (for example, different suppliers of ingredients) or can they be grouped together under one heading?

It's often worth breaking them down if they are likely to vary considerably, as you may want to use your spreadsheet to see the impact of these varying costs.

4. Insert Totals Rows

Now you'll add rows that will calculate and show the totals for your inflows, outflows, monthly totals, and running totals.

First, insert rows at the end of the inflow and outflow sections, labeling the rows "Total inflows" and "Total outflows" respectively. You can insert a SUM formula to calculate the total of the relevant cells.

Then, create a row showing monthly totals. This will represent the total of the inflow rows minus the total of the outflow rows. This shows your cash profit or loss for the month.

Underneath this, put in a running total (net cash) row. In this row, add your profit or loss for the period to the previous running total. This shows your financial position at the end of the period.

You can see this in the table below:

Dinner's On Us – Cash Flow Forecast January 1, 2013 to June 30, 2013
(All values in US$)
Item Jan Feb Mar Apr May Jun
Inflows            
Operations            
Sales of eat-in meals            
Sales of lunch platters            
Financing            
Loans            
Lines of credit            
Investing            
Investment income            
Total inflows            
Outflows            
Operations            
Ingredients            
Staff salaries            
Payroll taxes            
Publicity            
Equipment            
Maintenance            
Financing            
Loan repayments            
Investing            
Purchase of capital equipment            
Total outflows            
Monthly total            
Net cash (cumulative)            

5. Estimate and Input Values

You now have a spreadsheet with column headings and rows grouped into sections. Next, estimate your costs and sales, and insert them appropriately into the cells.

To keep your estimates realistic, base both projected costs and sales on existing and confirmed data. For example, when estimating projected sales, look at previous years' figures, trial marketing, and detailed market research. When estimating projected costs, look at quoted prices.

You should also include any information that you already have about any expected guaranteed sales, expenses, and financing and investing activities.

These estimations can be difficult if you are a start-up, as you won't have any historical information to use. Use a combination of research and intuition here, and take advice on the amount of leeway you should include for contingencies. As you experience actual operations, your projections will become more reliable.

Note:

When estimating operational outflows, remember that your production costs will vary with revenue. The more you sell, the higher your production costs will be. (In our example, below, you'll see that the cost of ingredients rises when more meals are sold.)

You may also have some fixed costs, such as rent and insurance.

In our example the cost of ingredients, publicity, and maintenance will be variable, while staff salaries, taxes, and equipment will be fixed.

Tip 1:

Include a note of how you have made your estimates. This will help you ensure that you are comparing like with like in the future.

Tip 2:

When you're doing a cash flow forecast for a new business, make sure that you're realistic about when you'll make your first sale. It can sometimes take much longer than you'd initially think!

Tip 3:
You can use cash flow forecasts to think about whether it's worth going ahead with a project or business. See our Bite-Sized Training session on Project Evaluation and Financial Forecasting for more on this.

The table below shows a completed cash flow forecast:

Dinner's On Us – Cash Flow Forecast January 1, 2013 to June 30, 2013
(All values in US$)
  Jan Feb Mar Apr May Jun
Inflows            
Operations            
Sales of eat-in meals (increase 10%/mo for first six months) 6,035 8,500 5,840 5,324 5,856 6,442
Sales of lunch platters (new income) 2,000 2,300 2,310 2,340 2,500 2,500
Financing            
Line of credit (loan) 2,000          
Investing            
Investment income (owner cash contribution) 5,000          
Total inflows 15,035 10,800 81,50 7,664 8,356 8,942
Outflows            
Operations            
Ingredients 1,600 1,800 1,600 1,600 1,600 1,600
Staff salaries 2,000 2,000 2,000 2,000 2,000 2,000
Payroll taxes 400 400 400 400 50 400
Publicity (includes Valentine's Day flyers) 1,200 1,320 600 600 500 600
Equipment 80 88 97 106 117 129
Maintenance (rent of premises + insurance) 1,500 1,500 1,500 1,500 1,500 1,500
Financing            
Loan repayments   500 500 500 500  
Investing            
Purchase of capital equipment 7,200          
Total outflows 13,980 7,608 6,697 6,706 6,267 6,229
Monthly total 1,055 3,192 3,001 958 2,089 2,713
Net cash (cumulative) 1,055 4,247 7,248 8,206 10,295 13,008

6. Calculate and Analyze

You can use your completed cash flow forecast to explore the consequences of different business situations. What If Analysis, Scenario Analysis, and Monte Carlo analysis can help you here.

In our example, investment in the new kitchen equipment and delivery van proved worthwhile. The managers of the restaurant were able to meet the repayments of the loan and cover the costs of the van out of their incomings.

Tip 1:

Cash flow forecasting and similar tools such as Cost/Benefit Analysis are relatively simple techniques for checking a project's viability.

Where large sums are involved, project evaluation can become a difficult and sophisticated art that uses complex techniques. To read more about this, consult Richard Brealey and Stewart Myers' book, Principles of Corporate Finance.

Tip 2:

Some people like to create best-case, worst-case, and most-probable-case cash flow forecasts to help guide their financial decisions.

Key Points

Cash flow forecasts are important tools for ensuring that you have sufficient cash in the period ahead, and for investigating whether a project or business is viable. These spreadsheets allow you to experiment with changing factors to see the impact that these changes will have.

You can set up cash flow forecasts in the following stages:

  1. Set out column headings for periods – normally months – during the forecast.
  2. Set out groups of rows for inflow and outflow from operations, financing and investing.
  3. Insert sources of income and expenditure to show how cash enters and leaves the business.
  4. Insert space for inflows and outflow totals, monthly totals, and running totals.
  5. Estimate and insert values within cells. Ideally, you should do this from real data or from formal market research. If this is not possible, then you will have to use the best estimates you can make with the information that you have.
  6. Calculate and analyze values. This can include changing key values (such as sales) to understand the impact of possible changes on cash flow.