Monthly Cash Flow Forecast Model

Monthly Cash Flow Forecast Model

With a rolling monthly cash flow forecast, the number of periods in the forecast remains constant (e.g., 12 months, 18 months, etc.).  The forecast is rolled forward every time there is a month of historical data to input.  Rolling forecasts work best when key cash flow drivers are modeled explicitly and directly drive forecast cash flow inputs.  We’ll look at the structure of a robust and flexible monthly cash flow forecast model for a retail store business in the following sections.

Inputs and Assumptions

Here are five important points to creating a strong input section for a cash flow forecast model:

Image Source: CFI’s FP&A Monthly Cash Flow Course.

1. Key cash flow drivers should be modeled explicitly.

In our example, a retail store business should start with the number of stores it plans to operate each month, then build up from there, based on the number of square feet and sales per square foot.  This will help the business to compute its revenue.

2. Inputs should only need to be input once.

It is important to group all inputs in the assumptions section so users can easily find, add, and modify them.

3. Inputs should be organized logically.

This helps users of the model to quickly understand and update the model when they first jump into it.

4. All model inputs should be of the same color.

Using identical colors for inputs allows users to easily distinguish between inputs and other calculated outputs.  Most financial models use a blue font or yellow shading for inputs, and black font for formulas.

5. Document your sources for model inputs where possible.

Make notes and comments in cells using keyboard shortcut SHIFT + F2 to indicate where you pull the assumptions from.

Processing

The processing section of a cash flow forecast model is located on the right-hand side of the historical results.  All cells in this section should be in formulas.

Image Source: CFI’s FP&A Monthly Forecasting Course.

1. Model calculations and processing should be transparent and easy-to-follow.

Use step-by-step calculations that are short in length. If the formulas are becoming too long, it is always a good practice to break them down into simple steps to allow efficient auditing and updates.

2. Hard-coded calculations should be avoided.

Everything to the right of the historical results should not be hard-coded.  All calculations should draw on explicit input drivers.

3. Put complicated calculations and processing on a separate worksheet.

Keep only the final figures on the output worksheets, and separate long and complicated formulas and calculations on another section of the model or worksheet.

4. Document how and why complicated calculations are structured.

This allows easy usability and audit-ability and brings confidence to the general process. All formulas should be transparent, clear, and well-documented so people can easily understand how the model works.

Outputs

The output section contains all the important figures we would like to get out of a cash flow forecast model.

Image Source: CFI’s FP&A Monthly Financial Modeling Course.

1. Models outputs should be easy to find and understand.

2. Model outputs should be grouped logically in one area.

Outputs are typically placed at the bottom of the cash flow model and grouped together using the Grouping function in Excel.

3. Model outputs should be formula-driven with no hard-coding.

4. Outputs should provide key results to aid decision-making.

Charts and graphs summarize the health of the business, point out any issues that need to be considered or addressed, and make it easy for executive management to understand what is going to happen over the period of the forecast and, thus, make important decisions.

Categories of Cash Flow Forecast

A rolling monthly cash flow forecast can be derived from a balance sheet and income statement driven by explicit inputs.  There are three categories of cash flow forecast:

Operating cash flows forecast

1. Starting with net income from the income statement, add back any non-cash expenses that are included in the income statement such as depreciation from the PP&E breakdown.
2. Adjust for changes in operating assets and liabilities (or working capital). Examples of working capital are trade and other receivables, inventories, and trade and other payables.
3. Forecast working capital using working capital ratios such as receivable days, inventory days, and payable days. For a monthly cash flow forecast, the following ratios should be used:

Monthly accounts receivable = Receivable days 30 * Sales

Monthly accounts payable = Payable days 30 * Cost of sales

Monthly inventory = Inventory days 30 * Cost of sales

Investing cash flows forecast

1. Cash outflows include money invested in property, plant, and equipment (PP&E) in the form of capital expenditures or acquisitions of new businesses.
2. Cash inflows include proceeds from disposals of PP&E or businesses.

Financing cash flows forecast

1. Cash inflows include cash raised by issuing equity or debt.
2. Cash outflows include cash used to repurchase or repay equity or debt, and dividends paid out.

Related resources

CFI is the official global provider of the Financial Modeling and Valuation Analyst (FMVA)® designation. To help you advance your career, check out the additional CFI resources below:

• Financial Modeling Best Practices
• Data Sources in Financial Modeling
• Statement of Cash Flows
• The Ultimate Cash Flow Guide
• See all financial modeling resources