001

Table of Contents
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers’ professional and personal knowledge and understanding.
The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation and financial instrument analysis, as well as much more.
For a list of available titles, please visit our Web site at .

001

Preface
Another book about financial modeling? You might be rolling your eyes and muttering under your breath, “Why? Aren’t there plenty of books that cover this topic?” Yet, you still chose to look inside and see what this one is about. The motivation behind looking at financial modeling books is most likely related to a desire to learn financial modeling in an easy-to-understand, time-efficient, low-cost manner. However, after poring over a few books with the words Financial Modeling in the title, you might be left feeling like you know more about specific skills and topics, but not a working financial model. Perhaps these books have given you an understanding of how the model should work, but you are confused as to how to practically implement the information provided. Ultimately, an easy-to-understand, integrated analysis still eludes you.
There’s a vast sea of approaches authors take with financial modeling books. Some try to encompass every concept in finance and provide examples of how to implement each concept in Excel. Those are the cookbooks of finance. Introduce a topic, show an Excel example, and then move on to the next topic. Others take a similar approach, but vary the medium. Rather than use Excel, they offer books on financial modeling entirely in code with languages such as VBA or C++. Although many of these books can be highly informative, they often leave it up to the reader to figure out how to connect the individual concepts.
The answer, some say, is books that focus on specific concepts. Rather than covering all possible finance topics, these books hone in on specific areas such as fixed income or derivatives. The problem with many of these books is they often rely too much on delving into the details of the topic and demonstrating formula derivations, instead of dedicating time to showing how to implement the concept. Or, they discuss the implementation and show some screenshots, but fail to provide clear instructions, open functions, and code, much less a complete working model.
To me, the best type of financial modeling book is one that is dedicated to a specific topic within finance, offers multiple examples of implementation, is written in a clear and easy-to-understand manner, and provides a completely integrated example model. There are a few books that have been written in this fashion on topics such as credit risk, interest rates, options, and structured finance, but I find that few have addressed corporate valuation in this manner.
It seems to me that corporate valuation modeling too often gets lumped together in the general financial modeling book category. Since a company encompasses many topics in finance it may seem appropriate to cover all of those topics and then assume that the reader can value the company. Unfortunately, connecting the concepts theoretically and implementing those connections on a computer can be just as hard as understanding the individual concept or computer-based implementation in the first place.
Take depreciation as an example. Some books show how to use Excel’s prebuilt depreciation functions to create a depreciation schedule. Others discuss depreciation concepts. Yet, few show readers how to create the depreciation schedule in a way that is automated with the associated asset’s creation. Further, the prebuilt depreciation functions in Excel need to be turned off so the asset is not overdepreciated depending on the forecast period of the model. Then, once we get the schedule correct, we have to accumulate the depreciation on the balance sheet, remove it from different sections of certain financial statements, and perhaps add it back when dealing with valuation calculations.
This book attempts to address many of these shortfalls by providing a compre hensive, integrated approach to modeling a corporate entity with the primary goal of determining a firm’s value. Theory is introduced to guide the reader along the valua tion process and connect each concept with the prior and future concepts. Along the way, clear, step-by-step instructions are provided that cover every cell of the included example model. No sections are hidden, password protected, or incomplete.
Beyond concept and implementation issues, after teaching courses on corporate valuation modeling hundreds of times, I have also come to realize that an added layer of complexity is the preexisting skill level of readers. Some are very new to finance and Excel, others new to just finance, others new to just Excel, and some are seasoned in both, but wanting to learn more. While the text itself addresses the finance topics and shows an integrated implementation, the Excel skills can be a challenge for some and a bore for others who already know them. For this reason, there is a Toolbox at the end of each chapter that provides additional information on the Excel functions and techniques that are used in the chapter. This way, the text is not full of background knowledge that would bore the intermediate Excel users, but the content is still there for the beginning Excel user to learn more.
I hope that this book is a valuable resource for people new to finance, seasoned professionals engaged in analysis, and experienced executives trying to learn what their junior staff is doing all night long. I also continually strive to improve my books, find the best possible methods to teach, and ensure that every reader learns. If you are confused by any section or topic related to this book or my other books, if you think you may have found an error, or if you just want to discuss finance-related topics, please feel free to review the Books and Blog section of my company’s web site or personally e-mail me at keith.allman@enstructcorp.com.
KEITH A. ALLMAN

Acknowledgments
My father always suggested that I focus on math and quantitative subjects. Early on, I rebelled, thinking he couldn’t be further off topic from what I would do in my career. Given that this is my third book on financial modeling, I suppose I should state that he was right. My mother was less adamant about the subject, but to not acknowledge her would undermine the value of her support even to this day. While on the family track, I should note two more family members who have influenced this work. The first is my sister, who was my academic rival when we were children. That energy fomented the fervor with which I have approached all subjects of interest to this day. The second is my grandfather, who lives and breathes the stock market. I am convinced our conversations subconsciously caused my gravitation toward finance. As for more direct acknowledgments, Susan Jane Brett reviewed the book in detail and offered critical comments that led to revisions and clarifications. Her thoroughness is very much appreciated. Also, all of my corporate valuation class participants over the past three years have contributed to this book through the study of their learning methodologies, the development of the curriculum for their courses, and the critical thought caused by their questions. Finally, I would like to thank all of the staff at John Wiley & Sons who work on my books, especially Bill Falloon, Meg Freeborn, and Mary Daniello.
K.A.A.

CHAPTER 1
Introduction
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.

OVERVIEW OF THE CORPORATE VALUATION 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.

Projecting Cash Flow

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.
Discounting expected cash flows is a method used in many areas of finance. Bond pricing, securities analysis, and project valuation all use discounted cash flow techniques. Any discounted cash flow technique has two general components: future expected cash flows and a rate or rates to discount those cash flows to bring them to the present value. The sum of all present-valued cash flows is the value. So the path we first go down is making sure we do the best possible job of estimating future cash flow and calculating discount rates.
Overview of the corporate valuation process.
002
Starting with the future cash flows, we have to think about what constitutes cash flow. Is it gross profit, or net income, or earnings before interest and taxes (EBIT)? While those are standard metrics for cash flow, they do not wholly represent cash that can be freely distributed to parties of the firm. We must be able to distinguish between real cash and non-cash items that flow through financial statements and ensure that the company can meet its capital expenditure requirements and fund working capital needs.
Each of the items that lead us to our cash flow can be broken apart into detail. Specific capital expenditures or debt financing plans can be modeled. For instance, we may anticipate debt financing and be the lead bank in a syndicated funding or be part of a bilateral arrangement. In such cases we may be concerned with the priority of cash flows. This level of detail can lead us to more accurate projections of cash flow.
The next challenge with cash flow is the duration of cash flow projection. In discounted cash flow modeling, we typically distinguish between a forecast period valuation and a terminal valuation. This means that we forecast specific cash flows only for a certain amount of time depending on the purpose of our valuation. Continuing with the debt example, if we plan to issue five-year debt, we may project out five years of a company’s cash flow. If we ignore time beyond the five periods, then we make the assumption that the firm is worth nothing after that time period; it essentially vanishes. This is unlikely, because at bare minimum a firm has assets that can be disposed of and the proceeds returned to debt and equity holders. It is even more likely that the firm would continue operations.
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.
003
Whether we assume the firm is liquidated after the forecast period or that it continues into perpetuity, we need to do more work to make a terminal value assumption. This could require altering assumptions to a long-term perspective and in general applying a different methodology than just cash flow forecasting. depicts forecast and terminal value periods.

Discounting Cash Flow

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.
The cost of equity can be determined using the capital asset pricing model (CAPM), which quantifies the rate of return for an equity investor based on a risk-free return, a market-based return, and a quantification of nondiversifiable risk. These factors materialize in the form of the risk-free rate, the market risk premium, and beta. Multiplying beta by the market risk premium and then adding the risk-free rate gets us to the cost of equity for one period. Although this is a good start, we may have different assumptions throughout time for each of these items.
The cost of debt also contributes to our eventual discount rate for the cash flows. The credit quality of the firm and current market conditions determine the spread over the risk-free rate that the company must pay for its debt. Because interest is tax deductible in most cases, the firm’s after-tax cost of debt is more relevant, meaning we need to also estimate the tax rate to get an accurate assumption.
Overall, both rates may change over the forecast period and can have completely different assumptions for the terminal value period. Further complicating matters is that we do not take a simple average of the two values, but weight each rate by the amounts of debt and equity. Whereas in theory these should be market values, book values are sometimes used as proxies in projections. In cases of expected capital structure changes, the weights can change over time and significantly affect the discount rates, which ultimately affect the valuation.

CONCEPTUAL ROADMAP

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 .
The first destination on this map is dates and timing, which provide the framework for our analysis. Once we know what timeframes we are working with, we need to fill in the required information for each period. Because dates and timing is the first concept, it does bring along with it some administrative qualities, such as setting up our assumptions in an intelligible manner and creating an auxiliary sheet to handle administrative items.
Building a corporate valuation model should be done using a conceptual approach.
004
We can then move to the next few locations on our map, which are the financial statements. It’s usually easiest to begin with the income statement. On that statement our biggest concern is most likely revenue growth, because many assumptions are predicated on this projection. We should therefore focus on understanding growth projection methodologies. Inextricably linked to the income statement is the balance sheet. Capital expenditures, depreciation, and debt are key items to the balance sheet, which require further analysis. Once both statements are established, we need to understand their linkages and create functionality that allows the sheets to work harmoniously in projections.
After building in the key calculations of cash flow, we need to make sure that we did so in a precise manner and in a way that is representative of the firm’s value. The cash flow statement is established to reconcile cash and validate the model’s calculations. Other tests are also built in to focus on important parts of corporate cash flow. Eventually we need to summarize this cash flow in a way that represents the value of the firm, otherwise known as free cash flow. The free cash flow is calculated for each period of the forecast period, a terminal value determined, and all values discounted back to the present value at appropriate discount rates.
While our core valuation ends there, we might want to analyze the system we have set up in more detail and build in efficiencies for working with our analysis. An output summary can reorganize information in formats that people are used to, charts can be created to graphically represent data, and automation can be built to allow sensitivity analyses en masse.

TECHNICAL ROADMAP

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:
1. Brainstorm and sketch
2. Data collection
3. Assumption verification and aggregation
4. Structural construction
5. Internal validation
6. Output reporting
7. Interpretation

Brainstormand Sketch

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.

Data Collection

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.

Assumption Verification and Aggregation

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.

Structural Construction

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%.

Internal Validation

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.

Output Reporting

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).

Interpretation

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.

A FEW BEST PRACTICES REGARDING FINANCIAL MODELING

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:
1. Use consistent formulas for rows or columns. Whether it is to be dragged across columns or up and down rows, the formula should be the same. Differences usually occur during certain time periods, which suggests the need for functions that give our formulas optionality.
2. Never combine a hard-coded assumption with a formula. If you find yourself inserting numbers into a formula, you should consider making that number a formal assumption in the appropriate section.
3. Hard-coded values should be formatted using blue bold font. Formulas are typically kept in black-colored font. The origins of these formatting conventions are unclear, but they are market practice and allow users to quickly identify assumptions and formulas.
4. Corporate models frequently organize time going across columns, whereas asset-based and project finance models occasionally organize time going up and down rows. This convention is due to the 256-column constraint of Excel 2003 and earlier. Although Excel 2007 has plenty of columns, I have found that many financial modelers still adhere to these conventions.

HOW THIS BOOK WORKS

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.
While theory and implementation are two critical elements, one of the biggest challenges of teaching financial modeling is the different skill levels of readers. In my classes, I am able to teach to various levels of difficulty and explain functionality as needed. In print, this is clearly not possible, but I have tried to address the issue of varying skill levels by creating sections at the end of each chapter called toolboxes. These sections explain Excel functions and techniques that are used throughout the chapter. Readers who are beginners will find it valuable to go through every Toolbox. Intermediate readers can selectively choose which Toolbox sections to read, and advanced readers can skip them altogether. depicts the book’s approach.

Excel 2003 and Earlier versus Excel 2007

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.
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.
005
Differences between Excel versions will not be an issue when this book discusses Visual Basic Applications (VBA) in Chapter 11, since the Visual Basic Editor (VBE) and the VBA code have largely gone unchanged. The only caveat is that users who are using Excel 1997 or earlier may encounter problems since there were many updates to VBA after that version.

A Few Words about Semantics

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.
The process of using workbooks and cells is relatively similar between Excel 2003 and Excel 2007. The key is that there are four main operations we will perform on a cell:
1. Enter a value. When the Model Builder exercises ask for a value to be entered, this will be a number, date, or Boolean (TRUE or FALSE) value. These are values that will be referenced for some type of calculation purpose.
2. Enter a label. A label is text in a cell to help the model operator understand values and formulas in relative proximity. Note that I use the word as a verb as well. For example, I may say “label cell A1, Project Basic Cash Flow.” This means that the text “Project Basic Cash Flow” should be entered into cell A1.
3. Name a cell or range of cells. Not to be confused with labeling, naming is a specific technique that converts the reference of a cell or range to a user-defined name. This process is detailed in the Toolbox section of this chapter.
4. Enter a formula. The core reason we are using Excel is for calculation purposes. A formula is initiated in Excel with the “=” sign. When I say to enter a formula, I will provide the cell it should be entered in and the exact formula that should be entered. Often I have copied this formula from the Excel model itself to ensure that the text corresponds to the example model provided on the CD-ROM.

MODEL BUILDER 1.1: INITIAL SETTINGS AND ASSUMPTIONS SHEET SETUP

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.
This first Model Builder is to make sure that our versions of Excel are all set to identical settings and to start constructing the model on the Assumptions sheet. Depending on how you installed Microsoft Excel or Office, you might need the installation disc to enable all of these settings.
1. We will be using a few functions and tools that require the Analysis Tool Pak, Analysis Tool Pak VBA, and Solver Add-Ins to be installed. To do this:
For Excel 2007: Select the Office button, select Excel Options, select Add-Ins, and then select the Go button, which is to the right of Manage, and a box that should default to Excel Add-Ins. This will bring up the same box as in
The Add-In selection box allows users to install pre-created or user-created add-ins.
006
. Check the boxes for Analysis Tool Pak, Analysis Tool Pak VBA, and Solver. Select OK. If the Add-Ins are not installed, it may prompt you with a few messages stating that Excel will need to install them. Depending on how Excel was initially installed, you might need the installation disc to complete the install.
For Excel 2003 and earlier: Select Tools, select Add-Ins, and check the boxes for Analysis Tool Pak, Analysis Tool Pak VBA, and Solver. Typically the Analysis Tool Pak and the Analysis Tool Pak VBA are the first two Add-Ins on the Add-Ins list. Solver is usually at the bottom. Select OK. If the Add-Ins are not installed, it may prompt you with a few messages stating that Excel will need to install them. Depending on how Excel was initially installed, you might need the installation disc to complete the install. depicts the Add-In selection box.
2. The next setting we should set is the ability to run macros. While the core model does not require the use of any macros, Chapter 11 will add significant automation and functionality through the use of VBA. If you would like to take advantage of this, you will need to complete the following steps.
For Excel 2007: Excel 2007 requires a bit more setup to work with macros. Select the Office button, and select Excel Options. On the default tab, the Popular tab, check the third checkbox down, entitled “Show the Developer tab in the Ribbon.” Press OK. Once the Developer tab is visible, select it and then select Macro Security. In Excel 2007, you have four options for Macro settings, three of which are similar to Excel 2003. The only exception is that you can disable all macros except ones with a digital signature. Since hardly anyone has taken Microsoft up on their security measures and people rarely use digital signatures for Excel files, we will ignore that option. We can safely set it to disable all macros with notification. The notification will occur when the workbook is opened and will be a button with Options... in it at the top of the sheet. Select this button. A new dialogue box will open. Within that dialogue box, under Macros and Active-X, select Enable This Content and press OK. This dialogue box is shown in . In Excel 2007, you should not have to restart Excel for this to take effect.
• For Excel 2003 or earlier: Select Tools, select Macros, select Security. You have the choice of either Low, Medium, or High. Low will allow macros
Once the macro security setting is set to Disable All Macros with Notification, the following Options... button appears when workbooks with macros are opened.
007
without prompting, medium will prompt you to enable or disable macros within a workbook when it is opened, and high disables macros in a workbook. The main concern is that viruses can be built into macros, which can cause significant damage or security concerns. The Corporate_Basic.xls model contains no viruses and can be safely opened with macros enabled. You might want to set your computer to medium security so that you enable only trusted workbooks. For the changes to take effect you must shut down Excel and reopen it. When prompted to enable macros for the Corporate_Basic.xls file, select Enable.
3. Once the Add-Ins are installed and the macro security is set, we can actually start constructing our model. The next step is to notice the default setting of the worksheets. There should be three blank sheets named Sheet1, Sheet2, and Sheet3. Change the name of Sheet1 to Assumptions.
4. Next we will create a label for the entire project. On the Assumptions sheet in cell A1, enter the text Project Basic Cash Flow. Format this text bold blue. The reasoning behind the formatting is grounded in a financial modeling convention, where all variables that are inputs entered as values (otherwise known as hard coded) are formatted bold blue. Values returned from formulas are typically left in standard black-font format.
5. Name cell A1 inputs_ProjName. Naming cells is distinctly different from entering text as the previous step instructed. For basics on naming cells, refer to the Toolbox section of this chapter for a thorough primer on naming cells and ranges. Cell A1 should look like .
6. The final step of this brief Model Builder is to save the file—a simple yet commonly forgotten step. As a suggestion, you might want to just add your initials to the end of Corporate_Basic.xls (Corporate_Basic_KA.xls, in my case). Most Excel 2003 or earlier users are familiar with the steps to saving, but Excel 2007 users should be careful as there are many new options. In Excel 2007, under Save As, if you select Excel Workbook it will save it as the default file for Excel. This is usually set to a macro-free .xlsx file. This means that if you created any code in the file, it will automatically be stripped out and lost. If you want to save a workbook with code (which will be the case if you implement the VBA for the Corporate_Basic model), then you should save it as an Excel Macro-Enabled
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.
008
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 .
Be careful when saving files in Excel 2007 as there are many more options.
009

TOOLBOX: NAMING CELLS

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:
1. Named cells are easier to work with in formulas since we can name them with meaning. For instance, rather than using the range B3 to refer to the current fiscal year date of a model, we could name cell B3 FY_Current. When working with formulas, the name would be used rather than B3, and the formula would be easier to understand.
2. Named cells automatically take on absolute references in formulas. When cells are referenced in formulas, their default setting is set to relative references. This means that if we referenced cell B3 in a formula and dragged the formula cell across one column, the new formula would reference cell C3. This can be prevented by locking down the cell, as described in Chapter 5’s Toolbox, or by using a name. A named range is automatically locked down. We can still use the row/column reference, but would have to enter this in by hand since named cells will automatically display the name when referenced.
3. Named cells allow us to reference values for data validation lists on sheets other than the sheet where the list is being created. If this is not clear, you should read about data validation lists in Chapter 2’s Toolbox section.
4. Named cells allow the user to find inputs and references faster. When we push cell F5, we are provided with the Go To dialogue box. This allows us to jump to sections in a model very quickly. When we use named ranges, we can move between them very quickly.
5. Named cells make in-and-out processes easier when we start using Visual Basic Applications (VBA). We will come back to this in Chapter 11 when we implement basic VBA code.
Cells can be named very quickly using the Name Box in both Excel 2003 and 2007. The Name Box is located near the upper-left corner of any Excel sheet. shows the location of this box.
The Name Box is an area that allows a user to quickly create a name for a cell or range of cells.
010
There are a couple of rules regarding naming:
1. Names cannot have spaces. If you want to use a name with spaces, use underscores. For instance, if you wanted a cell that was named Reserve Account, you would have to name it Reserve_Account.
2. In Excel 2003 and earlier, names cannot begin with a number. Excel 2007 will allow this; however, when the spreadsheet is saved in Excel 2003 or earlier, a prompt will be generated that informs a user that the names will have the number values removed. This can cause problems if the numbers were the only differentiation between names in the workbook.
Finally, the most common source of error in naming is when the names need to be changed or deleted. Many users try to change names by selecting the cell or cells that are named and typing over the name in the Name Box. While this will generate a new name, it will not get rid of the existing name. The same is true when a user selects a named range, highlights the name in the Name Box, and presses delete. In Excel 2003, names should be deleted or references edited under the Insert menu, Names submenu, Define option. This selection brings up the Name dialogue box, which allows a user to delete or edit the name reference. In Excel 2007, users can go to the Formulas tab and select the Name Manager button. This selection brings up a similar dialogue box as Excel 2003’s Name dialogue box; however, there is additional functionality. In particular, users can edit the name of a range directly through this dialogue box.

CHAPTER 2
Dates and Timing
Consider a world without time and how that would impact a financial analysis. It would greatly limit the methodologies we could use to value a company and simultaneously limit the value that could be derived for the firm. At the most extreme level, all we would have would be the current financial statements. Determining the best investment would be a relative analysis involving the highest multiple of earnings with consideration to a strong corporate structure at that given moment. Issues of revenue potential, future cost factors, operating expenditure plans, and financing strategies would not exist. In fact, most of us would be out of jobs since we get paid to project and manage the uncertainties caused by time.
A slightly more advanced level of analysis would give credit to the fact that items on the balance sheet can grow in value either by their operating potential or just by inflation. This still ignores many components of a fully operational firm. The more complex analysis that comes out of completely integrating all factors of time is a discounted cash flow methodology where we make projections of many facets of the firm’s structure and operations.
Within the complex framework of a discounted cash flow analysis, multiple time-related issues arise. Andy Warhol once said, “They say time changes things, but you actually have to change them yourself.” I rarely quote celebrities, but this epitomizes the issues we deal with in regard to time and discounted cash flow modeling. On one hand, we have to manage concepts that will naturally change over time, such as straight-line depreciation of an asset; on the other hand, we have the ability to change assumptions that affect how the concept changes over time. In the case of depreciation, we can change the useful life of assets that determine the depreciation amounts. These changing time-based variables are true for many of the topics in corporate valuation modeling.

THE NEED FOR A FLEXIBLE SYSTEM

Not only do dates and timing affect multiple parts of an analysis, but they also change frequently. Every day that passes can have a new effect on the analysis. For instance, our model could use the current stock price and shares outstanding to calculate the market value of equity. The stock price changes continuously throughout trading, while the shares outstanding can also change depending on corporate actions on any given day. As we push the analysis date further into the future, values of the many components of the company change: the rates from which variable-rate interest is indexed, debt amortization, asset depreciation, intangible amortization, capital expenditure plans, and so on. Therefore our modeling will require flexibility as to how we enter dates and set up timing.
Revenue is shown on a monthly, quarterly, semi-annual, and annual basis. Typically, models will have only one timing set for a scenario. This can be made flexible for fast customization.
011
Further complicating matters is the division of time into aggregated units. While we could attempt to model out a company on a daily basis or even more granularly on a real-time basis, the amount of data would be overwhelming. In order to rationalize the amount of data and to make the data discrete, we often group data into monthly, quarterly, semi-annual, or annual amounts. Setting such periodicity allows us to see trends and align important events that affect the company. For example, if most of the debt of a company is paying on a quarterly basis, it may be worth projecting the company’s cash flows on a quarterly basis to see how well the company can cover the periodic debt service. shows some of the common possibilities for organizing timing in a corporate model.

THE FORECAST PERIOD

Another issue that we will run into is the limit of our ability to forecast certain items. Corporate valuation using a discounted cash flow methodology is particularly challenging because we are trying to project cash flows that can have multiple uncertain factors. We are trying to capture many capricious elements such as management’s ability to adjust to changing economic and competitive conditions, market changes for unit volumes and prices, and variable capital structures and costs of financing. This is markedly different from other financial analyses, such as project finance or asset-based financings where contracts exist, which define assumptions that allow an analyst to have a clearer path to determining periodic cash flows.
Due to the increasing uncertainty of forecasted variables over time, we limit our detailed analysis to a forecast period. This forecast period is characterized by periods of frequently changing variables. For instance, if we were to take a look at an Internet company during the late 1990s, we would have expected a very high growth rate, with an eventual reduction of growth to a stable level. This period of high growth would be the forecast period. Converse to a high-growth scenario can be one of distress. An example of this is U.S. automakers, such as General Motors and Chrysler. At the end of 2008, they requested government support to stay solvent and were asked by Congress to provide projected financial statements. In their case, they would have had a forecast period that included a contraction of growth, lower price points, and perhaps increased costs until they could return to a stabilized level. The forecast period would focus on using assumptions that caused contracted growth, prices would be lower in each of the periods, and costs would be ramped up.
Forecast periods also can be determined by planned events. For instance, if a company knew it would have an aggressive capital expenditure plan, then the forecast period would be focused on the periods of capital expenditure. But this forecast period for the same company can vary by perspective. For example, the bank financing the capital expenditure would focus on the term of the debt used for the financing as the forecast period. The forecast period is a limited amount of time that analyzes unusual, short-term situations for a firm.

THE TERMINAL PERIOD

If we used only a forecast period in a valuation, we would be attributing value to the company for only those years. While this could be true for some companies, many companies believe they will be in existence in perpetuity—otherwise known as a going concern. For this reason, we must estimate a value for the company in perpetuity. This is done by changing assumptions for the short-term forecast period to a long-term expectation and applying a perpetuity-based formula. Typical changes would include switching the growth rate to a stable expectation, using maintenance capital expenditure assumptions rather than specific plans, altering the short-term working capital expectations, and so on. is a graphical representation of the difference between the forecast and terminal periods. We will look at the details of calculating a terminal value in Chapter 9, but for now we should understand that there will be a distinction between the forecast period and the terminal value.

HISTORICAL TIME PERIODS

Whereas the forecast and terminal periods will be the focus of analysis, the basis for these items is often rooted in historical data. It is convenient to store this data in the financial model since we may use it for multiple reasons. Historical income statement data can give us important information such as revenue growth rates, whereas balance sheet data can show us historical capital structure ratios. Often, historical, audited annual financial statements going back at least three to five years are used for these purposes.
In the forecast period, short-term assumptions are used, whereas in the terminal period we will switch variables to long-term, stable assumptions.
012
Where multiple years of data is useful to calibrate performance assumptions, a thorough analysis looks at trends within a year, particularly since many companies experience cyclicality due to the nature of their business or industry. For instance, agribusinesses have revenues and costs that correspond to the harvest season of the crops that they grow and sell. For this reason, we would want to examine historical data and perhaps structure our forecast period to a level of detail that captures the cycle. Adjusting the analysis for cyclicality can help ensure that there are no periods of stressed cash flow that causes liabilities to go unpaid.
Beyond normal operational trends, we should also be concerned by performance trends caused by unusual market or idiosyncratic forces. What if the industry or region is experiencing a negative trend in performance? How has a change in management affected the company’s performance? To help flush out these details, we may look at the last 12 months (LTM) of a company’s performance in detail. Although it is common to use the LTM, do not feel trapped by convention. If management changed, or if there was a severe industry dislocation further back, it might make sense to adjust a detailed historical analysis to coincide with such events.