Author: Monika Arora
"What-if" or sensitivity analysis is one of the most important and valuable concepts in management science (MS). To emphasize its practical relevance in a business environment, we teach students in our introductory MS course to analyze "goal seek" with Excel's built-in Goal seek. This case demonstrates the application of the goal seek tool with several examples.
Sesuki Mfg., Ltd. is a manufacturer of products such as electronic calculators. Sesuki has been searching for ways to increase quality and reduce production costs to remain competitive in world markets. Increasingly, they are substituting robots, automation, and computer-controlled manufacturing systems for blue- and white-collar workers. Sesuki is considering making some changes to automate the manufacture. Let us see how the manager takes the decision using a decision model. As one is aware about the popularity of excel as a tool for taking decision.
The immense popularity of Microsoft Excel throughout the business world today for processing quantitative data and developing analytical solutions is widely acknowledged. We have increased the emphasis on sensitivity analysis in several of the modules by introducing the use of "goal seek". With Excel's built-in Goal seek o tool. Students learn to take full advantage of the spreadsheet to create and analyze goal seek addressing various what-if questions about the results from a model. Since these skills are applicable to any worksheet with formulas constructed for a particular set of calculations, students can carry their ability to perform sensitivity analysis over to other business classes, such as finance and marketing research, and ultimately, into the workplace. Breakeven analysis (cost-volume analysis) is concerned with the interrelationship of costs, volume (quantity of output or sales), and profit. The Break-Even Point (BEP) The volume for
References: Bradfield, R., G. Wright, G. Burt, G. Cairns, and K.Van Der Heijden, (2005), "The Origins And Evolution Of Scenario Techniques In Long Range Business Planning," Futures, Vol. 37, pp. 795-812. Bradley, H. (2001), "What If?," PC Magazine, November 13, pp. 83-84. Also available online at http://www.pcmag.com/article2/ 0,4149,24074,00.asp , (last accessed on February 22, 2006). Brauers, J. and M. Weber (1988), "A New Method Of Scenario Analysis For Strategic Planning," Journal of Forecasting, Vol. 7, pp. 31-47. Bunn, D.W. and A.A. Salo (1993), "Forecasting With Goal seek," European Journal of Operational Research, Vol. 68, No. 3, pp. 291-303. Clemen, R.T. and T. Reilly (2001), Making Hard Decisions with DecisionTools ®, Thomson Learning, Duxbury, Pacific Grove, CA. Davis, N.P. (1968), Lawrence and Oppenheimer, Simon and Schuster, New York. Powell, S. G. and K. R. Baker (2004), The Art of Modeling with Spreadsheets: Management Science, Spreadsheet Engineering, and Modeling Craft, John Wiley & Sons, Inc. Ragsdale, C. T. (2001), "Teaching Management Science With Spreadsheets: From Decision Models To Decision Support," INFORMS Transactions on Education, Vol. 1, No. 2, http://ite.pubs.informs.org/Vol1No2/Ragsdale/ , (last accessed on February 22, 2006). Weida, N. C., R. Richardson, and A. Vazsonyi (2001), Operations Analysis Using Microsoft Excel, Thomson Learning, Duxbury, Pacific Grove, CA.