How to Build a Cash Flow Forecast in Excel

A Cash Flow Forecast should be your core business tool, 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 create a monthly cash flow forecast in Excel.

Download my Cash Flow Forecast Excel Template to follow the examples used in the steps listed.

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 it will come instrumental when presenting your business plan to potential investors.

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. Make sure you divide the assumptions into sections such as general, revenues, costs, etc.

List only the key drivers of your business model. Group smaller, 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 Excel Financial Model Template for an example of how to use an assumption book in a Cash Flow Forecast.

Step 2: Create a Monthly Cash Flow Model in Excel

Build a Cash Flow Forecast model in a logical, transparent and easy to follow way. Start with the timeline on top of a separate Excel sheet and then divide the calculations into individual sections. Make sure you clearly present Revenues, General Costs, Salaries, and Capital Expenditures. Include relevant drivers such as monthly sales volumes or headcount at the top of the relevant section.

Remember to link all the business drivers to your Assumptions sheet. The calculation of the growth in your revenues will be easy to follow if you include the customer numbers on top of the revenue part. To calculate the income, simply multiply them by a monthly revenue assumption.

Be careful not to overcomplicate your growth curve. It is better to use linear trend forecasting and divide the business growth into different periods than to have an overly complicated growth algorithm. Focus on conveying the key growth drivers of your model and their impact on the Cash Flow Projections in as simple a way as possible.

Use Freeze Panes functions in the view menu to fix the columns and rows to make the Excel sheets easy to navigate. Assumptions may change over time (e.g. acquisition costs) and should be displayed monthly on top of the relevant section.

My Excel Financial 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.

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

There are just a few most useful Excel formulas needed to create a Cash Flow Forecast. If you know SUM, IF, SUMIFS, and COUNTIFS formulas, you are ready to create an effective financial model.

The IF formula allows you to change the outcome of your Excel calculation depending on the assumption used in a given period. The SUMIF and COUNTIF formulas will come in handy when you need to create summaries of the financial numbers. Separate Year and Month rows in your timeline to you can use them as the formula criteria.

Assume that a person receiving the financial model has a moderate level of knowledge of Excel. It’s better to spend time explaining 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 Excel Cash Flow Model Template to see an example of how to use 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. To aid the presentation, create graphs to visualise the figures. Use the primary axis of the graph to show how the revenues and cumulative forecast cash flow evolve. Add your sales or customer numbers to the secondary axis to show their impact on the cash flow. Copy and paste graphs into your PowerPoint presentation as images using 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 the Cash Flow Forecast sheet of your Excel workbook. 

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

The Use of the Funds sheet in my Excel Cash Flow Model Template is an example of how to calculate the equity financing need and how the money raised is going to spend.

To calculate the equity financing requirement, deduct the cumulative forecast costs up to the break-even point from the forecast revenues. The result will give you a funding requirement figure. 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 Excel Cash Flow Model Template is an example of the level of return a given tranche or series of equity investments is likely to generate. The calculation will help you to estimate the value of the business in the event of a sale or an IPO.

The calculations in the template provided 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 risk of investment in later years when cash flow is more predictable, and the business carries less risk of investment.

The EBITDA multiplier takes the forecast earnings of the company 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. 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, Break Even points. Make sure to add margin percentage calculations for each income or profit line. 

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

Break-even points show when a business starts being profitable (EBITDA break-even) or when it starts generating cash (Cash Flow break-even). The payback period is an indicator of 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 Excel Financial Model Template has three columns for each business driver. This allows you to iterate between each scenario using a simple switch.

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 have a substantial impact on your projections. 

When you create a Cash Flow Forecast, be prepared to justify your assumptions and answer how you are going to mitigate the risk of deviating costs. In the instance of 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, keep in mind that this will make your cash flow projections more engaging and easier to understand.

Visit my tutorial on How to Create a Project Finance Model in Excel 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.

The P&L statement presents the cash flow projections in a Profit and Loss statement accounting format. The Balance Sheet calculates the monthly balance of Assets and Liabilities. 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. That way 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 Excel Financial Model Template is an example of how to add debt to cash flow 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 so it needs to be reflected in the balance sheet. Remember to deduct it while calculating the exit value of your company. Link the interest and principal repayments to the Profit & Loss, Balance Sheet and Cash Flow Excel sheets of the cash flow forecast model.

Visit my tutorial Financial Model with Debt Funding Excel Template to learn more about how to create a Cash Flow Forecast with debt.

Summary: How to Create a Cash Flow Forecast in Excel

This tutorial has shown you how to use Excel to create a Cash Flow Forecast in Excel. It started with an explanation of how to create a sheet with the main assumptions and how to link them to sales, revenue and costs assumptions. Finally, 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 an Excel 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 is going to 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 Excel Template

Click on the button below to download my Cash Flow Forecast template and an example of How to Create Cash Flow Projections in Excel. The 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 outputs of the cash flow model 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.

Get in Touch

Hi, my name is Jacek and I love Excel and financial modelling. Hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about financial analysis in general or any topic in particular, don’t hesitate to get in touch.

If you would like to book my One-to-One Financial Modelling Crash Course, click here. For more Financial Analysis Tutorials, click here. Or for more info about my Financial Modelling and Cash Flow Forecasting Consulting services, visit this page.

 

Related Posts:

Learn How to Create an Excel Financial Model with Debt Financing – This post explores creating a simple financial model with a more complex debt financing and its impact on the overall equity returns.

Creating a Subscription Model with Churn Calculation – Learn more about building an Excel cash flow forecast for a subscription-based business model with customer churn and lifetime value calculations.

Creating a Capital Investment Plan in Excel – Learn how to combine Capital Investment forecast with addressable market estimates and customer/sales growth. It also includes Revenue, Fixed and Variable Costs and Resource Planning calculations.

Merger and Acquisition Model in Excel -This tutorial shows how to create a cash flow forecast for merger and acquisition financing. In particular, it focuses on debt calculations and the target company’s valuation.

How to Create a Marketing Investment Plan in Excel – An example of how to model marketing cost and link it to customer growth. Also, it shows how to value an investment using a Customer’s Lifetime Value.

How to Create a Project Finance Model in Excel – This tutorial will teach you how to combine a series of project cash flows into one forecast. In addition, it will also show you how to calculate debt and returns on invested equity.

Creating a Real Estate Investment Model in Excel – I discuss projecting growth, revenue and build costs in this post. I also use a real estate business example to show how to add debt to the forecast.

How to Create a Telecom Investment Model in Excel – This tutorial is another example of how to forecast cash flow for a capital-intense infrastructure project. It includes a debt calculation, headcount projections and a return on equity investment estimate.

My Tips on How to Start a Consulting Business from Scratch – If you are starting a new business and need help making your first move, this guide will show you how to build a business and track your revenues and costs with a free Excel Budget Template.

How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel – This step-by-step tutorial will teach you how to use Excel to assign your users into cohorts and analyse their retention, churn and value to your business.

Your First Steps in Excel – Beginner’s Crash Tutorial – If you need to refresh your spreadsheet knowledge quickly, this blog will give you a quick overview of the basics of Excel with examples of calculations, formulas, and pivot tables.