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
Resources on Modeling Financial Statements
We recommend the following websites that lay out considerations for making assumptions for financial forecasting.
- This book chapter entitled Three Statement Financial Modeling provides an overview, designs and layouts, steps to simple financial modelings, and a final checklist.
- Blog on forecasting taxes and interest rates. Scroll down to the bottom of the page where it talks about interest income.
- This Entrepreneur article on Forecasting Revenue and Growth provides details on how to build financial forecasts when you’re just getting your business off the ground.
Approaces
- 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.
- 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
- 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.
- 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:
- Revenue Growth
- Gross Margin
- Operating Margin
- Historical Tax Rate
- Historical Interest Expense Rate
The following list provides more information about calculating the historical statistics.
- Revenue Growth (in %) = (Current Year’s Revenue / Previous year’s revenue) – 1
- 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.
- Operating Margin = Current Year’s Operating Income / Current Year’s Total Revenue
- Historical Tax Rate is the tax rate from the companies previous year’s tax rate.
- Historical Interest Rate is the interest rate coming from the previous year’s Debt Schedule.
Additional Resources on Developing Assumptions
We recommend the following websites that discuss considerations for making assumptions for financial forecasting:
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
- Which account is the opportunity with?
- How many units is the opportunity likely to book/sell?
- 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.
- 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
Additional Resources on Sales Forecasting:
As described in the video, there are several different ways and goals of sales forecasting. The following websites provide more examples of sales forecasting.
Top-Down Sales Forecasting
The model is focused on bookings per salesperson and is divided into four sections:
- Key Seller Assumptions & KPIs
- Sales Hiring Schedule
- Sales Productivity Schedule
- Projected Bookings per Sales Person
Leave a Reply