Financial Modelling Continuezz....

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 of time, but you need to have an overall design in mind or on paper depending on the complexity of the problem and your level of experience. As I discussed before, you also need to think about the kind of user interface you 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 this

step, 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 the problems (bugs) and fix them. The bugs that prevent the model from working at all or produce obviously wrong answers are generally easier to find and fix. However, models often include hidden bugs that create problems only for certain values or certain combinations of values for the input variables. To find them, you have to test a model extensively with a wide range of input variables. 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:

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

experience.

■ The better you understand a problem and a model, the easier it will be to debug it. If you understand how changes in certain independent variables affect the values of certain dependent variables, then you can change the values of the independent variables to see if the dependent variables are changing in the right direction and by the right orders of magnitude. This is one of the best tools, especially for debugging large models, and you should do a lot

of testing using this approach. You can also use this approach to hunt down the sources of the problems: Starting from a value that looks wrong, backtrack through the values of the intermediate dependent variables to see where the problem may be originating. This approach may sound somewhat vague and abstract, but with experience you will find that you can locate and fix most bugs rapidly using this approach.

■ 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

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. There is less need to protect VBA modules because most users do not even know how to open them. Nonetheless, if you think it is necessary, you can protect 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 figure out what it does, how it is structured, and what assumptions are built into it. One can then efficiently and effectively make changes to (update) the model if necessary. For large systems (for example, the reservation systems for airlines), the amount of necessary documentation can be enormous; it is often put on CDs for easy access and use. Professional system development organizations have elaborate standards for documentation, because different pieces of large systems are developed 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 documentation.

As long as you keep in mind the objectives I mentioned before, you have a lot of leeway to come up with your own system as well. Both Excel and VBA offer a number of features that let you easily do a lot of the documentation as you work on your model. You should take full advantage of them and do as much of your documentation as possible while creating the model. This is important for two reasons. First, if you write your documentation when things are fresh in your mind, it will save you time later and you will be less likely to forget to document important things. Second, everyone hates (or learns to hate) documentation. It is no fun at all, especially if you try to do it all at once at the end of the project. If you do not work on the documentation until the end, chances are you will never do it. Then, if you have to use the model again a few months later or have to update it, you will end up spending hours or even days trying to figure out what you did. Do your documentation as you go along and finish it immediately after your model is done. You have to take somewhat different approaches to when you document Excel 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 require updating at some point, either because some things have changed or because you want to adapt it to do something else. This is where the documentation becomes useful. Depending on how much updating is involved, you may want to go through all of the above steps again. You should also thoroughly update the documentation and include in it the information on who updated it, when and why, and what changes were made.

Don't sweat the quant stuff

We generally think that workers in India are good at the quant stuff. But is this just stereotype, or is it real? On one hand, 12th standard CBSE mathematics - which the ordinary Indian worker has generally mastered - exceeds what high school students in the US know. India has a significant edge here against the US, though not against Europe. On the other hand, college education in India is pretty awful, and this has generated skepticism about the potential upside for BPO in India. Very bright MBAs in India tend to be underskilled in probability, statistics, optimisation, financial economics; they tend to use spreadsheets too much. Their counterparts - the best MBAs from top schools in the US - seem to be better trained in these four critical blocks of knowledge. (I am comparing like to like: MBAs from the 3 best IIMs and ISB against MBAs from the top 10 schools in the US).

Thanks to Tirthankar Patnaik, I saw mention of an intruiging study by Ravi Aron of Wharton

Questions abound about job functions that are likely to gravitate to places like India sooner than others. Some insights into this can be had from the concept of a "mechanism for complexity arbitrage" that Aron and colleagues worked on in a recent research project funded by the Mack Center at Wharton. The project involved collecting data covering three years from several companies about various processes and the places where they were performed across the globe. Managers at these companies overseeing those processes where then asked to rate each of them on a complexity scale of one to seven, with seven being the most complex.

The researchers found a striking polarization in the way managers assessed the complexity levels in their processes. Those in the U.S. and the U.K. formed one group, while their counterparts in India, China and Singapore made up the other. "Whenever work involved number crunching, quantitative analysis, mathematical formulation, statistical data analysis and numerical calculations, managers in India, Singapore and China would say this is low complexity work," says Aron. "They could find the people to do those jobs and deliver quality on scale, month after month, year after year. But wherever work involves persuasion, communication, context-sensitive responses, interpretation, subjective judgment and cultural sensitivity, managers in the U.K and the U.S. will tell you that is low complexity work."

Are there natural limits to the kind of work that can be offshored? Of course, says Aron, but he adds that Indian service providers will not encounter them "as long as they do not breach the complexity divide." He says certain job functions are a natural fit for the U.S. and the U.K. and not easily outsourced, because they require customer interactions and persuasion. These are activities that are "embedded in the context of the market and they need context-sensitive communication," he says. "The Indian market is nowhere as sophisticated as that in the U.S.; it does not have the range of derivatives like in the U.S. market, and futures and options are just beginning to catch on.

Also see here, another demo of the external perception of Indian quant prowess. How fascinating. Managers in India, Singapore and China think that the quant stuff is "low complexity". :-) I believe similar things are happening in Silicon Valley: many startups are organising themselves as a marketing front-end in the Bay Area, and the engineering team in India. If these effects are real, global capitalism will reshape the geographical allocation of production: activities involving the quant stuff are going to increasingly move to India, Singapore and China. This bodes well for MIFC. If you have the MIFC book handy, see page 172-176 on human capital which offers a gloomy perspective on this, and Chapter 5 on BPO. Both segments of the MIFC book paint a relatively gloomy picture: but maybe (in the light of the above) things are actually a bit better than portrayed there.

Once such a process gets going, it will be self-reinforcing, and it will induce effects that are reminiscent of the specialisation seen in evolutionary biology, where small mutations are accentuated by natural selection, and species separate themselves out in plying different trades. In the decision-making of the global firm, tasks that involve quant skills would be more likely to get sent to India/Singapore/China, while tasks that involve more cultural context are more likely to get done in an industrial-country setting. In the decision-making of the individual, people with quant predilections are more likely to move to India and people in India face a higher marginal product of investing in quant skills. Both these effects (at the firm and at the individual) would reinforce each other.

How will human capital build up in the context of terrible colleges & universities? I think learning-by-doing is the key. The last paragraph of the above quotation rings true to me. With weak colleges and universities, learning is dominated by on-the-job accumulation of skills. I would advise every victim of the terrible higher education system in India to intern, intern, intern and build up on-the-job skills, accompanied by a higher intellectualisation based on a self-study program of reading the best books and textbooks so as to not become a narrowly-specialised technician

Index futures trading is active in India, so there is now plentiful talent that knows how to do index arbitrage. Interest rate futures trading in India is effectively banned, so naturally nobody knows anything about interest rate futures arbitrage. As the local financial system bulks up to higher levels of complexity, this feeds into the range of skills where there can be a global role for Indian professionals. There is thus a three-way synergy between (a) the BPO, (b) the local financial system and (c) the MIFC agenda: each feeds into the other. Conversely, a strategy of financial repression, where products and activities are banned in the domestic economy, has bigger costs than meets the eye: it hurts not just domestic finance but also the BPO and the prospect of MIFC.

                   Source     *** An Borrowing from Ajay Shah

Risky Business with Structured Finance

An HARVARD BUSINESS SCHOOL Published Business Finance Research and Ideas  -

How did the process of securitization transform trillions of dollars of risky assets into securities that many considered to be a safe bet? HBS professorsJoshua D. Coval andErik

Stafford, with Princeton colleague Jakub Jurek, authors of a new paper, have ideas. Key

concepts include:

• Over the past decade, risks have been repackaged to create triple-A-rated securities.

• Even modest imprecision in estimating underlying risks is magnified disproportionately when securities are pooled and tranched, as shown in a modeling exercise. Ratings of structured finance products, which make no distinction between the different sources of default risk, are particularly useless for determining prices and fair rates of compensation for these risks. Going forward, it would be best to eliminate any sanction of ratings as a guide to investment policy and capital requirements. It is important to focus on measuring and judging the system's aggregate amount of leverage and to understand the exposures that financial institutions actually have.

Inthe wake of the financial crisis, many once-esoteric investment terms have become a familiar part of our vocabulary. The role of structured finance securities such as collateralized debt obligations (CDOs), for example, and the part played by ratings agencies in legitimizing these products, has become all too clear. The pooling and repackaging of economic assets such as loans, bonds, and mortgages resulted in enormous yields for many investors—until, one day, they didn't.

"The Economics of Structured Finance," a paper [PDF] forthcoming in the Journal of Economic Perspectives, offers a close examination and clear explanation of how the process of securitization transformed trillions of dollars of risky assets into securities that many considered to be a safe bet. Authored by HBS professors Joshua D. Coval and Erik Stafford, with Jakub Jurek (HBS PhDBE '08), an assistant professor at Princeton University, the paper analyzes the difficulties of rating structured finance assets and the perils of relying on ratings to determine prices.

 

"We began studying the corporate bond CDO market roughly three years ago and reached the conclusion that it appeared to be severely mispriced," Coval remarks. "But we didn't have any sense that it would all come crashing down the way it did—we just thought that the mispricing would be corrected gradually."

Stafford adds that neither he nor Coval had done much work in the area of credit markets—yet these markets were growing at such an unprecedented rate that it piqued their curiosity. "People didn't explain anything about CDOs in a compelling way," he says. "They said, 'Oh, they're slicing and dicing.' Or they resorted to mathematical descriptions that were lacking any economic explanation as to why this product was so superior to other channels of financing. We also started hearing the phrase, 'These structures create yield out of thin air.' It implies magic, or a violation of a very standard notion in finance, a Modigliani-Miller proposition that the way you finance something is irrelevant to the value of the assets."

In their paper, the authors recount how, over the past decade, risks have been repackaged to create triple-A-rated securities. By mid-2007, they write, 37,000 structured finance issues in the United States had earned this top mark. In 2006, Moody's reported that 44 percent of its revenues came from rating structured finance products, versus the 32 percent of revenues generated from its traditional business of rating corporate bonds.

A simulation is "still a model and it's not exact, which is easy to forget."

-Erik Stafford

Yet the paper presents a modeling exercise (using the same computer tools employed by the ratings agencies) that demonstrates that the challenge of rating structured products lies in their extreme sensitivity to estimation errors—that even modest imprecision in estimating underlying risks is magnified disproportionately when securities are pooled and tranched. Their simulation uses pools consisting of 100 bonds with a five-year default probability of 5 percent and a recovery rate of 50 percent of face value conditional on default. Within each pool, the exercise creates a capital structure consisted of junior, mezzanine, and senior tranches. The junior tranche absorbs losses from the pool until the portfolio loss exceeds 6 percent, at which point it becomes worthless. The mezzanine tranche begins to absorb losses at that point, continuing to do so until the portfolio loss reaches 12 percent, with the senior tranche absorbing losses in excess of 12 percent. The scholars also run a simulation that constructs a "CDO²" by further dividing the mezzanine tranches, noting that due to the practice of subdividing large pools of residential mortgages, many CDOs of mortgage-backed securities were essentially CDO²s. That, coupled with the increase in subprime mortgages—from $96.8 billion in 1996 to approximately $600 billion in 2006—created a recipe for economic disaster.

The exercise clearly shows how the sensitivity of tranches to error in the estimate of default probability is determined by their seniority. An increase in the default probability from 5 percent to 10 percent results in a 55 percent decline in the expected payoff for the junior tranche, an 8 percent decline for the mezzanine tranche, and a .01 percent decline for the senior tranche. The effect is amplified in the CDO², with the value of the junior and mezzanine tranches falling quickly toward zero and the value of the senior tranche declining substantially as default probabilities rise. Earlier in their paper, however, the researchers illustrate the difficulty of estimating the default rate and assigning investment ratings of comparatively straightforward corporate bonds by citing 10-year historical data from Fitch on their default probability. Within the 10 investment grade rating categories of AAA to BBB-, the annualized default rate only varies between .02 and .75 percent, leaving little margin for error. The speculative grade range (from BB+ to C), meanwhile, has a default rate ranging from 1.07 percent to 29.96 percent.

Yet the ratings agencies aren't entirely at fault. "You can blame them a bit for going along with things and for overconfidence in their abilities," says Coval. "They were trying to rate these securities to the best of their abilities, and Wall Street kept telling them to rate more. They had a sense they were doing something wrong, but they were trying to improve their models as quickly as they could. I don't think they were deliberately manipulating the models to make them especially biased or imprecise." The computer simulations used to create the ratings are a standard tool, he says, adding that many would also describe it as a dangerous one because they create a false sense of confidence in what are essentially estimates.

"It's impossible to use a pencil and paper to characterize the interaction of a thousand different securities with different recovery rates and different business models," Stafford comments. "With a simulation you can get close to the right answer, but it's still a model and it's not exact, which is easy to forget. Often the people who are attracted to this approach are mathematicians, not economists, so they might not appreciate the underlying nature of some of the things they're describing." The paper notes that one neglected feature of the securities produced by structured finance is that it substitutes risks that are largely diversifiable for

risks that are highly systematic, or linked to events in the economy. As a result, ratings of structured finance products, which make no distinction between the different sources of default risk, are particularly useless for determining prices and fair rates of compensation for these risks.

The paper also raises larger questions. "There has been a significant change in the price of these securities," says Coval. "How much of the repricing that's occurred was an elimination of mispricing that was prevalent prior to the crisis? How much of it was a spillover from the mortgage markets to markets that were fairly priced? And if there was a high degree of mispricing before the crisis, what impact did that have for the level of credit that was extended in the economy? How much of the $9 trillion of assets that entered these structures wouldn't have been originated in the first place if you hadn't been able to put them in these structures and 'create yield out of thin air'?

"That, to me, is probably the most important question as we think about how deep this crisis is going to go," Coval continues. "If the economy over the last 10 years had, say, a trillion dollars or more of credit extended than was appropriate, given the risks that were being borne, then we now need to go through a significant adjustment."

The inevitable question, of course, is what to change going forward. Coval advocates eliminating any sanction of ratings as a guide to investment policy and capital requirements. "Without that, there's no cover for that investor who says, 'I don't understand this product, but it's rated AAA, and my board will accept that as a good enough justification to hold it.' Over time, we've relied on these ratings and created a system where investors could outsource their due diligence."

Stafford cites the need to focus on measuring and judging the system's aggregate amount of leverage and understanding the exposures that financial institutions actually have. "We created institutions that are too big to fail, and that's largely because we didn't appreciate their size and leverage," he says. "Because of the focus on ratings, there was an ease of avoiding critical economic analysis throughout the whole system." As is so often the case with large failures, everyone investors, ratings agencies, borrowers, and issuers—is a little bit to blame.

 ---- Sources - Harvard Business School / Working Knowledge / HBSWK.HBS.EDU

Introduction to Financial Modeling

What is a financial model? What is the difference between a financial model

and the spreadsheet solutions you create or VBA programs you write all the

time to answer financial questions or solve financial problems?

          A simple, practical answer is that a financial model is designed to represent

in mathematical terms the relationships among the variables of a financial problem

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 relationships as well and, therefore, can answer “what if” questions to some extent.

    But because they are not primarily designed with these objectives in mind, they

do not try to capture as many of these interdependencies as possible, and their

structures often make it cumbersome to answer “what if” questions or make projections with them.

        This may sound a little abstract. So let us look at a simple, concrete example.

Suppose you are using a spreadsheet to calculate, based on your taxable

income, 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 taxes

by multiplying your taxable income by one tax rate (30%, for example) and subtracting 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 the

different tax brackets), and enter it in the cell below. Then you can write an equation

in another cell to calculate your after-tax income by subtracting the tax in

the second cell from the taxable income in the first cell.

         This spreadsheet solution will give you the answer to your immediate question,

but it is not a useful financial model. Why? Because it does not capture the

key mathematical relationship between taxable income and taxes. The result is

that if you now try to answer the “what if” question, What would my after-tax

income have been if my taxable income were $10,000 higher?, you will have to

go back to doing the calculations by hand.

        However, you can set up your spreadsheet to calculate the taxes on any taxable

income (using the different tax brackets and tax rates) and use the computed tax

number 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 also

will 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 want

to capture as many of the interdependencies among the variables of the model as

possible. In addition, you want to structure your models in such a way that it is

easy to ask “what if” questions, that is, change the values of the independent

variables 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 even

unknown. You will have to come up with them based on financial theory, analysis

of data, and so on, and coming up with these relationships is one of the

major challenges of financial modeling. Generally, the more of these relationships

you can come up with and incorporate into your model, the more useful your

model 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 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 speed and 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

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.

  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 of time, but you need to have an overall design in mind or on paper depending on the complexity of the problem and your level of experience.  

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. Second, over time, you should try to create your own 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 almost at 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 the chances 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 take the time upfront to define and structure the problem and agree on them with the user (your boss, for example), you may end up having to extensively change the model you first create. When your boss asks you a question whose answer requires developing a model, she often has only a vague idea of what she is really seeking. As a finance person and a modeler, you are responsible for putting it all in 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 supposed 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 variables, and discovering and quantifying these can take a lot of time. How much effort you put into doing this should depend on how important the project is and how 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 provide them or where they will come from. This is crucial. For example, if you are creating a model to do the business plan for your company, the inputs must come from the business managers. You cannot just guess what sales growth rates they will be able to achieve, how much they will have to spend on plants and equipment

to support those sales growths, and so forth. You may not need the actual numbers upfront, but the list of inputs should be established based on your discussions with the business managers so that you can make them independent variables in your model. Otherwise you may have go back later on and change a

lot of things in 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.

 

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 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. How frequently a model will be used is another important issue. If a model is going to be used only once in a while, then it does not matter if it takes a long time to run or if it takes some extra work every time to create the outputs. A model 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 have to tell it exactly how all the calculations in the model will have to be done. In most 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 for the computer to do it. It does not pay to start building the model until you are

sure you could solve the problem by hand. 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.

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