How to Create a Bottom-Up Financial Model

Introduction

A bottom-up financial model is an analysis tool used to assess the financial performance of a particular business or project. It is mainly used to evaluate capital efficiency and cash flows, and is a great asset to businesses due to its capability to forecast long-term financial objectives. Compared to top-down financial models, bottom-up models consider a wider range of data points and are considered more reliable.

There are various reasons for using bottom-up financial models. One of the most important benefits is their ability to provide a comprehensive and accurate analysis of a business or project due to the fact that they assess a wide range of data points. Moreover, bottom-up models are also able to provide a better understanding of the drivers of a project or business’ financial performance.

Creating a bottom-up financial model can seem like a daunting task for those unfamiliar with the topic. In this blog post, we’ll provide an overview of the steps for creating a bottom-up financial model.

Key Takeaways

• Bottom-up financial models are an analysis tool used to assess the financial performance of a business or project.
• A bottom-up model considers a wider range of data points than a top-down model, making them more reliable.
• Benefits of bottom-up models include providing a comprehensive and accurate analysis and better understanding of financial drivers.
• This guide outlines the steps necessary to properly create a bottom-up financial model.

Step One: Define Your Model Structure

Creating a bottom-up financial model requires a three-step process: outlining the model structure, calculating the output, and interpreting the results. Each step helps create an accurate and dependable financial model. In step one, understanding the structure of your model is key to creating a successful forecast. This overview provides a guide to creating a basic framework for your financial model.

Decide Whether to Use a Forecast or Storage Method

The first thing to decide when constructing your financial model is choosing the type of method to use. A forecast model is used to predict a future outcome, while a storage model is used for tracking and performing financial analysis. The type of model you use depends on the purpose of the financial model and the level of accuracy and tracking that is needed.

Identify Entities and Activities to be Modelled

To define the structure of the model, it is important to identify all the applicable entities and activities included in the model. Entities can include people, organizations, and processes, while activities refer to the transactions or events that affect the financial model. A comprehensive list of entities and activities must be compiled to ensure a comprehensive and accurate model.

Assess Assumptions, Drivers, and Data Sources

The final step in outlining a financial model is assessing the assumptions, drivers, and data sources that will be used in constructing the model. This includes collecting data points, understanding the drivers of the model, and incorporating any external risks that may affect the results of the model. This will help create a well-rounded and comprehensive model that accurately reflects the financial picture.

Overall, creating the structure of a financial model is critical to its success. Taking the time to understand the entities, activities, assumptions, drivers, and data sources will ensure accuracy and reliability when constructing a bottom-up financial model.

Step Two: Develop Input Assumptions

Estimate Revenue Factors

Building a financial model from the ground up typically involves the formulation of various assumptions that will drive the model and its results. Every element of the model needs to be addressed and for revenue drivers, estimating their effect on the model can be the most challenging. Several factors can affect the estimated revenue of a project, such as price points for each product within the business model, seasonality of sales, or special events.

Estimate Operating Costs

Following the estimation of revenue factors, the cost side of the equation should be addressed. Labor costs, rent and other overhead, supplies, utilities, and other direct and indirect costs should be estimated. For each cost area, assumptions should be made in terms of salary and change overtime, cost per square foot of leased space, cost of materials and supplies, and other cost areas.

Estimate Depreciation and Amortization

Depreciation and amortization can have a significant effect on the financial performance of a project. A key decision is whether to use straight-line or accelerated depreciation as governing method. Depreciation estimates, such as the useful life of equipment or the estimated salvage value of an asset, need to be incorporated in the financial model to accurately reflect the expected expenses.

Estimate Capital Expenditures

Capital expenditures represent the costs associated with long-term investments in the business. With an assumed estimate of capital needed to expand or open a facility, the financial model should include an estimate of ongoing capital spent on equipment and assets. If a capital expenditure plan is necessary to adequately budget for future investments, the capital expenditures section of the financial model is where these assumptions need to be addressed.

Step Three: Construct Base Year Financial Statements

Creating a bottom-up financial model requires constructing financial statements for the initial base year. While in practice, the base year is generally the year before the projected year, any initial year can be chosen for the base year for the financial model. Constructing the initial base year financial statements requires projecting the income statement, balance sheet and cash flow statement.

Project the Income Statement

The income statement projection should begin with estimating sales. Depending on the type of business, sales can be estimated through a combination of historical sales data and anticipated growth rates. Once the sales figure is determined, other revenue and expense components can be forecasted.

Project the Balance Sheet

The balance sheet provides a snapshot of a company’s financial health, estimating the value of the company’s assets, liabilities, and equity. The balance sheet should be projected based on the current balance sheet and expected changes. To construct the balance sheet, it is important to properly forecast both current and non-current assets and liabilities.

Project the Cash Flow Statement

The cash flow statement measures the movement of cash activities into and out of the company. Projections for the cash flow statement include net income, non-cash expenses, changes in non-cash working capital and investing and financing activities. When constructing the cash flow statement, it is important to correctly calculate the changes in working capital to ensure that inflows and outflows of cash are accurately reflected in the statement.

Step Four: Estimate Facility

Estimating the value of a facility and proceeds from sale or refinancing is a crucial step for any financial model. Accurately estimating these values will allow you to develop a reliable bottom-up financial model that accurately depicts performance and financial soundness.

Calculate the value of a facility

The value of a facility can be estimated by looking at a variety of factors, including the current market value of the facility and the projected cash-flow benefits. Additionally, the actual condition and expected useful life of the facility should also be taken into account.

Estimate expected proceeds from sale or refinancing

When estimating the proceeds from the sale or refinancing of a facility, it is important to consider the potential costs associated with the transaction. This will ensure that the proceeds are accurately estimated and that any potential losses are accounted for in the financial model.

Factors to consider include legal and transaction fees, closing costs, and transfer taxes. Additionally, potential capital gains taxes should also be taken into account.

Step Five: Calculate Project Returns

Having your financial model in place, you can now use it to calculate the returns of a project or investment. Below are the three major calculations that are used to evaluate project returns.

Calculate Internal Rate of Return (IRR)

The internal rate of return (IRR) is arguably the most important calculation used in project return and investment assessment. IRR is determined by discounting the cash flows of a project to arrive at a single rate at which the net present value of the cash inflows equal the initial investment cost. The higher the IRR, the greater the value of the project.

Calculate Net Present Value (NPV)

The net present value (NPV) is the sum of all discounted cashflows minus the initial investment cost. NPV sums up all the cash inflows and outflows so you can compare the amount of money your project or investment will generate. A positive NPV indicates that there is value in investing in the project.

Calculate Payback Period

The payback period calculation shows the time it takes for your project or investment to cover all expenses, including the initial investment. The payback period focuses on the negative cashflows rather than the total discounted cashflow over the life of the project. It is generally expressed in number of years.

• For projects with steady cashflows, the payback period calculation is simple and straight forward.
• For projects with non-steady cashflows, different methods need to be applied to calculate the payback period. Depending on the situation, some of these methods include linear interpolation and discounted payback period.

Conclusion

Creating a bottom-up financial model eliminates the need for relying on top-down estimates or historical forecasts for financial planning. It enables actionable insights, better forecasting accuracy and greater clarity of the assumptions and drivers embedded in the model. Here are some of the key benefits and takeaways of building a bottom-up financial model.

Benefits of Creating a Bottom-Up Financial Model

• A bottom-up model is customizable and can be tailored to business-specific drivers. This flexibility allows it to capture the latest market conditions and trends which are essential for accurate forecasting.
• It provides enhanced transparency for stakeholders and enables better decision-making in regards to financial planning.
• It increases the accuracy of financial forecasts by removing the need for historical forecasts and top-down estimates.
• It facilitates exploratory and "what-if" analysis which allows for the quick incorporation of assumptions into the model.

Key Takeaways

• A bottom-up financial model aids in the identification of market trends and in predicting the effect of market changes on the business.
• It captures business-specific drivers and provides enhanced transparency for stakeholders.
• It enables accurate forecasting and provides flexibility to incorporate assumptions into the model quickly and easily.