Building a bottom up financial model can provide invaluable insights into a business. By forecasting the business from the ground up, you gain a detailed understanding of what is driving growth and profitability. This allows for smarter strategic decisions to be made
Constructing a bottom up model does require time and effort But the payoff is well worth it. This step-by-step guide will walk you through the process
What is a Bottom Up Model?
A bottom up financial model forecasts the business starting from its core components This is done by analyzing historical data to project future performance.
The model works up from individual business drivers. For example, revenue may be projected by looking at number of units sold, price per unit, etc. Costs are built up line-by-line in detail. The projections are then aggregated to complete the three financial statements – income statement, balance sheet, and cash flow statement.
This is in contrast to a top down model, which works from high-level assumptions downward. While top down models can provide a quick and dirty analysis, bottom up models offer greater precision and insights.
Why Build a Bottom Up Model?
Here are some of the key benefits of constructing a bottom up financial model:
-
Understand the business drivers – By modeling each line item individually, you gain an intimate understanding of what is driving the business. This allows for smarter decision making.
-
Test scenarios and sensitivities – Bottom up models allow you to flex assumptions and test different scenarios. You can see how output variables are impacted by changes in the input assumptions.
-
Forecast with greater accuracy – Projections are built from the ground up based on historical data, not just high-level guesses. This leads to greater forecasting accuracy.
-
Convince others – The transparency of a bottom up model makes it easier to convince stakeholders. They can clearly see the assumptions and methodology behind the projections.
-
Make better decisions – Strategic decisions can be evaluated through scenario analyses. You can assess the impact to the financial statements and KPIs under different scenarios.
How to Build a Bottom Up Model
Now let’s walk through the step-by-step process for constructing a bottom up financial model.
1. Determine the key drivers
The first step is identifying the key drivers of the business. These are the metrics that have significant impact on financial performance.
Examples include:
- Units sold
- Price per unit
- Utilization rate
- Billable hours
- Store count
Analyze historical financials, operating metrics, and industry research to determine the drivers.
2. Build the revenue model
With the business drivers identified, forecast out revenue line-by-line. Build schedules for each revenue stream based on the respective drivers.
For example, for a manufacturing company:
- Project units sold by product type
- Forecast price per unit changes
- Multiply volume by price forecasts to calculate revenue by product type
- Sum up the revenue streams to total revenue
3. Construct the cost model
Next conduct a similar bottom up build for the major cost line items. Leverage historical margins and ratios to project costs.
Examples:
- Forecast headcount by department to project personnel costs
- Calculate facility costs based on locations and square footage
- Build up COGS forecast from material costs, labor costs, etc.
4. Complete the financial statements
With revenue and costs projected, the rest of the core financials can be completed.
- Calculate EBITDA by subtracting costs from revenue
- Forecast below EBITDA line items like D&A, interest, and taxes
- Build the balance sheet and cash flow statement
Construct any supplementary schedules for KPIs, ratios, sensitivities, etc.
5. Test assumptions and scenarios
Flex input assumptions to see the impact on output variables. Test upside and downside cases along with the base case.
Assess scenarios like price changes, new product launches, cost-cutting initiatives. Evaluate strategic decisions and risks.
6. Refresh and update periodically
Bottom up models don’t end at the initial build. To remain useful, they must be revisited and refreshed periodically as new data comes available.
Update assumptions and rerun the model to keep projections accurate. Maintain discipline to review the model at least quarterly.
Best Practices for Bottom Up Models
Keep these tips in mind when building bottom up financial models:
-
Document assumptions – Label all assumptions clearly so stakeholders can understand the methodology.
-
Link assumptions – Use linking formulas to connect assumptions to revenue and cost projections. This allows for quick scenario testing.
-
Control scenarios centrally – Have a dedicated section to control base, upside, downside scenarios with data validation.
-
Focus on key drivers – Don’t get overwhelmed modeling tiny line items. Focus efforts on the metrics that truly drive performance.
-
Model at appropriate level of detail – Don’t get too granular and complex. But don’t be too high-level either. Find the right balance.
-
Use formulas, not hard-coded values – Formulas allow assumptions to flow through and projections to update dynamically.
-
Build modularly – Construct the model in digestible pieces and bring them together. Break apart tabs by statement, schedule, etc.
-
Error-check – Rigorously error-check to avoid #DIV/0!, #REF, and other errors that break the model.
Bottom Up Modeling Example
Let’s walk through a simple example model for a fictional retail coffee company to see the bottom up approach in action.
Drivers & Assumptions
First we identify some key drivers for the business:
- Number of stores – Impacts total sales, costs
- Revenue per store – Driven by transactions, ticket size
- Cost percentages – COGS, Labor, Rent, etc. as a % of Revenue
We forecast out these core assumptions across the 5-year projection period:
<img src=”https://i.ibb.co/NSRHsL0/image.png” width=”500″ alt=”Assumptions”>
These assumptions are based on historical figures, growth rates, planned initiatives, industry benchmarks, etc.
Next we build up revenue…
Revenue Build
We start with number of stores and multiply by revenue per store to calculate total revenue. The revenue schedule would resemble:
<img src=”https://i.ibb.co/gVMdvwP/image.png” width=”500″ alt=”Revenue”>
Note the assumptions directly link to the calculations. So if we change the assumptions, revenue updates.
Costs & Expenses
We take the revenue projections and apply the cost percentages assumptions to calculate expenses. For example:
COGS = Total Revenue * COGS %
<img src=”https://i.ibb.co/FsKkdnC/image.png” width=”500″ alt=”Expenses”>
Similarly for other operating costs like labor, rent, utilities, etc.
Bringing It Together
The pieces are aggregated into the complete income statement:
<img src=”https://i.ibb.co/gM5pOZk/image.png” width=”500″ alt=”Income Statement”>
Revenue and expenses flow through based on the bottom up projections.
The same approach is applied to build out the balance sheet and cash flow statement. Sensitivity analysis can be conducted around the core value drivers.
This is just a simple example, but it demonstrates the mechanics of a bottom up model. In practice, models incorporate many more detailed assumptions and schedule builds.
Tools for Bottom Up Modeling
Bottom up models are almost always built in Excel. It provides the flexibility to construct interlinked schedules and dynamically update projections.
Many modelers use Excel add-ins to enhance functionality, improve formatting, error-check, and more. Here are some popular options:
- Spreadsheet Inquisition – General modeling and analysis add-in
- AutoMacro – Record macros to automate repetitive Excel tasks
- Spreadsheet Checker – Audits and error-checks spreadsheets
- ClusterSeven – Automates cell styling and sheet formatting
Of course, Excel basics like keyboard shortcuts, functions, and named ranges are also critical skills for efficient modeling.
Bottom Line
Although it requires an upfront investment in time, a bottom up financial model provides a wealth of benefits. Models drive deeper understanding of the business, improve forecasting, and enable smarter decisions to be made.
With the right planning and preparation, any modeler can construct a bottom up projection model for their company. Follow the steps outlined to build it ground up based on business drivers.
While modeling is an art that improves over time, sticking to best practices will set you on the path towards mastering bottom up forecasting. The payoff will be strategic insights that were previously hidden from view.
What is Bottom Up Forecasting?
Bottom Up Forecasting consists of breaking a business apart into the underlying components that ultimately drive its revenue generation, profits, and growth.
Bottom-Up vs. Top-Down Forecasting: What is the Difference?
The purpose of a bottom-up forecast should be to output informative data that leads to decision-making supported by tangible data.
Bottom-up projection models enable management teams to develop a better perception of their business, which precedes improved operational decision-making.
Compared to the top-down forecasting 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.
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 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 by 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.
