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.

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.

FIGURE 2.1 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.

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.

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. Figure 2.2 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.

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.

FIGURE 2.2 In the forecast period, short-term assumptions are used, whereas in the terminal period we will switch variables to long-term, stable assumptions.

FIGURE 2.3 The information from 12/31/2007 is hard coded, historical information, whereas data to the right is based on percentage expectations.

Thus far we have discussed time on a macro level; however, date and timing issues also permeate throughout specific events within the forecast period. Such events could include debt issuance, capital expenditure, intangible acquisition, and so on. For each of these events there are specific dates and timing that initiate or terminate sub-events. For instance, assume a company anticipates purchasing a warehouse one year from the beginning of an analysis. During the first projection year there would be no capital expenditure related to this event. However, on the expected purchase date gross fixed assets would increase and either cash decreases or some other liability financing takes place. One period from the purchase of the warehouse, a depreciation calculation of some type would take place. The depreciation would continue every period until the asset is fully depreciated. At that point, the asset and its accumulated depreciation is kept on the books until it is removed by disposal. Figure 2.4 is a conceptual depiction of the event timing for a single capital expenditure. In Chapter 5 we will examine this concept further, but it is important now to realize that we must build in functionality to monitor and alter outcomes based on these sub-events.

FIGURE 2.4 Many items such as capital expenditures have specific sub-events that require the timing to be monitored.

We are now done with the core dates and timing functionality. Some of the functionality that we have implemented is incomplete at this point. While it is interesting to see the dates change by using a drop-down list and hide or disappear depending on our forecast period, our goal is valuation. If we try to maintain a conceptually based flow, once we have the shell of our model created from dates and timing, we should begin to fill it in. One of the most influential factors of a firm’s valuation is the earnings ability of the company. For this reason we will implement revenue generation and the income statement in Chapter 3.

This Toolbox will cover a number of Excel tools that allow us to control user entry, anticipate variable assumptions, and automate lookups. These include:

Good financial modelers work like computer programmers by reducing error before it can enter the system they are creating. One method is to limit a model user’s possible entries. This can be achieved in a few ways, each having its own advantages and disadvantages. The first one we will explore is creating a data validation list.

FIGURE 2.5 Data validation lists allow a user to select a value from a list of possible values that the model builder creates.

FIGURE 2.6 The Data Validation dialogue box allows a user to designate the cell where the list will be created and the reference for the items that will be contained on the list.

FIGURE 2.7 Named ranges must be used in the Data Validation dialogue box if the user wants to store lists on separate sheets from the cell containing the data validation list.

Financial modelers are consistently tasked with manipulating data throughout their models. Much of this is nonmathematical, but rather more administrative. The OFFSET function is a reference function, which is incredibly useful for moving and referencing data within a model. The function requires the following parameters:

FIGURE 2.8 The OFFSET function is a reference function to return values based on numerical parameters.

FIGURE 2.9 The OFFSET function is valuable to reference data in different directions and orders.

FIGURE 2.10 The starting reference point can be confusing for users new to the OFFSET function. When using OFFSET to reference single-dimension lists, start at the top. For two-dimensional sets of data, start at the corner.

On its own, the MATCH function is incredibly easy. It returns the ordinal value of a lookup value compared to a list of values. The entry parameters for the MATCH function are:

Since this function’s parameters may need to be reread a few times, it is probably best explained by looking at an example. In Figure 2.11 we have a list that contains the possible periodicities that a model could be set to: Monthly, Quarterly, Semi- Annual, and Annual. If a user provided the periodicity that she was looking for, the MATCH function would return the ordinal number from the list. So, if the user typed Quarterly in a separate cell and designated that cell as the lookup value and the list of periodicities as the list of values, then the MATCH function would return a 2.

FIGURE 2.11 The MATCH function returns a 2 because Quarterly is the lookup value and the second item on the lookup array.

We will definitely find utility for the OFFSET and MATCH functions on their own, but the real power of these two functions is when they are combined to find values or reference cells. Recall that the OFFSET function accepts numbers for the number of rows and columns to move away from a reference cell. It would be cumbersome to always have to provide OFFSET numbers as entries. Conveniently, we learned that the MATCH function returns numbers based on ordinal position. Let’s take a look at an example of the OFFSET MATCH combination, first in a decomposed form and then combined.

FIGURE 2.12 To demonstrate the utility of OFFSET MATCH, we will use a periodicity-relatedexample.

FIGURE 2.13 While not complete, we can see in this example that OFFSET uses a number to offset the top of the list and return the correct number of months between periods.

FIGURE 2.14 The final addition is to use the MATCH function to derive the number that is used by the OFFSET function. Combined with a data validation list, the user can now select a periodicity based on name and have the number of months between periods easily returned.

Figure 2.16 shows the completion of both of these efficiencies.

FIGURE 2.15 The OFFSET function is connected to the MATCH function’s return.

FIGURE 2.16 We can clean up the two separate cells by combining the functions.

Although the VLOOKUP function was not used in this chapter, it is frequently used by financial modelers, and therefore should be explained. The V in VLOOKUP stands for vertical. Pairing the words together we have a function that will return a value by looking at information in a vertical list. The entry parameters for VLOOKUP are:

As with many preliminary explanations in this book, a first read-through can be confusing. It’s best to show VLOOKUP within the context of an example. Let’s use an example similar to the one from the OFFSET MATCH combination. Figure 2.17 shows the setup.

FIGURE 2.17 The VLOOKUP function is an alternative to the OFFSET MATCH combination; however, it is less flexible and more cumbersome to use with complex lookups.

Realizing the importance of date functionality, Excel developers created the EDATE function. This function takes a starting date and a numerical entry for months, and returns a date based on those two parameters. For instance, if the starting date were October 1, 2009, and the numerical entry were 3, the date returned would be January 1, 2010. The function accepts parameters in the following way:

FIGURE 2.18 The EDATE function can accept positive or negative values to return a date in the future or past.

FIGURE 2.19 EOMONTH always returns the last day of the month.

The EOMONTH function is virtually identical to the EDATE function, with the difference being the day of month that gets returned. EOMONTH returns the last day of the month that is the specified number of months from the start date. The entry parameters are the same as EDATE:

Many readers are used to IF functions, but we should formally cover them in case there is any confusion. An IF function has one of two possible returns based on a conditional test. In normal-speak, that means we will devise some sort of test and depending on the outcome of that test return a value. The value could differ if the outcome of the test differs. We have quite a bit of flexibility in creating the test, but it must be a conditional test—a test that returns either a TRUE or FALSE depending on a conditional operator. What is a conditional operator? There are many. The conditional operators seen in Figure 2.20 are the most common ones in Excel.

Although this formula is incomplete, we are telling Excel that we want to test the condition 3 < 5. In this case it is TRUE. If the statement is TRUE, then we might want a value to be entered in the cell where this formula resides. If the statement is FALSE, then we might want a different value entered in the cell. We could write:

FIGURE 2.20 Conditional operators help build conditional tests.

Now the cell would have a text VALUE of “OK” since the statement is TRUE. IF functions return the first value that is after the conditional test if the conditional test is TRUE and the second value if the conditional test is FALSE.

This formula tests the value in cell A1 and returns “OK” if it is greater than the value in cell B1. If that statement is FALSE, then cell A1 is compared to cell A2, and “OK” is returned if that statement is TRUE. If all of the conditional tests are FALSE, then “ERROR” is returned. You can nest up to eight IF functions.