Financial Modeling in Excel For Dummies
Book image
Explore Book Buy On Amazon
When you then consider the benefits that a financial model can bring, it’s difficult not to get carried away thinking of the application potential of a financial model! When you understand the principles of financial models, you can begin to look at the most common scenarios in which a model would be implemented.

There are a variety of categories of financial models:

  • Project finance models: When a large infrastructure project is being assessed for viability, the project finance model helps determine the capital and structure of the project.
  • Pricing models: These models are built for the purpose of determining the price that can or should be charged for a product.
  • Integrated financial statement models (also known as a three-way financial model): The purpose of this kind of model is to forecast the financial position of the company as a whole.
  • Valuation models: Valuation models value assets or businesses for the purpose of joint ventures, refinancing, contract bids, acquisitions, or other kinds of transactions or “deals.” (The people who build these kinds of models are often known as deals modelers.)
  • Reporting models: These models summarize the history of revenue, expenses, or financial statements (such as the income statement, cash flow statement, or balance sheet).
Modelers generally specialize in one or two of these model categories. You’ll see some overlap between each type of model category, but most models can be classified as one model type.

Project finance models

Loans and the associated debt repayments are an important part of project finance models, because these projects are normally long term, and lenders need to know whether the project is able to produce enough cash to service the debt. Metrics such as debt service cover ratio (DSCR) are included in the model and can be used as a measure of risk of the project, which may affect the interest rate offered by the lender. At the beginning of the project, the DSCR and other metrics are agreed upon between the lender and borrower such that the ratio must not go below a certain number.

Pricing models

The input to a pricing model is the price, and the output is the profitability. To create a pricing model, an income statement (or profit-and-loss statement) of the business or product should be created first, based on the current price or a price that has been input as a placeholder. At a very high level:

Units × Price = Revenue

Revenue – Expenses = Profit

Of course, this kind of model can be very complex and involve many different tabs and calculations, or it can be quite simple, on a single page. When this structure model is in place, the modeler can perform sensitivity analysis on the price entered using a goal seek or a data table.

Integrated financial statement models

Not every financial model needs to contain all three types of financial statements, but many of them do, and those that do are known as integrated financial statement models. You may also hear them referred to as “three-way financial models.” The three types of financial statements included in an integrated financial statement model are the following:
  • Income statement, also known as a profit-and-loss (P&L) statement
  • Cash flow statement
  • Balance sheet
From a financial modeling perspective, it’s very important that when an integrated financial statement model is built, the financial statements are linked together properly so that if one statement changes, the others change as well.

Valuation models

Building valuation models requires a specialized knowledge of valuation theory (using the different techniques of valuing an asset), as well as modeling skills. If you’re a casual financial modeler, you probably won’t be required to create from scratch a fully functioning valuation model. But you should at least have an idea of what types of valuation financial models are out there.

Here are three common types of valuation financial models you may encounter:

  • Mergers and acquisitions (M&A): These models are built to simulate the effect of two companies merging or one company taking over the other. M&A models are normally undertaken in a tightly controlled environment. Due to its confidential nature, an M&A model has fewer players than other kinds of models. The project moves quickly because time frames are tight. The few modelers working on an M&A model do so in a concentrated period of time, often working long hours to achieve a complex and detailed model.
  • Leveraged buyout (LBO): These models are built to facilitate the purchase of a company or asset with large amounts of debt to finance the deal, called a leveraged buyout. The entity acquiring the “target” company or asset usually finances the deal with some equity, using the target’s assets as security — in the same way that many home loan mortgages work. LBOs are a popular method of acquisition because they allow the entity to make large purchases without committing a lot of cash. Modeling is an important part of the LBO deal because of its complexity and the high stakes involved.
  • Discounted cash flow (DCF): These models calculate the cash expected to be received from the business or asset a company is considering purchasing, and then discounts that cash flow back into today’s dollars to see whether the opportunity is worth pursuing. Valuing the future cash flows expected from an acquisition is the most common modeling method of valuation. Intrinsic to the DCF methodology is the concept of the time value of money — in other words, that cash received today is worth a lot more than the same amount of cash received in future years.

Reporting models

Because they look historically at what occurred in the past, some people argue that reporting models are not really financial models at all, but I disagree. The principles, layout, and design that are used to create a reporting model are identical to other financial models. Just because they contain historical rather than projected numbers doesn’t mean they should be categorized any differently.

In fact, reporting models are often used to create actual versus budget reports, which often include forecasts and rolling forecasts, which in turn are driven by assumptions and other drivers. Reporting models often start out as a simple income statement report, but end up being transformed into fully integrated financial statement models, pricing models, project finance models, or valuation models.

About This Article

This article is from the book:

About the book author:

Danielle Stein Fairhurstis a Sydney-based financial modeling consultant who helps her clients create meaningful financial models for business analysis. She is regularly engaged around Australia and globally as a speaker and course facilitator. She received the Microsoft MVP Award in 2021 in recognition of her technical expertise and contributions to the community.

This article can be found in the category: