Cash Flow Forecasting Methods with Assumptions

Welcome to the 4^th lesson of module 6. Henceforth, we’re going to build on the foundation of the cash flow template we laid in the last lesson, “Building a Monthly Cash Flow Tracker.”

In lesson 3, we created an automated cash flow tracker with only two assumptions – the volume growth and the opening cash balance. In this lesson, we’ll try to make the cash flow a little more realistic by introducing seasonality. This is because sales volume does not necessarily increase by 2% every month. There are months we’ll have more sales and months we’ll have fewer sales. We earlier assumed that the 2% growth rate would cover this fluctuating sales volume.

Overall, there are seasons when there are spikes in sales, and the 2% sales volume would become misleading. For example, in the bakery business, there are spikes in sales in December. This assumption may not be consistent with all locations.

This is why you should be sensitive enough to take note of months when there are likely to be spikes in sales for your products. For example, in our FMCG business, we usually notice that sales increase more during the school period than during holidays. This might be the opposite for some businesses, e.g., a hospitality business.

So, in this lesson, we shall introduce assumptions that will distort the cash flow we already built in the previous lesson.

Note that hard-coding is prohibited in our model, so we are using driver-based forecasting. Thus, like Fortune 500 companies, we predict behaviour rather than the actual amount.

The Cash Flow Forecasting Drivers for the Model

We will split our forecast drivers or assumptions into two: inflow or sales drivers, and the outflow or cost drivers. The inflow assumptions would affect the sales volume and price, while the outflow drivers would control the fixed and variable costs.

Both assumptions will only affect the operations cash flow. We will not touch the investing and financing cash flows because they don’t happen often. For startups, it may happen within the first year of the business. For existing businesses, it may happen once or twice a year, depending on growth prospects.

So, we’ll specify values for the following drivers or assumptions:

• Monthly sales volume growth rate.
• Seasonality, volume growth, and price percent.
• Inflation rate/ price hikes (especially during seasons)
• Fixed cost price adjustment due to inflation (especially, logistics costs during seasons or sudden rent increases).

We will always start with our base data using the Sourdough loaf (You can add more products on your own). The base data is:

• Sales volume = 2000 units per month.
• Selling price = 3500 per loaf.
• Variable cost per loaf = 1455.
• Total fixed cost per month = 1,625,000.
• Rent for Bakery = 450,000.

The assumptions that will drive our cash flow forecast are:

• Monthly Volume growth = 2%.
• Seasonality volume growth = 15% (For Month 12 only).
• Cost hike of 3% after 6 months.
• Seasonality cost hike of 7% on the 12^th month.
• Seasonality price hike of 7% on the 12^th month.
• Fixed cost increases:
  • Rent becomes 500,000 on the 9^th month.
  • Other overhead costs increased by 5% on the 12^th month.

With these assumptions, we can now create a realistic cash flow forecasting template.

Creating The Cash Flow Forecasting Template

Using the above assumptions, let’s now update our previous cash flow tracker. Including the above assumptions will make our forecast more realistic and reliable for business decision-making. Follow these steps:

Step 1: Set up the Worksheet

• Open the saved Greenleaf Bakery Financial Model workbook if it is not already open and navigate to the Cashflow worksheet.
• Right-click the Cashflow tab and select Move or Copy… from the context menu.
• In the dialog box that appears, select the Create a copy check box. Under Before sheet:, select Cashflow. Click Ok to duplicate the template. (You’ve created a copy of the cashflow worksheet.)
• Rename the worksheet to ‘Cash flow Forecasting.’
• If you protected the workbook earlier. Go to the Review tab, under Protect, select Unprotect workbook.

Step Two: Set up the Drivers in the Template

• On the cash flow forecasting sheet, add the following in the assumptions section:
  • In cell E2, type Seasonality and enter 15% in cell F2. Select cell F2, on the Review tab, under Comments, select New Comment,
  Then enter “Volume growth in December” in the comment box. In cell E3, type Cost Hike and enter 3% in cell F3. Select cell F3, on the Review tab, under Comments, select New Comment, then enter “costs increase from month 7” in the comment box. In cell H2, type Season Cost and enter 7% in cell I2. Select cell I2, on the Review tab, under Comments, select New Comment, then enter “material costs in December” in the comment box. In cell H3, type Rent Hike and enter 500000 in cell I3. Select cell I3, on the Review tab, under Comments, select New Comment, then enter “starts on the 9th month” in the comment box. In cell K2, type Season Price and enter 7% in cell L2.  Select cell L2, on the Review tab, under Comments, select New Comment,
  • then enter “December product price hike (month 12 only)” in the comment box.
  • In cell K3, type Fixed Costs and enter 5% in cell L3.  Select cell L3, on the Review tab, under Comments, select New Comment then enter “December costs hike (month 12 only)” in the comment box.
  • Select all the labels: Seasonality, Cost Hike, Season Cost, Rent Hike, and Fixed Costs, and bold them.
• Note that you can structure your assumptions section as you like. Use this format only for this lesson.

Now that we have created our assumptions, let’s create the engine of the cash flow template.

Step 3: Create the Automation using Functions

Here, we’ll retain the structure and format of the template and change the functions in the operating activities cash flow section only. Let’s proceed as follows:

1. Volume growth: Here, our formula will ensure that volume grows by 2% from month 2 to 11, and by 15% in month 12.
   1. For month 1, the existing formula remains [=Inputs!D36]
   1. For month 2, use the following formula: =IF(NOT(D5=”Month 12″),PRODUCT(C7,1+$C$2),PRODUCT(C7,(1+$F$2))). This will apply the 2% volume growth to months 2 to 11 and the 15% volume growth to month 12.
   1. For months 3 to 12, use the autofill handle to copy the formula to the other cells.
2. Cash Receipts (Sales): Here, our formula will ensure that the price of our product increases by 7% on the 12th month.
   1. For month 1, use the following formula: =IF(NOT(C5=”Month 12″),PRODUCT(C7,Inputs!$B$36),PRODUCT(C7,(1+$I$2)*Inputs!$B$36)).
   1. For months 2 to 12, use the autofill handle to copy the formula to the other cells.
3. Variable Costs: Here we’ll accommodate two cost changes – the first cost hike of 3%, which takes place from the 7^th month, and the seasonal cost of 7%, which takes place on the 12^th month. To get this right, we’ll use a function that will help us extract the values in the month items. That is, we need to extract 1, 2, … 12 in the Month X line items. This will help us apply operators to determine month ranges. We can use any of the following functions depending on your version of Excel: RIGHT, and TEXTAFTER. Use TEXTAFTER if you are using Excel 365 or Excel 2021 and above. So,
   1. For month 1, use any of the following formulas: =IF(VALUE(RIGHT(C5,LEN(C5)-6))<7,PRODUCT(C7,Inputs!$C$36),IF(VALUE(RIGHT(C5,LEN(C5)-6))<12,PRODUCT(C7,(1+$F$3)*Inputs!$C$36),PRODUCT(C7,(1+$I$2)*Inputs!$C$36))), OR =IF(VALUE(TEXTAFTER(C5,”Month “))<7,PRODUCT(C7,Inputs!$C$36),IF(VALUE(TEXTAFTER(C5,”Month “))<12,PRODUCT(C7,(1+$F$3)*Inputs!$C$36),PRODUCT(C7,(1+$I$2)*Inputs!$C$36))).
   1. For months 2 to 12, use the autofill handle to copy the formula to the other cells.
4. Take note of the following in the above formulas:
   1. LEN() is used to count the number of characters in ‘Month X’
   1. LEN( )-6 is used to subtract ‘Month ‘ from the number of characters. Note that the space is counted.
   1. TEXTAFTER(D5,”Month “) is used to identify ‘Month X’ and subtract ‘Month ‘ from it, leaving only the number, e.g. 1, 2, …, 12.
   1. VALUE( ): Remember that ‘Month X’ is a text. So, this function is used to convert the text item to a number.
   1. RIGHT(C5,LEN(C5)-6) is used to return the rightmost value after ‘Month ‘ has been removed. So, this function does the same thing as the TEXTAFTER function.
5. Fixed Costs: We’ll also use similar functions as used in the Variable Costs calculations. Take note that Rent increased by 50,000 in the 9^th month, and other fixed costs increased by 5% in the 12^th month. So,
   1. For month 1, enter the following formula: =IF(VALUE(TEXTAFTER(C5,”Month “))<9,SUM(tbl_Overheads),IF(VALUE(TEXTAFTER(C5,”Month “))<12,SUM(tbl_Overheads)-Inputs!$B$14+$I$3,SUM($I$3,(SUM(tbl_Overheads)-Inputs!$B$14)*(1+$L$3)))). Here, for months
   1-8 retains the base total overheads. We remove the base rent and add the new rent amount in months 9 – 12. And in month 12,
   1. we increase other fixed costs without rent by 5%.
   1. For months 2 to 12, use the autofill handle to copy the formula to the other cells.
6. Other sections of the cash flow template remain as they are.

Conclusion

This is a sample cash flow forecasting template reflecting business reality. By separating your Assumptions from your Calculations, you create a model that is flexible and alive. You can use this as a model to create your own cash flow forecasting template to reflect the realities of your business.

You can also use the template as a model to create cash flow forecasting templates with different scenarios: base, worst, and best case. To do this, adapt the system we used in Module 5 by creating a Scenario switcher, then different product volumes, costs, and prices based on scenario assumptions. This is your Module 6 Project. Share your scenario-based cash flow forecasting template with your colleagues for critiques, approval, and collaboration. If you have questions or need assistance at each stage of your template, do not hesitate to call our attention via the comments or using the question button.