How to Build a Cash Flow Forecast in Microsoft Excel (2024)

Create a Cash Flow Forecast whether you are planning to raise equity financing for a new venture or need to prepare financial projections for a company budget. This step-by-step tutorial will teach you how to build monthly cash flow projections in Microsoft Excel.

Download my Cash Flow Forecast Templateto follow the examples used in the steps listed.

You can alsowatch the video version of the tutorialat the end of this post.

Step 1: List the Business Drivers of Your Cash Flow Forecast

Write down your business drivers and assumptions on the first page of your Cash Flow Forecast. The sheet will become the core of your model and a reference book and will be instrumental when presenting your business plan to potential investors.

First, list all of the assumptions of your business plan in a separate Excel sheet and include a comment column. The comment column will help you list references to the sources and explanations of how you derived your numbers. Then, make sure you divide the assumptions into sections such as general, revenues, costs, etc.

List only the key drivers of your business model. Consequently, group non-significant assumptions into one line (such as ‘Other’). You can then link them to a detailed breakdown on a separate Excel sheet.

Download my Cash Flow Forecast Template for an example of using an assumption book in a Cash Flow Forecast.

Step 2: Create a Monthly Cash Flow Model

Build a Cash Flow Forecast model in a logical, transparent and easy-to-follow way. Firstly, start with the timeline on top of a separate Excel sheet and divide the calculations into sections such as Revenues, Direct and General Costs, Salaries, and Capital Expenditures. Then, include relevant drivers at the top of each section, such as monthly sales volumes or headcount.

Also, remember to link all the business drivers to your Assumptions sheet. For example, include customer projections at the top of the revenue section. Then, to calculate the income, multiply the numbers by the revenue assumption.

Keep the growth curve simple. It’s probably better to use linear trend forecasting and divide the business growth into different periods than to have an overly complicated growth algorithm.

Finally, use Freeze Panes functions in the view menu to fix the columns and rows and make the Excel sheets easy to navigate.

Note that my Cash Flow Model Template assumes that the revenues consist of sign-up fees and monthly recurring income such as subscriptions. You can easily add other fixed sources of income or group them into more categories.

Learn More: Visit my tutorial on How to Create a Subscription Model with Churn Calculation to learn more about User Growth and Revenue Forecast.

Step 3: Use Simple Excel Formulas to Build a Cash Flow Model

A few useful Excel formulas are needed to create a Cash Flow Forecast. You are ready to create an effective financial model if you know SUM, IF, SUMIFS, and COUNTIFS formulas.

For example, the IF formula allows you to change the outcome of your Excel calculation depending on the assumption used in a given period. Consequently, the SUMIF and COUNTIF formulas will be useful when summarising the financial numbers. Separate Year and Month rows in your timeline to use them as the formula criteria.

Finally, assume that a person receiving the financial model has a moderate knowledge of Microsoft Excel. It’s better to explain the logic behind your business model and cash flow projections than to get distracted by clarifying what each formula is trying to achieve.

Download my Cash Flow Model Template to see an example of using multiplications, additions, subtractions and other Excel formulas to create a cash flow forecast. Only more advanced calculations will require advanced formulas such as INDEX or OFFSET.

Step 4: Summarise Cash Flow Projections into Tables and Graphs

Present your Cash Flow Forecast output in an easy-to-follow way. For example, create graphs to visualise the figures to aid the presentation. Use the primary axis of the graph to show how the revenues and cumulative forecast cash flow evolve. Then, add your sales or customer numbers to the secondary axis to show their impact on the cash flow. Finally, copy and paste graphs into your PowerPoint presentation as images using Microsft Excel’s Copy as Picture function.

Add some checks and validations to the Summary sheet. Use SUM and IF formulas to check that your total revenues in the summary sheet are equal to the sum of all revenue rows in your Excel workbook’s Cash Flow Forecast sheet.

Step 5: Forecast Equity Financing Requirement and the Use of Funds

Using the Funds sheet in my Cash Flow Model Template is an example of calculating the equity financing need and how the money raised will be spent.

First, deduct the cumulative forecast costs up to the break-even point from the forecast revenues to calculate the equity financing requirement. The result will give you a funding requirement figure. Then, use the breakdown of the costs to allocate the funding proportionally to the forecast cash flow and projected expenditure. For example, let’s assume that the financing requirement is $100,000, of 50% is the marketing spend. In this case, $50,000 of the investment (i.e. 50%) will fund the marketing.

Step 6: Calculate the Projected Enterprise Value and Equity Returns

The Capital Table calculation in my Cash Flow Model Template exemplifies the level of return a given tranche or series of equity investments is likely to generate. The calculation will help you estimate the business’s value in the event of a sale or an IPO.

The calculations in the template use two methods to calculate the enterprise value and the exit proceeds: Discounted Cash Flow (DCF) and EBITDA Multiplier. The Discounted Cash Flow option uses variable discount rates reflecting the decreased investment risk in later years when cash flow is more predictable and the business carries less investment risk.

On the other hand, the EBITDA multiplier takes the company’s forecast earnings and multiplies it by a numerical factor. Because of that, this valuation method is easier to use and explain to your audience, especially for established businesses with a historical track record. Importantly, the value of the multiplier differs, and it’s usually dependent on the industry. Note that the higher the multiplier, the more impact your final year earnings will have on the final valuation.

You can find examples of EBITDA multipliers by industry here. The first tranche should usually generate higher returns as it is taking more risk in investing in an untested business model.

Step 7: Include the Key Financial Metrics of Your Cash Flow Forecast

When creating cash flow projections, list financial indicators such as NPV, IRR, Equity Financing Requirement, and Break Even points. Make sure to add margin percentage calculations for each income or profit line.

For example, the IRR shows the equity returns in percentage terms generated by the forecast cash flows. The NPV shows the equity return in today’s money, assuming a discount rate reflective of the project’s risk level. Calculate them in Microsoft Excel by using IRR and NPV formulas, respectively.

Finally, break-even points show when a business starts becomingprofitable (EBITDA break-even) or generating cash (Cash Flow break-even). On the other hand, the payback period indicates how quickly a potential investor can recover their investment. Make sure that the margins are comparable to the competitors, or be ready to explain why they differ.

Step 8: Stress Test Your Cash Flow Model with Sensitivity Analysis

Sensitivity analysis helps you find the critical drivers of your financial model and test where your cash flow forecast breaks.The ‘Assumptions’ sheet in my Cash Flow Model Template has three columns for each business driver. Consequently, this allows you to iterate between scenarios using a simple switch.

For example, check how a change in an assumption affects the financial returns and the equity requirement. While a percentage change in staff cost may be insignificant, an increase in the customer acquisition cost can substantially impact your projections.

Finally, be prepared to justify your assumptions and answer how you will mitigate the risk of deviating costs. For example, with marketing costs, you can present data from your previous campaigns or benchmark data showing conversion rates and an average cost per click per industry.

Letting anyone stress-test the drivers of your business model may be nerve-wracking. However, remember that this will make your cash flow projections more engaging and easier to understand.

Learn More: Visit my tutorial onHow to Create a Project Finance Model to learn more about using data tables to test scenarios in a Cash Flow Model.

Step 9: Other Excel Sheets to Include in the Cash Flow Forecast

Don’t forget to include a Profit and Loss, a Balance Sheet and a Cash Flow Statement when you create a cash flow forecast.

For example, the P&L statement presents the cash flow projections in a Profit and Loss statement accounting format. On the other hand, the Balance Sheet calculates the monthly balance of Assets and Liabilities. Finally, the Cash Flow Statement restates the forecast into Operations, Investment, and Financing Cash Flows.

Create links between each sheet. For example, the cash flow forecast model provides numbers for the P&L and Cash Flow Statement sheets, which become the source of numbers for the Balance Sheet. As a result, changes in one part of the sheets automatically update the rest of the workbook.

Step 10: Check the Impact of Debt Financing on Your Forecast Cash Flow

The Debt Model in my Cash Flow Model Template is an example of how to add debt to financial projections. Include debt financing later in the cash flow forecast when the business generates a steady level of profits. Because of the improved security of earnings, debt financing usually comes cheaper than equity financing.

Clearly show the calculations of Interests, Debt Amortisation and Principal Repayments over time. The closing debt balance is a liability that must be reflected in the balance sheet. In addition, remember to reflect the amount while calculating the company’s exit value. Finally, link the interest and principal repayments to the Profit & Loss, Balance Sheet and Cash Flow Excel sheets of the cash flow forecast model.

Learn More: Visit my tutorial Financial Model with Debt Funding Templateto learn more about creating a Cash Flow Forecast with debt.

Summary: How to Create a Cash Flow Forecast in Microsoft Excel

This tutorial has shown you how to use Microsoft Excel to create a Cash Flow Forecast. First, it explained how to create a sheet with the main assumptions and link them to sales, revenue and cost assumptions. Then, it helped you calculate the terminal value and showed how to use it to estimate returns on the invested equity.

Here is the list of steps needed to build the Cash Flow Forecast used in this tutorial:

1. Create a Spreadsheet with Key Drivers of your business plan. Remember to include a comment column explaining the rationale of each assumption.
2. Build a Monthly Cash Flow Forecast on a separate Excel sheet. Link the revenue and cost assumptions to your projections.
3. Use simple Excel formulas for your calculations. Learn using formulas such as SUM, IF, SUMIFS, and COUNTIF.
4. Summarise Your Cash Flow Projections into quarterly or annual views. Add graphs to better understand trends in the forecast.
5. Forecast the Equity Requirement to fund your model. Break down cost categories to explain how the investment will fund your plan.
6. Calculate the Enterprise and Exit Values using your projections. Discount the cash flows to calculate the returns on investment.
7. Add Key Financial Metrics such as NPV, IRR or Cash Flow Break-even. Calculate margins to evaluate the viability of your forecast.
8. Build Scenarios and Test Your Cash Flow model using the Assumptions sheet. Stress test the model to understand your forecast’s breaking points.
9. Include Cash Flow, P&L and Balance Sheet Statements. Interlink the Excel sheets so a change in one affects the output of the other.
10. Test the Impact of Debt Calculations on your Cash Flow Forecast. Note changes in the equity requirement and investment return metrics.

Download: Cash Flow Forecast Template

Click on the button below to download my Cash Flow Forecast template. The Excel file contains the following sheets and functions described in each step of this tutorial:

• Assumptions:Lists the main drivers of the business model. The inputs are linked to the rest of the model and are the foundation of the resulting cash flow projections.
• Sensitivity Analysis: This allows you to set model scenarios and test the impact of the forecast cash flow and returns.
• Cash Flow Model:See the calculations and Excel formulas to understand the relationship between projected sales and forecast revenues, operating costs, and capital expenditures.
• The Use of Funds: Analyses how the forecast investment will be spent.
• Cash Flow, P&L and Balance Sheet: The cash flow model outputs are summarised in separate cash flow, profit and loss, and balance sheet statements.
• Equity Returns:Uses the cash flow projections to calculate the enterprise values and equity returns.
• Debt Model:A debt repayment model with simplified calculations of interest and principal repayments.
• Discounted Cash Flow (DCF):Calculations of the terminal value and Discounted Cash Flow linked to the model’s output.
• Capital Structure: A simulation of a company’s debt and equity structure with returns and share of ownership for each tranche of investment.

The file is in Excel format, but you can convert it to Google Sheets using the instructionshere [external link].

Video: How to Use the Cash Flow Forecast Template

For more details and a step-by-step explanation of using and creating the Cash Flow Forecast Template, watch my video tutorial below:

Get in Touch

Hi, my name is Jacek and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. If you have any questions about creating a Cash Flow Forecast in particular or financial analysis in general, don’t hesitate to get in touch.

Explore my other tutorialsto learn more about financial modelling or data analysis. If you need further support, find out about myOne-to-One TrainingandFinancial Modelling Services.

Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More

Financial Model with Debt Financing – Create a financial model with a more complex debt financing and its impact on the overall equity returns.

Monthly Budget Forecast – Another example of monthly projections of costs and revenue, including a budget conversion into a long-term plan.

Subscription Model with Churn Calculation – Build a subscription-based cash flow model with customer churn and lifetime value calculations.

Capital Investment Plan – Measure the impact of capital investment forecast on the addressable market and customer growth estimates.

Merger and Acquisition Model – Consolidate companies’ cash flows, emphasising synergies, debt calculations and the impact on the overall valuation.

Marketing Investment Plan – Link marketing expenses to customer growth and a cash flow projection.

How to Create a Project Finance Model– Combine a series of project cash flows into one forecast with debt and returns on invested equity calculations.

Real Estate Investment Model – Another example of projecting growth, revenue, and building costs with debt financing.

Telecom Investment Model – Calculate revenue for different product and service types, headcount projections and a return on equity estimate.

How to Start a Consulting Business from Scratch – A tutorial with a spreadsheet to help you track a business’s revenues and costs.

Using Cohort Analysis to Calculate Retention and Churn Rate in Microsoft Excel – Assign users into cohorts and analyse the impact of their retention and churn on a business.

Your First Steps in Microsoft Excel – Beginner’s Crash Tutorial – Refresh your spreadsheet knowledge with this quick basics overview.