This week we will take a practical look at creating a Cash Flow Forecast model.

What is a Cash Flow Forecast and Why we need one

The Cash Flow forecast is a crucial planning tool for financial management. It is the process of preparing an estimate of future financial performance, based on anticipated cash disbursements and cash receipts.

Cash Flow forecasting assists us with liquidity management and helps us ensure the company has enough cash to cover obligations without running into funding issues. It also helps us with better managing working capital, forecast covenants calculation, and reduce interest and debt.

We usually create a rolling forecast of cash flows, that covers a 12 or 18-month period and is rolled over with each new month, when we have historical data. We use the newly available data to adjust predictions for future months.

Developing a Cash Flow Forecast and keeping it up to date helps businesses to find new financing or take other steps before they run out of cash. Companies can elect to re-forecast every three months, monthly, or in some cases even every week, especially where cash flow assumptions are highly volatile.

Assumptions

When creating a Cash Flow Forecast, we always start with documenting our assumptions, e.g., expected sales growth, anticipated interest percentage for borrowings, tax percentage, and others.

Some things we need to remember while setting up our assumptions and inputs section are:

• We need to group these in one place and make sure we enter them just once, and this will simplify our model for future adjustments and changes;
• Inputs and assumptions should be visibly separated so external viewers of the model can understand it more clearly and be able to introduce changes and modifications quickly;
• Time passes, and we tend to forget how we decided on specific assumptions, so we should always document the source of our assumptions and the reasoning behind them;
• It is an excellent idea to model key drivers explicitly, e.g., model the pricing tiers for a licensing company and forecast the number of licenses sold from each tear, making the sales forecast a formula of the pricing tiers and volumes, thus ensuring that it’s easy to introduce changes in the model.

Calculations

Here we perform the actual forecasting. When performing our estimates part, we should adhere to the following:

• Should all be formulas based on assumptions and historical data (inputs);
• We need to keep those easy to follow to ensure the model is simple enough for third parties to understand;
• A good idea is to break-down complex calculations into a few steps, thus minimizing the risk of error;
• We can separate complex and extensive calculations with many levels in their sheets and include only the result in the final forecast model, and these should be linked so that the final model reflects all changes in the calculation sheet immediately;
• We should always document why calculations are performed in that manner, as we tend to always forget our reasoning behind such decisions, no matter how obvious it seems when we come up with it;
• We should always avoid hard-coded numbers within our calculations, as these are hard to change and maintain later on.

Presenting the results

After doing all our calculations, we should present the figures we wanted to get out of the estimations. These outputs are the reason to create the model, they must be easy to find and understand, grouped logically in one place, so that third parties can read the model and see the final forecast. We should keep those formula-based as well, no hard-coded values, so that they update anytime we change a calculation logic or some inputs and assumptions. Here is also where we can use charts and graphs to visualize the results and facilitate the process of decision making, that the model is supporting.

Categories of Cash Flow Forecasts

When creating a Cash Flow Forecast, we can generally make three types of estimations:

• Operating Cash Flow Forecast;
• Investing Cash Flow Forecast;
• Financing Cash Flow Forecast.

Operating Cash Flow Forecast

When working on this cash flows projection, we start from Net Income, add back non-cash items (e.g., depreciation, amortization, impairment, and others) and then adjust for changes in working capital.

When calculating our working capital estimation, the best practice is to base the working on Receivables Days, Inventory Days, and Payables Days. As an example:

• Monthly Accounts Receivable = Receivable Days 30 * Sales;
• Monthly Accounts Payable = Payable Days 30 * COGS;
• Monthly Inventory = Inventory Days 30 * COGS.

Investing Cash Flow Forecast

The Investing Cash Flows estimation includes forecasted outflows for CAPEX, acquisitions of business, and others. Inflows will comprise items like sale of assets or disposal of companies.

Financing Cash Flow Forecast

The inflows we estimate for the Financing Cash Flow forecast come from the cash raised by issuing equity or debt instruments. Outflows are from the repurchase of issued capital, settling of debt, or paying dividends.

General Steps to forecast Cash Flows

Let us look at a more basic model structure, that can be used for a small business or for a project to launch a product in a bigger company. Keep in mind that the same principles also apply in more complex models.

Sales revenue forecast

For existing businesses, we would usually base our sales forecast on historical data, while for new companies it is often a better idea to start with the cash outflows and see how much cash we need, and then calculate sales based on that, while taking into consideration the collection periods for credit sales, and expected returns.

Other cash inflows estimation

We also need to consider possible other cash inflows. Some of these can be:

• Additional investments from owners;
• Government or other grants;
• Cash received from royalties or licenses;
• Loans received;
• Sale of assets;
• VAT or other tax reimbursements.

Expenses and COGS forecast

It is a best practice to start with the estimation of the Cost of Goods Sold, based on the planned sales volumes in the revenue forecast. If the business has no production, this can be replaced by the cost per hour to provide services, or others. The other administrative and operating expenses that have to do with the main activity of the business are in the same group with COGS. These include, but are not limited to, wages, payroll expenses, office rent, consulting services, selling expenses, transportation costs, and others. Admin and operating expenses depend highly on the type of business and industry.

Other cash outflows estimation

Apart from the regular running costs for the business, we will most probably have additional cash outflows. Some of those can be:

• Buying assets that are not part of regular activities;
• One-off bank fees, e.g., loan application and utilization fees;
• Repayment of loan principals;
• Payments to owners;
• Investing surplus funds into other businesses.

Tie it all up!

To wrap it all up, we need to remember that the primary purpose of the Cash Flow Forecast is to calculate Cash at Hand at the end of each period (usually for each month). Outlining cash balances can help us catch early on when we expect to have cash shortages. Knowing this in advance can greatly reduce the liquidity risk for the business and provide management with enough time to find a solution.

Example Model

We are going to take a look at a practical model I have prepared to showcase the principles discussed above. You can download the whole Excel working at the end of the article and take a closer look into how it’s structured.

First, we have our Assumptions and Inputs. I usually mark those with the Input cell format, so that they are easy to see, and users can quickly find out what they are expected to provide as input data and assumptions.

We start with Sales, stating our products, the forecasted ratio between cash sales and credit sales, considering that we usually collect credit sales in the month following the sale. We also take into consideration the return percentage, that can be obtained by looking at historical data, or data for the industry.

We should have an Other inflows section, where we can put in additional investments or government grants we expect to qualify for. This is the section where we can include more investment needed in the periods where we cannot generate enough funds to take care of our company obligations. We leave this empty for now and decide if we need to plan for more investments after we have included all the cash outflows. If the investors are unable to provide more funding, we can also look into financing from financial institutions or other government programs we might qualify for.

Moving to Admin and Operating costs, this section is highly specific for different businesses, but in general, we want to put here all expenses that are not directly related to the produced goods or rendered services but are required for the business to operate. This section will usually consist of salaries and payroll taxes for administration employees, advertisement and marketing costs, selling expenses like transport and others.

One of the most specific distinctions between a cash flow statement and an income statement is that one is cash-based and the other is accrual-based. One of the major points here is that purchases of assets or capital expenditures (CAPEX) are presented in full in the cash flow, while they are spread over time in the income statement through depreciation and amortization. When creating a Cash Flow Forecast, we must remember to include the full cost of any CAPEX in the months when we expect to purchase such assets as furniture, hardware, and software.

When we are building a model, it is always a good idea to implement an option to review different scenarios. What I did in this model is I included a multiplier into each section of cash flows, that I can control with some Cash Flow Scenario Modifiers. We also have the starting date of the model here, based on which we calculate the 18-month periods.

Each of those is a drop-down that allows us to select an additional increase/decrease for each section, to look at scenarios based on better or worse performance.

Based on the inputs, assumptions, and the selected cash flow modifiers, we can calculate the Cash Inflows and Cash outflows. Here are the cash inflows from sales revenue and other cash inflow sources. Note that Credit Sales Collection for January sales happens in February, this is due to the one month credit term on our invoices.

We then follow with the cash outflows for production, administrative and selling costs, and CAPEX.

The final step is to calculate Cash at the end of the period. After all, this is the main point of our model. I used some conditional formatting to draw attention to the months where we have the lowest value of cash at the end of the period. The first thing we notice is that in June we will face liquidity issues, as we won’t have enough money to cover our obligations. This is where we will have to figure out how to get more cash before June.

We can now use our Scenario Modifiers to analyze how would various changes in our cash flows affect the bottom line (cash on hand). Raising the sales value with 3% will make sure we won’t face any issues with liquidity, and by the end of the 18 months, we will have built a balance of 32 thousand.

Also looking at a scenario where sales value decreases with 3%, we notice that even after 18 months we won’t have a successful project.

Conclusion

The model we observed was straightforward, and we have barely scratched the surface of cash flow forecasting, but I hope it gives you an idea of how to go about creating your own models. Incorporating scenario handlers from the beginning is a good idea as it will make it much easier to tweak your models once you’ve built them and figure out what you need to do, to get the desired results from the project.

Thank you for reading!

You can download the Excel working below and look into the model in more detail.