Wall Street Prep

Bottoms-Up Forecasting

Bottoms-Up Forecasting Explained w/ Example Tutorial and Template

Learn Online Now

Bottoms-Up Forecasting

Key Learning Objectives
  • What are the main steps to creating a bottoms-up forecast?
  • Why is it important for management teams to use bottom-up forecasts?
  • What are the advantages / disadvantages of bottoms-up forecasting?
  • What are some examples of different industry-specific metrics often used in forecasts?

Using the insights derived from a robust bottoms-up forecast, the management team of a company can more accurately anticipate revenue in real-time as new data on customer demand and monthly sales come in, as well as predict fluctuations such as cyclicality or seasonality.

Bottoms-up forecasts are considered to be more credible, because the process of arriving at the forecasts takes into account product-level historical financial data as well as findings from the evaluation of ongoing market trends and comparables.

If the actual anticipated financial results of a company end up deviating from initial projections, the company can then assess and understand the reasoning behind why the actual results were below (or exceeded) expectations in order for the proper adjustments to be made.

This can lead to identifying areas of weakness in the business model, which could cause cash flow and profitability problems later down the road unless the issues are addressed promptly.

The fundamentals-oriented approach is thereby viewed as more logical because the thought process behind each assumption can be supported and explained in detail.

Bottoms-Up Forecasting: Advantages vs. Disadvantages

The purpose of a bottoms-up forecast should be to output informative data that leads to decision-making supported by tangible data.

Bottoms-up forecasting models enable management teams to develop a better perception of their business, which precedes improved operational decision-making.

Compared to the top-down approach, the bottoms-up forecast is much more time-consuming, and sometimes, can become even too granular.

The key is being granular enough that assumptions can easily be supported by historical financial data and other supportable findings, but not so granular that the construction and maintenance of the forecast is unsustainable.

Top-Down Forecasting

If a financial model is composed of too many different data points, the model can become inflexible and overly complex (i.e., “less is more”).

For any model to be useful, the level of detail must be properly balanced with the right drivers of revenue identified to effectively serve as the core infrastructure of the model.

Otherwise, the risk of becoming lost in the details is too substantial, which defeats the benefits of forecasting in the first place.

Another potential drawback to bottoms-up forecasting is that the approach increases the probability of receiving scrutiny from outside parties like investors.

While a top-down forecast is broadly oriented around a prediction that the company can capture a certain market share percentage, a bottoms-up forecast leads to setting specific goals and opens up the door for more criticism.

This is inevitable as specificity when setting financial targets tends to be interpreted from stakeholders (or the public) as being more precise – and thus, held to a higher standard with regards to accuracy.

But in general, a bottoms-up forecast is viewed as being far more versatile, as well as more meaningful in terms of how valuable the model-derived insights are.

Bottoms-Up Forecasting: Overview of Formula

Unlike top-down forecasts, bottoms-up forecasting can be driven off an extensive variety of industry-specific assumptions.

However, at its core, all bottoms-up models essentially follow the same base formula:

Bottoms-Up Forecasting Formula

The unit economics used is going to be company-specific, but common examples of metrics used to calculate revenue include:

Industry Price Metrics Quantity Metrics
B2B Software
  • Average Contract Value (“ACV”)
  • Average Revenue Per Account (“ARPA”)
  • Number of Active Accounts (or Leads in Pipeline)
  • Sales Productivity (New Customers Acquired Per Rep)
  • Average Contract Term
Online B2C / D2C Businesses
  • Average Order Value (“AOV”)
  • Average Selling Price (“ASP”)
  • Average Number of Orders Placed (and Products Per Order)
  • Average Number of Orders Per Year
  • Average Daily / Monthly Traffic (and % of Visitors Paying)
E-Commerce Platforms (or Marketplace)
  • Transaction Take Rate %
  • Premium Monthly Fee
  • Gross Merchandise Volume (“GMV”)
  • Number of Active Seller and Buyer Accounts on Platform
In-Person Stores (e.g., Retail)
  • Average Revenue Per Store
  • Average Order Value
  • Sales Per Square Foot
  • Same-Store Sales
  • Number of Open Stores
  • Average Number of Store Sales Representatives
  • Average Number of Products Per Order
  • Paying Customers % of Store Traffic
Trucking Transportation (Freight / Distribution)
  • Revenue Passenger Mile (“RPM”)
  • Average Revenue Per Driver (or Truck)
  • Pricing Rate Per Delivery Request
  • Average Miles Driven Per Hire
  • Number of Available Drivers (or Buses / Trucks)
Airline Industry
  • Average Revenue Per Kilometer (“RPK”)
  • Average Revenue Per Trip
  • Average Booking Fee Per Flight
  • Average Miles Flown Per Month (or Year)
  • Average Number of Passengers Per Flight
  • Number of Licensed Planes
Sales-Oriented Companies (e.g., Enterprise Software Sales, M&A Advisory)
  • Average Deal Size (Dollar Value)
  • Average Commission % Per Closed Deal
  • Number of Deals Closed Per Rep
  • Number of Sales Representatives
Healthcare Sector (e.g., Hospitals, Medical Clinics)
  • Average Patient Fee (Segmented by Type of Medical Procedure)
  • Reimbursement Rates (e.g., Medicare, Medicaid, Managed Medicare / Medicaid, etc.)
  • Treatment Costs for Uninsured Patients
  • Average Length of Stay
  • Average Number of Beds Per Hospital
  • Average Occupancy Rate %
  • Inpatient / Outpatient Mix
Hospitality Industry
  • Average Room Rate (and Booking Fee)
  • Cancellation Fee
  • Average Occupancy Rate %
  • Total Number of Rooms
Subscription-Based Companies (e.g., Streaming Networks)
  • Monthly Subscription Fees (Tier-Based)
  • Average Revenue Per User (“ARPU”)
  • Total Active Subscriber Count
  • Monthly Churn Rates (or Retention Rates)
  • Returning Customers Rate %
Social Media Networking Companies (Advertising-Based)
  • Charged Rate Per Unit of Time
  • Pay-Per-Click (“PPC”) Fee
  • Premium Subscription Fee Per Customer
  • Daily Active Users (“DAUs) or Monthly Active Users (“MAUs)
  • Clicks on Ads Per Account
Services-Based Companies (e.g., Consulting)
  • Average Hourly Billing Rate
  • Average Project Fee
  • Average Project Duration
  • Average Contracted Projects Per Year
Financial Institutions (Traditional, Challenger / Neo Banks)
  • Transaction Fee (% of TPV)
  • Tier-Based Payment Fee
  • Average Dollar Amount Per Lending Agreement (and Pricing Rates)
  • Late Fee Structure
  • Total Payment Volume (“TPV”)
  • Freemium to Paying Customer Conversion %
  • Number of Active Client Accounts

The process of selecting the right metrics to use is similar to that of picking the variables for a Sensitivity Analysis, in which the practitioner must choose relevant variables that have a material impact on the financial performance of the company (or the returns).

Bottoms-Up Forecasting: Template Download

In our example tutorial, the hypothetical scenario used in our bottoms-up forecast is of a direct-to-consumer (“D2C”) company with roughly $60mm in LTM revenue.

The D2C company sells a single product with an ASP ranging around $100-$105 in the trailing three years and a low product count per order (i.e., ~1 to 2 products each order historically).

Additionally, the D2C company be considered as being in the Late-Stage of its developmental lifecycle, as indicated by its sub-20% YoY revenue growth.

To follow along, download the template by filling out the form below:

dl
Submitting ...

Bottoms-Up Forecasting: Revenue Build

Revenue Key Drivers

We begin by identifying the fundamental drivers of revenue for a standard D2C business:

  • Total Number of Orders
  • Average Order Value (AOV)
  • Average Number of Products Per Order
  • Average Selling Price (ASP)

Since we are given the total revenue and the total number of orders for the past three years, we can back out of the estimated average order value (AOV) by dividing the two metrics.

Bottoms-Up Forecasting AOV Finished

For instance, the AOV in 2018 is $160 and this figure grows to approximately $211 by 2020. Note that we are intentionally using the total revenue as opposed to the net revenue, as we do not want the typical order value to be skewed by refunds.

Later on, we will forecast the refund amounts separately. The inclusion of the refund amount in our formula by using net revenue would cause us to make the mistake of double-counting.

Using the provided “Average Number of Products Per Order”, we can then estimate the ASP for each year by:

  • ASP = AOV ÷ Average Number of Products Per Order

Bottoms-Up Forecasting ASP As we can see from the calculation above, the ASP of an individual product comes out to about $100 in 2018, which grows to around $105 in 2020.

Revenue Assumptions Table

Now, we can create assumptions for these drivers with three different scenarios (i.e., Base Case, Upside Case, and Downside Case).

The finished assumption section is shown below. The three variables that we will project are:

  1. Total Number of Order % Growth
  2. Number of Products Per Order % Growth
  3. Change in Average Selling Price (ASP)

Bottoms-Up Forecasting Revenue Assumptions

In practice, the assumptions used should take into account:

  • Historical Growth Rates
  • Comparable Companies’ Forecasts and Pricing Data – Similar to Trading Comps
  • Industry Trends (Tailwinds and Headwinds)
  • Competitive Landscape
  • Industry Research Reports from 3rd Party Sources
  • Estimated Market Sizing (i.e., Sanity Check Assumptions)

With the historical AOVs and ASPs calculated and the forecast of the three drivers ready, we are now prepared for the next step.

Revenue Forecast Build-Up

Since we worked our way down to ASP, we will now work our way back up by starting with forecasting ASP.

Here, we will use the XLOOKUP function in Excel to grab the right growth rate based on the active case selection.

Bottoms-Up Forecasting ASP Calculation

From above, the XLOOKUP formula contains three parts, with each pertaining to three distinct scenarios:

  1. Active Case (e.g., Base, Upside, Downside)
  2. ASP Array for the 3 Cases – Finds the Line w/ the Active Case
  3. Array for the ASP Growth Rate – Matched to the Active Case Cell (and Outputs Value)

Therefore, in the example above for 2021, the ASP growth rate is 2.2% as the active case is switched to the base case.

ASP FormulaThen, the prior year ASP will be multiplied by (1 + growth rate) to arrive at the current year ASP, which comes out to $107.60.

The same XLOOKUP process will be done for the number of products per order.

Alternatively, we could have used the OFFSET / MATCH function.

BF Order Count

In 2020, the average number of products per order was 2.0, and after growing by 9.1% YoY, the number of products per order is now ~2.2 in 2021.

The AOV was excluded from the revenue assumptions section, as this metric will be calculated by:

  • AOV = Average Number of Products Per Order × Average Selling Price

Bottoms-Up Forecasting AOV Formula

Based on this calculation, the projected AOV in 2021 is about $235 (i.e., ASP is $107.60 and each order contains about 2.2 products on average).

To wrap up the revenue projection assumption linkages, we now grow the total number of orders using XLOOKUP again.

Bottoms-Up Forecasting Order Count Projection

And finally, we can forecast the total revenue by using the following formula:

  • Total Revenue = Total Number of Orders × Average Order Value

Bottoms-Up Forecasting Total Revenue Formula

Now, we have all the calculations set for the first projection year, which we can now extrapolate forward for the rest of the forecast.

Net Revenue Calculation

Returning to refunds, which are very common and must be included in models for e-commerce and D2C companies, we simply divide the historical refund amounts by the total revenue.

The refund as a percentage of total revenue comes out to roughly 0.1%-0.2%. As this is an insignificant number, refunds will be straight-lined as shown below.

Bottoms-Up Forecasting Refunds

The projected refund amount will be:

  • Refunds = Total Revenue × (Refunds % of Total Revenue)

Bottoms-Up Forecasting Refunds Calculation

With the refund forecast filled out, we can move onto calculating the net revenue, which accounts for the refunds and avoids double-counting.

Bottoms-Up Forecasting Total Revenue

Bottoms-Up Forecasting: Output Sheet

Interpreting the Growth Metrics

Found below is a screenshot of the finished revenue bottoms-up build:

Bottoms-Up Forecasting Finished Revenue Build

From a glance, the increase in AOV seems to be the main cause of the revenue growth, as seen from the expansion of AOV from $211 in 2020 to $298 by the end of 2025.

Upon a closer look into the same time frame, that 7.2% CAGR of AOV is being driven by the:

  • Average Number of Products Per Order: 2 → 2.6
  • Average Selling Price (ASP): $105 → $116

In closing, we can see that the net revenue of the D2C business is anticipated to grow at a 5-year CAGR of approximately 10% throughout the forecast period.


Additional Resources

Comments
guest
0 Comments
Inline Feedbacks
View all comments
VC Term Sheets & Cap Tables Demystified

Learn Online: Understand the analysis done by venture capital professionals in early-stage investing.

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.