Bottom-Up Forecasting

What is “Bottom-Up Forecasting”?

Bottom-up forecasting is a method used by analysts for estimating future revenues and earnings. It centres on looking at units sold and price and making projections for company sales based on this series of  ‘micro-level’ estimates. The context of being ‘bottom up’ is that it is based on building forecasts driven by the small contributing factors to a company’s products and sales. This is rather than taking a ‘top down’ view of the overall market (and assuming a share of market) and then translating it into company revenues.

The basic inputs for a bottom-up projection of revenues are the price/unit and quantity of goods (or services) expected to be sold in each of the projected periods.

To build a bottom-up model it is helpful to look at the potential components in more detail.

Price Input

Most sectors allow forecasting to build up on a series of sales scenarios based on price and unit. The price/unit element is simply the estimated price that a company will charge its customers for a specific product or service. Forecasters can base this on historical pricing and include any variable factors such as inflation or rising/falling costs. A company may have multiple products and multiple prices per product (or per SKU) which can also be included in the model. If the number of products becomes too large and cumbersome to work with in a model, there are other methods (such as average order size) to forecast pricing. Usually the bottom-up sales components can be modelled on a separate excel sheet to the income statement and then linked up.

Here are some examples of ways to model price without looking at it by specific product. For example:

For the Price of Goods (Products)

• Average Selling Price: take an average price for all products or similarly grouped products and apply to units sold
• Average Revenue per Store: it can be simpler to take an average price of all units sold per store and then apply across the revenue channels
• Average Order Value: some sales models are based on forecasting the average price per customer order (typically used for ecommerce price estimates) and then multiplying across the order volumes

For the Price of Services

• Average Contract Value: this uses the average price for select service platforms
• Average Fees per Customer: average price for customers grouped by certain demographics or markets which can capture the variations in the pricing structure
• Average Hourly Billing Rate: the average price based on service provider

Quantity Input

Quantity is the estimated number or average number of units of goods sold or services ordered and delivered. This can be split out by the products, services or customers as best fits the pricing modelling. Often details of the units sold are provided in the company earnings statement. Additional ad hoc details may also be given in company updates and press releases. The number of goods or services sold is typically tracked by customer or by physical channels (i.e. stores). When forecasting bottom up volumes and pricing it is always helpful to cross reference the forecasts with historical sales figures and sense check the total quantities sold versus previous total sales.

Formula

Price x Quantity = Forecasting Revenues

Once the price and quantity inputs have been defined and their growth projected for each of the forecast periods, the gross revenues can be calculated by multiplying price by quantity.

It is important when using the bottom-up forecasting methodology, that the price and quantity inputs are based on actual metrics that are relevant to the company’s business model. The strength of bottom-up forecasts relies on having a detailed starting point in terms of pricing and potential units sold – which requires a good understanding of what the company utilisation rate and capacity is.

Understanding as much of a company’s business cycle, seasonality, customer data, store data, and other factors impacting price and quantity (and updating these inputs based on real time data) is critical in developing a robust and accurate revenue projections model. For example, some retailers may see a high proportion of annual sales in the Holiday season (calendar Q4) so this ought to be reflected in the modelling process. Also some companies may experience higher/lower sales at different points in the economic cycle.

Advantages of a Bottom-Up Model

When price and quantity inputs are developed carefully and are relevant to a company’s business model, then the bottom-up forecast methodology can be a practical and credible tool in forecasting, evaluating and adjusting the projections.

It is a useful methodology for companies that operate in diverse markets with different characteristics. It is a very helpful methodology when a company has a key product or where investors can see details of sales (such as retail or consumer data).  This financial modelling can be helpful in monitoring these data points and monitoring the sales performance.

Disadvantages of a Bottom-Up Model

One of the main disadvantages is that bottom-up modelling can be a time-consuming process particularly when there are multiple products and pricing (or new products/services being launched). There is the potential risk of creating overly detailed and cumbersome forecasts that are not user friendly. Investors do not always want to know the small details of 10-20 different products, but rather a broader view of the overall sales potential.

Example

The table below shows some examples of bottom-up forecasting, download the template and try it yourself:

• Price/unit assumptions – here 3 products (or SKUs) pricing have been forecast to take into account inflation and other annual price adjustments

• Quantity assumptions – the quantity assumptions take the 3 products and show the quantity sold in Store 1 and Store 2

• Product splits – The annual quantity growth rates are now split by product and by store, we can see from the model where the faster (and slower) sales growth is predicted to be

• E-commerce forecasts – this is a calculation using the annual sales figure and an average sales basket. The model allows to manipulate both the volume of orders and the average price of the order. This can then be multiplied to derive the sales from this division.

• Revenues – Total sales for the company can be calculated by taking the total from each forecast division and combining. There can sometimes be costs to factor in, perhaps associated with a particular tax or charging system

• Service forecasts – we can see the same technique used for forecasting services when split by a basic and high-end package

Conclusion

As it is based on actual company price and quantity historical assumptions, the bottom-up revenue forecast is considered a more accurate and realistic methodology. However, it takes time to understand the company’s sales model and develop relevant and accurate price and quantity inputs. If there are time constraints or a large amount of price and sales data, the bottom-up forecasting methodology may prove too cumbersome to use in a high level of detail. Take our financial modeling course, learn to build a balance sheet model & much more.

Additional Resources

Complete Investment Banking Course

Linking Three Financial Statements

Top-down Forecasting

DCF Modeling Guide