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.
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.
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.
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.
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:
Start your model by making some broad assumptions, i.e., identifying the fundamental drivers of revenue for your business. For example,
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.
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:
Additionally, it helps to make assumptions for three different scenarios: base case, upside case, and downside case, this provides you with all possible outcomes.
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
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!