financial modeling using excel and vba filefinancial modeling using excel and vba chandan sengupta...

Financial Modeling Using Excel and VBA

CHANDAN SENGUPTA

John Wiley & Sons, Inc.

ffirs.qxd 1/5/04 12:57 PM Page iii

Innodata0471651095.jpg

ffirs.qxd 1/5/04 12:57 PM Page vi


ffirs.qxd 1/5/04 12:57 PM Page i

Founded in 1807, John Wiley & Sons is the oldest independent publishing company inthe United States. With offices in North America, Europe, Australia, and Asia, Wiley is glob-ally committed to developing and marketing print and electronic products and services forour customers’ professional and personal knowledge and understanding.

The Wiley Trading series features books by traders who have survived the market’s everchanging temperament and have prospered—some by reinventing systems, others by gettingback to basics. Whether a novice trader, professional, or somewhere in-between, thesebooks will provide the advice and strategies needed to prosper today and well into thefuture.

For a list of available titles, visit our Web site at www.WileyFinance.com.

ffirs.qxd 1/5/04 12:57 PM Page ii


CHANDAN SENGUPTA

John Wiley & Sons, Inc.

ffirs.qxd 1/5/04 12:57 PM Page iii

Copyright © 2004 by Chandan Sengupta. All rights reserved.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey.Published simultaneously in Canada.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in anyform or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise,except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, withouteither the prior written permission of the Publisher, or authorization through payment of the appro-priate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA

Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons,Inc., 111 River Street, Hoboken, NJ 07030, 201-748-6011, fax 201-748-6008, e-mail: [email protected].

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their bestefforts in preparing this book, they make no representations or warranties with respect to the accu-racy or completeness of the contents of this book and specifically disclaim any implied warrantiesof merchantability or fitness for a particular purpose. No warranty may be created or extended bysales representatives or written sales materials. The advice and strategies contained herein may notbe suitable for your situation. You should consult with a professional where appropriate. Neitherthe publisher nor author shall be liable for any loss of profit or any other commercial damages,including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services, or technical support, please contactour Customer Care Department within the United States at 800-762-2974, outside the UnitedStates at 317-572-3993 or fax 317-572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in printmay not be available in electronic books.

ISBN 0-471-26768-6

Printed in the United States of America.

10 9 8 7 6 5 4 3 2 1

ffirs.qxd 1/5/04 12:57 PM Page iv

01923, 978-750-8400, fax 978-750-4470, or on the web at www.copyright.com. Requests to the

For more information about Wiley products, visit our web site at www.wiley.com.

http://www.copyright.comhttp://www.wiley.com

For Preety

ffirs.qxd 1/5/04 12:57 PM Page v

ffirs.qxd 1/5/04 12:57 PM Page vi

Contents

vii

About This Book xi

CHAPTER 1 Introduction to Financial Modeling 1

Part One: Excel for Financial ModelingCHAPTER 2 Excel Basics 13Improving Your Excel Skills 14The Basic Excel Features 20

CHAPTER 3 Advanced Excel Features 29

CHAPTER 4 Excel’s Built-In Functions and Analysis Tools 71Financial Functions 73Logical Functions 86Statistical Functions 86Mathematical and Trigonometric Functions 97Lookup and Reference Functions 101Date and Time Functions 107Text Functions 110Information Functions 112The Analysis ToolPak 113

Part Two: Financial Modeling Using ExcelCHAPTER 5 How to Build Good Excel Models 119Attributes of Good Excel Models 119Documenting Excel Models 122Debugging Excel Models 124Using Formula Auditing Tools for Debugging 127Learning Modeling Using Excel 128

ftoc.qxd 1/5/04 1:00 PM Page vii

CHAPTER 6 Financial Statements Forecasting 131Review of Theory and Concepts 131Modeling Examples 141

CHAPTER 7 Time Value of Money 185Review of Theory and Concepts 185Modeling Examples 199

CHAPTER 8 Financial Planning and Investments 223Review of Theory and Concepts 223Modeling Examples 230

CHAPTER 9 Analyzing Market History 247Review of Theory and Concepts 247Modeling Examples 252

CHAPTER 10 Bond Pricing and Duration 267Review of Theory and Concepts 267Modeling Examples 274

CHAPTER 11 Simulating Stock Prices 285Review of Theory and Concepts 285Modeling Examples 295

CHAPTER 12 Options and Option Portfolios 307Review of Theory and Concepts 307Modeling Examples 317

CHAPTER 13 Binomial Option Pricing 345Review of Theory and Concepts 345Modeling Examples 349

Part Three: VBA for Financial Modeling

CHAPTER 14 Introduction to VBA 363Example of a VBA Model 368Recording Macros 378Using VBA’s Help 382The Strategy to Learn VBA Efficiently 384

CHAPTER 15 VBA Essentials 385The Visual Basic Editor 386Some Basics 390Variables, Constants, and Arrays 401Objects, Properties, and Methods 413

viii CONTENTS

ftoc.qxd 1/5/04 1:00 PM Page viii

Branching 417Looping 423Using Built-In Functions 430

CHAPTER 16 Sub and Function Procedures 433Sub Procedures 433Functions Procedures 438

CHAPTER 17 Debugging VBA Models 445

Part Four: Financial Modeling Using VBA

CHAPTER 18 How to Build Good VBA Models 453Attributes of Good VBA Models 453Documenting VBA Models 457Learning Modeling Using VBA 459

CHAPTER 19 Time Value of Money 463Review of Theory and Concepts 463Modeling Examples 467

CHAPTER 20 Financial Planning and Investments 519Modeling Examples 519

CHAPTER 21 Analyzing Market History 549Modeling Examples 549

CHAPTER 22 Simulating Stock Prices 575Modeling Examples 575

CHAPTER 23 Options and Option Portfolios 583Modeling Examples 583

CHAPTER 24 Binomial Option Pricing 599Modeling Examples 599

Appendix A Keyboard Shortcuts for Excel 613

Appendix B VBA Quick Reference 615

Appendix C Excel and VBA Built-In Functions 617

Index 635

About the CD-ROM 655

Contents ix

ftoc.qxd 1/5/04 1:00 PM Page ix

ftoc.qxd 1/5/04 1:00 PM Page x

About This Book

xi

How do you get to Carnegie Hall? You practice, practice, practice.The same is true of financial modeling. The only way you can learn to

develop good financial models is by practicing a lot. Fortunately if you learn andpractice modeling the right way, you will not have to practice even one-tenth ashard as a performer does to get to Carnegie Hall.

The primary objectives of this book are to show you how to learn and prac-tice financial modeling the right way and to provide you with a wide range ofreal-world financial models—over 75 of them—to imitate and use for practice sothat you can be on your way to financial modeling’s Carnegie Hall. Financialmodeling is an essential skill for finance professionals and students, and Exceland its built-in programming language, Visual Basic for Applications (VBA), arethe preferred tools for the job. However, modeling using Excel and VBA is rarelypresented as an integrated subject in books or classrooms. The result is that bothpractitioners and students follow time-consuming trial and error approaches tomodeling and end up with models that are not sufficiently flexible and powerful.

This book, designed for self-study, classroom use, and reference, presents acomprehensive approach for developing simple to sophisticated financial modelsin all major areas of finance using both Excel and VBA. The approach is basedon my long experience in the business world developing a wide variety of finan-cial models and in the classroom teaching an MBA course in financial modelingthat students find very useful not just in their other course work but in their sub-sequent professional careers as well.

Developing good financial models requires combining knowledge of finance,mathematics, and Excel and VBA using modeling skill. In each of these areas, the fol-lowing is what I assume you already know and what you will learn from this book.

In finance and mathematics, I assume that you have the necessary basicknowledge. Nonetheless, in each chapter I have included a review of the theoryand concepts you will find useful for working on the models within that chapter.Because I cover a wide range of topics in the book, I think some of this materialwill be new to you. By immediately applying the newly acquired knowledge to

flast.qxd 1/8/04 11:49 AM Page xi

“real world” problems, you will expand your knowledge of finance in some areasin which you may have been interested for some time.

In Excel, I assume you know the basics, and I cover the advanced features ofExcel that you need for modeling in detail. You may be amazed to find out howmuch those whiz kids from Redmond have squeezed into Excel that many of usdo not even know about.

VBA will be one of the most important things you learn from this book. Iassume that you know nothing about it. VBA is a powerful and very useful toolthat people who have Excel already have sitting inside their computers. Unfortu-nately, very few people use it because they are afraid of learning “programming.”I will teach you VBA and modeling with VBA using a simple class-testedapproach. The key is to learn VBA as a language the same way you learned yourmother tongue—by imitating how to say things you want to say, without worry-ing about learning all the rules of grammar or trying to acquire a large vocabu-lary that you do not need. You will be surprised to find out how little you haveto learn to be able to develop models with VBA that are often more useful, pow-erful, and flexible than Excel models.

Finally, I assume that you are new to modeling. Even if you have some expe-rience, you will quickly find yourself challenged as you build on your skills. Youwill learn by imitating and practicing on numerous models from all areas offinance, and you will be able to challenge yourself further by developing exten-sions to these models.

I have not tried to cover every type of financial model that you may need todevelop over the years, nor have I tried to cover modeling in depth for one or twoparticular areas of finance (such as derivatives). The reality—and my assump-tion—is that once you develop your financial modeling skill and learn to useExcel and VBA well, you will be able to develop models for any problem as longas you know the financial theory and mathematics needed to solve it conceptu-ally. I have therefore focused on helping you develop the skill of financial mod-eling, and the best way to develop that skill is to work on a broad range ofmodels instead of narrowly focusing on any one area of finance.

The CD that accompanies this book includes complete working versions ofall the models in the book. In the text I provide the modeling strategy for eachproblem, detailed instructions on how to build each model, and thorough analy-sis of all the VBA codes for the models. I also explain how you can cover thematerial following different learning tracks depending on your background, howmuch time you have, and how good you want to become in financial modeling.

The book and the CD also include several special tools (for example, a VBAQuick Reference and a selected list of the most useful Excel and VBA built-infunctions) that you can personalize, add to over time, and keep easily accessibleon your computer’s hard disk.

Financial modeling is finance in action. It is challenging and it is a lot of fun.I hope this book will show you how to have fun with it and benefit from it at thesame time.

xii ABOUT THIS BOOK

flast.qxd 1/8/04 11:49 AM Page xii


flast.qxd 1/8/04 11:49AM Pagexiii

flast.qxd 1/8/04 11:49 AM Page xiv

CHAPTER 1Introduction to Financial Modeling

1

What is a financial model? What is the difference between a financial modeland the spreadsheet solutions you create or VBA programs you write all thetime to answer financial questions or solve financial problems?

A simple, practical answer is that a financial model is designed to representin mathematical terms the relationships among the variables of a financial prob-lem so that it can be used to answer “what if” questions or make projections.Some of the spreadsheet solutions that people create capture some of these rela-tionships as well and, therefore, can answer “what if” questions to some extent.But because they are not primarily designed with these objectives in mind, theydo not try to capture as many of these interdependencies as possible, and theirstructures often make it cumbersome to answer “what if” questions or make pro-jections with them.

This may sound a little abstract. So let us look at a simple, concrete exam-ple. Suppose you are using a spreadsheet to calculate, based on your taxableincome, what your after-tax income was last year. Income tax rates vary in steps(brackets) for different income levels. So you cannot simply calculate your taxesby multiplying your taxable income by one tax rate (30%, for example) and sub-tracting it from your taxable income to get the after-tax income.

Consider two approaches to setting up a spreadsheet to calculate the after-tax income. In the first approach, you can enter your taxable income in a cell,calculate the tax on the income (using a hand calculator and the tax rates for thedifferent tax brackets), and enter it in the cell below. Then you can write an equa-tion in another cell to calculate your after-tax income by subtracting the tax inthe second cell from the taxable income in the first cell.

This spreadsheet solution will give you the answer to your immediate ques-tion, but it is not a useful financial model. Why? Because it does not capture thekey mathematical relationship between taxable income and taxes. The result isthat if you now try to answer the “what if” question, What would my after-taxincome have been if my taxable income were $10,000 higher?, you will have togo back to doing the calculations by hand.

01_chap_sengupta.qxd 12/31/03 12:07 PM Page 1

However, you can set up your spreadsheet to calculate the taxes on any taxableincome (using the different tax brackets and tax rates) and use the computed taxnumber to calculate your after-tax income. You will then have a financial model,because it will capture the relationship between taxable income and taxes. You alsowill be able to use this model to answer the “what if” question I posed before.

In creating financial models, you always have to keep in mind that you wantto capture as many of the interdependencies among the variables of the model aspossible. In addition, you want to structure your models in such a way that it iseasy to ask “what if” questions, that is, change the values of the independentvariables and observe how they affect the values of the key dependent variables.You also should recognize that some of the relationships, as in the case of taxes,are easy to establish and exact; but many others will be approximate or evenunknown. You will have to come up with them based on financial theory, analy-sis of data, and so on, and coming up with these relationships is one of themajor challenges of financial modeling. Generally, the more of these relationshipsyou can come up with and incorporate into your model, the more useful yourmodel will be.

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 handmost of the problems for which you will be creating models. I also assume thatyou are familiar with the basics of Excel and have experience creating spread-sheet solutions to at least simple problems. You do not need to have knowledgeof Excel’s advanced features or of modeling; I will cover both in detail. You alsodo not need to have any knowledge of VBA. A key objective of the book is toteach you VBA and modeling using VBA from scratch by way of an easy andeffective method.

Another important assumption I am making is that you will be developingthe models primarily for your own use or for use by people who have some expe-rience with Excel, but not necessarily with VBA. When you create models for useby people who have little or no familiarity with Excel, it requires adding specialuser interfaces to make the models easy to use. One must build into them spe-cial features to make them “bulletproof”—that is, to make sure that the modelswill not crash or produce wrong results if someone enters inappropriate inputs.I will discuss some design methods and Excel features that make models easier touse and more “bullet-resistant.” Most everyday modelers do not need to gobeyond this.

Excel and VBA as Modeling Tools

Even in the mid- to late 1990s, Excel was not considered a powerful enough toolfor serious financial modeling, in part because the PCs available at the time had

2 INTRODUCTION TO FINANCIAL MODELING


speed and memory limitations. With advances in PCs and improvements in Excelitself, the table has now turned completely: Excel has become the preferred toolfor creating all but the largest and most computationally intensive financial mod-els. The advantages of Excel for financial modeling are so obvious that it is notnecessary to go into them. However, for those who have not worked with otherprograms or programming languages for modeling, it is worthwhile to point outthat one of the important advantages of Excel is that with Excel you can createexcellent output with very little work. You should learn to take full advantage ofExcel’s power in this respect.

If Excel is so good, then, why bother with VBA? VBA is a programming lan-guage, and if you do not know anything about programming languages, it will bedifficult for you to appreciate the advantages of VBA at this point. Let me touchon only a few key reasons here, and I will answer the question in greater detailwhen we discuss modeling with VBA.

Despite its power, Excel has many limitations, and there are many financialmodels—some even relatively simple ones—that either cannot be created in Excelor will be overly complex or cumbersome to create in Excel. What’s more, whenyou create a highly complex model in Excel, it can be difficult to understand,debug, and maintain. VBA generally offers a significant edge in all these respects.

The problem that most people have with VBA is that it is one more thing tolearn, and they are somehow afraid of trying to learn a programming language.The reality is that if you follow the right method, learning a programming lan-guage is not particularly difficult—especially if you selectively learn what you willreally use (as we will do in this book) and not let yourself get lost in all the otherthings you can do with VBA but probably never will. The truth is that you do notneed to learn all that much to be able to create very useful and powerful finan-cial models with VBA. What you will need is a lot of practice, which you will getas you go through this book. VBA offers you the best of both worlds: you cantake advantage of all the powers of Excel including its ability to easily create ex-cellent outputs, and supplement them with VBA’s additional tools and flexibility.

Independent and Dependent Variables

We can say that the purpose of a model is to calculate the values of certaindependent variables for the values provided for its independent variables. It istherefore important to understand the difference between independent and depen-dent variables.

Independent variables are also called the input or external variables. Themodel’s user or creator inputs the values of these variables—they are not calcu-lated by the model. These are the variables you change to ask “what if” questions.For example, in our simple model the taxable income is an independent variable.

A model may also include a special type of input variable called a parameter.Parameters are independent variables in that their values are also provided by the

Introduction to Financial Modeling 3


creator or user of the model. The difference is that their values are expected toremain constant or change infrequently within the context of the model. For ex-ample, the tax rates and the tax brackets in our simple model can be consideredparameters of the model because their values have to be provided for the modelto work, but these values are not expected to change frequently. As you create amodel, it is useful to keep the parameters together but separate from the otherindependent variables. They should still be easy to see and change, however.

The variables whose values are calculated by the model are called the calcu-lated or dependent variables. Some of them may be intermediate variables, cal-culated for use in other calculations. Others are of primary interest to the userand are the output variables of the models. Models are almost always created toobserve how the values of the output variables will change with changes in thevalues of one or more independent variables. Dependent variables are the oneswhose values we want to project or determine when we ask “what if” questions.

It is possible to distinguish between intermediate dependent variables andoutput dependent variables; intermediate dependent variables are used in furthercalculations, whereas output dependent variables are not. This is generally not auseful distinction, however. It is better to look at the dependent variables of pri-mary interest as the output variables of the model irrespective of whether they areused in further calculations. One must also recognize that, from time to time,some dependent variables that were previously not considered output variablesof a model can become so and vice versa.

STEPS IN CREATING A MODEL

Whether you are creating a financial model using Excel or VBA, you must take asystematic approach. A systematic approach always involves planning ahead andthis takes some time. Most people do not like to plan and think they can savetime 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 someplanning and not taking a systematic approach ends up being both frustratingand a waste of time.

Here are the key steps you should follow in creating both Excel and VBAmodels. The details vary somewhat depending on whether you are working withExcel or VBA, and I will discuss them in later chapters. You should keep twoother things in mind. First, in practice, you do not have to follow the steps strictlyin this order, nor do you have to finish one completely before going onto the nextone. Most of the time you will have to go back and forth to some extent. It willdepend on the circumstances. Second, over time, you should try to create yourown variation on this basic approach and learn to adapt it to different situations.

Excel and VBA are flexible tools and you can usually make changes almostat any stage without a great deal of difficulty. But this still will take more time



than if you do it right the first time, and making changes later increases thechances of missing some of the other changes that have to go with them.

Step 1: Define and Structure the Problem

In real life, problems rarely come neatly defined and structured. Unless you takethe time upfront to define and structure the problem and agree on them with theuser (your boss, for example), you may end up having to extensively changethe model you first create. When your boss asks you a question whose answerrequires developing a model, she often has only a vague idea of what she is reallyseeking. As a finance person and a modeler, you are responsible for putting it allin more concrete terms before proceeding.

Start by discussing and defining why the model is needed and what decisions,if any, will be made based on its output—that is, what questions the model is sup-posed to answer. Then establish how accurate or realistic the outputs need to be.As we discussed, all models have to capture the relationships among their vari-ables, and discovering and quantifying these can take a lot of time. How mucheffort you put into doing this should depend on how important the project is andhow accurate or realistic the outputs need to be.

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 providethem or where they will come from. This is crucial. For example, if you are cre-ating a model to do the business plan for your company, the inputs must comefrom the business managers. You cannot just guess what sales growth rates theywill be able to achieve, how much they will have to spend on plants and equip-ment to support those sales growths, and so forth. You may not need the actualnumbers upfront, but the list of inputs should be established based on your dis-cussions with the business managers so that you can make them independentvariables in your model. Otherwise you may have go back later on and change alot of things in the model.

Make a list of the tabular, graphical, and other outputs the model needs tocreate. To some extent, these should be driven by the decisions that will be madebased on them. One advantage of Excel is that a lot of the output can be justprintouts of your spreadsheets, provided the spreadsheets have been laid outproperly. If you plan ahead and lay out your spreadsheets with the outputs inmind, you will save yourself a lot of time later on.

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

Who will use the model and how often it will be used make a lot of difference.In this book, I am assuming that you are developing the models either for your



own use or for use by others who are familiar with Excel and understand themodel, at least to some extent. When you create models for others’ use, itinvolves much more work. You have to make sure that these people cannot enterdata 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 arecollectively called the user interface, and the more elegant, more easy to use, andmore robust you want to make a model, the more work it is. You also have toplan for many of these features ahead of time.

How frequently a model will be used is another important issue. If a modelis going to be used only once in a while, then it does not matter if it takes a longtime to run or if it takes some extra work every time to create the outputs. Amodel that will be used frequently, however, should be designed differently.

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

It is important to remember that the computer cannot do any thinking; you haveto tell it exactly how all the calculations in the model will have to be done. Inmost situations, if you do not know how you would do the calculations by hand,you are not going to be able to write the necessary formulas or instructions forthe computer to do it. It does not pay to start building the model until you aresure you could solve the problem by hand.

It usually takes beginners a lot of time to create a model and they often thinkthat it is their Excel or VBA skills that are slowing things down. This may bepartly true, but at least as often the problem is in their understanding of thefinance and mathematics of the model they are trying to create. You will save lotof time if you do not even sit down in front of the computer to create a modeluntil 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 Excelor VBA will have to follow to solve the problem. For simple models, you maywant to write down only the broad steps or perhaps even do it in your head. Formore complex problems, however, you should work on paper and use a degreeof 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 youtime compared to plunging into your spreadsheet or VBA program without sucha sketch of the model.

The other aspect of design is planning how the model will be laid out in Excelor VBA. Are you going to do the entire model in one spreadsheet (or VBA mod-ule) or split it into several spreadsheets (or VBA modules or procedures)? Editingan Excel or VBA model is easy. So you do not have to decide every detail ahead



of time, but you need to have an overall design in mind or on paper dependingon the complexity of the problem and your level of experience.

As I discussed before, you also need to think about the kind of user interfaceyou want to create and the reports you want the model to produce.

Step 6: Create the Spreadsheets or Write the VBA Codes

For most models, this is the big step. Most of this book covers the details of thisstep, so there is no need to get into them here.

Step 7: Test the Model

Almost no model works correctly the first time it is used; you have to find theproblems (bugs) and fix them. The bugs that prevent the model from working atall or produce obviously wrong answers are generally easier to find and fix. How-ever, models often include hidden bugs that create problems only for certain val-ues or certain combinations of values for the input variables. To find them, youhave to test a model extensively with a wide range of input variables.

You have to take somewhat different approaches to testing and debugging amodel depending on whether you are working with Excel or VBA. Both Exceland VBA provide some special tools for this purpose; I will discuss these tools andprovide suggestions on how to debug models in Excel and VBA in later chapters.Here are a few helpful hints that apply to both:

■ There is no standard approach to testing and debugging a model. You almostalways have to use your ingenuity to figure out what will be the best way totest and debug a particular model. Your ability to do so will improve withexperience.

■ The better you understand a problem and a model, the easier it will be todebug it. If you understand how changes in certain independent variablesaffect the values of certain dependent variables, then you can change the val-ues of the independent variables to see if the dependent variables are chang-ing in the right direction and by the right orders of magnitude. This is one ofthe best tools, especially for debugging large models, and you should do a lotof testing using this approach. You can also use this approach to hunt downthe sources of the problems: Starting from a value that looks wrong, back-track through the values of the intermediate dependent variables to see wherethe problem may be originating. This approach may sound somewhat vagueand abstract, but with experience you will find that you can locate and fixmost bugs rapidly using this approach.

■ Checking a model’s output against hand-calculated answers is a common andeffective approach to debugging. In some situations, doing hand calculationsmay not be practical, but you may be able to use Excel itself to do some sidecalculations 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 toothers to use, you should consider protecting it against accidental or unautho-rized changes. In addition, you may also want to hide parts of the model so thatothers cannot see certain formulas, data, and so on. Excel provides several flexi-ble tools that you can use to hide and protect parts or all of your model. A goodstrategy is to cluster and color code all the input cells of a model and protect andhide everything else in the workbook.

There is less need to protect VBA modules because most users do not evenknow how to open them. Nonetheless, if you think it is necessary, you can pro-tect parts of your VBA models as well.

Step 9: Document the Model

Documenting a model means putting in writing, diagrams, flowcharts, and so on,the information that someone else (or you yourself in the future) will need to fig-ure out what it does, how it is structured, and what assumptions are built intoit. One can then efficiently and effectively make changes to (update) the model ifnecessary.

For large systems (for example, the reservation systems for airlines), theamount of necessary documentation can be enormous; it is often put on CDs foreasy access and use. Professional system development organizations have elabo-rate standards for documentation, because different pieces of large systems aredeveloped by different people—many of whom may not be around for very long.Also, it is almost certain that the systems will have to be constantly updated.

Over time, anyone who creates models develops his own system of docu-mentation. As long as you keep in mind the objectives I mentioned before, youhave a lot of leeway to come up with your own system as well. Both Excel andVBA offer a number of features that let you easily do a lot of the documentationas you work on your model. You should take full advantage of them and do asmuch of your documentation as possible while creating the model.

This is important for two reasons. First, if you write your documentationwhen things are fresh in your mind, it will save you time later and you will beless likely to forget to document important things. Second, everyone hates (orlearns to hate) documentation. It is no fun at all, especially if you try to do it allat once at the end of the project. If you do not work on the documentation untilthe end, chances are you will never do it. Then, if you have to use the model againa few months later or have to update it, you will end up spending hours or evendays trying to figure out what you did. Do your documentation as you go alongand finish it immediately after your model is done.

You have to take somewhat different approaches to when you documentExcel and VBA models. I will discuss how in the appropriate later chapters.



Step 10: Update the Model as Necessary

This is not a part of the initial model development, but almost all models requireupdating at some point, either because some things have changed or because youwant to adapt it to do something else. This is where the documentation becomesuseful. Depending on how much updating is involved, you may want to gothrough all of the above steps again. You should also thoroughly update the doc-umentation and include in it the information on who updated it, when and why,and what changes were made.

HOW THIS BOOK IS ORGANIZED

To use this book effectively, it will be helpful to understand some of my thinkingbehind its organization. Modeling is a skill that you can develop only by creat-ing a variety of models. Once you have developed the basic skill, you should beable to create increasingly complex models—as long as you understand thefinance and mathematics of the underlying problems. In some ways, it is likelearning a language, especially when you are working with VBA. Once you learnthe language, you can say new things in that language—you can make up sen-tences that you have never heard before. I therefore emphasize learning the lan-guage by exposing you to a variety of models in different areas of finance insteadof concentrating on one particular area like derivatives or trying to anticipate andinclude every model you may need to develop.

For both Excel and VBA, I have provided one part that covers the “gram-mar” of the language (Parts One and Three) and then a second part that providesthe examples of its use (Parts Two and Four) to create models. Just as you do notneed to know everything about the grammar of a language before you can startusing it, you do not need to know everything about Excel or VBA before you canstart creating models using them. I suggest that you start with Part One tobecome familiar with Excel and quickly move on to working on the models inPart Two. Do not spend a lot of time trying to master the grammar. Come backand learn it as you need it.

In each chapter in Part Two, I have included a section called “Review of The-ory and Concepts.” These are the theories and concepts of finance that you willneed to work on the models in that chapter. If you are familiar with them, youcan skip them and go on to the models. If you want to refresh your memory andunderstanding, the material is there.

You will notice that many of the modeling chapters in Parts Two and Fourhave the same titles. There is a reason for including such parallel chapters. It hasbeen my experience that the easiest way to learn modeling with VBA is to startwith problems that you can already model in Excel, because then you alreadyknow a lot about the problem and you can focus on the VBA aspects of it. In



many cases, the parallel VBA models also demonstrate that even if you can modela problem using Excel, using VBA can provide additional flexibility, power, andso on. Most of the VBA chapters include additional models that are cumbersomeor impossible to create using Excel.

What this means is that you may be better off covering Parts One and Twobefore you go on to the VBA parts. However, if you are already good at model-ing with Excel and are primarily interested in learning VBA and modeling usingVBA, you can start with Part Three. Then, as you cover the chapters of Part Four,you can review the corresponding chapters in Part Two as necessary.

Because there are certain differences between Excel-based models and VBA-based models, I have provided in the first chapters of both Parts Two and Fouradditional information on how to develop good models using them. I have alsoincluded suggestions on how best to use the material in both parts to improveyour modeling skills rapidly. You will progress faster if you read and follow thesesuggestions instead of taking a haphazard approach.



PARToneExcel for Financial Modeling


CHAPTER 2Excel Basics

13

In this book, I assume that you already know the basics of Excel—that is, youhave been using Excel for some time and can create spreadsheet solutions forsimple financial and other problems, plot charts to present your results, and printout your results. So rather than cover all the basics of Excel in detail, I will firstoffer some general suggestions here on how to improve your Excel skills. I willthen list all the basic features of Excel that you are likely to use extensively. In theprocess I will also point out things to which you should pay special attention andprovide other guidance on the safe and efficient use of Excel.

If you are not familiar with any of these basic features of Excel, you shouldlearn it now using Excel’s online Help or other general Excel books. Knowingthese basic features well is essential for learning the advanced features covered inthe next chapter as well as for developing your financial modeling skills.

Incidentally, throughout the book I refer to all the things you can do withExcel (or VBA) as features. Features include everything from the various waysthat you can move around a worksheet to the many sophisticated tools, built-infunctions, and so on, that Excel offers. Although Excel has literally thousands offeatures, in most of your modeling you will use only a selected group of them;however, you will need to know this group of features well. This is the group offeatures that I will cover in this part of the book.

I should also point out that the distinction I am drawing between basic andadvanced features in this and the next chapter is somewhat arbitrary. For exam-ple, you will find that in the next chapter I cover in detail how to write formulaswith relative and absolute references, enabling you to create formulas in other cellsto do similar calculations simply by copying and pasting the original formula intothem. Many people consider this to be one of the basic features of Excel becausea lot of Excel’s power derives from this feature. I cover some such features in detailin the next chapter instead of just listing them as basic features here because theyare so important that you must know them very well. The distinction betweenbasic and advanced is not important. What is important is that after you coverthese chapters on Excel in this part of the book, you will be able to use Excel moreeffectively and more efficiently as you develop financial models.


THE DIFFERENT VERSIONS OF EXCEL

The differences among Excel 97, Excel 2000 and Excel 2002 are mostly cosme-tic; there is little substantive difference, especially for financial modeling. If youare 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 ofExcel are, however, quite different, and if you are using an earlier version, youwill need to upgrade to one of these later versions, especially before workingwith VBA.

The only two new features in Excel 2002 that I find useful (and I will discussthem in the appropriate places) are AutoRecover and the Ask a Question windowat the right end of the menu bar. One of the conspicuous new features of Excel2002 is a panel on the right side of the screen. It is called the Task Pane and saysNew Workbook in its title bar. I do not find it useful, and—because it cuts intothe visible worksheet area—I keep it closed. It is easy to use the File menu to doall the things you would use the Task Pane for.

SAVING YOUR WORKBOOKS

Although you probably have heard hundreds of times by now that you shouldsave your workbooks frequently, the importance of the message generally doesnot sink in until you have suffered a major disaster of your own. If your luck hasheld up so far, do not push it. Develop the habit to save your workbooks—whichare files in Windows parlance—frequently. You should also learn to do it theright way as described here. Remember that you can never be too protective ofyour files. You should also establish and follow a plan to periodically back up yourfiles from the hard disk on your computer to some removable storage medium likeZip disks or CDs.

To guard against the loss any of your current work, save your workbook byclicking the floppy disk icon on the toolbar, by pressing Ctrl+S, or by selectingFile ➩ Save every time you have made substantial changes or additions to yourworkbook. Depending on your pace of work, at times this may mean savingevery few minutes—at other times, much less frequently. (You cannot save anyindividual worksheet of a workbook; Excel always saves entire workbooks.)

Here is something very important about saving workbooks that you shouldkeep in mind. Whenever you save a workbook, Excel overwrites the copy youhad saved previously under the same name with the current workbook, and theold copy is permanently lost. Also, once you save a workbook, you will not beable to use Excel’s Undo to backtrack to any earlier step. So, as I will discuss

14 EXCEL FOR FINANCIAL MODELING

Improving Your Excel Skills


financial modeling using excel and vba filefinancial modeling using excel and vba chandan sengupta...

Documents