Inputs, assumptions, processing, and outputs in a cash flow forecast model
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
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:
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.
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.
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.
The output section contains all the important figures we would like to get out of a cash flow forecast model.
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
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.
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.
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
Below is a break down of subject weightings in the FMVA® financial analyst program. As you can see there is a heavy focus on financial modeling, finance, Excel, business valuation, budgeting/forecasting, PowerPoint presentations, accounting and business strategy.
A well rounded financial analyst possesses all of the above skills!
Additional Questions & Answers
CFI is the global institution behind the financial modeling and valuation analyst FMVA® Designation. CFI is on a mission to enable anyone to be a great financial analyst and have a great career path. In order to help you advance your career, CFI has compiled many resources to assist you along the path.
In order to become a great financial analyst, here are some more questions and answers for you to discover:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.