How to Create A Cash Flow Forecast – Part 2

Of all the metrics, ratios and reports you need to manage your business, watching cash is the most important. However, as mentioned in my first post on this subject, there is not one great way to have this information with the standard financial reports. Creating a custom spreadsheet to track cash will give you clarity, not on taxable income, but where your cash actually goes.

Most of the reports you receive from your accountant are next to worthless. If you are obligated to have an audit or review performed, you have my condolences.

Paying for a financial statement audit or review is little more than another tax on your business. The reports are fairly standard and the notes to the financial statements are so arcane that only other accountants know what they mean.

For this reason, many business owners (and their financial partners) find no to little value in the notes about future lease agreements, subsequent events, loan payments or a discussion about categorizing leases.

These financial reports simply do not effectively communicate what business owners need to know.

You are not concerned about taxability nor are you concerned whether the outflow is displayed on the balance sheet, profit and loss or statement of cash flows. Your concern is cash so we construct this report uniquely. This report will not align with either the book or taxable income of the company. The bottom line of this report is the cash balance, not income.

Track all outgoing cash.

Anything that causes your company to lose cash will show up on this report and will be tracked here.  Common cash outflows not included in other financial reports are: distributions or owner draws, the principal portion of loan payments, and tax payments/deposits.  It does not matter whether these outflows are taxable, expensed, or capital purchases.  It is critical not to confuse the outflows with expenses.

With that disclaimer, let us begin.

Figure out your future cash outflows based on the prior year’s outflows.

Just like you did with the income portion, start the outgoing portion of this report by printing the profit and loss statement by month in the cash basis.  The profit and loss shows the flow of costs through your company and is the best report to start with.  You will use this as a starting point to modify to arrive at your cash flow.

Export your prior year’s month’s profit and loss report to a spreadsheet.

Search for relationships between your variable expenses and sales.

It is often said that there are two types of people in this world: those who can categorize everyone into two types and everyone else!

Expenses are either fixed or variable.  A fixed expense is a flat fee for the business regardless of sales.  Rent is a good example of a fixed expense.  Regardless how much you sell, the rent stays the same.

In contrast, variable expenses change with the sales.  One type of variable expenses are cost of goods sold.  Cost of goods sold (COGS) is the amount of money spent to create a marketable product.  This includes the product inputs and labor.

It makes sense then that if you sell more product, you will spend more to purchase the inputs and pay employees to produce those products.  In this case there is a strong correlation between the sales figures and the costs of those numbers.

Assembling a forecast that has any meaningful information requires an understanding between the relationships in your business.

The most useful part of creating this forecast is for you to understand the relationships between your numbers.

That’s really it.

The amount of money it costs you to create a product as a relationship to your selling price tells you (and your industry) how efficiently you manage your business.  Creating a breakdown of your variable costs and understanding those as a percentage of your revenue is the first step.

Once you have calculated the percentage, apply that to your cashflow.  For example, if you know that the wages to your technical professional represent 30% of service income, you have a great idea of how much cash will go out for the cost of your “goods”.

Review all expense accounts for consistency.

Now that you’ve divided all your cash outflows between variable and fixed, copy all the fixed expenses down below.  These are expenses insulated from the swings in sales volume.  Insurance, rent, repairs, office supplies and utilities are constant regardless of sales.  Copy these costs from one year to the next with usually a slight increase for inflation.

Remove non-cash expenses from your profit and loss.

Depreciation, amortization, accrued and prepaid expenses need to be removed to determine your cash outflow.  Depreciation is a non-cash expense, meaning it impacts your profitability without impacting your cash.  (For a more detailed list of income and expenses impacting your taxes but not your cash, see this article.)  To prepare a cash flow, you don’t need to concern yourself with these expenses.

Remove all interest expense.

Delete interest expenses from your cash expenses.  For loan payments, interest is separated from the principal amount and expensed through the income statement.  The actual cash outflow includes the interest and the required principal portion of the loan payment.

Create a separate section for loan payments.

On a profit and loss, the only part of your loan payments you can deduct is the interest.  Unfortunately, the bank requires you pay the whole amount, not just the interest.  This creates a conundrum for many people.  Where does the other amount go?

The portion of a loan payment that is not interest is principal.  When you pay a loan, you deduct the interest on the profit and loss but subtract the principal from the loan on the balance sheet.

As a business owner you really don’t care about the breakdown because the bank needs the full amount!  You really need to see the amount of cash needed for the loan.

Setting up a separate tab for loan payments solves this problem.

In this example, the Equipment Term Loan #1 is the monthly payment for the equipment.  The payment amount of $1,500 represents principal, interest and any other fees or insurance required on this payment.

Include all loan payments due in one tab.  Total the amounts and link it into the forecast.  This method is a clean way to track all the required loan payments out of your company.

You are dividing your cash outflows into three segments; variable costs, fixed costs and debt service.

You are removing expenses from the profit and loss that do not impact cash.  You have removed depreciation and amortization.  You removed interest expense (but you added it back in when you calculated the debt service with the principal).

At this point you should have a great idea of three cash outflows:

  1. The amount of cash spent for the cost of goods sold in a month (and as a percent of sales).
  2. The remaining fixed expenses of the business.
  3. The amount of cash outflow to financial institutions for loan payments broken down by month.

With these three amounts, you can prepare the cash outflows portion of your cash flows.  Organize your cash outflows by month starting with COGS, followed by expenses, with debt service at the bottom.

A highly simplified example looks something like this:


You have now created the basic flow of cash through your business. Starting with cash receipts, you have systematically traced the flow of cash through your business.  As such, creating a solid forecast with what you know will allow you to estimate your cash balance throughout the year, significantly reducing the stress and anxiety you feel before tracking this.

Most business owners sense this information but aren’t good at communicating it to others.

Understanding the cash flow of your business is critical. Whether it is investors, banks or management, the discipline of creating a report will serve to communicate your business’ cash cycle and allow everyone to sleep better at night.