How to do Cashflow Forecasting

One of the most unpleasant surprises new and fast-growing eCommerce companies face is how quickly they run out of cash.  There are a few culprits here that cause a company with record sales sales to quickly become cash starved:

Financing Purchase Orders:  The biggest drain on cash is having to front funds for for purchase orders of ever-increasing size.  In order to keep having products to sell, you’ve got to order goods 4-6 months ahead of time which is a massive drain on your cash reserves.

Inventory Purchases Aren’t Tax Deductible:  Issue #1 is compounded by the fact that inventory purchases are not expenses that lower your tax bill.  So if you made $200K in profit last year and used it all to buy $200K in inventory, that doesn’t eliminate your profit.

It means you still made $200K in profit and now owe the government a big, fat tax payment without the cash to pay it.  How to avoid running out of cash and getting yourself in a pinch or, worse, doing time with a cellmate named Bubba for missed tax payments?

To the Rescue:  The Cashflow Forecast Model

Salvation lies in something called cashflow forecasting which is the process of projecting your financials into the future understand if/when you may run out of money so you can pro-actively deal with the situation NOW.

In this post I’m going to teach you how to do a proper cashflow forecast.  And because it’s a fairly complex process and probably not your first choice of how to spend an afternoon I’ve put together a model to make the process easier for you.  You can sometimes use Xero or Quickbooks to do this but their tools are often limited and not very customizable.  Hence, our model built specifically for eCommerce sellers.

Cashflow Model Download:  You can download the customizable model here and I’ll walk you through the process of using it below.  The model is read-only in Google Docs so you’ll need to make your own copy in order to edit and play along.

Important:  In the model it’s VERY important that you only change numbers that are blue.  Blue numbers indicate cells that are intended to be changed and customized by you.  They are the assumptions that drive the model.  Black numbers are formulas that should not be edited.  If you do, you will break the model.

This model is intended to serve as a high-level forecasting tool only.  Please chat with your accountant and tax advisor before you make any critical tax, finance or business decisions.

Step 1:  Set Your Assumptions

First, set the assumptions at the top of the model.  Here’s a quick explanation of each:

Cashflow Forecast Assumptions

Cashflow Forecast Assumptions

Product Margin:  This is the margin on your product ONLY.  It’s important to distinguish between your product margin and your entire gross margin which includes variable costs (like shipping, cc fees, etc) because product costs are generally not a cash expense at the time of sale (you pull from inventory) while shipping and credit card fees are a cash expense that happens when you make a sale.

Gross Margin After CC Fees, Shipping etc:  This is your gross margin AFTER taking into account product costs and cash variable costs.  This should be lower than your product margin.  If not, you’ve done something wrong.

State/Federal Tax Rate:  Pretty straight forward, your overall income tax rate that you’ll need to consider for tax payments.

PayPal/Shopify Loan Rev. Repayment Rate:  If you have a revenue-based repayment loan this is the % of revenue is allocated toward loan repayment.

Credit Card Avg. Interest:  The approximate interest rate for your credit card debt.  If you have multiple cards and rates do your guess the blended rate.

Interest Rate on Line of Credit:  The interest rate on any line of credit you have.

Step 2:  Set Beginning Balances

The next step is to input the beginning balance of cash and debt.  This is pretty straightforward, you’ll simply enter your beginning cash balance as well as any outstanding debt balances you have across lines of credit, credit cards or any revenue-based loans.

Beginning Cash Balance

Beginning Cash Balance

Time to pull up an income statement from the last year!  Referring to your past financial performance make your best estimates for the following:

Step 3: Revenue, Overhead and Advertising

Projected Revenue:  Using your current growth rate and any business-related insights project monthly revenue over the course of the coming year.

Advertising Expense:  Project your monthly advertising spend on a monthly basis.  This would include anything you spend for paid traffic, PR, etc.  If you know you ramp up spending at certain times of the year do your best to reflect that in your monthly forecasts vs. cost averaging it across the year.

Fixed Expenses & Overhead: This is how much you spend on anything non-product or fulfillment related.  These are expenses you’d incur even if you didn’t sell a single thing during a month and would include rent, salaries, insurance, etc.  Take a look at your total fixed expenses over the last year, divide by 12 and add in any incremental monthly expenses you anticipate having.

Revenue Forecast

Revenue Forecast

Step 4:  Purchase Order Forecast

This part will probably take the most time.  Looking at your revenue forecasts and considering the specific terms you have with suppliers do your best to project out your purchase order payments to suppliers going forward over the next 12 months.  There’s a specific line item each individual purchase order to help keep them organized over time.

These amounts should include all payments to suppliers as well as fees for customs, importing and shipping.

Purchase Order Forecast

Purchase Order Forecast

At this point you should have a high-level financial model of your financial business.  Before we start using the model to predict the future let’s talk about how it actually works.

Step 5:  Understanding How the Model Works

As mentioned above, the tricky thing about a cashflow forecast is differentiating between expenses on your P&L and actual cash movement in your business.   If you’re doing accrual based accounting (which any inventory-based business should really be doing) these are not the same thing.

Example:  When you run your income statement for July you may see $200,000 in revenue and $60,00 in COGS.  The $200,000 could very well be true cash that entered your business but that $60,000 is NOT a cash expense for July.

Why not?  Because you almost certainly ordered that inventory and paid for it back in April, May or some other time in the business.

It’s correct to account for $60,000 of expense in July because under accrual accounting rules you want to line up your expenses in the same time period as you incurred them.  It’s the right way of looking at your business from a profitability standpoint. But it makes cashflow planning a nightmare.  🙂

Our little model creates a basic financial forecast and uses it to track only activities that impact cash.   At a very high-level here’s how it does it:

  • Starting with beginning cash position 
  • Adjust for financing activities (add new borrowings that creates cash, subtract interest expense and principle repayment)
  • Adjust for operating activities (add revenue, subtract non-product fulfillment costs, advertising, overhead, taxes, dividends)
  • Adjust for inventory purchases (subtract purchase orders and freight/custom expenses)
  • Compute ending cash position

If you have a lot of accounts receivable (perhaps you do wholesale and offer terms) or accounts payable outside of POs you’ll want to add/tweak this model accordingly as it’s not built with those in mind.

Step 6: Evaluating Your Cash Position and Fill Gaps

Now that you have a rough idea of what we’re doing let’s dive-in and see how good (or dire) your cash situation is!

Take a look at your Ending Cash Balance line over the upcoming year.  Does it get worrying low?  Or even go negative?  If so that’s a sign you’ve got some issues coming up and need to take some action.

What to do?

Once you identify an area where you’re short on cash use the ‘New Borrowing’ section to enter additional funds from your desired source.  The model will automatically track the new debt balance, interest payments and impact on cash flow going forward.

Borrowing to Create Cash Flow

Borrowing to Create Cash Flow

Credit Cards:  These should be your absolute last option given their very high interest rates.

PayPal/Shopify Revenue Loans:  While their APR can be very expensive, especially if paid back quickly, these loans usually require no personal guarantee and can be received quickly.  The downside is that their APR can be high, especially if paid back quickly, and they are repaid from a fixed % of revenue which means it has the potential to starve your company for cash.  PayPal and Shopify offer this loans as well as many others including companies like ClearCo.

Letter of Credit:  Issued from a bank these can be good options for short-term financing needs.

Note that while Revenue-based loans from Shopify/PayPal include the interest and principle as one payment, credit card and line of credit payments in the model go only toward interest.  If you want to work to pay down the balance you’ll need to indicate that in them model in the respective section below.

Debt Repayments Cashflow

Debt Repayments Cashflow

Renegotiating Supplier Terms

One financing option that often gets overlooked is negotiating better terms with your supplier.  I know lots of owners who have been able to grow their business more quickly and rely on less financing by simply negotiating better payment terms with their suppliers.

Sean Frank, from Ridge wallet who I interviewed here on the podcast, negotiated 180 day terms with their supplier which allowed them enough time to sell through the ordered goods before their POs were due!

That’s a pretty exceptional case but there’s often room for better terms with suppliers, especially if you’ve worked with them for a while and have built up some trust and rapport.

Getting More Help from Here

Hopefully this helps give you a sense of your cash needs for the next 12 months and made the process a bit less painful!

If you’re interested in improving your cash position, forecasting and accounting chops even more you should consider joining us inside the eCommerceFuel Community.  We’re the world’s largest group of 7- and 8-figure store owners.

Inside you’ll find hundreds of veteran store owners eager to help with things like:

If that sounds interesting and you own a 7- or 8-figure business you can apply to join us right here.

Andrew Youderian

Post by Andrew Youderian

Andrew is the founder of eCommerceFuel and has been building eCommerce businesses ever since gleefully leaving the corporate world in 2008.  Join him and 1,000+ vetted 7- and 8-figure store owners inside the eCommerceFuel Community.

Similar Posts