Modeling

• refers to using certain inputs and using those inputs to predict how a business metric will perform. The time duration of your forecast will depend on the type of business and the business metric:
  • Startups – usually forecast 6-12 months out
  • Established companies – usually forecast out a few years
  • Sales bookings – a few months out

Approaces

1. Top-Down Approach – takes a macro approach to forecasting. It is less credible and typically adopted when there’s limited historical data.
   • You start with the best estimate of the larger size of the market narrowing it down to identify the portion of the market that the company is serving
   • Then estimate what it will take to capture that portion of the market.
2. Bottom-Up Approach – takes a micro approach to forecasting. Its based on assumptions. However, unlike a top-down approach, it is built on previously attained numbers. These numbers are specific to how the company has performed, and not generalizations that relate to the market as a whole.
   • This approach starts by looking at historical data. The more data you have, the better. But often, even as little as six months to one year of data is used in this approach.
   • The model forecasts are based on this data to make assumptions about how the key metrics will behave, and then we forecast out the revenue based on these assumptions.

Components of a Forecast Model

1. Inputs or Drivers: These are the inputs that drive the output of the model and can include a combination of historical data, assumptions, and scenario analysis.
2. Outputs: This is the metric being forecasted within the model.

Historical Data and Assumptions

Historical Data – about what your performance metrics show for the past.

Formulas for Calculating Historical Financial Metrics

Typically, the historical statistics or metrics used to forecast financial metrics in an Income Statement are:

1. Revenue Growth
2. Gross Margin
3. Operating Margin
4. Historical Tax Rate
5. Historical Interest Expense Rate

The following list provides more information about calculating the historical statistics.

1. Revenue Growth (in %) = (Current Year’s Revenue / Previous year’s revenue) – 1
2. Gross Margin = 1 – (Current Year’s Cost of COGS / Current Year’s Total Revenue)

Keep in mind the two terms COGS and Cost of Revenue can be used interchangeably.

1. Operating Margin = Current Year’s Operating Income / Current Year’s Total Revenue
2. Historical Tax Rate is the tax rate from the companies previous year’s tax rate.
3. Historical Interest Rate is the interest rate coming from the previous year’s Debt Schedule.

---

Sales Forecasting

Bottom-Up Sales Forecasting

Sales metrics track the conversions from prospects into sales bookings. The layers of the funnel are:

• Prospects
• Leads
• Qualified Leads
• Conversion/Bookings

1. Which account is the opportunity with?
2. How many units is the opportunity likely to book/sell?
3. Based on the duration of the sales contract, what would sales look like if they are spread out on a month-to-month basis? This provides the booking forecast.
4. How likely is the probability of winning this opportunity? This provides a weighted bookings forecast.

Assumptions and KPIs

Here are the formulas used in the sales forecasting example described in the video above.

• Contract Terms = Number of months in the contract
• Price per Unit (by mon) = Units needed in 1 month X Price per Unit
• Bookings Forecast = Price per Unit X Contract Term (month)
• Closed/Won Probability = Probability of Closing the deal
• Weighted Bookings Forecast = Bookings Forecast * Closed/Won Probability

Top-Down Sales Forecasting

The model is focused on bookings per salesperson and is divided into four sections:

1. Key Seller Assumptions & KPIs
2. Sales Hiring Schedule
3. Sales Productivity Schedule
4. Projected Bookings per Sales Person