Get ready for 2021. Save $35 with code BYE2020

Forecast

The Forecast sheet contains the core growth, adoption, conversion, retention, and revenue calculations, expenses, accounting, cash, and key metric calculations.

The Forecast sheet contains the core calculations of all Foresight models. Currently a feature in the Standard Financial Model. The Starter Finacial Model contains a Forecast sheet with a couple key differences:

  • The Starter model excludes the prebuilt revenue model, and any growth and revenue calculations must be built in custom
  • The Starter model excludes the Forecasting Drivers used in many revenue and expense lines in the Forecast model
  • The Starter model excludes a number of detailed calculations around accruals, actuals, revenues by cohort, MRR and ARR, and more.

How to use

The Forecast sheet is where the core calculations of the model resides, and handles revenues, expenses, and much more. It is built around a few core concepts:

  • Link all revenues into the Revenues section, and all expenses into Expenses, use the category dropdowns to categorize the meaning of each line, and the model handles everything else - accounting treatment, revenue recognition, cash impact, etc. - automatically.
  • Create new revenue, growth, or conversion methods however you want, the model is modular and completely customizable. Just remember the above and link them into the financial core through Revenues and Expenses.
  • Edit the category names, and create more, to fit your needs. Anything you add will become available as a dropdown menus in the applicable places.
  • The Forecasting Drivers are available as dropdowns in many places in the model. Any line with a forecast method has a number of prebuilt options that can be applied to that line, and you can select the starting amount, the start date, and the appropriate curve for what you are looking to do. That formula also handles pulling in external data and actual financial results, if those options are being used. Details at Forecasting Drivers ›
  • The Forecast sheet is where the forecast is created, Hooks is where any actual results are linked in, and the model will automatically create rolling forecasts as desired (detailed at Actual and Historical Financials).
  • The Hooks sheet exposes many balance sheet items for manual or custom schedules of many items, and you will see the results on Forecast.

The revenues section on the sheet has a fair bit of complexity built into it so that the model can handle revenue recognition and cash separately. Below is a quick overview of how bookings, billings, and revenues works in that section.

The payroll and expenses section have a fair amount of options built into how you can use them. Below are two overviews on how to use them.

 

The primary sections of the sheet are:

  • Revenues. The prebuilt growth, conversion, retention, and revenue section is detailed at Revenue Model ›
  • Payroll. The primary input area for salary and contractor costs, to calculate those costs as well as headcount.
  • Expenses. The primary input area for all expenses and “expense-similar” cashflows.
  • Categories, Forecast. These set the revenue categories, expense categories, and business segments (used by Breakdown), and reflects the forecast.
  • Categories, Actual & Forecast. This combines any actuals input on Hooks for the period where actuals are entered with the forecast to create the rolling forecasts used in all the summaries, reports, and financial statements. This is also used for the Budget variances, explained at Budget.
  • Forecasting Drivers. A prebuild selection of drivers that can be applied to any growth, revenue, and expense line in the model, simply by selecting a dropdown, and are highly customizable by changing the specific number inputs for each line. They represent a base library of curves to cover one time, monthly recurring, and periodic recurring incidents that stay the same or change over time. Forecasting Drivers ›
  • Recurring Revenue Analysis: Optional, a full build on subscriber/user/etc., showing beginning, new, renewals, churns, end of period, and overall churn, for each of the two channels or conversion steps in the model. A full build on MRR (marginal recurring revenue) and ARR (annualized recurring revenue), done for existing, renewal, new, expansion, contraction, and churned MRR and ARR.
  • Accruals, Taxes, VAT. Optional, for calculation of balance sheet changes from certain expense items, and includes deferred revenues to automatically calculate the balance sheet impact of billings and deferred revenues.
  • Inventory and COGS. Optional. Prebuilt section that uses the inputs regarding inventory from Get Started, any forecast of inventory purchases, and the forecasted cost of sales (if applicable) to calculate new purchases of inventory flowing through work in progress and cost of sales to calculate ending inventory at the end of each period. This is done at a currency level and not a unit level.
  • Debt Repayment. Optional, a cohort-based build that forecasts principal and interest payments from any debt taken out, using the assumptions from Get Started around timing, term, and interest rates.
  • CAC. Optional, calculates acquisition expenses from the expenses entered above. Note, LTV was moved to the Unit Economics sheet.
  • Working Capital Line. Optional. If applicable, calculates a working capital line (or called a bank revolver or revolving line of credit) using assumptions from Get Started.
  • Cap Table. Optional. This is intentionally simple and does not go into the detail of each shareholder. For individual shareholder details and more complicated cap tables, use the Cap Table and Exit Waterfall Tool.
  • Net Revenue by Cohort. Optional. This section aggregates revenues by cohorts for the chart on the Key Reports sheet. Can be deleted if not required, and can delete the corresponding chart, but if used correctly, it’s a powerful way to show how revenues build up annually from the effects of acquisitions, retention and churn.

Of all of the additional sections, the most important is the Forecasting Methods, which is heavily leveraged through the Forecast sheet as well as on Pricing and Pipeline.

How it works

By far the most important sheet in the model, it’s also the most detailed, so the documentation on how each section works will be broken out into individual components. The documentation inside the model is detailed within each section, web documentation is getting better

Here’s an overview on the logic behind the Inventory calculations:

Inputs

Revenue model inputs are covered at Revenue Model. Expense inputs are coming soon, right now best documented at Start. The only other inputs are small section-specific inputs under CAC, LTV, Market Size, Net Revenue by Cohort, and Debt Repayment.

Common Modifications

The sheet is completely open for editing and can be customized by users to handle anything you want to add to the model. To help understand the structure, start with:

Questions, support, or customization help, check out Support & Customization.