## Modeling Strategy

This is another realistic constraint and is easy to implement. In calculating total dividend amount for a year, the model now has to calculate it in two ways, once as 40 of net income and once at a per share dividend rate 10 higher than the previous year and then select the lower of the two. It is important to remember that the growth rate constraint is on dividend per share and not total dividend. If number of shares outstanding remains the same then the two are the same but if shares...

## Building the Model

As always, unless otherwise noted, you can copy the formula for 2003 into the cells for the other years. 1. Calculate STD In J44 enter 30 x 40 12 to reflect the percentage of STD in total capitalization. In F44 enter the formula F 40- F 43 F 45 F 49 F 50 J44. As discussed, the plug here is the amount of total assets the first term , less the total of all liabilities other than debt the second term . 2. Calculate LTD In J48 enter 30 x 60 18 to reflect the...

## The Geometric Brownian Motion Model

Based on extensive analysis of historical data and other considerations, we generally assume in finance that prices of stocks that do not pay any dividend follow a special type of stochastic process known as geometric Brownian motion. The geometric Brownian motion model assumes or implies the following properties for stock prices They are continuous in time and value. They follow a Markov process, meaning that only the current stock price is relevant for predicting future prices in this...

## Stock Prices Are Lognormally Distributed

Another implication of our assumption that stock prices follow a geometric Brownian motion is that stock prices are lognormally distributed, that is, the natural logarithm of stock prices are normally distributed. If S0 is the stock price now and ST the price at time T we can write Note that the mean of the distribution for the logarithm of the stock price ratio is not but - 72 2, which is important. Because the stock price itself is lognormally distributed, we cannot calculate the expected...

## Uses of the Model

This model is even more realistic than the previous model because all corporations would want to keep a minimum balance in the C amp MS account. This level does not have to be constant. For example, if the corporation wants to maintain a C amp MS level tied to sales, for example, 2.1 of sales as in the original assumptions , we can enter 2.1 in cell J27 and then in F44 enter the formula MAX and copy and paste it for the other years. Income Statement and Balance Sheet for Vitex Corp. Liabilities...

## Testing the Model

You have to thoroughly check a model like this. First check it line by line making sure you are using the right assumptions and the formulas you have entered are correct. Also make sure that you have copied over all the formulas for subtotals and totals as well as for calculating the retained earnings and accumulated Statement of Cash Flows for Vitex Corp. Oecrease increase in Accounts Receivable Decrease Increase In Olher Current Assets Increase Decrease in Accounts Payabte increaseADeorease...

## Proportional Returns on Stocks Are Normally Distributed

Because stock prices follow geometric Brownian motion, we can write the proportional return change in the stock price divided by its initial value over a short interval of time as Here, AS is the change in the stock's initial price S over a very short period of time At. From the equation, we see that the proportional return is made up of two components. The first is a certain component and the second is an uncertain component, which makes the proportional return uncertain or random. In the...

## Step 4 Build The Model And Debug It

The best way maybe the only way to learn how to build financial statement models is to work through a number of them, and this is what we will do in the modeling section. Let me mention a few things here that you need to keep in mind when building your model. One of the important advantages of the universally used double-entry bookkeeping system is that it automatically maintains certain essential ties between the income statement and the balance sheet for actual transactions and does not let...

## Growing Annuities in Arrears

For the growing annuity in arrears shown in Figure 7.4 where the first cash flow of A 1 g at time 1 grows at the rate of g per period, we can calculate the present value as A 1 g A 1 g 2 A 1 g This looks a little more complex than the corresponding constant annuity formula, but notice that if we substitute 1 k for 1 r 1 g then the formula for PVG0 becomes the same as the formula for PVC0 with k substituted for r. So _A 1 g A 1 g 2 A 1 g 3 A 1 g 4 A 1 g n-1 A 1 g n -1 n FIGURE 7.4 A timeline for...

## Rolling Period Returns

Sometimes we want to know how an asset performed over a particular length of time 10 years, for example . We can, of course, pick a specific 10-year period to look at, but that may not be representative because we cannot tell what period should be considered representative. So we often pick a length of period, like 10 years, and ask how investment in the asset performed in every possible ten-year window over the whole or part of a longer period for which we have data available. For example, if...

## Annuity Functions

Excel provides several functions to do different kinds of calculations for constant annuities, which you can use for both loans and investments. Constant annuities are equal cash flows either all inflows or all outflows that take place at equal time intervals. Constant annuities are governed by a set of 4 variables if you specify 3, then the fourth can be calculated. The Excel functions for calculating these variables are PV, FV, PMT, RATE, and NPER. These functions actually go one step beyond...

## Free Cash Flow

The regular financial statements, even the statement of cash flows, do not provide a good measure or explanation of the cash that a company is generating or consuming. Free cash flow is the measure generally used for that. Free cash flow is a company's true operating cash flow. It is the total after-tax cash flow generated by the company that is available for distribution to all providers of the company's capital creditors as well as shareholders. It can be thought of as the after-tax cash flow...

## Growing Annuities

Often in real life we encounter annuities where the annuity amount grows at a constant rate over time instead of remaining constant. For example, if you were planning for your retirement, you would probably want to plan to withdraw annually an amount that grows at the expected rate of inflation instead of remaining constant. Otherwise the buying power of your annual withdrawal and your standard of living will steadily decline. Even at modest-sounding inflation rate like 3 , over the years the...

## Using Excels Auto Save in Excel 97 and 2000

Note that in Excel 2002, AutoSave has been replaced by AutoRecover, which is much better. I will discuss it in the next section. In Excel 97 and Excel 2000 you are stuck with AutoSave, which is still a useful feature. If you want Excel to remind you to save your workbooks at a set time interval, turn on Excel's AutoSave. Select Tools O AutoSave to open the AutoSave dialog box. Select the check box labeled Automatic Save Every and then enter an appropriate interval in the Minutes box. You may...

## Constant Annuities in Arrears

Figure 7.2 shows the timeline for a regular constant annuity where A is the constant periodic cash flow, n is the number of cash flows, and r is the periodic discount rate. Most often the word constant is dropped and unless otherwise qualified, an annuity is assumed to be a constant annuity. In a regular annuity, the first annuity payment occurs at time 1, that is, in one period from now or at the end of the first period. Such an annuity is called a regular annuity or an annuity in arrears. We...

## Discussion of the Results

As expected, if these operating goals can be achieved, then Vitex's performance will improve significantly over the years. Study the financial statements and the Income Statement and Balance Sheet for Viten Corp. Property. Plant and Equipment. Gross Liabilities and Shareholders' Equity Total Liabilities and Shareholders' Equity FIGURE 6.12 Model 6 Projected financial statements for Vitex Corp. with negative short-term debt. various financial indicators from this version with those from the...

## Review of Theory and Concepts

Financial statement modeling generally involves modeling all the three primary financial statements the income statement, the balance sheet, and the statement of cash flows. This last one, however, does not have to be modeled independently because it is derived from the other two. The models almost always include a series of financial indicators that can be used to make decisions. It is also customary to prepare a number of sensitivity tables or charts to show how some of the projections...

## Forecasting Line Items

There is no one right way to forecast any line item the method you choose depends on your understanding of the business and what you think will produce good forecasts. The one method that people think of right away but which generally does not work well is regression analysis. If you see any obvious trends in the historical data, use them. Usually, however, the number of data points available are so few that you can judge any trend just by looking at the numbers as well as you will be able to...

## Two Useful Custom Number Formats

In financial models such as in models of financial statements , you often want to display a number in thousands or millions that is, scale it by a multiple of one Funding needed is ,S .0,, million FIGURE 3.7 Examples of custom number formats. FIGURE 3.7 Examples of custom number formats. thousand. To do so, at the end of the format add one comma to display the number in thousands, two commas to display it in millions, and so on. See the examples in Figure 3.7. At times you may want to hide the...

## The Problem

You have been asked to prepare projected financial statements for Vitex Corp. for 2003-2006 starting with the historical statements for 1999-2002. For your fore- 7 Sefng, General amp Admn. Expenses 17 Cash and Marketable Securities 21 Property, Plant and Equipment, Gross 23 Property, Plant and Equipment, Net 26 Liabilities and Shareholders' Equity 32 Other Non-Current Liabilties 40 Interest on cash amp marketable securities 41 Number of shares outstanding Will grow at 5 , based on input from...

## Learning Excel Features

You can save a lot of time by learning Excel features the right way and thoroughly. The best way to learn any Excel feature is to use the try and check method, which means you try it out in a few simple made-up examples generally on a new worksheet and check to make sure that it is working the way you think it should work and that it gives you the right answers. You can check the answers using a hand calculator. If you try to learn a feature by using it immediately in a large model, it may be...

## Creating Other Types of Names

There are two other types of names you should be aware of, although you may use them only in special situations. If you are working with the sales data for three products for six months as shown in Figure 3.3, you may want to name the range B4 B9 as ProdA and the range B4 D4 as Jan and so on. You can create the names one at a time using one of the techniques we have discussed before, or, to do it faster, select the entire table A3 D9, select Insert O Name O Create, and click OK. You can now use...

## Using Workbook and Worksheet Level Names

As mentioned earlier, any name you define in a workbook is available for use in all the worksheets in the workbook. A name you create using one of the standard methods described so far can refer to only one cell or range no matter where in the workbook you define or use it. These names can be called workbook-level names and you can use the same name only once at the workbook level. If you accidentally or intentionally give a different cell or range the same name using one of the standard...

## Contents

CHAPTER 1 Introduction to Financial Modeling 1 Part One Excel for Financial Modeling CHAPTER 3 Advanced Excel Features 29 CHAPTER 4 Excel's Built-In Functions and Analysis Tools 71 Mathematical and Trigonometric Functions 97 Lookup and Reference Functions 101 Part Two Financial Modeling Using Excel CHAPTER 5 How to Build Good Excel Models 119 Attributes of Good Excel Models 119 Using Formula Auditing Tools for Debugging 127 Learning Modeling Using Excel 128 CHAPTER 6 Financial Statements...

## Excel Basics

In this book, I assume that you already know the basics of Excel that is, you have been using Excel for some time and can create spreadsheet solutions for simple financial and other problems, plot charts to present your results, and print out your results. So rather than cover all the basics of Excel in detail, I will first offer some general suggestions here on how to improve your Excel skills. I will then list all the basic features of Excel that you are likely to use extensively. In the...

## Step 2 Define the Input and Output Variables of the Model

Make a list of all the inputs the model will need and decide who will provide them or where they will come from. This is crucial. For example, if you are creating a model to do the business plan for your company, the inputs must come from the business managers. You cannot just guess what sales growth rates they will be able to achieve, how much they will have to spend on plants and equipment to support those sales growths, and so forth. You may not need the actual numbers upfront, but the list...

## Excel and VBA as Modeling Tools

Even in the mid- to late 1990s, Excel was not considered a powerful enough tool for serious financial modeling, in part because the PCs available at the time had speed and memory limitations. With advances in PCs and improvements in Excel itself, the table has now turned completely Excel has become the preferred tool for creating all but the largest and most computationally intensive financial models. The advantages of Excel for financial modeling are so obvious that it is not necessary to go...