Chapter 2: Steps for Building a Financial Model

The process of building a financial model can be broken down into several distinct stages. Most of these stages can run concurrently with others, while some can't start until others are concluded. This chapter introduces you to the steps required for building a model, with explanatory notes on the nature of each of the steps.

Any project you wish to undertake should begin with gaining an accurate understanding of what the project is all about. If you start off in the wrong direction, one of three things will happen:

• Partway through the project, you'll realize that this is not what the client wants, and you'll then have to start all over again.
• You'll end up convincing the client to accept a project that was never intended.
• You'll persist with the wrong project, and it'll end up being rejected.

So much depends on this stage that it typically takes up about 75% of your total modeling...

Discussions with management

This is where you determine or confirm the scope and target of your model. Management is also the primary source of information about future plans and trends.

Usually, it's not possible to get all of the details at the first time of asking. You should, therefore, be prepared to go back to the section heads and ask the same, or similar, questions from a position of better understanding.

The following paragraphs describe the steps to take to ensure that you get the most out of your discussions with management, as well as where to concentrate your attention.

Gauging management expectations

While discussing with management, you need to get a clear understanding of their expectations from the assignment and what they hope will be achieved.

If all that is required is a projected cash flow, then a full-blown valuation model would be a waste of time and resources, and you probably wouldn't get paid for the extra work. We will take a detailed...

Building assumptions

Financial modeling is all about projecting results or behavior into the future.

To do this, you will need to build up a set of assumptions to bridge the gap between actual performance and future results. Although you will need to project every single item in the model, your assumptions will focus on items that will have a material effect on the final results. Other non-material items can be projected as, say, a percentage of turnover (for revenue items) or a best-judgment figure (for balance sheet items).

Your assumptions will need to consider whether items will increase, decrease, or stay the same. How you calculate the projected change is referred to as the growth driver. For example, for revenue items, it could be inflation, year-on-year growth, or some other indicator.

In the course of the assignment, you may need to make new assumptions and/or modify others. A great way to make your model easier and faster to navigate and update is to standardize...

Building a template for your model

It is always important to be systematic in the way you build and maintain your model. Even if only you will make use of the model, whenever you have cause to revisit the model after a period of time, you do not want to have to wade through various schedules and worksheets to find what you need.

This is even more important if your model is going to be used by someone else.

A good way to ensure that your model is easy to follow and use is to build a template (a standard format) with some simple rules guiding how data is to be input and presented. In general, you will require at least six columns of figures, three each of historical and projected years, and another three or four columns for descriptive information. The template should enhance navigation and be easy to follow. The first major decision is whether to adopt a single-worksheet or multiple-worksheet approach.

In the following sections, we will learn about some of the features of both...

Uploading historical financial data

Once you have the template in place, the next step is to obtain historical financials. With historical data, we are interested in the balance sheet, profit and loss account, and cash flow statement. It is common, in the course of preparing financial statements, to have a number of initial drafts that may have content that will be superseded when the final statements are agreed upon. Ensure that the financials you are given are the final audited financial statements.

The more information you have, the more accurate your projections will be; however, you must not get carried away, as too much information will make the model unnecessarily cumbersome. Generally, historical data is limited to 5 years, with another 5 years of projected financials. Try to get soft copies of the historical financials in Excel readable format, as this will significantly reduce the amount of time you will need to spend converting into your template format.

Inevitably...

Projecting the balance sheet and profit and loss account

In order to project the financials, you will need to determine the growth drivers for the balance sheet and profit and loss account. Growth drivers are those parameters that best capture the movement in individual items over the years. The nature of the item and your expertise will determine which parameter you select as an appropriate growth driver. An example of a growth driver for turnover is the year-on-year growth or inflation.

You should know that the balance sheet growth drivers are not as straightforward as with the profit and loss. We will cover this in detail in Chapter 6, Understanding Project and Building Assumptions.

Once the historical growth drivers have been calculated, you will need to project them over the next 5 years. You will be guided by your notes on the discussions with management and, in particular, the section heads for their suggestions on how growth is likely to behave in the next 5 years....

Additional schedules and projections

At this stage, it would have come to your attention that Balance Check for the projected years is now red and FALSE. This is because our balance sheet and profit and loss account are not yet complete.

We have projected growth for most items, but there are some items that require specific treatment, such as CapEx, depreciation, loans, and interest.

ASSET SCHEDULE is prepared to capture the movement in property, plant, and machinery. The following screenshot shows the full Capex and Depreciation Schedule values:

Figure 2.8 – ASSET SCHEDULE

The company's plans for CapEx over the duration of the model will be reflected here. Historical CapEx and disposal of assets will be shown under the years during which the expenditure or sale took place. The schedule will also take into account the cost of the assets and useful life and depreciation rates and methods. Assets with different depreciation rates will be treated...

Cash flow statement

Unlike other items, it is not possible to project cash. The cash balance is a fallout from all transactions carried out during the period under review.

This fact is captured in the cash flow statement, which considers the inflows and outflows of cash. The net result is then applied to the opening cash balance to arrive at the closing cash balance at the period end. The following screenshot shows a completed cash flow statement, which ends with a closing balance for cash that is taken to the balance sheet:

Figure 2.10 – Cash flow statement

When the closing cash balance is taken to the balance sheet, the balance check for the projected years should now show TRUE on a green background, giving some assurance that calculations up till that point are correct.

The cash flow statement is one of the most important statements for a company. To most investment analysts, cash is king.

You may wonder why you need another statement that...

Preparing ratio analysis

With the preparation of a cash flow statement, we now have the core content of a set of financial statements. These financial statements, now referred to as a statement of financial position, statement of comprehensive income, and cash flow statement, along with explanatory notes and schedules, are distributed to shareholders of the company and government. It is also these financial statements that are available to other interest groups, such as investors and holders of the debt capital of the company.

The financial statements provide a significant amount of information about the company and its results for the period under review; however, on their own, they are not adequate for decision-making. Ratio analysis provides an in-depth look at the details behind the figures. The following screenshot is an example of a set of ratio analyses:

Figure 2.11 – Ratio analysis

By looking at the relationship between strategic pairs of...

Valuation

There are two main approaches to valuation, which are as follows:

• Relative approach: In this approach, you have the following methods:
  • The comparative company method of valuation: This method obtains the value of a business by looking at the value of similar businesses and their trading multiples, the most common of which is enterprise value (EV) and earnings before interest, tax, depreciation, and amortization (EBITDA), where EV is divided by EBITDA.
  • The precedent transaction method: Here, you compare the business to other similar businesses in the industry that have recently been sold or acquired. Again, you can use multiples to derive a value for your business or company.
• Absolute approach: This approach estimates all future free cash flows of the company and discounts it back to today. It is called the discounted cash flow (DCF) method. Essentially, the approach considers that the worth of a company can be equated to the amount of cash it can generate after...

Summary

In this chapter, we looked at the steps to be followed in building a financial model. We gained an understanding of why it is necessary to have a systematic approach. We went through the steps from discussions with management through to calculating a valuation of the enterprise and the shares of the company, understanding the purpose and importance of each step.

In the next chapter, we will look at how we can use Excel formulas and functions to speed up our work and make modeling a more rewarding experience.