Most accounting systems have a statement that tells the company what their cash flow has looked like in the past. It describes if you have more or less money at the end of the month and what contributed to that change, which can help with forecasting cash flow.

How does this help with forecasting cash flow? By listing when accounts receivables are due from customers (a positive cash in flow) and when accounts payables is to be paid to vendors (a negative cash out flow).

But many of these reports are too general for forecasting the future because they leave many things out that can affect your cash flow.

Knowing how much money you will have in the bank is a critical element to running any business effectively. Simple models can be created in a spreadsheet to provide a forecast. Data for this model can come from historical information you already have in your financial statements.

Here is a step-by-step process that any business can use to set up a spreadsheet model for forecasting cash flow if your accounting system doesn't generate an accurate report:

Your Inputs​

Many of the inputs for a spreadsheet forecasting cash flow can be found on your profit and loss statement or future budget. As with most systems, putting inaccurate information into the model will result in inaccurate information coming out and render any analysis almost worthless.

Revenue

Enter forecasted monthly revenue in as much detail as possible. This can include volume and price by item or service. If the company has multiple locations, this can be added as an additional metric.

These revenue numbers form the basis for the cash flow that eventually will be generated by the company over the selected period of time.

Then subtract:

Cost of Goods (COGS) or Cost of Service (COS) 

This can include all variable costs that are tied to selling an item or delivering a service. This is critical to identify since these costs will be listed in accounts payable and will eventually be paid out of the cash that is in the bank.

General and Administrative Expenses

In other words, regular monthly expenses that are not tied to specific sale levels. Again, these will need to be paid out of the any cash that comes into the company.

Effective Tax Rate

This can affect cash-flow forecasting if company taxes are paid quarterly since on top of all the other expenses, the government will need to be sent a check.

Capital Expenditures

List any one-time expected capital expenses that aren't included in general and administrative expenses and when they will be paid.

Working Capital Assumptions

Your working capital assumptions include the average number of days it takes to collect accounts receivables, average days products are in inventory (they are called turns) and the average number of days before paying accounts payable (your vendor terms). Many accounting systems will report these historical numbers.

The inputs should all be calculated on one worksheet. You can make them all a single color for ease of identification, and add comments to each cell that explains where the information came from.

Your Outputs

With correct inputs, the output section has all the numbers for forecasting cash flow monthly. It includes:

1. Net Accrual Income

Subtract the four inputs you listed form the revenue (cost of good sold, general and administrative expenses, one time capital expenditures and tax payments) to get a forecasted net income number on an accrual basis.

2. Working Capital Changes

In order to convert the net accrual income to the expected cash that will be in your bank, add changes in cash. These include:

Change in accounts receivable.

You can calculate this by looking at the revenue forecasted this month and how long, on average, it takes to collect the money. (These are both listed on under inputs.)

Simple models can be created in a spreadsheet to provide a forecast. Data for this model can come from historical information you already have in your financial statements.

For example, a company has $100,000 in forecasted revenue this month and $75,000 last month. On average it takes 30 days to pay. Accounts receivable will then increase $100,000 this month (since your customer will pay next month), but be reduced by $75,000 from those who paid from last month.

In this case, the change in accounts receivable is an increase of $25,000. (Remember, a positive change number is a cash outflow.)

Changes in accounts payable.

This can be calculated by looking at the expenses forecasted this month and how long on average it takes to pay then. (These are listed on the inputs spreadsheet.)

For example, a business has $50,000 in forecasted expenses this month, $35,000 last month and on average it takes 30 days to pay.

Accounts payables will then increase $50,000 this month (since your vendors will be paid next month) but be reduced by $35,000 from those that were paid last month. In this case, the change in accounts payable is an increase of $15,000. (A positive change number is a cash inflow in this case.)

Changes in inventory if the company sells a product.

When new inventory is brought in, it may sit on your shelves instead of being sold right away to a customer—and then it still needs to be paid for.

Inventory's effect on cash flow can be calculated by looking at how long a product stays in inventory before it is sold (turns). For example, if a product only turns six times a year, then any inventory purchased this month with stay in inventory an average 60 days before its sold.

If $30,000 of inventory was purchased in addition to the $100,000 revenue that was forecasted to be sold this month, the change in inventory will be an additional $30,000. (In this case, a positive number is a cash outflow.)

3. Financing Capital 

This is any outside cash added or paid back for the month for banks, credit cards or investors.

If your company took out a loan for $10,000 or used credit cards to pay bills this month, that is a cash inflow.

4. Operating Cash Flow for the Month

This number is calculated by adding the positive or negative changes listed above in working and financing capital from the net accrual income for the month.

5. Expected Bank Balance

Adding the operating cash flow number from the beginning bank balance may give you the expected cash bank balance at the end of the month. Later, both forecasted and actual results can be listed on one worksheet and updated as time progresses.

There are also other automated tools that can be used in forecasting cash flow. If you have questions after you've created your spreadsheet for forecasting cash flow, please ask your accounting adviser.

Read more articles on managing money.

Photo: Getty Images