How to Create a Cash Flow Forecast

Posted on June 11, 2015 By

Cash Flow Forecast

A Cash Flow Forecast is a handy business tool that is rarely discussed.  What is it? It is a spreadsheet that quantifies the total money coming in and going out of your business.  It’s also the secret cash management tool for accountants.

You say, “that’s what I have online banking for!  I don’t need this!”  Except you do need it.

Online banking and current online software does not show the inflows and outflows of FUTURE transactions.  They rarely show any projections.  A monthly budget to actual results report can yield insights, but that is historical information that won’t help you with how much money is needed next week for payroll. Updating a daily cash budget with actual information is more useful.

Note:  A Cash Flow Statement sounds extremely similar.  It is a financial statement that shows cash flow trends over a period of time.  It’s a historical record of what happened according to GAAP.

There is a pretty good chance that you aren’t spending an average amount each day and you aren’t receiving an average amount in the bank.  Nope, it’s more like large lumpy bits of expense and income.  Even burger restaurants make more during their rush time vs. their slow days.  All that money swirling about has a pattern.  A cash flow forecast is one the best ways to track that pattern and spot problems before they start.

Cash Planning

The importance of cash planning cannot be overstated.  Businesses go under when they run out of cash, not income.  Sales don’t mean a thing unless the customer ultimately pays actual money.  Some examples of issues?

  • Credit card payments can take days to clear.  Most merchant service companies have sped up the process so that it doesn’t take six days from the customer purchase to the receipt of cash. Double check and track how long it is actually taking for your merchant services.  A new trend is to delay payment if it isn’t a verified credit card. Check with your vendor to see if that is holding up payments.
  • Not tracking merchant service fees.  That 3.5% fee might be draining your bank account.  Review the fees annually.
  • Non payment of trade accounts. Much more common than most new business owners realize. Be strict with which customers have trade accounts. Don’t be so eager for the sale that you overextend credit. Ask for credit references, call them and check out their credit score.
  • Returns and Refunds. The after Christmas return season can put a dent in your cash flow. Have a sensible return policy that is available to customers.
  • Business Debit Cards. Using debit cards randomly and not tracking the outgoing cash is quite common in micro businesses. Does your accounting software import bank downloads? If it doesn’t, you can also set up Mint or Quicken to do the heavy lifting. This is a primary cause of bank fees and overdrafts.  Don’t let your Starbucks addiction cause $$$ in bank fees.
  • Subscriptions. Software subscriptions, Google AdWords, insurance and other automatic payments can dip a just cash positive bank account into negative territory.
  • Bank Fees. I’ve seen some bank accounts trigger thousands of dollars in over draft fees because the bank account balance was never monitored. Review it daily.

Create a Daily Cash Report

Creating a useful report takes time and effort to maintain. It requires being authentic and real about where your cash is going to and from. I consider it to be almost a true confession for a business owner.

Start off with basic spreadsheet.  Enter some categories:

Incoming Cash

Create rows/categories for each major cash source.

Customers: If there is a customer who is responsible for most of your sales, give them a separate row.

Payment methods: Categories might be:  Cash/Check, Merchant Service, paypal or other payment systems.  They could also be:  Retail, Wholesale, or Trade payments.

Financing or Investors:  Have a separate line item for this.  If you have a revolving credit line, I like to create a sub category showing the balance and the ins and outs of the account.  Hitting bottom on your revolving credit can cause multiple issues.

Subtotal the incoming cash.

Incoming Cash Flow Forecast

Outgoing Cash

Create rows/categories for outgoing cash.

Loan Payments:  These are regular payments that need to happen to maintain a good credit standing. Once you have listed all the past ones, go forward and post all the future ones as well.

Payroll and Payroll Taxes:  This might be the largest, most inflexible cash drain. Make sure you give this proper attention. Know what specific day and amounts the cash will be deducted. After you know the pattern add those amounts to the future columns.

Operating Expenses:  Use this category to post the weekly check run. I usually create a separate row for large vendors.

Cost of Goods Sold: Do you have large payments for Cogs? Usually businesses buy in bulk quantities and sell over a month or more. Make sure those large payments are accounted for.

Insurance:  Large lumpy payments also deserve their own category

Rent:  Enter past payments and future payments.

Fixed Assets/Capital Improvements: Plan your improvements.

Taxes:  Estimated federal, state or local tax payments.

Dividends or Owner Draws: Budgeting these items will help ensure that future funding is available.

Create a subtotal for this section.

Outgoing Cash Flow Forecast

Enter a Beginning Balance

Enter beginning balance and an ending balance.  Apply some basic algebra you have a running cash balance.  Enter the dates across the top and you have a cash flow calendar.

E-PNL-003 Daily Cash Flow Spreadsheet

I also like to add a low cash warning to the spreadsheet:

E-PNL-003 Negative Cash Warning

Monthly or Weekly Cash Reports

If daily cash tracking is too detailed, try weekly or monthly cash flow forecasting.  It won’t specifically show trends, but it can help you keep cash positive and track spending trends.

Monthly Cash Worksheet

 

A little Bit of Work with Tremendous Payoff

Once this very basic data entry becomes a daily task; it will help you plan and focus your energy.  It will be easier to see that the payroll is going to be underfunded, a large tax payment is due, and the operating account is almost negative.  In the heat of the moment, many people make these decisions after the bank account goes negative.  Cash flow is variable, but there is a pattern.  Plan ahead for the dips.

The SBA has some templates you might be interested in:

SBA Link

I have a sample Daily Cash Flow Forecast for sale on Organized Bookkeeping:

Organized Bookkeeping Daily Cash Flow

 

 

Laura Dodson
Follow me

Laura Dodson

Laura Dodson, CPA is a Seattle Financial Planning & Analysis consultant.She has attended Western Washington University, Pierce College and Bates Technical College. She has written four accounting instructional books. She has worked for small family businesses, mid-sized businesses and a Fortune 500 company.She founded and operated Blue Stone Accounting LLC for five years.She currently runs Paper Butterfly Forge LLC.
Laura Dodson
Follow me

Latest posts by Laura Dodson (see all)

Uncategorized     , ,