How to Create a Simple Cash Flow Forecast using Spreadsheets

Creating a cash flow forecast is a fundamental aspect of managing your business finances. It helps you predict when money will come in and go out, allowing you to plan for future expenses and investments. Furthermore, it is an essential planning tool that investors will seek for visibility into the viability of your business model. 

This guide will walk you through creating a simple  forecast using a spreadsheet, ensuring you have a clear and actionable plan for your financial future. You don’t need complicated software to make your cash flow forecast, follow this guide and you will have a professional spreadsheet up and running in no time! 

Why a Small Business Needs a Simple Cash Flow Forecast

A cashflow forecast is crucial for any small business because it provides a clear picture of your financial health. As a business owner, cash flow is something you must pay attention to, so sharpening this muscle is critically important for your long-term success. Here’s some reasons why having a simple cash flow forecast is essential:

1. Predict Cash Shortfalls
2. Manage Payments and Receivables
3. Support Decision-Making
4. Prepare for Growth
5. Avoid Overdrafts and Penalties
6. Build Investor Confidence
7. Reduce Stress

Building Your First Forecast Using a Spreadsheet

Follow these steps to create your spreadsheet:

Set Up Your Spreadsheet

1. Open a New Spreadsheet:
   • Use your preferred software (Excel, Google Sheets, etc.).
   • Label the first sheet or tab as “Cash Flow Forecast  – Q1 2024”
   • Create a second sheet or tab as “Planning”
2. Tab 1 – Your Forecast
   • The first tab will actually be mostly driven by formulas (more on that later)
   • This will be your management view of your cash flow forecast, which will display weekly cash adjustments (or monthly if you prefer, depending on your business model)
   • To start, in the first row create the following headers:
     • Date: List the dates of your forecast period (e.g., the specific date)
     • Opening Balance: The amount of cash you have at the start of the period.
     • Cash Inflows: Money coming into your business.
     • Cash Outflows: Money going out of your business.
     • Net cash flow: Difference between inflows and outflows.
     • Closing Balance: Cash at the end of the period (Opening Balance + Net cash flow).
3. Tab 2 – Your Planning 
   • This is where the fun happens! Your planning tab will be your worksheet for planning business events that generate revenue and expenses.
   • In the first row, create the following headers:
     • Date: List the dates of your forecast period (e.g., the specific date)
     • Category: List the categories that are common for your business revenue and expenses
     • Revenue and Expenses: LIst the forecast revenue or expense.
     • Notes: Use this column to add notes to help you manage your forecast over time. This spreadsheet will be a living and breathing document, so it’s important to have reference notes so you can remember the “why” behind your adjustments and entries. 

Important Tip: Make sure that you are using the proper format for each column. The date column in particular is very important over time in case you want to sort or filter your data. This will be really handy if you ever want to do forecasts in the future!. 

Populate Planning Data

Before we build the functions that will create your forecast, we need some data. Don’t overthink this step – because the goal is to add and adjust this spreadsheet on a weekly basis. Typically we recommend planning cash flows on a quarterly basis, but this can be done annually or monthly depending on your business needs. If this is your first time building a forecast model, start simple. Use these general principles to enter your data. 

1. Try to create repeating events
   • Rather than plan every nuance, try to create some repeated categories that will help you simplify your model. For some B2B business models (like consulting) you might list a weekly invoice revenue category. Obviously you will be getting checks from clients at different times, so the goal here is to think generally about the revenue that will be coming in. 
   • For other businesses like retail, you might forecast your weekly revenue based on an average.
   • Other common repeating events that can save time and add structure to your forecast:
     • Rent payments
     • Payroll
     • Office Expenses
     • Quarterly Investments into Capital
     • Monthly Utilities and Bills

2. Build out 1-2 quarters of data
   • Once you have mapped your repeating events, start thinking about specific things you know will be happening for your business. 
   • For example – if you are planning on finishing a major project and collecting a large invoice. Or if you know there will be a sale happening at your retail store that will generate a lot of cash. 
   • Use the data columns to help organize and build out the data. 
   • Again, try to emphasize a basic model so you can move onto the next step of building your formulas. 

Create Cash Flow Formulas

1. Create a new tab 3 for DATA Pivot
   • A Pivot Table is one of the wonderful functions available in spreadsheet software
   • Select the columns in your Planning tab, and click Insert  > Create Pivot table if you are using Excel. 
   • The main idea is you want to dynamically calculate your weekly summed total of revenue and expenses.

2. Cash Flow Forecast Formulas
   • Once you have your pivot table assembled, you can use a lookup formula to populate the weekly values in tab 1 for Cash Inflows and Cash Outflows
   • This is the most technical step of the process, but a good exercise in improving your spreadsheet chops. The most common functions will be the VLOOKUP:
     1. Start your formula by typing “=VLOOKUP(“
     2. You will be prompted in Google Sheets and Excel to create your mapping logic. Think about it this way:
        1. Lookup Value: Value to match, in this case it should be the week or month you have summed together in your Pivot Table above. 
        2. Table Array: This is the range of data you will match. Select your pivot table in Tab 3 of your spreadsheet 
        3. Column Index Number: This is the column in your pivot table with your summed total for revenue and expenses. 
        4. Range Lookup: This should always be set to “False” which is an exact match lookup. 

*If you don’t have experience building Pivot Tables or Lookup Functions, use our example sheet below to help get an idea of how this works. 

3. Create the math functions for your Opening Balance, Net Cash Flow, and Closing Balance
   • Now that you have pulled in your Cash Inflow and Outflows, you just need a few more formulas to make your forecast work.
   • Opening Balance: The first row should be entered manually with your current bank account balance for the business (or an approximation) This value should be equal to the Closing Balance on the previous line for each subsequent row. 
   • Net Cash Flow: The summed total of Cash Inflows – Cash Outflows. 
   • Closing Balance: The total of the Opening Balance + Net Cashflow

Review and Adjust

1. Review Your Forecast:
   • Look for any periods where your closing balance is negative. This indicates potential cash shortages.
   • Adjust your inflows or outflows to ensure you maintain a positive cash balance.
2. Adjustments:
   • If you notice a negative balance, consider:
     • Reducing unnecessary expenses
     • Increasing revenue through promotions or additional sales efforts
     • Planning for a loan or investment to cover shortfalls

Example

You can use this spreadsheet file as a starting point to help guide your cash flow forecast. We recommend you spend at least 1 day per week of upkeep time on this file for your business planning. You should make it a habit that you are constantly checking and marking every change to your business cash flow. The key is consistency!

Tips for Maintaining Your cash flow Forecast

• Update Regularly: Keep your forecast up-to-date by recording actual inflows and outflows.
• Monitor Trends: Look for patterns in your cash flow to better predict future cash needs.
• Plan for Contingencies: Set aside a reserve for unexpected expenses or drops in income.

Now, it’s your turn

Creating a cash flow forecast is a straightforward process that can significantly impact your financial planning. By following these steps and regularly updating your forecast, you can ensure your business stays financially healthy and prepared for future growth. Happy forecasting!