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