Spreadsheet Modeling on the Microsoft Windows Platform - Essay Example

Running Head: Models, problems, and decisions Models, problems, and decisions Type here Type here Introduction Mostly, majority of the businesses today work on the Microsoft Windows platform. Spreadsheets come as a part of that platform providing businesses with tools that can help them form business models. These tools are easy to use and extremely effective. With technology taking over almost all processes of the business, businesses are increasingly getting involved in using spreadsheets to form business decision models. A number of benefits are associated with the use of spreadsheet based business models: Simplicity and precision Transferability We can describe spreadsheet modeling as a document specifically designed to fulfill a particular objective in any industry. It could be used to test various possibilities without any major risks regarding the inventory levels, leftovers, lost or theft, etc. As mentioned earlier, the main advantage of models is that they allow tremendous flexibility in terms of making changes, experimentation, etc. Various models exist such as budgeting models, debt analysis models, etc. Although there is a lot of comfort that comes with the usage of business models; however, most people who form those models do not follow a particular framework of model development. The basic problem arises when people start working at the top left hand corner and start moving and copying cells and introducing variables in a haphazard manner. It is also a wrong approach to develop formulae when you need them. We might end up developing a model, the quality of the model development process and the developed model suffers (Spreadsheet model for managers, 2010). Problems with Spreadsheets All business related curriculums teach their students about spreadsheet model and how to model problems faced by businesses, however, very few focus on teaching a particular framework that can be utilized for building a proper high quality model. There are several problems that one may encounter while modeling business models in spreadsheets. The few very common problems are: Lack of a fixed structure One major problem that one might encounter while modeling on spreadsheets is that they do not have a proper structure for designing or laying out the model. We can quickly jump on model formulation, however, we would realize while developing the model that we need to bring in a proper structure or some sort of rules as the model stretches to more than one sheet. Models spread on more than one sheet could get pretty complex, and difficult to extend or edit if in case the requirements or the basis change. Redundancy and model error Another problem with spreadsheet modeling is that there could be several mistakes while copying formulae. The formulae applied in spreadsheet perform single cell calculations. However, mostly all models need the formulae to be copy pasted across more than one cells. This process would not end up having errors if we develop the spreadsheet properly. This problem could be resolved using proper structuring of the model, identify the constants and the variables required in the formulae so that when we copy them to other cells, the formulae do not become erroneous. Lack of Clarity The notations used in spreadsheet modeling can become quite confusing for people only using the spreadsheet. Complex formulae refer to multiple cells and they might not be self-explanatory. Therefore, it becomes complex for anyone besides the developer to understand the model. The solution to this problem could be a detailed analysis and a proper documentation. Two Dimensional Models The spreadsheets have two axes: rows and columns. However, it is the requirement of a number of models that it has the ability of being copied to other dimensions such as in product groups, different segments of the markets, different units of business, and channels of distribution and then find a cumulative answer. Copying of formulae to another spreadsheet might bring the risk of inconsistency in the formula making the problem even more complex than it was (Quantrix and excel: 3 key differences, 2010). Business Models and Decisions The main purpose behind forming a business model is to reach on accurate decisions in as little time as possible. Businesses need to take quick decisions if they need to cope up with the rest of their fast changing market and its environment. We have identified this earlier that it is possible for developers to develop a model according to their own set ways and it might be according to the requirements as well. However, in the absence of systematic structured approach, it might be unable to fulfill the whole purpose. The main idea behind building models is to analyze a problem and seek help in decision making. For example, a person entering in the business of computer retailing might need to analyze the correlation between quantity, price, market segment, and then reach on the decision as to if entering the market is feasible for him or not. The decision making process is not so direct at all times, however, this is the sort of decision making that an efficient business model would help in (Mather, 1999). Steps of General process of modeling with spreadsheets Phase I – The first phase of modeling with spreadsheets is the planning phase. In this phase, we find out the goal or the main purpose behind formulating the model. In this phase, we also do some hand calculation for a preliminary analysis of the problem at hand, along with sketching out a basic structure of the spreadsheet in the form of a chart or using some sort of visual aid. Phase II – The second step in the modeling with spreadsheets is building a small model, which could be referred as a prototype. The prototype would be a smaller version of the larger model. It would help in understanding the requirements of the model as well as the problems that would come in the formation of the larger model. Phase III – The next phase is the testing phase, where in we test the smaller version for errors. It helps us in identifying the problems that already exist so that if we need to make changes or review requirements, we can do them easily. Phase IV – Once we phase through the third phase, we move onto the bigger problem, which is to form the larger model. Now this becomes easy because of the third step, we already know about the problems and their solutions so it is likely that same mistakes would not be repeated. Phase V – The final phase is model analysis which is done using the test results from the small version testing as well as the complete model that we developed in phase IV. If everything is according to what was required, then we can finalize the model, or we can go back to the previous phases to make changes. This is an iterative process and it would keep on going till we get the desired results. Steps of Building good spread sheet models The first step in the building of a good spreadsheet model is that we must organize the data according to variables, constants, dependent variables, etc. The next step is to enter the data pieces into separate cells to avoid any clutter and keep them organized. Another important step would be to keep the formulae and numbers separate and avoid using numbers in formulae. Moreover, another tip is to keep the naming conventions simple and using range names. Moreover, we should also use limitations, restrictions, and parameters. Another good idea is to use borders, shades, and different color to distinguish between different types of data sets and cells. Another important phase of model formulation is testing and debugging. We need to continuously keep on testing the model and study each formula so that we know it is doing what we intended it to do. We need to identify errors and correct them early to avoid complexities, because it is extremely difficult to change the model once it has been completely formed. Benefits of What-if Analysis to managers There are several benefits of what-if analysis for managers. It helps in finding newer and different parameters and also helps in analyzing how close we want the estimate to be to avoid getting an incorrect solution. This means that the analysis would point out the sensitive parameters that are affected by small changes in values or placement. Sometimes, the scenario changes once the research has completed. In such cases, what-if analysis leaves sign posts to indicate if a change in a parameter would have an impact on the optimal solution. This helps managers have a clear insight on the elasticity of each of the parameters with the optimal solution. It also helps in analyzing the impact of changes in policies. Sometimes, some variables are the result of certain policies and if the policies change, the variables change as well. Thus, a what-if analyze helps manager in understanding the impact of their policy decisions on certain parameters and in turn their impact on the optimal solution enabling them to make friendlier policies or reach on correct decisions. Networks Networks are a depiction of relationships between the various elements of a system with the help of a visual aid and a conceptual diagram. It is used in all fields such as scientific, social, and economic. In recent years, there has been quite some developments both in method and application of network optimization problems. The types or examples of networks could be: transportation, Electrical and Communication network, production networks, distribution network , project management, location management, resource management, and financial modeling and planning. Types of network optimization problems Minimum Cost-flow problems – This problem utilizes the distribution network and tries to minimize the costs of transportation of goods. The main objective is to minimize the transportation cost while using the distribution network. The property of this model is that at least one node would be demand and one would be supply whereas the rest are transshipment nodes. It allows flow in dual directions and the cost of flow is proportional to the amount that flows through it. Maximum flow problems – In this model, we try to maximize the amount that flows through the network. The main idea behind this model is to increase the amount of flow from source to destination. It maximizes the supply flow from the vendors to the processing facilities. All the flow begins from a single node whereas all other nodes behave as transshipment nodes. Shortest path problems – The objective is the model is to find the shortest possible path from the source to destination within the network. Yes – or – No decisions Managers or people face with yes-or-no decision while choosing a particular option or making a certain decision, they have only the option of choosing between yes or no, i.e. they can either go ahead with the decision and do it or they can stop it. Binary Variables The choice that we make through yes – no decisions can be depicted by binary variables. Binary variables are those that can only assume the value of 0 or 1. Binary decision variables A binary variable is assigned the value ‘1’ to depict yes and the value ‘0’ to depict No. Thus, in case of yes – no decisions, we can use binary variables to depict the decision with 1 representing positive decision and 0 representing No. BIP, Pure BIP and Mixed BIP Binary integer program models are those models that fit the linear programming approach except that they utilize binary decision variables. In a pure BIP model, all the variables used are only binary variables whereas in mixed BIP, as the name suggest we use simple variables as well as binary variables. Examples could be location decisions for manufacturing plant whether to build or not to build. References Quantrix & excel: 3 key difference (2010). The problems with spreadsheets. Retrieved on November 14, 2010 from < http://www.planningmodels.co.uk/index.php?page=quantrix-versus-spreadsheets > Mather, D. (1999). A framework for building spreadsheet based decision models. Retrieved on November 14, 2010 from < http://web.utk.edu/~noon/mather.pdf > Spreadsheet model for managers (2010). Chaco Canyon consulting. Retrieved on November 14, 2010 from < https://www.chacocanyon.com/workshopsseminars/smm.shtml> Read More