plura Financial Blog

blog for plurafinancial.com, an online matchmaker between banks & small businesses

How to Create a 13 Week Cash Flow Forecast Model

leave a comment »

-Brandon Hinkle/www.plurafinancial.com

If your company is growing quickly, or just tight on cash, you should prepare a “13 Week Cash Flow Forecast” to avoid running out of cash at any given point.  Why 13 weeks?  Because this ensures monthly AND quarterly debt payments are included.  Beyond 13 weeks is less relevant; the short term is much more predicable than the outer months.  The goal is to protect & predict cash flow.

Step 1:  Gather the required info

  • Pull the company’s checking account statements for the last 3-4 months.  Highlight all recurring expenses/payments for salaries, debt, leases & rent, utilities, insurance, bank fees, distributions, capex, etc.
  • Print out your detailed Accounts Payable (“A/P”) aging report.
  • Print out your detailed Accounts Receivable (“A/R”) aging report.

Step 2: Create an excel spreadsheet that shows the weeks across the top, tracking the inflows & outflows of cash.  You should also create a column for Actual next to Budget each week so that you can compare actual activity to forecasted activity.  For example:

  • Create rows for large sources & uses of cash.  For example, payments from large customers, payments to large vendors, salary expense, debt payments, etc.
  • Enter your regularly scheduled payments (the ones you highlighted in the bank statements) in the weekly outflows column.
  • Enter your Vendor payments when they are scheduled to be paid, per your A/P aging.  When you issue a purchase order (“PO”), enter the to-be-purchased amount into the Budget period you expect to make the payment.
  • Enter your Customer cash receipts when they are to be received, per the A/R aging.
  • Your total inflows less your total outflows equals your net cash flow for the week.
  • You weekly net cash flow less your beginning cash balance = ending cash balance.
  • Your ending cash balance plus your line of credit availability = total liquidity.

Step 3: Beware of common mistakes

  • Keep a running tally of cash & liquidity.  For example, next week’s beginning cash balance should be equal to the cash balance at the end of this week.  If you have a line of credit, be sure to include line draws as a source (inflow) of cash and line paydowns as a “use” (outflow) of cash.  If you have a $1 million line of credit with an $800k balance, you have $200k of availability.  If you borrower another $50k this week, then you’ll only have $150k available next week.  The whole point of maintaining a 13 Week Cashflow Forecast is to protect & predict cash and liquidity so this part is important.
  • Keep your line of credit availability updated, particularly if you’re on a borrowing base.
  • Don’t confuse cash bank balance with book balance.  You can’t pay the payroll with funds in float…
  • Be mindful of slow paying customers that don’t pay on time, don’t assume they will start now.
  • Don’t forgot about open POs.  Just because it’s not on the A/P doesn’t mean it won’t need to be paid; some vendors send invoices late, don’t forget about the open POs.
  • Ensure you can easily track results. Make sure the forecast is constructed with the line items that you can easily track.  Elegant projection models are useless unless the line items correspond to easily accessible data.  This helps easily explain/calculate any variance to the budget.
  • Capex happens.  Build in some cushion incase vehicles break down or roofs leak.
  • Be conservative, there’s no upside to making an aggressive cash flow projection.
  • Be proactive.  It’s always best to seek financing when you don’t need it; utilize free resources such as pluraFinancial or local Small Business Development Centers to help match you with the best banks for your business.

 

Advertisements

Written by entrabanker

March 14, 2012 at 3:50 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: