How to make a cash flow forecast with a custom model

How to do a cash flow forecast

One of the most unpleasant surprises for new and fast-growing e-commerce companies is how quickly they run out of cash. Here are a few culprits that cause a record sales company to quickly starve of cash:

Funding purchase ordersThe biggest drain on cash is raising funds for ever-increasing purchase orders. In order to keep selling products, you have to order products 4-6 months ahead of time, which is a huge drain on your cash reserves.

Purchases of supplies are not tax deductibleProblem #1 is compounded by the fact that inventory purchases are not expenses that reduce your tax bill. So if you made $200k in profit last year and used it all to buy $200k in stock, that doesn’t wipe out your profit.

That means you still made $200K in profit and now owe the government a big, fat tax bill without paying it in cash. How do you avoid running out of cash and not breaking even or, worse, spending time with a roommate named Bubba for missed taxes?

For salvation. cash flow forecasting model

The salvation lies in something called cash flow forecasting, which is the process of projecting your finances into the future to understand if/when you might run out of money so you can proactively deal with the situation NOW.

In this post, I’m going to teach you how to do a proper cash flow forecast. And since it’s a pretty complicated process and probably not your first choice of how to spend an afternoon, I’ve put together a template 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. Therefore, our model is built specifically for e-commerce sellers.

Cashflow Model Download. You can download the customizable template 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 create your own copy to edit and play with.

ImportantIn the model it is VERY important that you only change the numbers that are there Blue. Blue numbers indicate cells that are meant for you to change and customize. Those 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 speak with your accountant and tax advisor before making any important tax, financial or business decisions.

Step 1: Define your assumptions

First, put the assumptions on top of the model. Here’s a quick explanation of each.

Cash Flow Projection Assumptions

Product marginThis is ONLY your product margin. It’s important to distinguish between your product margin and your total gross margin, which includes variable costs (such as shipping, cc fees, etc.), because product costs are typically not a cash expense at the time of sale (you take from inventory) but at the time of shipping and credit. Card charges are cash costs that occur at the time of sale.

Gross margin after CC charges, shipping, etc. This is your gross margin AFTER factoring in product costs and cash variable costs. This should be lower than your product margin. If not, you did something wrong.

State/federal tax rateQuite simply, your total income tax rate is what you need to consider for tax payments.

PayPal/Shopify Credit Rev. repayment rateIf you have an income-based repayment loan, this is the percentage of income that is allocated to repaying the loan.

Credit card average. InterestApproximate interest rate on your credit card debt. If you have multiple cards and rates, guess the mixed rate.

Credit line interest rate. The interest rate on any line of credit you have.

Step 2: Determine the initial balances

The next step is to enter the beginning balance of cash and debt. It’s pretty simple, you just enter your starting cash balance, along with any outstanding debt balances you have between lines of credit, credit cards, or any income-based loans.

Beginning Cash Balance

It’s time to compile last year’s income statement. Looking at your past financial performance, make your best estimates for:

Step 3: Revenue, overhead and advertising

Estimated income. Using your current growth rate and any business insights, project monthly income over the coming year.

Advertising costsProject your monthly advertising spend on a monthly basis. This will include anything you spend on paid traffic, PR, etc. If you know you’re spending more at certain times of the year, do your best to reflect that in your monthly projections versus the average for the year.

Fixed costs and overheadsThis is how much you spend on a product or something related to performance. These are expenses that you would incur even if you didn’t sell anything for a month, and they would include rent, wages, insurance, etc. Look at your total fixed expenses for the last year, divide by 12 and add. any additional monthly expenses you expect.

Revenue forecasting

Step 4: Forecasting the purchase order

This part will probably take the most time. Looking at your revenue projections and considering your specific terms with suppliers, do your best to forecast your purchase order payments to suppliers over the next 12 months. Each individual purchase order has a specific line to help organize them over time.

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

Purchase order forecasting

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: Understand how the model works

As mentioned above, the tricky thing about cash flow forecasting is distinguishing between the expenses on your P&L and the actual cash flow in your business. If you’re doing accrual-based accounting (which any inventory-based business really should be doing), they’re not the same.

For example: When you run your income statement for July, you can see $200,000 in revenue and $60,00 in COGS. The $200,000 may be real cash coming into 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 in April, May, or some other time in business.

It is correct to count the $60,000 expense in July because, according to accrual accounting rules, you want to line up your expenses in the same period as you incurred them. This is the right way to look at your business from a profitability perspective. But it makes cash flow planning a nightmare. 🙂

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

  • Starting with an initial cash position
  • Adjust for financial activities (add new loans that generate cash, deduct interest expense and principal repayment)
  • Adjust for operational activity (add revenue, subtract non-product performance costs, advertising, overhead, taxes, dividends)
  • Adjust inventory purchases (minus purchase orders and shipping/customs charges)
  • Calculate the 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 them in mind.

Step 6: Assess your cash position and fill in the gaps

Now that you have a rough idea of ​​what we do, let’s dive in and see how good (or terrible) your cash situation is.

Look at your Ending Cash Balance line for the coming year. Does it worry low? Or even negative. If so, it is a sign that you have some problems and need to take some steps.

What to do?

Once you’ve identified an area where you’re short on funds, use the New Loan section to enter additional funds from your desired source. The model will automatically track the new debt balance, interest charges, and impact on future cash flows.

Borrow to generate cash flow

Credit cardsThese should be your absolute last resort given their very high interest rates.

PayPal/Shopify Earnings CreditsAlthough their APR can be very expensive, especially if paid back quickly, these loans usually do not require a personal guarantee and can be obtained quickly. The downside is that their APR can be high, especially if paid back quickly, and they are repaid at a fixed percentage of income, meaning it has the potential to starve your company of cash. PayPal and Shopify offer these loans, as do many others, including companies like ClearCo.

letter of creditIssued by a bank, they can be good options for short-term financing needs.

Note that while income-based loans from Shopify/PayPal include interest and principal as a single payment, credit cards and lines of credit are only interest-only in the model. If you want to work to pay off the balance, you must indicate in the appropriate section below that they are in the model.

Debt Repayments Cash Flows

Review of Provider Terms

One financing option that is often overlooked is negotiating better terms with your supplier. I know many owners who have been able to grow their business faster and rely on less financing simply by 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 gave them plenty of time to sell through the ordered products before their POs expired.

This is a rather exceptional case, but there is often room for better terms with suppliers, especially if you have worked with them for some time and have built up trust and rapport.

Get more help here

We hope this helps you understand your cash needs for the next 12 months and makes the process a little less painful.

If you are interested in improving your cash position, forecasting and accounting reductions, you should consider joining us in the eCommerceFuel community. We are the world’s largest group of 7 and 8 figure shop owners.

Inside, you’ll find hundreds of veteran shop owners willing to help with things like:

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

Andrew Yuderian

Andrew Yuderian’s post

Andrew is the founder of eCommerceFuel and has been building eCommerce businesses since he happily retired from the corporate world in 2008. Join him and the 1000+ verified 7 and 8 figure store owners in the eCommerceFuel community.

Source link