Monthly Cash Flow Forecast Model

Forecasting Monthly Cash Flows

The first step to creating a monthly cash flow forecast model is to project your company’s future revenue and expenses. Note that the model assumptions driving the forecast must be based on valid reasoning to justify the projection.

Examples of Cash Flow Drivers

• Average Revenue Per User (ARPU)
• Average Order Value (AOV)
• Average Sale Price (ASP)
• Average Number of Items Per Order

The more existing historical data there is to confirm the validity of the assumptions, the more reliable the forecast becomes.

Early-stage investors usually take the forecasted monthly financials and market sizing estimates of seed-stage start-ups with a grain of salt.

But at the same time, monthly cash flow forecast models are not meant to manage urgent liquidity requirements, as is the case for the thirteen-week cash flow model (TWCF) used in the restructuring of distressed companies.

Variance Analysis

Once the 12-month projections are complete, updates to the existing model are continuously made as new financial data rolls in and are collected internally.

Variance analysis is the difference between two metrics:

1. Expected Performance
2. Actual Performance

The management team of a company should strive to minimize the difference between expected and actual performance, especially as they gain more experience and knowledge of the industry, competition, etc.

Improving the accuracy of cash projections year-over-year is a sign that management is developing a better understanding of operating their company, although there are inevitable circumstances when unexpected events can change a company’s trajectory.

Comparing past projections to actual operating results can improve the accuracy of future projections, especially if management can spot long-term trends and recurring patterns.

Through experience, management can better determine factors that contribute toward outperformance, performance in line with expectations, or underperformance.

Favorable variance refers to when actual performance came in better than originally projected – similar to a positive “earnings surprise”.

But in the case of negative variance, the actual performance was underwhelming and came in below management expectations, similar to a public company missing an earnings per share (EPS) target.

Monthly Cash Flow Forecast – Excel Template

We’ll now move to a modeling exercise, which you can access by filling out the form below.

First Name *

Email *

Submit

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Email provided

Your Download is Ready

Submitting...

Monthly Cash Flow Forecast Model Assumptions

For our monthly cash flow model, we’ll be creating a 12-month forecast model for a small business (SMB).

Coming up with the operating assumptions, which is the most time-consuming portion of the analysis, will not be part of our exercise.

In reality, the numbers input for the “Expected” column would be linked from a granular model that accounts for customer cohorts, pricing plans, customer pipelines, and more.

If that were the case, the figures listed under the “Expected” column would be in black font color, as opposed to blue, to reflect the fact that they’re linkages to another tab within the model.

Since building a comprehensive model and then defending each assumption is not realistic for a simplistic modeling exercise like ours, we’ll instead hardcode each projected figure.

But first, we need to set up the monthly structure for our model, which we’ll accomplish using “=MONTH(1)” for January, and then “=EOMONTH(Prior Cell,1) for each subsequent month until we reach December.

For each month, we’ll split up the financials between two columns titled:

1. Expected
2. Actual

The model assumptions for the forecasted performance have been listed in the following sections:

Monthly Expected Cash Receipts

• Cash Revenue: $125,000 Per Month
• Accounts Receivables (A/R) Collection: $45,000 Per Month
• Interest Income: $10,000 Per Month

The concept of revenues and cash receipts is similar, but revenues are recorded on the income statement under accrual accounting reporting standards while cash receipts are based on cash-based accounting.

Cash receipts directly increase the total cash amount recorded on the balance sheet, but revenue can be earned but recognized as accounts receivable (A/R) instead of as “revenue” on the income statement, for example.

Monthly Expected Cash Disbursements

• Inventory Purchase: $40,000 Per Month
• Capital Expenditures (CapEx): $10,000 Per Month
• Employee Wages: $25,000 Per Month
• Marketing Costs: $8,000 Per Month
• Office Rent: $5,000 Per Month
• Utilities: $2,000 Per Month
• Income Taxes: $85,000 @ Quarter End (4x Per Year)

Tying all of the assumptions together, total cash receipts are expected to be $180,000 each month.

As for cash disbursements, the expected monthly expenses are $90,000. However, in the months when taxes are due, cash expenses increase to $175,000. Note that even for small businesses, this sort of tax treatment is a simplification and is NOT meant to reflect reality by any means (i.e. different rules by jurisdiction, local/regional taxes, real estate taxes, etc.).

Monthly Cash Flow Forecast Model Example

Next, we’ll populate the columns titled “Actual” with the assumptions shown below.

For cash receipts, expected performance was understated by $16,000 each month ($196,000 vs. $180,000).

Conversely, the cash disbursements were also understated – but in the case of expenses – higher values have a negative impact on cash flow and reduce profitability.

In non-tax paying months, expenses were $105,800 each month when the projected amount was $90,000, which comes out to a difference of $15,800.

And for the tax-paying months, monthly expenses are $190,800, versus expectations of $175,000.

The “Net Change in Cash” is calculated at the bottom by adding the “Total Cash Receipts” to the “Total Cash Disbursements”.

• Expected Net Change in Cash (Non-Tax Months): $90,000
• Actual Net Change in Cash (Non-Tax Months): $90,200

For the months in which taxes are paid:

• Expected Net Change in Cash (Tax Months): $5,000
• Actual Net Change in Cash (Tax Months): $5,200

The monthly variance across the entire forecast is $200, which reflects a very accurate estimation given the minimal difference between the expected and actual performance.

As a recommended modeling best practice, we’ve calculated the totals for the Year 2022, for which we use the “SUMIF” Excel function to add the relevant figures.

Monthly ➞ Annual Excel Formula

“=SUMIF (Range of Expected and Actual Columns, “Expected” or “Actual” Criteria, Range of Values to SUM)”

Here, we can see the summarized sources of the variations, as well as the offsetting factors.

For instance, cash revenues were understated by 20%, A/R collection was overstated by 20%, and there were no surprises in the amount of interest income received (i.e. fixed income).

Regarding the cash outflows, the higher disbursements directly connected to higher revenue generation (i.e. variable costs) like inventory purchases, CapEx, and employee wages, which were 20% higher than anticipated.

Marketing expenses were relatively aligned with management expectations and were 10% higher than the original forecast.

Fixed costs such as office rent and utility bills were held constant, as well as income taxes, since the applicable tax rate is known and can be estimated upfront as new sales figures come in.

The expected net change in cash for 2022 was off by only $2,400, or 0.3%, in favor of the company – meaning there is more cash on hand for the company than originally forecasted.