20-10-2012, 02:13 PM
Financial Modeling Using Excel and VBA
15253251FinancialModelingbyChandanSengupta.pdf (Size: 8.92 MB / Downloads: 108)
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, to make sure that the models
will not crash or produce wrong results if someone enters inappropriate inputs.
I will discuss some design methods and Excel features that make models easier to
use and more “bullet-resistant.” Most everyday modelers do not need to go
beyond this.
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
INTRODUCTION TO FINANCIAL MODELING
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 to appreciate the advantages of VBA at this point. Let me touch
on only a few key reasons here, and I will answer the question in greater detail
when we discuss modeling with VBA.
Despite its power, Excel has many limitations, and there are many financial
models—some even relatively simple ones—that either cannot be created in Excel
or will be overly complex or cumbersome to create in Excel. What’s more, when
you 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 to
learn, 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 language
is not particularly difficult—especially if you selectively learn what you will
really use (as we will do in this book) and not let yourself get lost in all the other
things you can do with VBA but probably never will. The truth is that you do not
need to learn all that much to be able to create very useful and powerful financial
models with VBA. What you will need is a lot of practice, which you will get
as you go through this book. VBA offers you the best of both worlds: you can
take advantage of all the powers of Excel including its ability to easily create excellent
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 certain
dependent variables for the values provided for its independent variables. It is
therefore important to understand the difference between independent and dependent
variables.
Independent variables are also called the input or external variables. The
model’s user or creator inputs the values of these variables—they are not calculated
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 to
remain constant or change infrequently within the context of the model. For example,
the tax rates and the tax brackets in our simple model can be considered
parameters of the model because their values have to be provided for the model
to work, but these values are not expected to change frequently. As you create a
model, it is useful to keep the parameters together but separate from the other
independent variables. They should still be easy to see and change, however.
The variables whose values are calculated by the model are called the calculated
or dependent variables. Some of them may be intermediate variables, calculated
for use in other calculations. Others are of primary interest to the user
and are the output variables of the models. Models are almost always created to
observe how the values of the output variables will change with changes in the
values of one or more independent variables. Dependent variables are the ones
whose values we want to project or determine when we ask “what if” questions.
It is possible to distinguish between intermediate dependent variables and
output dependent variables; intermediate dependent variables are used in further
calculations, whereas output dependent variables are not. This is generally not a
useful distinction, however. It is better to look at the dependent variables of primary
interest as the output variables of the model irrespective of whether they are
used in further calculations. One must also recognize that, from time to time,
some dependent variables that were previously not considered output variables
of 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 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.