Forecasting

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The best computer model to create for your business is the cash flow forecast. In its simplest form, it is the sum of all your normal incomings and outgoings, totalled on a month-by-month basis and extrapolated into the future. This sounds easy, but where do you start? A cash flow, as you would guess, is best calculated on a spreadsheet program, as it takes care of all the adding, subtracting, and carry forwards that are straightforward to do but devilishly easy to make a mistake on.

The next thing to know is that there is an unwritten format and conventions for the way cash flow is laid out and calculated. So don't just rush and start slapping figures anywhere. Accountants and other friends with financial training can help here.

In Figure 18-1 I've assembled a condensed cash flow forecast sample. You can add entries that occur to you that are relevant and particular to your business. To condense the layout I've placed several entries on the same line. The standard convention is to list them individually, line-by-line. By convention, money in is always listed before money out. Whether you include employment tax in the salary or as a separate item is up to you, as long as you don't forget it.

Theoretically, you can run a cash flow forecast forward several years, but the further ahead you extrapolate, the less accurate it will be. Six months ahead is generally considered as far ahead as is realistic. Cash flow forecasts also provide an excellent model for you to see what might happen if you double your staff or move to larger premises (the "what if" scenarios).

If your forecast is going to be close to accurate, it's essential that you allow realistic delays between sales and purchases and the money being received and dispatched. Remember, your staff needs to be paid on the button at the end of each month. Most of your suppliers will give you 30 days' credit.

In a startup situation there are two break-even dates that especially interest investors:

♦ In which month is the firm notionally able to take more money in than it spends?

♦ When will the operation pay its debts and become profitable?

These critical dates are typically referred to by numbers of months. Being able to say, "We first go into profit in month 11 and the operation becomes cash flow positive in month 18" tells investors how long they have to wait before the firm is self-supporting.

Cash Inflow

Month 1

Month 2

External Funding

Sales

Product Sales (New/Upgrades/Maint)

Maintenance, Consultancy, Bespoking

Other Revenue

TOTAL INFLOWS

Cash Outflow

Salaries

Management, Development, Sales, Admin

Product Manufacturing Costs

Media, Packaging, Distribution

Marketing Costs

Origination, Production, Media, Exhibs

Development Costs

Hardware, Software

Premises

Rent, Rates, Insurance

Heating, Electricity, Security

Cleaning/Maintenance

Professional Fees

Audits, Legal (setup/contracts)

Insurance (Business/Empl/Prof Indemn)

Administration Costs

Stationery (Corporate/Office)

Postage/Telephone/Fax/Mobiles

Training/Recruitment

Leases/Entertainment/Travel/PC

Other Costs

Bad Debts, Bank, Interest Charges

TOTAL OUTFLOWS

Net Cashflow

Cumulative Cashflow

Figure 18-1: Sample cash flow forecast worksheet.

Figure 18-1: Sample cash flow forecast worksheet.

You'll have appreciated by now that cash flow forecasts combine both fact and fiction. While the past is known, the future has to be estimated. While outgoings are quantifiable, sales and other forms of income are initially wishful thinking. As such, the cash flow is one of the most useful yet potentially misleading business aids you have.

As your business gets started, or your product is launched, you will be able to supplant fictions increasingly with facts. Update your future forecasts using the current week's figures. In time you'll learn the seasonal trends associated with your product—typically people buy less during vacation months, and sales trail off before an upgrade and temporarily mushroom when it comes out. If you are unfamiliar with these just ask a friend about other software operations. Again, build these fluctuations into the spreadsheet, and remember to include future payments or known contracts that should come in.

Tip When the future is uncertain, it may pay you to have two forecasts, a best case and a worst case.

When you start to introduce variables, your forecast becomes a model. As such, it can be an incredibly valuable and valid tool. Apart from an up-to-date cash flow forecast, you will need a financial model so you can play with impacts of overruns and various levels of sales and know which elements really make the biggest difference. Information on recent sales is the best indication you can get of how future buyers will behave; whether there is likely to be sizeable repeat business; the probable value of updates; and what you can expect to earn from maintenance contracts. You can also analyze niche user profiles.

Cash flows are private documents. The information is sensitive. Never make copies. Never show them to anyone who does not need to know. When they must be presented, they should be presented well. Lay yours out so it is easy for any bank manager to understand.

Was this article helpful?

0 0

Post a comment