How to Build a Bottom-Up Financial Model in Excel

Financial modeling is a tool that allows you to forecast a business' financial performance into the future. It enables business owners to accurately predict changes in revenue to overcome cash flow issues. The output of a financial model is used for decision-making, for instance, budgeting, raising capital, or divesting assets.

Bottom-up forecasting takes internal company-specific data and works up to project revenue and future company performance. In essence, it works up from the products or services to sales orders in order to determine company revenue. The purpose of a bottom-up model is to help you develop a better perception of your business, which would, in turn, lead to improved decision-making.

Compared to the top-down approach, which works down to revenue from high-level market data, a bottom-up model is incredibly granular. The key is to provide enough detail that any assumptions made can easily be supported by historical financial data. Otherwise, the risk of getting lost in the details becomes too substantial.

Creating a bottom-up financial model from scratch can be complicated, but you should be able to build a working model with the following guidelines.

Proper Layout and Structure

When building any financial model, it's crucial to clearly distinguish between the inputs (assumptions) and outputs (calculations) by color-coding. The three characteristics of an excellent financial model are consistency, efficiency, and clarity. Color-coding your model can help you achieve all three.

Here's a color scheme you can use to differentiate the different data types you're working with.

• Blue for inputs or any hardcoded data like assumptions and historical values
• Black for formulas
• Green for formulas and references to other worksheets

You can also use other conventions like shading cells. Ideally, color coding should be used to highlight important information as too many colors within one spreadsheet could detract from the quality of the financial model.

Building Formulas

With bottom-up models, you’re looking at how certain drivers fuel business growth. For instance, how the number of coffee shop visitors and average order value determines revenue.

Step #1 – Your Company’s Financial History

For a financial model to accurately predict the future, it has to be based on reliable historical data. The more data you have, the more realistic your projections will be, and any inaccuracies in historical data will result in incorrect forecasts.

The metrics you’ll use will depend on your business, but consider including the following data in your model:

• Cost of goods sold
• Selling, General, and Administrative expenses (SG&A)
• EBITDA (Earnings before interest, taxes, depreciation, and amortization)
• Sales revenue

Step #2 Revenue Build Model – Key Drivers

Start your model by making some broad assumptions, i.e., identifying the fundamental drivers of revenue for your business. For example,

• Number of customers who visit your coffee shop in a year
• Percentage annual increase in customers
• Total number of orders in a year
• Average order value (AOV)
• Average number of products per order
• Average selling price (ASP)

Here’s where your historical data comes in handy: you can determine AOV by dividing total revenue in a year by the total number of orders. It’s then possible to estimate the average selling price (ASP) by dividing AOV by the average number of products per order.

Step # 3 Revenue Forecast Assumptions

At their core, all bottom-up financial models follow the same base formula.

Revenue = Price x Quantity

But generally, the unit of economics used will be specific to the type of business. That said, to project future revenue, you’ll need to determine how much your revenue changes each year. For instance, do your customers increase by a certain percentage each year?

In practice, any assumptions you make should consider:

• Historical growth rates
• Market trends
• Competitive landscape
• Estimated market sizing

Additionally, it helps to make assumptions for three different scenarios: base case, upside case, and downside case, this provides you with all possible outcomes.

Step #4 Financial Model Build Up

Now, this is where you work your way up to revenue.

Let’s say your customers increase by X% each year, leading to a Y% change in the volume of orders. With the new volume of orders and average net sales price in place, you can estimate future revenue.

Total Revenue = Total Number of Orders × Average Order Value

At this point, you can extrapolate forward for the rest of the forecast using the % growth rate. You can then subtract the cost of goods sold for each year to determine the gross profit.

Gross Profit = Revenue – Cost of Goods Sold

Assuming your operating expenses like labor, rent, and SG&A increase by a certain percentage each year, you can project future operating expenses. Determine earnings before tax by subtracting operating expenses from your gross profit.

EBITDA = Gross Profit – Total Expenses

Use the tax rate to determine your net income for future years.

Net income = Earnings Before Tax × Tax Rate

Depending on the level of detail of your financial model, you can also include other assumptions such as customer retention rate and churn rate.

Excel Tips and Trips

• Keep the formulas as simple as you can. You can break down complex calculations into steps if need be.
• Color code your inputs and outputs. Use blue for hard-codes and black for formulas.
• Separate assumptions from the rest of the model for clarity
• Use the cell comments function in Excel (shift + F2) to describe any calculations or drivers that need explaining
• Repeat any information that helps new users follow the flow of the model
• Avoid linking to other Excel workbooks unless absolutely necessary

We hope that you now have a better understanding of how to build a bottom-up financial model from scratch. Whether you are the CEO of Fortune 100 company or someone just getting started, we recommend putting together a sound financial model so your decisions aren't made in blind!

As a final tip, be cognizant of your time and skills. If Excel isn’t your forte, consider finding someone to help you. What might cause you many nights of hair pulling frustration, would be a cinch to an experienced financial expert and only take a few hours to build.

If you need advice on building a financial model, we are here to help. Check out our services or contact us today!