Wall Street Prep

Monthly Cash Flow Forecast Model

Learn the Steps to Create Monthly Cash Flow Forecast Models

Learn Online Now

Monthly Cash Flow Forecast Model
Monthly Cash Flow Forecast Model
In This Article
  • What are the benefits to companies in setting up a rolling monthly cash flow forecast model?
  • What types of insights can you get from tracking monthly cash flow forecasts?
  • In Excel, how can monthly forecast models be turned into annual (and multi-year) models?
  • Why is it so important for companies to measure monthly cash flow forecasts?

Monthly Cash Flow Forecast Model Importance

A company’s ability to produce positive cash flows over the long run determines its success (or failure).

The cash flows of a company – in its simplest form – refers to the cash that comes into and out of the company.

Monthly forecasts establish limits on a company’s spending based on income and retained earnings.

The chart below lists some common cash flow drivers:

Cash Inflows (+) Cash Outflows (–)
  • Customer Cash Payments
  • Supplier Payments
  • Collection of Accounts Receivable (A/R)
  • Employee Wages & Benefits
  • Interest Income
  • Utility Bills
  • Sale of Assets (e.g. PP&E)
  • Local, State & Federal Taxes

Monthly Cash Forecast Models vs Financial Statements

Under accrual accounting, public companies must submit filings with the SEC each quarter (10Q) and at the end of their fiscal year (10K).

On the other hand, monthly forecast models are internal tools often used by FP&A professionals or owners of small businesses.

While large, publicly-traded companies will certainly have their own set of internal models updated constantly on a daily (or weekly) basis, our post will focus on providing a basic overview of monthly cash flow models.

Cash-Based Accounting vs Accrual Accounting

One distinction between monthly cash flow forecasts and the financial statements filed by public companies is that the former typically abides by cash accounting.

Using cash-based accounting tends to be more common for smaller, private companies, which have far less sophistication in their business models, financing structures, etc.

  • Cash-Based Accounting: Under cash accounting, recognition of revenues and expenses occurs once cash is received or physically transferred, regardless of whether the product or service was delivered to the customer.
  • Accrual Accounting: For accrual accounting, “earned” revenue (i.e. the associated product/service has been delivered) and the coinciding expenses are recognized in the same period (i.e. the matching principle).

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.

“Rolling” Cash Flow Forecasts

Once the monthly cash flow forecast (and the variance analysis) is complete, the recommended next step is to aggregate the monthly data into an annualized section.

Companies can then assess the current year from a high level, as well as create multi-year projections with the compiled data sets – a long-term process that starts with monthly financial models.

Excel Template Download

Now that we’ve gone over the importance of monthly cash flow forecasts, we’ll create an example forecast.

To download the Excel file, fill out the form linked below:

dl
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.

Monthly Forecast Done

Variance Analysis Example Questions
  • Which neglected factors led to the 20% underestimation of cash revenue?
  • How can our company’s A/R collection processes be improved to fix the current issue ($432k collected vs $540k expected)?
  • While the increases in inventory purchases (COGS) and CapEx are reasonable considering the revenue increase, was the recent spending in-line with historical trends as a percentage of revenue?

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.

Comments
guest
0 Comments
Inline Feedbacks
View all comments
Learn Financial Modeling Online

Everything you need to master financial and valuation modeling: 3-Statement Modeling, DCF, Comps, M&A and LBO.

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.