How to extend the models for more months, quarters, and years

By default, the models are built by month for either 3 or 5 years. Here's how to extend them if you need to model more periods.

The Starter Model and a few of the free tools are built to cover a time period of up to 47 months 1, summed to quarters and years to cover at least 3 full years, while the Standard Model and the add-ons are structured to cover 61 months, or up to 5 years.

I chose the time periods for a reason: for early-stage entrepreneurs at an idea or pre-seed funding stage, forecasting beyond three years is incredibly difficult, and I believe that the goal of doing a five year forecast - to prove the long-term revenue and earnings potential of the business - is better proved by demonstrating the market size and the potential of the business to gain significant traction in that market. For companies with more visibility or understanding of their business model, a market size-based approach to demonstrating the potential of the business is still valuable, but a five year forecast becomes more viable.

That said, some investors will request five year projections, or even more for more mature businesses, or you may simply want to forecast more time periods, so to meet those requests, all Foresight models are open for editing and can be extended to any time period you desire.2 The process is fairly simple and does not take long, but does require a specific process. Here’s how you do it:

  1. Add in the desired number of additional months in-between the last and second-to-last months in the model by inserting that number of columns on ALL sheets that are built monthly (often Forecast, Statements, and if applicable, Revenue Model, Actuals, Valuation, Actuals + Forecast, and a few more).
  2. Add in the additional number of quarters and years for each of the sheets using the same methodology, between the last quarter and second-to-last quarter, or last year and second-to-last year.
  3. After you’ve added in all the extra columns, it’s time to carry over the formulas. I copy the second-to-last month / quarter/ year and past it over the blank columns, including the last column, and do this on each sheet.
  4. Next, I have to extend any of the calculations that are based on monthly cohorts. In the Starter and the Standard, that is only the debt repayment schedule, which is located on the Forecast and Costs sheet, respectively. On the principal and interest payment schedules, insert inthe same desired number of additional months in between the last and second-last months by inserting that number of rows. After I’ve inserted the rows for both, I copy the formulas down by copying the row of the second-to-last month and pasting it down over the blank rows, including the last month.
  5. Next, I update the Summary by adding in the desired number of years, and copying the formulas over using the same method.
  6. Last, I update the Key Reports by editing each Key Report so that it covers the desired time period. On these, you need to insert the columns a little differently, by highlighting the appropriate cells that you want to move to the right and shifting them to the right the appropriate number of times for the number of time periods you want to add. If you shift over the last period in the report, and then copy over the formulas from the second-to-last period, then the chart will update automatically and you won’t have to edit the source data in each chart.

It’s important that you add in the extra columns on all the sheets before you copy over any formulas, or else the formulas will not work correctly as the linking between sheets will not correspond.

All of that should take you less than 15 minutes with the Starter Model and less than 25 with the Standard Model. Questions, or want me to do it for you, contact me ›

  1. The Starter is built for 47 months, so that you can have a partial year 1 of a couple months or so and still generate full three years of forecasts. The core calculations go for 47 months (or 36 months + max of 11 months for partial year), but all the summaries and reports go for 36 months or 3 years. Feel free to alter the summaries to show that extra year if desired. 

  2. Why not just make all the models five years? Because unnecessary details - and unnecessary projections - have an odd way of skewing what we pay attention to, and often the best path is to simply not project what’s unnecessary. 

Was this helpful? Yes / No

Thanks, I appreciate the feedback.