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.