Problem Solving & Spreadsheet Modeling
Problem Solving & Spreadsheet Modeling
This paper talks about the problem solving process along with examples, as well as one of more commonly used modeling and analytical techniques, i.e. spreadsheet modeling. It also talks about its dependency, its uses, disadvantages and well as explores ways to better this very new, but powerful technique. |
Modeling is the process of creating a simpliﬁed representation of real life problems and representing them in an organized fashion in order to understand a particular problem. These models can take many forms such as mental, visual, mathematical or electronic spreadsheets to name a few. Their uses can be seen in almost every field of our daily lives such as businesses, governments, human resources, science and engineering, and medicine etc. Their primary function is to gain insight into a specific problem and after getting a clear understanding of the topic, one can easily approach it from different directions with possible solutions. This paper discusses this process in detail, starting with gaining insight into a specific problem by using a problem solving model, and then moves on to how it applies to spreadsheet modeling.
It also discusses different aspects of electronic spreadsheet engineering, its uses and caveats, as well as its dependency in the business world these days. Before creating any models, visual, mathematical or spreadsheets, one has to start with the problem solving process. The problem solving process consists of six stages that can be followed to solve almost any problem. These stages or steps are employed and are repeated multiple times, not necessarily in the same order, to obtain an efficient and refined solution to a particular problem. The first of these stages is exploration of a “mess”. “A mess is a morass of unsettling symptoms, causes, data, pressures, shortfalls, and opportunities” (Baker & Powell, p.18). One of the key issues in solving any problem is to be able to take any given situation and extract key information, problems, issues as well as opportunities from it. A problem never presents itself in a clear, well-posed statement.
It is usually hidden in a scenario, along with a lot of other trivial information and any problem solving should start by understanding a given situation and be able to extract key information from it. “During this phase, questions that are explored include listing problems or opportunities that are to be faced, gaps between current and desired situations, and thinking about stated and unstated goals.” (Baker & Powell, p.21) Baker and Powell use an example of a pharmaceutical company in their book, where majority of that company’s revenue is generated from the production and distribution of a single drug, and present a scenario where the patent for that drug heads towards its expiration date in upcoming months. Using this scenario, exploring the mess stage would involve the company officials to perform individual researches and evaluate any threats and affects the expiration of the drug patents would have on their revenues as well as taking initial steps to address the upcoming problems (Baker & Powell, p.21). Second stage of the problem solving process involves searching for information.
“This includes opinions, raw data, impressions and published literature etc. and involves casting about widely for any and all information that might shed light on what the problem really is” (Baker & Powell, p.22). This is basically a “brainstorming phase”, and usually involves approaching the mess from different directions. In the case of the pharmaceutical company, for example, senior managers would handle this phase by holding meetings and addressing their discomfort towards the upcoming problem as well as hiring outside consultants to perform company as well as industry research and gathering all the necessary facts involving this problem. “This stage is usually complete when relevant information for the situation at hand if found and organized and when initial hypotheses about the source of the problem and potential solutions are discussed.” (Baker & Powell, p.22) The next phase of the process, known as “identifying the problem”, starts out in its divergent phase with a few possible problem statements that can be inferred from earlier stages. These problem statements are evaluated and results in a final, more detailed, problem statement that may or may not be an aggregation of one or more initial statements.
It is imperative that each of the initial problem statements are examined individually and eliminated carefully and the end result is an unambiguous problem statement. For example, the pharmaceutical executives may initially come up with two different problem statements, one dealing with somehow extending patent dates and focusing on revitalizing their strategy around the existing drug, while the other group thinking about how to come introduce other drugs into the market to cover the decline in revenues. Another option looks at decreasing costs and expenses overall to cover the losses incurred.
From these initial statements, developing a portfolio and introduction of new drugs seems to win the general consensus. The next phase of searching for possible solutions to the now defined problem statement involves open-ended process searches for good, even radical, solutions. This can only be done however, when a problem statement has already been described. “Considering possible solutions to an ambiguous problem is not only inefficient but also dangerous as well, since it can lead to solutions that were nowhere close to the original problem and can result in a lot of wasted time and resources.
This stage is complete when a list of potential solutions is produced and perhaps a list of advantages and disadvantages for each one” (Baker & Powell, p.23). In the case of the pharmaceutical example, this phase can start out with solutions including speeding up FDA approval process for the new drugs, investing R&D resources and funds towards drugs with the highest percentages of approval and focusing on a new portfolio of drugs in the same medical category, and thus providing expertise in one or two specialty areas (Baker & Powell, p.23). Next comes the stage of evaluating all possible solutions and deciding on the preferred solution. This is done through evaluating each of the initial solutions against a set of given criteria. “This can lead directly to a preferred alternative. More often, this process leads to changes, and improvements, in the solutions themselves. Often, stronger points of multiple solutions are grafter together while less attractive aspects are removed and through this process, a final, preferred solution is refined” (Baker & Powell, p.24).
For example, for the pharmaceutical drug scenario, after evaluating and grafting various solutions, it can be decided that one most critical area for improvement is how R&D funds are allocated over time. And finally, the problem solving process concludes with implementation the preferred solution. In this phase, a problem-solving team will identify sources of resistance as well as support for their solution. During this stage, once a solution has been implemented, a final evaluation is done after some time to see if the implementation has been successful or not. This stage concludes when an implementation plan is produced and executed enough so that the team may begin to evaluate how well it is succeeding. For example, the pharmaceutical problem solving team will have to get approval and support from the senior management for their solution and when they carry the plan forward, they must monitor its impacts and modify it as needed, and solve a new set of problems as they arise.
In order for a solution to be refined, these six steps must sometimes be repeated multiple times, each time creating a new solution to the problems in the previous cycle. “It is also worth stating that only rarely are these six stages followed in a strict sequence. Most problem-solving processes move back and forth from one stage to another, perhaps rethinking the problem statement while evaluating solutions, or returning to an information-gathering mode while searching for solutions (Baker & Powell, p.25).” Now a brief overview of the problem solving process has been outlined, we can discuss one of the more common problem solving and analyzing techniques used in the business world today, i.e. spreadsheet modeling. Baker and Powell explain it in their book that “spreadsheet is the second best way to do many kinds of analysis and is therefore the best way to do most modeling (p.4).”
Basically what this means is that for any singular modeling task, there is always some better, more powerful software available that can handle that specific task. However, due to the complexity and costs related to such applications and their very narrow scope of problem solving capabilities, it becomes inefficient for most companies and analysts to use separate software for separate models. Therefore, most of the modeling in the business world is done in electronic spreadsheets due to its nature of being able to handle and process a wide scope of modeling and analytical tasks. There are, however, limitations to electronic spreadsheets due to its broad capabilities but most modeling tasks in many companies can be easily handled through spreadsheet modeling and analytical tools. Despite their limitations, spreadsheets allow modelers to build more detailed and more complex models and also have the advantage of being pervasive in business analysis. On the other hand, however, using electronic spreadsheets is not without its downsides.
The business world is still at an early stage of understanding how to develop error-free spreadsheets. And one of the caveats of using electronic spreadsheets is assuming that they are error free. In fact studies and research have shown that only less than ten percent of spreadsheets in individual studies have been free of any errors (Graves, p.1). These errors may be small and would seem very insignificant, such as improper sorting or entering numbers as text, but in larger companies, the results can be devastating and can cause effects ranging from ineligible candidates becoming police officers to a school losing more than $50,000 in funding due to budget understatements (Baker & Powell, p. 6). When using electronic spreadsheets, or any software for that matter, it is also imperative to ensure that the software packages are always up to date. This can range anywhere from applying security updates to upgrading software versions altogether.
“Upgrading a business’ software is more than just an economic business decision. It is a matter of security, productivity, lower costs, and employee morale” (Garger, p.1). The longer any software has been around, the more vulnerable it gets to malicious viruses and security breaches. Therefore any software must be updated or upgraded routinely since newer versions would be safer from a security standpoint guarding against vulnerabilities from previous versions. Another reason to keep software up to date is from a budget point of view. Usually, older versions of any software are more expensive to maintain than the newer versions (Garger, p.1). And probably the most important reason to update any software, and especially spreadsheets, is an increase in productivity. Software companies are continually adding newer features to their applications that increase productivity and are efficient in handling various tasks.
More often than not, newer versions usually have larger set of capabilities and tools under their belt and can perform more tasks in lesser time and with more ease (Garger, p.2). Even though electronic spreadsheets are still quite new in the business world, their dependency has been increasing drastically in the past two decades. About 25 years ago, the idea of personal computers was unknown to most and “modeling was performed only by specialists using demanding software on expensive hardware. This meant that only the most critical business problems could be analyzed using models, because only these problems justified the large budgets and long time commitments required to build, debug, and apply the models of the day (Graves, p.1). However, since then, the ever evolving technological advances starting with personal computers, spreadsheets and now the add-on features for specialized analyses have greatly changed the business world.
Nowadays, anyone with a laptop and little background in spreadsheet modeling can easily handle tasks that would have been thought of as quite complex 25 years ago and would have required teams of specialist to perform (Baker & Powell, p.5). In fact, “these applications have become a staple of end users and business professionals and are widely used by businesses, service agencies, volunteer groups, private sector organizations, scientists, students, educators, trainers, researchers, journalists, accountants and others” (Graves, p.2) In conclusion, spreadsheets represent an ever-present platform for businesses and our dependency on them cannot be understated.
However, as with any great tool, there are many stipulations. Any tool can be only as useful and powerful as its user. Therefore, in order to use electronic spreadsheets properly, one has to be proficient in their use and must acquire the necessary skill set to create and analyze powerful and effective business models. Knowing the capabilities of any given software as well as its limitations is crucial. And finally, planning a spreadsheet model properly and never assuming that it will be error-free are necessary to create successful business models. “Only with a solid foundation in spreadsheet engineering can the business analyst effectively generate real insights from spreadsheet models (Baker & Powell, p.8).
Baker, Kenneth R.; Powell, Stephen G. (2010-10-08). Management Science: The Art of Modeling with
Spreadsheets, 3rd Edition. John Wiley & Sons, Inc. Kindle Edition.
Garger, John. “The Advantages of Upgrading Your Business’ Software”. Bright Hub. Nov 8th, 2010. Accessed Dec. 12th, 2012. <http://www.brighthub.com/computing/hardware/articles/43180.aspx>
Graves, Sherry. “The Importance Of Excel In The Workplace”. SparkNET. Sept. 10, 2007. Accessed on Dec.12th, 2012. <http://ezinearticles.com/?The-Importance-Of-Excel-In-The-Workplace&id=724646>
Subject: Problem solving,
University/College: University of Arkansas System
Type of paper: Thesis/Dissertation Chapter
Date: 26 October 2016
We will write a custom essay sample on Problem Solving & Spreadsheet Modeling
for only $16.38 $12.9/page