The business world today viewed as fast paced, diversified and full of money to be made. If you have the drive to go out there take calculated risks and carve out your place in this ever changing world it could turn out to be a lucrative adventure. The entrepreneurs of today are doing just that, they are examining what consumer??™s trends would be beneficial to their ideas, services and or products. To be successful in today??™s market the modern day entrepreneurs are observing four key environments; The Economic and Legal, Technological, Social, and the Competitive.
In the United States we have the freedom to start a business at any time without excessive government regulations. An individual or group can start a business in their home or in a facility tailored to suit their business needs. There are many things that need to be considered when opening a new business. The four important issues are the economic and legal environment, the technological environment, the social environment, and the competitive environment.
The economics of starting a business are very important. Getting the money to start a business is one of the first things that should be considered before a business is started. The risk of losing the money has to be worth the reward. Before you start your business you need to decide what financial risk you??™re willing to make for the possible reward. When I asked Jamie how much financial risk he was willing to take he had thought the greater the risk the greater the reward. If you really believe in your product taking a big risk is the best way to start a business. Jamie has a simple motto that he goes by when starting a business ???Go big or go home.???
There are many ways that you can get the money to start a business, take out a loan on an asset that you own, have family and friends invest in your business, or get the money from organizations such as the SBA. An estimated ninety percent of all startups receive money from family and friends (Nickels, McHugh, McHugh, 2008). This can be a great option if the loan has little or no interest. Family and friends may also be more flexible on how they are paid back. Often times when starting a new business your income may be inconsistent, so flexibility of how the loan is repaid can be a great option for your company. The down side to borrowing from family and friends is that you run the risk of damaging your personal relationships if you cannot pay the loan back in a timely manner or the business is not a success. If this is the option that you decide make sure specific guidelines are laid out of how the loan well be repaid in multiple circumstances. During my interview I learned that Jaime started his business by taking out a home equity loan. This is also a great option, because the interest on you loan is at a much better rate then on a business loan. Some home equity loans work like a credit card so you only have to pay interest on the money that you are using and you get the great interest rate on a home equity loan. The disadvantage to a home equity loan is that if you default on the loan you run the risk of losing your home.
Starting with a business plan that has a detailed description of how to handle the finances is very important. The first thing that you need to know is how much money you need to get your business started. Starting with a rough budget well help you come to a number of how much money that you will need to get started. This budget may change as your business gets started but it is important not to change your bottom line. If you only have a set amount of money, if you go over that amount something else well not get paid, or you may end up paying the expense of high interest credit cards.
Make sure that all of the things that you will need are in your budget. Such as rent, utilities, inventory, and salaries. A large mistake that people make is that they forget to pay themselves. Be realistic about how much money that you need to live. Nothing makes morale worse than if you??™re not making enough money to live.
Another item that you need to keep a close watch on is your inventory. Make sure that your inventory is not growing faster than you can pay for it. Capital goods are also a large expenditure that can grow out of control very easily. Having the latest and greatest capital does not always work to the companies benefit. Your revenue has to be high enough to keep the capital working efficiently. For example, if you are buying a copy machine and you plan on charging for each copy. There is a copy machine for sale for one thousand dollars and it can copy seven thousand prints per day. You can instead buy a copy machine for two thousand dollars that can print twenty five thousand prints a day. The more expensive copier might seem like a better deal, but if you only make one thousand prints a day the more expensive copier is not necessary. Over time you may be able to utilize the more expensive copier but during the time you do not need the more expensive copier you are paying the extra interest on it.
The government plays a large role in your business as you get started. Understanding how the tax laws work is important. As a starting business, you may want to structure your business so that you can push off paying as much tax as you can. When starting out your cash flow becomes very important to the health of your business.
How to legally structure your company can be a key to your success. Each situation has a different way to set your company up. If you are in a business that the worst case scenario is that you can lose everything that you own, setting up as a corporation may be your best choice. Because then you can protect your personal assets. The biggest problem that I have found with corporations is that when you do go and try to get credit they make you sign a personal guarantee that the money well be paid back. Corporations are also a lot more paper work then a sole proprietorship. A new company usually does not need more work to do. For these reasons a sole proprietorship maybe the best choice for your business. I think that if you are in business by yourself and plan to stay small a sole proprietorship maybe your best choice.
Another choice that you have is to a partnership when your business starts. There are many advantages to a partnership. The amount of time taken to run a business is cut in half which can be a great option. You well have more time to expand the business rather than spending all of your time on day to day operations. Another advantage is help with the financial side. Your partner has to come up with half the money that is needed to start the business. It is also a great asset to half another set of ideas that can help your business start. The biggest disadvantage is that there may be disagreement of how the business should be run. I believe that one person should make decisions. Sometimes splitting up the responsibilities is a great way to handle a partnership. I also believe that silent partner is a great way to start a business. You get a lot of advantages without the disadvantages.
Webster??™s dictionary defines technology as the practical application of knowledge especially in a particular area. When you think of technology this way it changes what most people are picturing in your head as technology. I believe most people think of technology as the newest laptop computer or gadget like an iPhone. While these do fit the definition for technology, if they are not used for the proper purpose they are just another wasted piece of equipment. A better saw blade to most would be considered a tool but not technology, the other side of this definition is something most people would not consider to be technology but really is if used properly. An example of this is if you own a marble business and this new saw blade helps to reduce waste and increases output, it is now a great piece of technology. If a new business takes these three questions into account when looking for the appropriate technology it should do well, what is out there to help the business, what is the appropriate way to use it and is this technology cost effective for the business
Finding technology that will benefit your business is one of the best ways to make the people working there as efficient as possible. There are all kinds of technologies out in the marketplace to help businesses succeed so the first thing you need to do is find what is appropriate to help your business. If your company distributes product to stores all over a designated area and you buy laptops for the trucks so they can constantly be in contact and give and get updated information in real time. This is great use of technology and allows you to have an efficient delivery workforce; however if you did the same thing but later realized that they didn??™t have cellular reception capabilities this technology really would not provide near the value. This is an easy example of two very similar pieces of technology and one provides a business with the efficiencies it needs and the other that is just technology without the benefits needed.
Once a business finds the technology to help improve the business the next step is how it makes use of that piece of technology. With the company I talk about above they could find the best distribution software they can afford and if they do not implement it properly it will not bring them the benefits that they need or expected. There are many ways once a company finds the technology they need to improve the business where things can still go wrong. Implementing technology can be difficult and getting people to use it can be just as much of a problem. It is possible that implementing a piece of technology can cost as much or more then the initial expense to buy it. I have seen people who have been shown simple ways to make their job easier through the use of technology and they continue to do things in a manual way just because they are comfortable with how they are doing it. Technology has many challenges so it is important to remember that purchasing it is just the first step in becoming a more efficient organization.
When it comes to a new business and technology I feel the cost of the technology needs to be looked at two ways. The first is can the organization afford to purchase whatever the technology is. If an organization finds the absolute perfect software to manage its inventory but spending the money on the software will leave the company in such a financial position that they will not have the ability to pay other bills there is no benefit. The other side of the cost effectiveness of technology is the return on investment. Companies have all kinds of return on investment they need to hit to spend money on something and that is ok. As a new company you do need to make sure that the things you spend money on will give you a positive return on your investment otherwise you probably should not spend the money. The one real exception to this is if this technology is something that your business can not exist without, such as a restraint cannot exist without an oven. This leaves an organization with the knowledge that technology for the sake of technology is no good there has to be a benefit or a return on your investment to make a purchase worthwhile.
The social environment that an organization is starting out in is important to understand if you want to be as successful as possible. As the second generation owner of S.C. Johnson, H. F. Johnson Sr. said in his 1927 profit sharing speech ???The goodwill of the people is the only enduring thing in any business. It is the sole substance??¦the rest is shadow.??? If a new organization uses this philosophy in its dealings with its own workforce, customers and the community it operates in it will have a better chance of surviving then one that does not.
Diversity in the United States is a fact of life and companies that understand that have a better chance of being successful. Diversity in a workforce brings more experience, knowledge and opportunity to an organization. Companies that do not take advantage of diversity are not giving themselves all the opportunity to succeed that they should.
Society has trends that are an important part of business and if a company understand these trends then and uses them to their advantage they can grow rapidly. A good example of this is Jane Fonda and the trend of exercising; she was in the right business at the right time and used that to have an incredibly successful line of exercise videos. This trend has lasted a long time but most trends have a shorter lifespan so it is important to understand them and where they are in their life cycle. A business who strives to understand society??™s trends will usually find itself with a plan to take advantage of them.
A new organization that strives to be a good neighbor in the community it exists in is being smart. Small things can make a large difference when a new organization is trying to get positive information out about itself. Generating positive information about any company is important and should never be taken for granted.
When it comes to competition every business wants to know what their competitors are doing, they in turn mimic that product or service but at a cheaper rate. The companies of today from the largest fortune five-hundred companies all the way down to the mom and pop shops are starting to gravitate away from quality of products and services that the consumers are starting to demand.
When it comes to starting and running a business a business owner has to have quality in mind over quantity and eventually when that business becomes bigger and there is more demand on their products or services that company must find a happy medium between quality and quantity to ensure the needs and or wants of the consumers are met.
The struggle between quality and quantity is a constant battle with business owners, especially entrepreneurs as a new product or service has to overcome great opposition from competition that has been well established in the market place in some cases that competition has been established for decades. The entrepreneurs must have a clear business plan in reference to dealing with stiff competition; their product must surpass customer expectations.
So in reference to jams and jellies the CEO of Cobblestone Orchard stated, that if a new business can offer consumers a wholesome jam that is not massed produced with a ton of preservatives in it. Consumers generally will pay the little bit higher price for the quality product. This is part due to the new business catering and thoroughly understanding the consumers??™ wants of having something healthier to give their children, which in turn that consumer will offer free advertising for the company by word of mouth to friends and relatives that this new product is the best way to go. The only down side to that is, the company now has set the standards of quality and expectations in which they must abide by at all times. Due to the extremely high standards they have set for their product and care they have put into the product. If the company deviates from that quality they set the standards for and have forgotten about that quality and exceeding consumer expectations they will lose money and customer base. The consumers will feel betrayed and let down by the company, that the company sacrificed their core values for bigger profit margins.
Another focal point on the competitive market is to exceed consumer expectations, if a new business offers the same old service or product at the same price and are not trying to wow the consumer they will not succeed in moving business to themselves; consumers will stick to their norm of spending and not venture out to see the competition. The new markets of today states that consumers want to be wowed with new fresh ideas that are high end but easy on the pocket book. As previously stated the entrepreneurs of today have to in order to be successful always have the edge over their targeted competition with the ???wow??? factor when it comes to new products.
The ???wow??? factor is achieved by always getting feedback from consumers positive and constructive, by listening to consumers, understanding their needs, wants, and or concerns a business can become very successful. When consumers buy a product or a service they are not only looking for the obvious purchasing of the product, but also the total shopping experience and if a business can offer a quality product with competitive pricing and an unforgettable shopping experience that business will take off.
The way a business can overcome the competitive field is to understand everything previously stated, but also companies have to empower their employees to aid in the consumer experience, as employees are a businesses frontline and even the face of a business. If employees have to go through the chain of command to please a customer it can take several minutes or even hours to get manager approval. However, if employees are empowered to make sound business decisions based on their initial thoughts, the consumer will have an all around more enjoyable experience. This can be evident anywhere where shopping is found one such example comes from entrepreneur and multiple business owner Jamie Patrick this entrepreneur owns four very different types of businesses, during an informal interview with him in regards to empowering his employees, he stated that all employees and employers must have an understanding that decisions can be made by everyone and backed up by the managers and owner/owners without fear of repercussions. What this boils down to is the trust in the employees that decisions for improving the customers shopping experience can be made at the time of check out or during shopping. This freedom allows the employees who are at the forefront of any business to make decisions that can improve and will pay the company back ten-fold, through the word of mouth from a customer and their positive shopping experience.
We have found through our interview with Jamie and from personal experiences there are a number of things that have to be considered when starting a business, a few of these components are the economic and legal, technological, social, and the competitive. By entrepreneurs taking these things into consider they will increase their business??™ chance of becoming successful.
Johnson, H. F. (1927.). SC Johnson-Our Philosophy. Retrieved July 6, 2009, from
Merriam-Webster Dictionary. (n.d.). Retrieved June 27, 2009, from
Nickels, W.G., McHugh, J.M., & McHugh, S.M. (2008). Understanding business (8th
ed.). Burr Ridge, IL: Irwin/McGraw-Hill
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 1
Introduction to Financial Modeling
hat 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 aftertax 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.
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 2
INTRODUCTION TO FINANCIAL MODELING
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
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 3
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
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 4
INTRODUCTION TO FINANCIAL MODELING
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. 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
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 5
Introduction to Financial Modeling
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
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 6
INTRODUCTION TO FINANCIAL MODELING
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.
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
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 7
Introduction to Financial Modeling
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 to test individual parts of the model.
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 8
8 Step 8: Protect the Model
INTRODUCTION TO FINANCIAL MODELING
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.
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 9
Introduction to Financial Modeling
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.
HOW THIS BOOK IS ORGANIZED
To use this book effectively, it will be helpful to understand some of my thinking behind its organization. Modeling is a skill that you can develop only by creating a variety of models. Once you have developed the basic skill, you should be able to create increasingly complex models??”as long as you understand the finance and mathematics of the underlying problems. In some ways, it is like learning a language, especially when you are working with VBA. Once you learn the language, you can say new things in that language??”you can make up sentences that you have never heard before. I therefore emphasize learning the language by exposing you to a variety of models in different areas of finance instead of concentrating on one particular area like derivatives or trying to anticipate and include every model you may need to develop. For both Excel and VBA, I have provided one part that covers the ???grammar??? of the language (Parts One and Three) and then a second part that provides the examples of its use (Parts Two and Four) to create models. Just as you do not need to know everything about the grammar of a language before you can start using it, you do not need to know everything about Excel or VBA before you can start creating models using them. I suggest that you start with Part One to become familiar with Excel and quickly move on to working on the models in Part Two. Do not spend a lot of time trying to master the grammar. Come back and learn it as you need it. In each chapter in Part Two, I have included a section called ???Review of Theory and Concepts.??? These are the theories and concepts of finance that you will need to work on the models in that chapter. If you are familiar with them, you can skip them and go on to the models. If you want to refresh your memory and understanding, the material is there. You will notice that many of the modeling chapters in Parts Two and Four have the same titles. There is a reason for including such parallel chapters. It has been my experience that the easiest way to learn modeling with VBA is to start with problems that you can already model in Excel, because then you already know a lot about the problem and you can focus on the VBA aspects of it. In
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 10
INTRODUCTION TO FINANCIAL MODELING
many cases, the parallel VBA models also demonstrate that even if you can model a problem using Excel, using VBA can provide additional flexibility, power, and so on. Most of the VBA chapters include additional models that are cumbersome or impossible to create using Excel. What this means is that you may be better off covering Parts One and Two before you go on to the VBA parts. However, if you are already good at modeling with Excel and are primarily interested in learning VBA and modeling using VBA, 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 VBAbased models, I have provided in the first chapters of both Parts Two and Four additional information on how to develop good models using them. I have also included suggestions on how best to use the material in both parts to improve your modeling skills rapidly. You will progress faster if you read and follow these suggestions instead of taking a haphazard approach.