Corporate valuation modeling consistently proves challenging because it requires a thorough understanding of two bodies of thought that demand disparate skill sets: finance and technology. On the finance side, we must understand fundamental topics such as time value of money, growth rates, debt calculations, and other subjects that blend accounting, economics, and mathematics. In particular, accounting is a subject that corporate valuation analysts must be well versed in because generally a subject that corporate valuation analysts must be well versed in because generally accepted accounting principles (GAAP) or international financial reporting standards (IFRS) need to be followed to make sure analyses are consistent. On the technology side, we must select a program or programming language to utilize and understand the technical functionality of that program well. In many cases, the program is Excel, which requires knowledge of a number of program-specific functions and techniques in order to transfer the financial concepts to an orderly, dynamic analysis. Prior to jumping right to the construction process, we will take a step back and examine the overall process.

The corporate valuation analysis process itself is quite complex with many moving parts that are intricate to stitch together. Taking a reverse approach, that is, starting with the firm value and tracing back its calculations and components, is a good method of gaining an overview of this process.

Figure 1.1 provides a graphical overview of the discounted cash flow valuation process. First, we should establish that we will take a discounted cash flow approach to determining corporate value. Many other methods exist, such as relative valuation and adjusted present value, but the most popular detailed analysis is to discount expected future cash flows.

FIGURE 1.1 Overview of the corporate valuation process.

FIGURE 1.2 The forecast period is typically in alignment with a unique period of time for the company. This can be due to a startup period, distress, buyout, new funding, or new projects. The terminal value is the assessment of value after the forecast period.

Once we are confident in our cash flow and terminal value, we must determine the proper discount rates to apply to the values in order to get the present-day valuation. If we are looking at the firm from a comprehensive viewpoint, we need to examine what both an equity holder and a debt holder would demand for the firm’s risk, respectively known as the cost of equity and the cost of debt. Picking apart those calculations leads us to further detail.

Our heads may be spinning in a whirlwind of financial concepts right now. The immediate way to bring order to this chaos is to open our medium of operation, Excel, and start entering information. But without a carefully laid-out plan, this can be disastrous. To prevent such disaster, we will lay out a conceptual roadmap that will guide the corporate valuation process and its materialization in Excel. This conceptual roadmap is shown in Figure 1.3.

FIGURE 1.3 Building a corporate valuation model should be done using a conceptual approach.

While the concepts behind corporate valuation may start to make sense, actually transforming these concepts into a model adds the final layer of challenge. Depending on one’s background, utilizing Excel for the transformation of corporate valuation concepts to a corporate valuation model is much more challenging than understanding the concepts themselves. Quite frequently people suffer from what I have termed sheet 1 syndrome. This condition occurs when someone is intimidated by the vastness of the financial modeling process and stares at the first sheet of a new, blank Excel workbook, wasting time and fretting about what to do first. We will quickly develop a technical roadmap to prevent such an unpleasant condition. Overall, our technical progress should take the following steps in order:

While this seems as if we are going back to elementary school, it is worth taking 30 minutes to an hour to think about the problem that requires modeling. You should employ techniques that are optimized for how you work through problems. For example, I am a very visual problem solver and like to draw out each sheet as a box, connect lines to boxes that represent links in the future Excel model, and write out notes that indicate special functionality that might be required. I once started a model while at a job late at night without taking this step. On the second day of working through the model, I realized I had forgotten a core component and then spent an entire half-day linking up the inserted component. I wasted hours verifying that the links were correct, and probably would not have had to do so if I had inserted the concept in a logical order.

In this book, all of the necessary data is provided, which is unrealistic in our day-today jobs. Most financial analysts spend a significant amount of time searching for the best data to use for their analyses. This is done by searching through financial statements, industry reports, consultant studies, and market databases, and engaging in ongoing client communication.

Once our data has been collected it is rarely in a format that is ready for use. Financial data may be in values that we do not want to use for our analysis. For instance, we most likely would want a revenue growth rate assumption for our company. We could look historically at revenue amounts and then try different methods of calculating the growth rate. Once we settle on a growth rate methodology, we might want to verify that this is in line with management’s plans or that there are non-historical factors that might affect the assumption. We should be rigorous in our approach and do this for as many assumptions, in as much detail, as possible.

Constructing the framework for calculation is the focus of much of this book. We will definitely cover topics such as assumption verification and aggregation, outputs, and so on, but the core problem people have is binding all of this together in a cohesive model. I believe that the structure of the model is the easy part of the analysis process, once it is understood. After you gain fluidity in the model construction process, the actual framework for the model should occupy about 20% of your time. Determining what goes into the model and understanding the correct analyses to make should occupy the remaining 80%.

Unfortunately, many people are so anxious to get a result that the moment they come up with a figure they stop. There are many more steps to a proper modeling analysis. As a model is being built it should be constantly tested for validity. Concepts such as assets equaling liabilities plus shareholder’s equity or cash from the cash flow statement equaling the cash from the balance sheet should be tested.

In my first position in a quantitative analytics group there came a time when I finally was responsible for my own analysis. When I turned the analysis in, I handed over nearly a hundred pages of cash flow scenarios with a summary sheet on top. My manager took the packet of information, ripped off the summary sheet, and threw the rest in the trash bin. The point of explaining this is that you should understand what data your audience wants. In that case, I was presenting results to a manager who wanted only a top-level understanding of the data. If I had presented the data to a risk manager or another quantitative analyst, they might have wanted the cash flow scenarios. The best models and modelers can get overlooked due purely to output presentation (sad, but true in the field of finance).

Finally, you must understand the model that you have built. Especially if you must present the analysis to others, you must be well versed in the resulting changes in the model given changes in the assumptions. This means that you should test the model with reasonable extremes. Take growth down to 0% in one scenario. What happens to the firm’s value? Then take growth up to 100% each period. Does the firm’s value increase? Try out many combinations of assumptions, such as increasing capital expenditures and adding a debt layer to pay for it. Can the company afford the expected debt payments each period? Similar to output reporting, people lose faith in a model that returns unexplainable results.

Over the years of financial modeling development, I have discovered a number of best practices. Conforming to the following allows other users easier interpretation and prevents errors:

This book is designed in a manner similar to the corporate valuation courses I teach in person. Both rely on theory and practical exercises to transform the concepts into a dynamic, usable model. Just as my courses work through individual modules of corporate valuation that culminate in a complete firm valuation, this book has readers work through similar modules, chapter after chapter. Each section begins with a discussion of theory and then moves on to a Model Builder exercise where the theory is transferred to an application in Excel. Eventually, as all theory concepts are read and Model Builder exercises completed, the reader should have an operational model that is identical to the one included on the CD-ROM that is packaged with this book.

At the current time, many users have switched to Excel 2007; but many, if not more, are still using Excel 2003. While the powerful differences between the two versions of Excel are related to memory accessibility and usage, there are major shifts in the menus. When technical books provide instruction for only one version, and the user has a different version, the alterations to the menus can cause confusion. For this reason, I will provide instruction for both versions of Excel wherever there are instructions that could be significantly different between the two versions.

FIGURE 1.4 Each chapter will follow a similar pattern, starting with corporate valuation theory, then model implementation, followed by a Toolbox to assist with Excel functions and techniques.

Learning about financial modeling can be tricky in written form since words translate into commands, which can be very specific for computer programs. In this text we are using Excel as the modeling program, which is primarily operated by menus, worksheets, and cells within the worksheets. For the menus in Excel 2003, I will often use the word select, which would be synonymous with left-clicking the stated menu. There could be multiple options, where once you left-click you might have to move the cursor down and over to find the correct sub-selection. For instance, if you wanted to open the Add-Ins dialogue box you would have to select Tools, then move the cursor down to Add-Ins and select or left-click again. The process is slightly different for Excel 2007, where there is a ribbon system. In the ribbon system you still must select or left-click on a tab, but instead of having a drop-down of sub-selections there are graphical icons that must be selected. These graphical icons are grouped into subsets, such as the Font subset, within the Home tab.

In our first Model Builder, we should take a moment to understand how this section differs from other parts of the book. Each Model Builder is an instructional section that should be completed with the use of a computer running Excel. It should be followed step-by-step using the instructions. Each Model Builder assumes that the previous Model Builder was read and implemented. The eventual result of the Model Builder sections is the Corporate_Basic.xls model provided on the CD-ROM. If at any point you find yourself lost, you should open the Corporate_Basic.xls file to see how the relevant section should be completed.

FIGURE 1.7 In cell A1, on the Assumptions sheet, we create a label for the cell by entering text. Notice the cell is also named, as seen by the name inputs_ProjName in the Name Box.

Workbook. Both of these formats are .xlsx and are not compatible with earlier versions of Excel unless the user downloads a special file from Microsoft. If you or another user anticipate using this file with lower versions of Excel, you should save the file as an Excel 97-2003 Workbook. This format is .xls and will not automatically remove macros. The possible file formats for Excel 2007 are shown in Figure 1.8.

FIGURE 1.8 Be careful when saving files in Excel 2007 as there are many more options.

A very common technique used throughout financial modeling is to name a cell or range of cells. To name a cell or a range of cells is to provide an alternative name other than the standard row/column name, such as cell A1. Naming cells has a number of advantages:

FIGURE 1.9 The Name Box is an area that allows a user to quickly create a name for a cell or range of cells.