## Excel Secrets Everyone Should Know

## 101 Secrets of a Microsoft Excel Addict

This ebook from Francis Hayes gives you professional tools to help you get the most out of your Excel program. Any one of these secrets could be the only one that you ever need to know for Excel, but this ebook includes bunches of those tips and tricks! Just think of all of the useful information you can get from it! If you have ever been frustrated at your lack of progress in Microsoft Excel, this guide will teach you everything that you need to know to harness the powerful functions or time-saving elements of Microsoft Excel. Excel is used by offices all over the world, but so few people take the time to teach you anything important about it. Too much time is usually wasted searching the internet for tips on how to use it more efficiently Learn the best way to master Excel in this ebook!

#### 101 Secrets of a Microsoft Excel Addict Summary

Contents: Ebook

Author: Francis Hayes

Price: $9.97

#### My 101 Secrets of a Microsoft Excel Addict Review

I usually find books written on this category hard to understand and full of jargon. But the author was capable of presenting advanced techniques in an extremely easy to understand language.

Do not wait and continue to order 101 Secrets of a Microsoft Excel Addict today. If anytime, within Two Months, you feel it was not for you, they’ll give you a 100% refund.

## 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 into them. However, for those who have not worked with other programs or programming languages for modeling, it is worthwhile to point out that one of the important advantages of Excel is that with Excel you can create excellent output with very little work. You should learn to take full advantage of Excel's power in this respect. If Excel is so good, then, why bother with VBA VBA is a programming language, and if you do not know anything about programming languages, it will be difficult for you...

## The Different Versions Of Excel

The differences among Excel 97, Excel 2000 and Excel 2002 are mostly cosmetic there is little substantive difference, especially for financial modeling. If you are still using Excel 97 or Excel 2000, there is no reason for you to upgrade. Everything I say in this book applies to all three versions. The earlier versions of Excel are, however, quite different, and if you are using an earlier version, you will need to upgrade to one of these later versions, especially before working with VBA. The only two new features in Excel 2002 that I find useful (and I will discuss them in the appropriate places) are AutoRecover and the Ask a Question window at the right end of the menu bar. One of the conspicuous new features of Excel 2002 is a panel on the right side of the screen. It is called the Task Pane and says New Workbook in its title bar. I do not find it useful, and because it cuts into the visible worksheet area I keep it closed. It is easy to use the File menu to do all the things you...

## Using Excels Auto Recover in Excel 2002

In Excel 2002, AutoRecover has replaced the AutoSave of the older versions. It is a useful improvement, and it keeps working in the background once you turn it on. If you have the AutoRecover turned on and your computer or Excel encounters a problem or stops responding, you will be able to recover most, if not all, of the work that you have not saved yet. To turn on AutoRecover, select Tools O Options and then the Save tab in the Options dialog box. Select Save Auto-Recover Info Every and enter a time interval in the text box next to it. You can also specify an AutoRecover save location. At the specified intervals, Excel will save the necessary information to update your workbook(s) from the last manual save so that if you set the interval at 5 minutes, at worst you will lose only the I do not think this is a substitute for the procedure I recommend below for saving your workbook frequently and at times under a different version name. If you are using Excel 2002, though, you should...

## Using Excels Online Help

Excel has extensive built-in (called online) Help that is particularly useful because it is always right there for you to access. Most people, however, do not take the time to learn how to use it properly to take full advantage of it. Invest a few minutes to learn how the system works and then keep using it. You will quickly become familiar with it, and it will save you a lot of time and frustration over the years. There are two primary ways to access the online Help through the Office Assistant and directly through the full Help window. (In Excel 2002, there is a small window at the right end of the menu bar labeled Ask a Question which works like the Office Assistant, except that it is always open and never gets in your way.) In addition, there are a few shortcut ways of getting Help for certain items, which I will discuss first.

## Customizing The Excel Work Environment

For example, to have Excel show the formulas instead of the numbers in a worksheet, you have to go to the Options dialog box, click the View tab, select Formulas in the Window options section, and then click OK. (Note that this procedure changes the column widths and you may want to adjust them to fit your screen or a printed page.) There are additional customizing options available in the Customize submenu under the Tools menu.

## An Excel notebuilding good financial models

If you've gotten this far in Chapter 1, you've probably put together a few basic Excel spreadsheets. You'll be doing lots more in the rest of this book, and you'll be amazed at the insights Excel gives you over even complicated financial problems. Here are three important rules for good Excel modeling

## Advanced Excel Features

In this chapter, I will discuss a series of advanced Excel features that are used often in financial models. Even if you are familiar with some of these features, you may not know them well and may have difficulty learning them thoroughly from other general Excel books or Excel's online Help. You do not need to learn all of them right away. The Overview of each feature tells you what you may use it for and how often you are likely to use it. You should read the Overviews for all of these features to get an idea of what tools you have access to and then learn the ones that you think you will use now. You can come back and learn the others as you need them. The large number of built-in functions that Excel provides is one of its most important advanced features. I will cover a few of these functions in this chapter and then devote the entire next chapter to a discussion of the other built-in functions as well as the tools available in Excel's Analysis ToolPak.

## Using Excels Normal Functions

Excel Normal Functions for N(0, 1) Figure 2.7 Excel's general normal distribution functions in the SNorm sheet Figure 2.7 Excel's general normal distribution functions in the SNorm sheet Excel provides an excellent range of functions for data summary, and for modelling various theoretical distributions. We make considerable use of them in both the Equity and the Options parts of the text.

## Advanced Excel Functions and Procedures

The purpose of this chapter is to review certain Excel functions and procedures used in the text. These include mathematical, statistical and lookup functions from Excel's extensive range of functions, as well as much-used procedures such as setting up Data Tables and displaying results in XY charts. Also included are methods of summarising data sets, conducting regression analyses, and accessing Excel's Goal Seek and Solver. The objective is to clarify and ensure that this material causes the reader no difficulty. The advanced Excel user may wish to skim the content or use the chapter for further reference as and when required. To make the various topics more entertaining and more interactive, a workbook AMFEXCEL.xls includes the examples discussed in the text and allows the reader to check his or her proficiency.

## Excels BuiltIn Functions and Analysis Tools

Excel provides numerous useful built-in functions in several different categories as well as several useful tools in the Analysis ToolPak. In this chapter, I selectively cover only the functions and tools that I think you will find useful for financial modeling. Of those, I discuss in some detail the functions that I think you will use most often or that you may have difficulty fully understanding from Excel's Help. For the others, I provide only short descriptions so that you know they are there, and if you need to use any of them you can look them up in Help. In the Financial Functions category I have included all the available functions. However, I have not covered here any of the functions in the Database and Engineering Functions categories, because financial modelers will rarely use any of the functions in these categories. (Appendix C provides a list of selected Excel and VBA functions available, with brief descriptions.) In organizing the functions, I have used the same...

## 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.) Remember that most Excel features are designed to be intuitive if you understand what a feature is supposed to do, you may be able to figure out how it works just by trial and error. This is a good approach to take because the more features you figure out on your own, the better you will get at doing so and the more confidence you will develop with Excel. But do not become too stubborn to look for help if you cannot get a feature to work in two or three trials. Sometimes you can easily waste hours trying to make a feature work by trial and error. Also, it is good to look...

## Accessing Functions In Excel

Excel provides many worksheet functions, which are essentially calculation routines that have been coded up. They are useful for simplifying calculations performed in the spreadsheet, and also for combining into VBA macros and user-defined functions (topics covered in Chapters 3 and 4). As well as all Excel functions, the Paste Function button also provides access to the user-defined category of functions which are described in Chapter 4.

## Useful Excel Functions

Excel has many probability and related functions of value in OR management. One of the most useful is the Percentile() function, which returns the percentile of a given data set. It is illustrated in the workbook Operational Risk 02.xls worksheet Case study 2.2 and also discussed further in review question 6 below. Other valuable functions include the Normsinv(), which calculates the percentile function for a standard normally distributed random variable, and Normsdist(), which calculates the probability distribution of a standard normally distributed random variable. Other useful functions for continuous probability distributions include Betadist() for the cumulative beta probability density function, Chidist() for the chi-squared distribution, Expondist() for the exponential distribution, Fdist() for the F distribution, Gammadist() for the gamma distribution, Lognormdist() for the lognormal distribution, Tdist() for the Student's t distribution, and Weibull() for the Weibull...

## Documenting Excel Models

There is no standard format or structure for documenting a model. So you have to be guided by the objectives mentioned above. Here are some common approaches and tools for documenting Excel models. Every model needs to be documented differently and everyone does documentation differently. Over time you will develop your own style.

## Common Ways To Document Your Excel Model

One great advantage of Excel is that it makes documenting a model convenient. If your model is well-organized, tables have clear titles, footnotes, and column and row labels, important variables have descriptive names, formulas are easy to read and understand, and so on, then it may require very little additional documentation. Remember that it is easy to make your worksheets show the formula instead of the values in the cells, and it is easy to print out the worksheets in this form. This by itself can constitute a significant part of your documentation provided the formulas are easy to understand and are short. You can supplement it with printouts of a list of all the cell comments and cell and range names and addresses in your workbook. (To have a worksheet show formulas instead of values, select Tools O Options. In the View tab, select Formulas under Window options. If you print out worksheets with formulas, make sure you choose the printing option to include the row and column...

## Learning Modeling Using Excel

You will learn to model with Excel faster if you systematically work through the models in the following chapters of Part Two instead of just browsing through the completed models. (For each model there is a workbook on the CD with all the necessary data and the solution.) If you are comfortable with the topic of a Whenever you need to use in a model an Excel feature with which you are not thoroughly familiar, go back to the appropriate chapter and section in Part One to fully understand it before trying to use it in the model. Trying to use a new feature in the middle of a complex model for the first time often makes things unnecessarily frustrating.

## Excel Note Data tables

This may be the appropriate place to review Data Tables, which are covered in Chapter000. What may be confusing in the previous data table is the 65.71 in cell B74. This is a reference to the share price calculation in the initial model The data table asks Excel to redo this calculation for the sales growths in cells A75 A84.

## Excel Functions For Hypothesis Testing

The common sampling probability distributions of the normal, Student's t, chi-square, and F distributions can be accessed in Excel using the functions Normsdist(), Tdist(), Chidist(), and Fdist() respectively. Inverse or per-centile functions are also readily available. We have made some use of Normsinv() for the normal distribution. We can also use Tinv(), Chiinv(), and Finv() for the percentile functions of the Student's t, chi-square, and F distributions, respectively. The tables in Appendix 1 were all produced using Excel. Excel also provides a number of test procedures based on sampling distributions. Chitest(), Ztest() and Ttest() based on the Chi squared, Normal, and Student's t sampling distributions, respectively.

## Using Excel Functions and VBA Functions in Code

As seen in the Quartiles subroutine, to use an Excel function in a VBA procedure, you need to prefix the function name with Application. In Excel 2000, the function name can be prefixed by WorksheetFunction or Application. For compatibility with earlier versions of Excel, we continue to use Application. In contrast, VBA functions require no prefix, as we have seen with MsgBox, IsNumeric, InputBox, etc. In fact, there are relatively few specific numeric functions built into VBA, currently just Abs, Cos, Exp, Int, Log, Rnd, Sgn, Sin, Sqr and Tan, where Log is the natural log (usually denoted Ln). So some VBA functions are spelt differently from the parallel Excel function (e.g. Sqr in VBA for square root, whereas the Excel function is SQRT). To resolve the conflict, if both a VBA and an Excel function exist for the same calculation, the VBA form must be used rather than the Excel function. (For example, write Log rather than Application.Ln when you require the natural logarithm of some...

## TABLE 41 Sample Excel Sheet for Inputting Data

Put a getmatrix command in a nearby cell of the Excel spreadsheet which includes the name of the variable in MATLAB and the range of cells allotted for the variables. Debugging Excel Link See Appendix B Excel Link Debugging, on page 455, which lists a number of tactics for debugging this useful but tricky utility. 1. Get the information into the MATLAB workspace. This can be done in any of the normal methods, using Excel Link, importing from other sources, direct input, or loading data stored in .mat files. If you are organizing data from multiple entities or concepts, it may be useful to have the data from only one concept at a time in the workspace. 1. We assemble the information on the subject businesses, including sales history, profit margins, cost of capital, projections of future performance, and other relevant information in a worksheet. We then get that information into the MATLAB workspace by using Excel Link. 56 HTML was created by Tim Berners-Lee at CERN in Geneva. 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 want to change the interval from time to time to suit your pace of work.) Decide if you want to save only the active workbook or all open workbooks, and then select the check box labeled Prompt Before Saving. It is important to select this option because otherwise Excel will automatically save your workbook(s) at the specified interval without asking you and overwrite the previously saved copy of the workbook. You may not always want this to happen. If you select the Prompt Before Saving option,...

## Excel note Relative versus absolute copying

The dollar signs within a formula indicate that when the formulas are copied the cell references to the model parameters should not change. The technical jargon for this in Excel is absolute copying as opposed to the relative copying when variables are indicated without dollar signs. The distinction between absolute and relative copying is critical for financial planning models if you fail to put the dollar signs correctly in the model, it will not copy correctly when you project years 2 and beyond.

## Using Regression on Excel

Spreadsheet programs such as Excel have an easy-to-use regression routine. To utilize Excel for regression analysis, three steps need to be followed 3. Click Analysis ToolPak. (If Analysis ToolPak is not listed as an available add-in, exit Excel, double-click the MS Excel setup icon, click Add Remove, doubleclick Add-ins, and select Analysis ToolPak. Then restart Excel and repeat the above instruction.) Excel Regression Input Dialog Screen Excel Regression Input Dialog Screen Excel Regression Output Exhibit 16.5 shows an Excel regression output that contains the statistics discussed so far. Note To obtain a scattergraph, use Excel's Chart Wizard. The result shows

## Two Key Differences between Excel and VBA

There are two key differences between Excel and VBA that you need to pay special attention to while you are learning VBA. Afterwards it will become second nature. First, in Excel almost everything happens only once and each cell represents only one thing. For example, we looked at a loan amortization problem in both Excel (in Part Two) and VBA (in the previous chapter). In the Excel version, we have a separate cell for the year-beginning balance for each year. Each value is calculated only once and stored in its own cell. In VBA, we often use the same variable again and again, as we did with yrBegBal to calculate the year-beginning balance for all the years. If we want to preserve the intermediate values, either because we will need them in later calculations or for some other reasons, we have to write them down or store them somewhere before we calculate the value of the variable again. VBA does not do this automatically you have to include instructions in the code to do it. (In the...

## Using Excels Goal Seek

We saw before that another problem of using Goal Seek in Excel models is that you cannot automate its use by embedding it in a formula in the worksheet. It has to be used manually, and a user who does not know how to use Goal Seek will not be able to use the model. As we will see, you can program Goal Seek into a VBA model that the user can run and he will not even have to know that he is using Goal Seek.

## General Overview of Microsoft Excel 2007 Features and Functionality

This appendix provides a targeted overview of Microsoft Excel 2007 features and functionality related to building financial models. While many full-length books on the market today cover each feature of Microsoft Excel in detail, this appendix covers the most frequently used features of Excel at a general level. As this book assumes a basic level of understanding of Microsoft Excel, this appendix should serve as a reference and or starting point from which you can delve more deeply into the vast capabilities of Excel. Note that this appendix applies to Microsoft Excel 2007, which differs from earlier versions of Excel in a number of significant ways.

## Two options for connected Excel XY Charts

Tell Excel where to put the graph (in this case, on the spreadsheet labeled Merck data , which is also the spreadsheet where our data is stored. Change 1 The Excel default graph has a murky gray area where the data is graphed. This looks alright on the screen, but it looks terrible when you print it. All the graphs in this book have this -gray graph area blanked out. To do this, mark the graph area

## Excel and VBA BuiltIn Functions

This appendix contains categorized selected lists of the built-in functions available for use in Excel (worksheets) and VBA along with a brief description for each. I have used the same categories as are used by Excel in the Paste Function dialog box to make it easy to find a function you are looking for. Each category includes both the available worksheet and VBA functions, and the scope of each function (discussed below) indicates where and how you can use it. For brevity, the description of each function includes only the information needed to quickly decide if a particular function may be useful for a particular model or analysis. If you are not familiar with the function, you will have to look up its description in Chapter 4 Excel's Built-In Functions and Analysis Tools and also in Help.

## Excel and VBA Functions for Correlation Covariance and Variance

The correlation between two variables can be calculated in Excel using the Correl() function. Covariance can be calculated using the Covar() function. Given a sample of risk indicators or OR events, the variance and standard deviation are best calculated using the Var() and Stdev() functions, respectively. An alternative, if we have the entire population, is to use the Varp() function. We discuss the calculation of these measures of dispersion using sample data in more detail in the next chapter. Excel does not have a function for calculating the variance of the sum of two or more random variables. However, it is straightforward to write a simple function to calculate the variance between two random variables. We do this in the function Var_2(). The code for this function is Once entered into Excel, the function can be called by entering Var_2 (datarange1, datarange2). We illustrate its use in more detail in Case Study 4.1 and in the Excel workbook Operational Risk 04.xls.

## Excel notesolving circular references

Financial statement models in Excel always involve cells that are mutually dependent. In our model, for example, the interest earned on cash depends on the profits of the firm, but the profits depend on the interest earned on cash. Another example of mutual dependence in our model involves the fixed asset accounts Fixed assets at cost are the sum of net fixed assets plus accumulated depreciation, but accumulated depreciation is a function of the fixed assets at cost. As a result of these inevitable mutual dependencies, the solution of the model depends on the ability of Excel to solve circular references. To make sure your spreadsheet recalculates, you have to go to the Tools Options Calculation box and click Iteration. If you open a spreadsheet that involves iteration, and if this box is not clicked, you will see the following Excel error message Microsoft Excel Microsoft Excel cannot calculate a formula, Cell references in the formula refer to the formula's result, creating a...

## Common Excel Link Problems

Excel indicates Runtime error '429' Active X component can't create object. 2. Excel does not start MATLAB automatically. 3. Your commands show up in Excel with errors. 4. Excel Link shuts down entirely, taking Excel with it. If you are having problems with Excel Link, try the following Check Excel Link Files and References 1. Check, and if necessary reinstall, the correct Excel Link files, using the Excel Tools Add-Ins function. If you have switched MATLAB versions, try the steps listed under Run the Program with A Newer Version of MATLAB on page 456. 1. Most users of Excel Link will probably also use a Microsoft operating system, as Microsoft also sells Excel. Such users should review Appendix A, Troubleshooting, on page 449. 1. MATLAB should start automatically with Excel if Excel Link is working properly. If it does not, a brute force method is to use the Excel command Tools Macro, and then enter matlabinit. 1. If you run both a current version and the R11 versions of MATLAB, note...

## Excel Solutions

The spreadsheets demonstrate how Excel can be used as a prototype for building models. Within the individual spreadsheets, all the formulas in the cells can easily be examined and we have endeavoured to incorporate all intermediate calculations in cells of their own. The spreadsheets also allow the hallmark ability to 'what-if' by changing parameter values in cells. Some of the VBA procedures are macros, normally seen by others as the main purpose of VBA in Excel. However, the majority of the procedures we implement are user-defined functions. We demonstrate how easily these functions can be written in VBA and how they can incorporate Excel functions, including the powerful matrix functions. The Goal Seek and Solver commands within Excel are used in the optimisation tasks. We show how these commands can be automated using VBA user-defined functions and macros. Another under-used aspect of Excel involves the application of array functions (invoked by the Ctrl+Shift+Enter keystroke...

## 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 process I will also point out things to which you should pay special attention and provide other guidance on the safe and efficient use of Excel. If you are not familiar with any of these basic features of Excel, you should learn it now using Excel's online Help or other general Excel books. Knowing these basic features well is essential for learning the advanced features covered in the next chapter as well as for developing your financial modeling skills. Incidentally, throughout the book I refer...

## Excel Link Debugging

For users comfortable with spreadsheets, Excel Link greatly increases the ability to import and report data. We suggest a strategy for importing and reporting data using Excel Link in Chapter 4, Importing and Reporting Your Data, on page 63. However, Excel Link is not as reliable as MATLAB. Furthermore, it has a number of quirks that often require debugging. We collect a number of troubleshooting suggestions into this appendix to assist you in using this tool. Keep in mind that this is not a book on Excel, and there will be future versions of both Excel and MATLAB. Therefore, expect many of these suggestions to eventually become obsolete, and always check the help information for your specific version.

## My Assumptions about You and the Users of Your Models

In this book I assume that you know the basics of finance and can solve by hand most of the problems for which you will be creating models. I also assume that you are familiar with the basics of Excel and have experience creating spreadsheet solutions to at least simple problems. You do not need to have knowledge of Excel's advanced features or of modeling I will cover both in detail. You also do not need to have any knowledge of VBA. A key objective of the book is to teach you VBA and modeling using VBA from scratch by way of an easy and effective method. Another important assumption I am making is that you will be developing the models primarily for your own use or for use by people who have some experience with Excel, but not necessarily with VBA. When you create models for use by people who have little or no familiarity with Excel, it requires adding special user interfaces to make the models easy to use. One must build into them special features to make them bulletproof that is,...

## Sidebar Functions and Dialog Boxes

The dialog box which comes with an Excel function is a handy way to utilize the function. There are several ways to get to a dialog box. We'll illustrate with the example of the FV function in Section 1.1. Suppose you're in cell B16 and you want to put the Excel function for future value in the cell.

## Changes in the Third Edition

Part VI on Financial Planning contains significant new material. Chapter 17 on Financial Statement Analysis now discusses the measurement and interpretation of economic value added. An Excel spreadsheet with a long-term financial plan has been integrated into Chapter 18. Chapter 19 on Working Capital Management and Short-Term Planning similarly contains a cash management spreadsheet.

## Step 3 Decide Who Will Use the Model and How Often

Own use or for use by others who are familiar with Excel and understand the model, at least to some extent. When you create models for others' use, it involves much more work. You have to make sure that these people cannot enter data that do not make sense, they cannot accidentally damage parts of the model, and they can get the necessary outputs automatically and so forth. These are collectively called the user interface, and the more elegant, more easy to use, and more robust you want to make a model, the more work it is. You also have to plan for many of these features ahead of time.

## Additional Information

Building Financial Models with Microsoft Excel is an independent publication and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft Corporation. Microsoft, Microsoft Excel, and Windows Vista are either registered trademarks or trademarks of Microsoft Corporation in the United States and or other countries. Microsoft product screen shots are reprinted with permission from Microsoft Corporation.

## Step 4 Understand the Financial and Mathematical Aspects of the Model

It usually takes beginners a lot of time to create a model and they often think that it is their Excel or VBA skills that are slowing things down. This may be partly true, but at least as often the problem is in their understanding of the finance and mathematics of the model they are trying to create. You will save lot of time if you do not even sit down in front of the computer to create a model until you are sure that you know how to solve the problem.

## Step 5 Design the Model

There are two aspects to designing a model. One is to sketch the steps that Excel or VBA will have to follow to solve the problem. For simple models, you may want to write down only the broad steps or perhaps even do it in your head. For more complex problems, however, you should work on paper and use a degree of detail that suits your level of experience and the complexity of the problem. The less experience you have, the more detailed the sketch should be. Once again, remember that this may seem like a waste of time, but ultimately it will save you time compared to plunging into your spreadsheet or VBA program without such a sketch of the model. The other aspect of design is planning how the model will be laid out in Excel or VBA. Are you going to do the entire model in one spreadsheet (or VBA module) or split it into several spreadsheets (or VBA modules or procedures) Editing an Excel or VBA model is easy. So you do not have to decide every detail ahead

## Step 7 Test the Model

You have to take somewhat different approaches to testing and debugging a model depending on whether you are working with Excel or VBA. Both Excel and VBA provide some special tools for this purpose I will discuss these tools and provide suggestions on how to debug models in Excel and VBA in later chapters. Here are a few helpful hints that apply to both Checking a model's output against hand-calculated answers is a common and effective approach to debugging. In some situations, doing hand calculations may not be practical, but you may be able to use Excel itself to do some side calculations to test individual parts of the model.

## Step 8 Protect the Model

Once you have completed a model, and especially if you are going to give it to others to use, you should consider protecting it against accidental or unauthorized changes. In addition, you may also want to hide parts of the model so that others cannot see certain formulas, data, and so on. Excel provides several flexible tools that you can use to hide and protect parts or all of your model. A good strategy is to cluster and color code all the input cells of a model and protect and hide everything else in the workbook.

## Steps In Creating A Model

Whether you are creating a financial model using Excel or VBA, you must take a systematic approach. A systematic approach always involves planning ahead and this takes some time. Most people do not like to plan and think they can save time by starting to build a model right away without spending time on planning. However, for all but the simplest models, not taking the time upfront to do some planning and not taking a systematic approach ends up being both frustrating and a waste of time. Here are the key steps you should follow in creating both Excel and VBA models. The details vary somewhat depending on whether you are working with Excel or VBA, and I will discuss them in later chapters. You should keep two other things in mind. First, in practice, you do not have to follow the steps strictly in this order, nor do you have to finish one completely before going onto the next one. Most of the time you will have to go back and forth to some extent. It will depend on the circumstances....

## Organization of the Book

We present methods to get your data into the MATLAB environment and to report it out. The chapter also contains a specific introduction to the use of Microsoft Excel as an environment for easily collecting, importing, and reporting data. Three appendices to this chapter discuss XML and structured data, creating and using custom data structures, and also importing files of various formats. Most readers will again benefit from these chapters, although those with some familiarity with the MATLAB environment will skim portions of them. number of charts. The chapter starts with a review of the classic rules for graphical excellence and descriptions of the all-too-common graphical errors such as chartjunk and distortion due to improper use of 3-D graphics. There are very few texts in the social sciences which offer guidance in the proper use of graphics, and we intend this to be one.

## Target Audience For This Book

Building Financial Models with Microsoft Excel is for business professionals, entrepreneurs, and students who currently, or would like to, create or use financial models and or statements as a part of their work. This book is targeted at individuals with a beginning to intermediate level of experience with both Microsoft Excel and finance accounting. While many business professionals and students have a working knowledge of Excel, few people possess the skill set required to build and maintain a financial model from the ground up. This is surprising, given the fact that several hundred thousand new businesses are launched and several hundred thousand business students graduate each year in the United States alone. I have designed this book as a practical guide to get you started building a financial model quickly. As such, electronic copies of each of the examples and answers in the book are provided as Excel worksheets on a compact disc that is included with the book.

## Saving Workbooks under Different Version Names

Whenever you save a workbook using any of the three methods I mentioned before, Excel overwrites the copy you had saved previously under the same name, and the old copy is permanently lost. At times, this may not be what you want. For example, you may later discover some mistakes you made along the way and want to go back to an earlier point to start over. Or you may want to go back to an earlier point and take a different direction with your model. Remember that you can backtrack using Excel's Undo only through the changes you have made since you saved the workbook the last time. Once you save a workbook you cannot backtrack to any earlier stage. I find it safer and more convenient to save my workbooks under different version numbers as I go along so that I can easily backtrack as much as I want. I include in the name of my first workbook of a model the version number V1 (for example, Retirement Planning V1 ). As I work, I keep saving the normal way. After I have made some progress...

## Using the Office Assistant to Get Help

To get help on any topic, click on the Office Assistant, which will open a popup window. In the box near the bottom of the window, type a description of what you want help on, and click Search. You do not have to type in a full sentence or question you can just type in one or more keywords that you think Excel will recognize. After searching, the Assistant will show you a list of topics. Click the one that sounds closest to what you are looking for and the Microsoft Excel Help window will open up with the help information. Working with this Help window and finding additional information by clicking on various items it presents is fairly straightforward. Remember that once you start exploring the related topics by clicking on them, you can move back and forth among them by using the left and right arrows in the top pane of the Help window. The only problem with finding help on something with the Office Assistant is that you often have to know the keywords Excel uses for it. Otherwise,...

## Using the Full Help Window to Get Help

This tab is particularly helpful if you want to learn about some aspect of Excel instead of looking for help on something specific. Also, if you have not been able to find help on something through the Office Assistant because you did not use the right keyword, you may be able to find it through this tab by guessing which chapter and section includes the information you need.

## Using Keyboard Shortcuts

Note that the keyboard shortcuts for many submenu items (for example, Copy in the Edit menu) are shown next to them in the dropdown menus where they appear. There are also shortcuts for navigating the worksheet and doing many other things efficiently. In Appendix A, I have included a selected list of keyboard shortcuts that you are likely to find useful for everyday work. To see complete lists of all keyboard shortcuts organized in several different ways, search in Excel's Help under keyboard shortcuts. You may want to personalize the list in Appendix A by adding to it other shortcuts you want to learn and use. However, remember that unless you use a shortcut frequently, you will soon forget it. So learn only the ones you think you will use, especially the ones that work across several office applications. For example, Ctrl+S saves the current workbook in Excel and also the current document in Word.

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

Make a list of the tabular, graphical, and other outputs the model needs to create. To some extent, these should be driven by the decisions that will be made based on them. One advantage of Excel is that a lot of the output can be just printouts of your spreadsheets, provided the spreadsheets have been laid out properly. If you plan ahead and lay out your spreadsheets with the outputs in mind, you will save yourself a lot of time later on.

## Why Do I Need the SAP Business Information Warehouse

Standardized reporting across all SAP systems and modules Data from external systems can be integrated Simple navigation in Web reports Excel functions in the BEx Analyzer The SAP BW system includes an easy-to-use reporting tool. You can either display reports in an Excel worksheet, with full support for the Excel functions, or embed them in an HTML template for display in a Web browser.

## Entering And Working With Formulas

Ever possible. (See the more detailed discussion on creating formulas by copying and pasting in Chapter 3 Advanced Excel Features.) Learn the order in which Excel performs the mathematical operations within a formula and how you can use parentheses to change this order to suit your needs. You may also want to use parentheses in a formula even when it is not essential, either to make the formula more readable or to make doubly sure that Excel will do the calculations in the exact order you have in mind. If Excel has a problem with a formula you have entered or in calculating its value, it often displays an error code starting with a pound sign ( ). For explanation of what the different error codes mean, search for help on Error values and then choose Troubleshoot formulas and error values. If the entire cell is filled with signs then the cell is not wide enough to display the value. You have to either increase the column width or change the number format of the cell to fit in the...

## Controlling When Formulas Are Calculated

While the automatic calculation is turned off, you can press F9 to force recalculation of formulas in all open workbooks or Shift+F9 to recalculate formulas only in the active worksheet. (If you are using an older version of Excel 97, use Ctrl+Alt+F9 to force a complete recalculation.)

## Simple Binomial Tree

1.5 Excel solutions 3 1.7 Related Excel workbooks 5 Part One Advanced Modelling in Excel 7 2 Advanced Excel functions and procedures 9 2.1 Accessing functions in Excel 9 2.3.3 Using Excel's normal functions 15 2.13.7 Summary of Excel's matrix functions 37 Summary 37 3.4.5 Using Excel functions and VBA functions in code 52 4.8 Using Excel and VBA functions in user-defined functions 85

## Learning the Features Efficiently

I will explain most features by walking you through one or more examples of its use. To learn a feature efficiently, copy its example(s) from the workbook Chapter 3 Advanced Excel Features from the CD, clear all the cells other than those with headings, labels, and input data (the cells shaded gray), and then work through the example following the instructions. Once you are sure you have the right solution, you may want to compare it with the completed example from the CD. If necessary, repeat the process and also try out the feature on some other examples you make up for yourself. Do not try to learn a feature by looking through the completed example first. You will learn much faster if you work through the example first on your own. Also, do not try to use the feature in a large model Remember that the fastest way to learn any Excel feature is to experiment with it.

## Copying Formulas Using Absolute And Relative Cell References Overview

The ability to create formulas in many cells by copying them from one cell is one of the most powerful and timesaving features of Excel. It is no exaggeration to say that without this feature, spreadsheet programs like Excel would never have gained the popularity that they have. It is that important.

## The Spreadsheet Modeling Series

N Spreadsheet Modeling in Corporate Finance, n Spreadsheet Modeling in the Fundamentals of Corporate Finance, n Spreadsheet Modeling in Investments, and n Spreadsheet Modeling in the Fundamentals of Investments. Each book teaches value-added skills in constructing financial models in Excel. Complete information about the Spreadsheet Modeling series is available at my web site

## Copying Using Relative Cell Reference

If you look at cell C10, the formula reads C9*C6, which is different from the formula you had in B10 because when copying the formula, Excel intelligently modified it. Excel recognized that you do not want to copy the formula literally. Instead, what you really want is to multiply the values in the cells 1 row and 4 rows above the cell into which you are pasting the formula, that is, for the copied formula, it used cell addresses relative to the address of the cell into which you are pasting the formula. Unless you tell Excel to do otherwise we will see in a moment how you do this Excel copies formulas using relative cell references. This is what you want most of the time.

## Copying Formulas with Absolute Cell References

As before, Excel copied the formula using relative reference. The formula in C11 therefore reads C6*C9*(1-C3). But C3 is blank or zero and is not the discount the bookstores get. This is what caused the problem. You really wanted Excel to continue using B3 and not change it to C3 or D3 as you pasted the formula into the different cells. You can specify this by changing the original formula in B11 to B6*B9*(1- B 3), where the signs in B 3 tell Excel that in pasting the formula, it should not modify the cell reference relative to the cell where the formula is being pasted. The B 3 is called an absolute cell reference, which is not modified when formulas are copied and pasted. If you now copy and paste the new formula into cells C11 and D11, you will get the right answers. If you look into the pasted formulas, you will see that Excel has left the B 3 part of it as is in both of these cells.

## Creating Names Automatically

To do so, select the range A4 B8 and then select Insert O Name O Create to open the Create Names dialog box. Note that Excel has already checked the box labeled Left column because it rightly guessed that you want to use the texts in the column to the left to create the names. Click OK and Excel will create the names for you. Note that if a text you are trying to use will result in an invalid name (for example, if it has a space in it), Excel will modify it to create a valid name Monday Sales will be modified to Monday_Sales. But sometimes the modifications that Excel makes may not be what you want. Make sure that the texts you are using will result in valid names and always double check the names you create using the automatic procedure.

## Suggestions for Faculty Members

A technique that I have used for the last seven years is to require students to do big spreadsheet modeling projects in groups. I assign students to groups based on a survey of students, where they self-rate their own Excel skills on a scale from 1 to 10. This allows me to create a mix of Excel skill levels in each group. Thus, group members can help each other. I have students write a report to a hypothetical boss, which intuitively explains their method of analysis, key assumptions, and key results.

## Incorporating Names in Formulas

You can enter names in formulas in a few different ways. You can manually type in the names instead of cell addresses as you write a formula or, better yet, use the pointing method to write formulas. With this method, Excel will automatically use names instead of addresses for cells and ranges that you have already named. You can also have Excel paste a name into a formula. To do so, when you get to the point in a formula where you want to enter a name, select Insert O Name O Paste, which will bring up the Paste Name dialog box. Now doubleclick the name you want to insert or select it and click OK. (You can also get to the Paste Name dialog box by pressing F3.)

## Applying New Names to Existing Formulas

When you create new names for cells and ranges, Excel will not automatically substitute them for the corresponding cell or range addresses in the existing formulas. To do so, select Insert O Name O Apply to bring up the Apply Name dialog box. In the list of names in the Apply names box, select the name(s) you want substituted in the existing formulas and then click OK. (You can leave the two check boxes at the bottom selected, or, to be safer, you may want to deselect the check box labeled Ignore Relative Absolute.) Note that when you use Apply with a newly created name, it replaces references to the cell only in formulas on the same worksheet and not in formulas in the other worksheets that refer to the cell. So if you are creating a multi-sheet model, make sure you create all the names you plan to use before writing your formulas and then use the names as you create the formulas.

## Using Names with Different Types of Cell or Range References

Excel will allow you to create names with relative or mixed cell references as well. But they are rarely used, and you should avoid them because they can be confusing and work in unexpected ways. As we will see later, if you have to develop a large, complex model where the formulas are likely to become long and difficult to understand and check, you may be better off developing it in VBA instead of Excel.

## Using Workbook and Worksheet Level Names

Creating worksheet-level names is extra work. Why would you want to do it Because at times it can be a powerful tool. Suppose you have created a model on a worksheet to analyze the data for January and your model uses several names. When the February data comes in, ideally you want to be able to do the same analysis for February by creating a copy of the January worksheet and then entering the February data. This simple procedure will work fine if you created and used worksheet-level names in the January worksheet, but it will not work if you used workbook-level names that referred to data specific to a month. (When you copy a worksheet or a part of it that includes formulas that use worksheet-level names to a different worksheet, Excel automatically creates and uses corresponding worksheet-level names in the new worksheet.)

## Limitations of Using Names in Formulas

Unfortunately, using names in formulas is not as useful as it appears in the beginning. One of the reasons that spreadsheet programs like Excel have become so popular is that if you create a complex formula in a cell using the a proper mix of relative, mixed, and absolute cell references, you can copy it into hundreds of cells where it will work properly the program will make the necessary adjustments to the formula as it is pasted into the different cells. Because names can be used primarily in place of absolute references, they cannot help much to make these complex formulas easier to read or check. As you will find out, VBA can be of great help in situations where formulas get unwieldy.

## Using Menus And Other Commands

Note that when you open a menu from the menu bar, the dropdown list may initially show only a few of the submenu items (including those you used recently) instead of all the submenu items available under the menu. You can see the rest by clicking on the downward-pointing double arrows at the bottom, or, if the right option is active in your Excel, the rest of the submenu items will appear automatically after you wait a few seconds. If you find the wait irritating, select Tools O Customize and in the Options tab deselect Menus show recently used commands first. (This will make the same change in all the Office applications on your computer.)

## Dates And Timing On The Inputs Sheet

Working with the 30 360-day-count system is made very easy in Excel, thanks to the DAYS360 function. This function requires three inputs a start date, an end date, and a method. Another popular day-count system is actual 360, which is primarily used for money market securities and U.S. Treasury bills. This system calculates an interest period as the actual number of days between two dates. It is important to note that to represent the day difference as a fraction of a year, the denominator uses 360 days, rather than 365. Since the natural format for dates in Excel are serial numbers, beginning with 1 for January 1, 1900, the actual difference between two dates can be calculated by subtracting the beginning date from the end date.

## How To Build Your Own Spreadsheet Model

The Excel PV function can be used to calculate the present value of a single cash flow, the present value of an annuity, or the present value of a bond. For a single cash flow, the format is -PV(Discount Rate Period, Number of Periods, 0, Single Cash Flow). Enter -PV(B5,B6,0,B4) in cell B18.

## Setting Up Data Validation

One important choice in the dropdown list under Allow is List. You will use it when you want to restrict cell entries to the data from a list you specify. If the list is short (for example, you want the user to enter only P or C), enter the valid entries separated by commas in the box under Source. To create a longer list of valid entries, type them either down a single column or across a single row on the same worksheet, making sure you do not include any blank cells in the list. Then provide reference to the range by selecting it while your cursor is in the Source box. You can also name the range and enter the name of the range in the box. If you want Excel to show in the restricted cell (when it is selected) a dropdown list of the valid entries that the user can choose from instead of typing in an entry, make sure that the check box to the left of In-cell dropdown is selected. Leave the check box to the left of Ignore blank selected. To have Excel display an appropriate message if...

## Controlling the Accuracy of Your Answer

You can control the accuracy of the answer and how long you want Excel to try to produce an answer before stopping. To see how, select Tools O Options and then the Calculation tab. You can set two conditions here in the section for Iteration. In the box for Maximum iterations, specify the number of iterations that Excel should try before stopping (even if it does not reach an answer with the desired accuracy). The value you enter in the Maximum change box controls the accuracy of your answer. It says iteration should stop when the values of the variables involved change from one trial to the next by an amount smaller than this number. Iteration will stop as soon as one of the two conditions is satisfied. (If you leave these boxes blank, Excel uses default values of 100 and 0.001 respectively.)

## The Process Of Building A Cash Flow Model

Although the primary purpose of this book is to guide a reader through the mechanics of constructing a cash flow model, there are some steps that should be taken before and after the model is created in Excel. In particular for readers new to financial modeling, it is important to go through each of these steps to save time. As one becomes more fluent in financial modeling, the steps can be combined, such as building both the basic and advanced framework at the same time rather than in two separate steps. However, it should be noted that a flaw, which even seasoned financial modelers make, is skipping the plan-and-design and testing steps. A major design problem encountered halfway through building a new model may have been prevented by investing even a minimal amount of time planning. Even worse is not realizing that the model has a problem before using it for final results. The first step, planning and design, is what good financial modelers and computer programmers spend most of...

## Hiding and Unhiding Rows and Columns

To hide one or more rows (or columns), select the rows, right-click to bring up the shortcut menu, and click Hide. Alternately, after you have selected the rows, select Format O Row and then click Hide. When Excel hides a row it does not renumber the rows to exclude the hidden rows. To find out if a worksheet has hidden rows, look down the row numbers to see if any of them are missing. Where rows have been hidden you will also notice a slightly thicker line than is found elsewhere between rows. Hidden rows are not printed and data in them are not included in charts. If you create a chart and then hide some rows or columns that included data used in the chart, the chart will look different or incomplete. Other than these effects, hidden rows are used in all calculations as if they were not hidden.

## Access To Data Analysis And Solver

Excel has some additional modules which are available if a full installation of the package has occurred but may be missing if a space-saving installation was done. We use both Solver and the Analysis ToolPak regression routine, so it is worth checking on the Tools menu that both are available. See Figure 2.22 which includes both Solver and Data

## Estimation and hypothesis testing in EViews example 2 the CAPM

This exercise will estimate and test some hypotheses about the CAPM beta for several US stocks. First, Open a new workfile to accommodate monthly data commencing in January 2002 and ending in April 2007. Then import the Excel file 'capm.xls'. The file is organised by observation and contains six columns of numbers plus the dates in the first column, so in the 'Names for series or Number if named in file' box, type 6. As before, do not import the dates so the data start in cell B2. The monthly stock prices of four companies (Ford, General Motors, Microsoft and Sun) will appear as objects, along with index values for the S&P500 ('sandp') and three-month US-Treasury bills ('ustb3m'). Save the EViews workfile as 'capm.wk1'.

## Creating Array Formulas

To do the same calculation using an array formula and show the result for each lot separately (as in column C), select D5 D9 and in D5 enter the formula (A5 A9*B5 B9* B 13). Then, instead of pressing Enter, press Ctrl+Shift+Enter. This last step is critical because it tells Excel that you want the formula entered as an array formula in all the selected cells. If you select any cell in D5 D9, you will see it has the same formula, and the formula is enclosed in a pair of braces ( ). This is Excel's way of writing array formulas. You cannot type in the braces, however. Excel enters them automatically when you enter an array formula using the special combination of keys I mentioned. Let us now understand this formula. It is easiest to view an array formula like this one as a set of parallel formulas(in this case, 5 parallel formulas, because each array here has 5 elements). Excel takes the corresponding elements from each array to break down the array formula into a set of 5 ordinary...

## Expanding and Contracting Array Formulas

Suppose you want to add another transaction to the model by inserting a new row. Excel will not let you insert a row within an array that contains an array formula until you remove the array formula or convert it into ordinary formulas. Select D5 D9, press F2, and then press Ctrl+Enter. This will enter individual formulas (which will look a little strange if you look into them) into all the cells in the array. You have to do the same with E5 E9. Now insert the row, select the new range D5 D10, press F2, and then press Ctrl+Shift+Enter. Do the same with the new range in column E as well.

## Websites for Conducting Economic Research

If you are doing regular economic research, we urge you to test drive a number of the sites to find the format that works best for you. Some sites allow data to be downloaded into an Excel spreadsheet, while others present the data only in a plaintext format. Your needs will dictate which site to choose among those with similar data.

## Creating Other Types of Names

You can also name a range that extends over the same cell or range in several worksheets in a workbook. This can come in handy when you are working with a series of similarly laid out worksheets (for example, each with data for a month). Excel calls it 3-D reference and to learn how to use it, search in Help for 3-D reference.

## Arguments of Functions

You can, of course, enter values as arguments. You can also use cell range references or cell range names, expressions (that is, formulas that Excel can evaluate), and other functions. As long as Excel can unambiguously evaluate what you provide to come up with a value for the argument, it will accept it. If you are in doubt about if you can input an argument in a certain way or how Excel will interpret it, check it out first in a small, made-up example.

## Introduction to Matrices

In algebra, rectangular arrays of numbers are referred to as matrices. A single column matrix is usually called a column vector similarly a single row matrix is called a row vector. In Excel, rectangular blocks of cells are called arrays. All the following blocks of numbers can be considered as matrices

## Creating an Income and Expense Tracking Document

Even if you use software such as Quicken by Intuit or Microsoft Money (covered in more detail in Chapter 2, Getting the Figures to Match ), you might find it helpful to keep a separate income and expense tracking document. You can run reports in your money management program that show you how you're doing with your financial goals, but the act of hand-writing or keying in the figures in a separate document can help you see the big picture more easily. I use QuickBooks (the business version of Quicken) to record all my transactions and balance all of my accounts electronically, but I also use another spreadsheet to list all of the data I want to see together, not all of which has a place in QuickBooks where it can be entered. I've tried to create just the right custom report in QuickBooks to eliminate the need for this spreadsheet, but so far nothing has worked as well as my trusty Excel document. You can create your spreadsheet by hand on graph paper, in a ledger book purchased from...

## Free and feebased site

At the other end of the spectrum, from a cost perspective, is the Econ-omy.com site, FreeLunch.com. The FreeLunch site limits access to a mere 900,000 economic and financial data series, but all the data is free. Users can search the databases and download data directly into Microsoft Excel.

## Spreadsheet Strategies

More and more, businesspeople from many different areas (and not just finance and accounting) rely on spreadsheets to do all the different types of calculations that come up in the real world. As a result, in this section, we will show you how to use a spreadsheet to handle the various time value of money problems we presented in this chapter. We will use Microsoft Excel , but the commands are similar for As we have seen, you can solve for any one of the following four potential unknowns future value, present value, the discount rate, or the number of periods. With a spreadsheet, there is a separate formula for each. In Excel, these are as follows

## Estimating Historical Simulation

Estimate it more directly by using the 'Large' command in Excel, which gives us the kth largest value in an array. Thus, if our data are an array called 'Loss_data', our VaR is given by the Excel command 'Large(Loss_data,6)'. If we are using MATLAB, we first order the L P data using the 'Sort()' command (i.e., by typing 'Loss_data Sort(Loss_data)') and then derive the VaR by typing in 'Loss_data(6)' at the command line. More generally, if we have n observations, and our confidence level is cl, we would want the (1 - cl) n + 1 highest observation, and we would use the commands 'Large(Loss_data, (1 - cl)* n + 1)' using Excel, or 'Loss_data((1 - cl)*n + 1)' using MATLAB, provided in the latter case that our 'Loss_data' array is already sorted into ordered observations.

## How Asset Generation Is Demonstrated In Model Builder

The easiest asset amortization to learn is a single loan or representative line created within Excel sheet formulas. This is a useful level at which to start because, as a more detailed asset generation tool is created, the basics can all be found in a single loan. This is why Project Model Builder that you started to create in Chapter 1 uses a single asset or single representative line. To create a more robust model that can handle multiple representative lines or tens of thousands of loans some type of computer code is required. Visual Basic for Applications (VBA) is perfect for such a task and is used frequently in more advanced models. A highly developed asset generation tool has the flexibility to accept any type of loan or amortize loans in groups based on many different criteria.

## Advantages and Disadvantages of Array Formulas

You use single-cell array formulas incorporating Excel's built-in functions (as we did to calculate the average cost). Another advantage is that Excel will not let you or a user accidentally change or overwrite a multi-cell array formula. However, Excel will not protect against overwriting a single-cell array formula. One disadvantage of array formulas is that they can get cryptic and difficult to read. A second disadvantage is that because most Excel users are not familiar with array formulas, the average colleague may not be able to understand a model that uses array formulas. Finally, as we saw, expanding or contracting an array formula (for example, adding more transactions to our model) is somewhat complex.

## Functions for Coupon Dates Calculations

Excel provides six functions to calculate the number of days since the last coupon payment, number of days between two coupon payments, and so on. All the functions use the same four arguments, which are the settlement date on which you are buying the bond, the maturity date for the bond, the frequency of coupon payment per year, and the day-counting convention to use. You can enter the settlement and maturity dates either as text strings with quotation marks (e.g., 5 1 1999 ) or as serial numbers representing the dates using Excel's convention (that is, serial date values). You can also enter the dates in cells and refer to those cells. COUPNCD Returns a number (Excel's date serial number) that represents the next coupon date after the settlement date. To view the number as a date, use a date format for the cell. COUPPCD Returns a number (Excel's date serial number) that represents the previous coupon date before the settlement date. To view the number as a date, use a date format...

## Normal Probability Distributions

A normal distribution with a mean of 0 and standard deviation of 1 is called a standard normal distribution. You can, of course, use the functions for normal distributions for standard normal distributions as well. However, because people use standard normal distributions frequently, Excel provides the functions NORMSDIST and NORMSINV specifically for standard normal distributions. They are similar to the two functions for the general normal distributions. Excel also provides a function called STANDARDIZE, which you can use to calculate how many standard deviations away from the mean a particular observation is. You will rarely use this function.

## System of Four Annuity Variables

The formula for the Payment (Present Value) ((1 - ((1 + Discount Rate Period) A (Number of Periods))) (Discount Rate Period)). Enter B7 ((1-((1+B5)A(-B6))) B5) in cell B10. The Excel PMT function can be used to calculate an annuity payment using the following format PMT(Discount Rate Period, Number of Periods, -Present Value, 0). Enter PMT(B5,B6,-B7,0) in cell B11. 4. Discount Rate Period. The Excel RATE function can be used to calculate the discount rate period for an annuity using the following format RATE(Number of Periods, Payment, -Present Value, 0). Enter RATE(B6,B4,-B7,0) in cell B14. 5. Number of Periods. The Excel NPER function can be used to calculate an annuity payment using the following format NPER(Discount Rate Period, Payment, -Present Value, 0). Enter NPER(B5,B4,-B7,0) in cell B17.

## Advantages Of Mastering

Macros form an important part of an experienced user's palette for two main reasons they are an excellent way to control repeated calculations, and they can be written to assist third-party users who might have less familiarity with spreadsheets. From our perspective, the main objective of mastering VBA is to be able to automate calculations using functions and macros. Spreadsheet models are more robust if complicated sequences of calculations are replaced by function entries. Excel provides an excellent range of functions and VBA can be used to extend that range. In addition, VBA macros are a useful way to produce charts and to automate repeated operations, such as simulation. In both instances, the resulting procedures are programs in VBA, but in the first case, they are user-defined functions and in the second, macros (or subroutines). Whilst the code is largely common to both types of procedure, this chapter focuses on writing macros, whereas Chapter 4 deals solely with...

## Constant Discount Rate

We will calculate the net present value of this project in two equivalent ways. First, we will calculate the net present value using a time line, where each column corresponds to a period of calendar time. Second, we use Excel's NPV function for the net present value.

## Using a Computer to Determine the Future Value

With the popularity of personal computers, there are several programs that can be used to determine the future value. Worksheet 6.3 illustrates the use of Microsoft's Excel program and Quicken's software. Using Microsoft's Excel spreadsheet, click on f *, which is on the top row of the toolbar. A list of functions pops up. Highlight FV in the right-hand box and click OK. A box comes up with five rows, as shown below.

## Copying Formulas with Mixed Cell References

What happened here is that in copying the reference to cell B6 (the book's list price) in the formula, Excel used relative reference and changed it to B11, which is not the book's list price. To solve this problem, change the formula in B10 to B9*B 6, which makes the column name B of B6 relative but the row number 6 absolute. This is called mixed reference and it tells Excel that in copying and pasting the formula for the book price, it should make the column number relative, that is, change it as it is pasted into cells in different columns (because in each year you want the price to come from the column for that year). However, it will make the row number absolute because the price is always in row 6. You can now copy and paste the correct formula from B10 into B15 D15. To keep your formulas consistent, you should paste the corrected formula into cells C10 and D10 as well.

## The Writing Environment

The Visual Basic Editor (referred to as the VBE) is where subroutines are written, tested out and debugged. We assume Excel's workbook environment, its menu, commands and toolbars are familiar territory. When developing VBA procedures, it helps to have Excel's Visual Basic toolbar visible in the Excel window (shown in Figure 3.1). As with other toolbars, this has buttons which are programmed to carry out sequences of relevant menu commands, here Tools Macro commands such as Run Macro, Record Macro and the VBE (first, second and fourth from left), etc. Figure 3.1 Excel's Visual Basic toolbar VBA code is written (or recorded) not in worksheets but in Module sheets. In Excel 97 (and subsequent versions) these appear in a different window, the Visual Basic (VB) window, and the process of developing macros involves switching to and fro between the Excel and VB windows (using the keystroke combinations Alt+Tab or Alt+F11) or the equivalent buttons. To try this out for yourself, open a new...

## Easily Understandable Formulas

Many Excel models especially large, complex ones often include formulas that go on for lines. No one other than the person who built the model can decipher and check them, let alone update them. Unfortunately, one of Excel's key strengths is also one of its big weaknesses. To create formulas by copying and pasting, you generally have to use relative and mixed references instead of cell and range names, but this makes formulas difficult to read.

## Appropriate Numbers Formatting

Format numbers with the minimum number of decimal points necessary. Using too many decimal points makes numbers difficult to read and often gives a false sense of precision as well. Make the formatting of similar numbers uniform throughout the model. (Remember that displaying numbers with fewer decimal points does not reduce the accuracy of the model in any way because internally Excel continues using the same number of significant digits.)

## Populations And Samples

Agbaje's calculations are given in the workbook Operational Risk 04.xls. Take a look at the worksheet Case Study 4.1. It gives the data and details of how the calculations can be carried out in Excel. The variance of a random variable is calculated using the function Var(). For example in cell E27 the function Var(E11 E23) calculates the variance of the competitor fees and returns a value of 0.23 percent. The only other built-in Excel function used in this worksheet is the function Covar() in cell E29, which calculates the covariance between competitor fees and economic growth. We could also use the function Var_2(), which calculates the variance of the sum of two random variables, introduced in the previous chapter. Its use in this example is shown in cell E35. The worksheet Case Study 4.1 simulation illustrates the calculation of business risk in differing economic and competitor fee environments. The simulation can be activated by pressing . It makes use of...

## Objectoriented Aspects Of

A few concepts that you need to grasp follow from the fact that VBA is an 'object-oriented' programming language. Each Excel object represents a feature or a piece of functionality in Excel, e.g. workbooks, worksheets, ranges, charts, scenarios, etc. are all Excel objects as is Excel itself (the Application object). You program in VBA to manipulate the properties and apply methods to Excel objects. The first statement is Objects come in collections. For example, the Workbooks collection consists of all open workbooks, similarly the Worksheets (or Sheets) collection (all the sheets in a workbook), the Scenarios collection (all scenarios associated with a particular sheet), the Charts collection (all the charts on a sheet), etc. However, some objects come as singular objects (i.e. collections of one member only), for example, Excel has only one Application object (itself) and for any cell on the spreadsheet there is only one Font object (although this object has several properties, such...