Excel Data Analysis Hector Guerrero Excel Data Analysis Modeling and Simulation 123 Dr.HectorGuerrero MasonSchoolofBusiness CollegeofWilliam&Mary Williamsburg,VA23189 USA [email protected] ISBN978-3-642-10834-1 e-ISBN978-3-642-10835-8 DOI10.1007/978-3-642-10835-8 SpringerHeidelbergDordrechtLondonNewYork LibraryofCongressControlNumber:2010920153 ©Springer-VerlagBerlinHeidelberg2010 Thisworkissubjecttocopyright.Allrightsarereserved,whetherthewholeorpartofthematerialis concerned,specificallytherightsoftranslation,reprinting,reuseofillustrations,recitation,broadcasting, reproductiononmicrofilmorinanyotherway,andstorageindatabanks.Duplicationofthispublication orpartsthereofispermittedonlyundertheprovisionsoftheGermanCopyrightLawofSeptember9, 1965,initscurrentversion,andpermissionforusemustalwaysbeobtainedfromSpringer.Violations areliabletoprosecutionundertheGermanCopyrightLaw. Theuseofgeneraldescriptivenames,registerednames,trademarks,etc.inthispublicationdoesnot imply,evenintheabsenceofaspecificstatement,thatsuchnamesareexemptfromtherelevantprotective lawsandregulationsandthereforefreeforgeneraluse. Coverdesign:WMXDesignGmbH,Heidelberg Printedonacid-freepaper SpringerispartofSpringerScience+BusinessMedia(www.springer.com) Tomywonderfulparents...PacoandNena Preface WhydoestheWorld Need—ExcelDataAnalysis,Modeling, and Simulation? When spreadsheets first became widely available in the early 1980s, it spawned a revolution in teaching. What previously could only be done with arcane software and large scale computing was now available to the common-man, on a desktop. Also, before spreadsheets, most substantial analytical work was done outside the classroom where the tools were; spreadsheets and personal computers moved the work into the classroom. Not only did it change how the analysis curriculum was taught, but italsoempowered students toventure out on their own toexplore new ways to use the tools. I can’t tell you how many phone calls, office visits, and/or emailsIhavereceivedinmyteachingcareerfromecstaticstudentscrowingabout whattheyhavejustdonewithaspreadsheetmodel. Ihavebeenteachingcoursesrelatedtospreadsheetbasedanalysisandmodeling forabout25yearsandIhavewatchedandparticipatedinthespreadsheetrevolution. Duringthattime,Ihavebeenawitnesstothefollowingobservations: • Each year has led to more and more demand for Excel based analysis and modelingskills,bothfromstudents,practitioners,andrecruiters • Excel has evolved as an ever more powerful suite of tools, functions, and capabilities,includingtherecentiterationandbasisforthisbook—Excel2007 • TheingenuityofExceluserstocreateapplicationsandtoolstodealwithcomplex problemscontinuestoamazeme • Thosestudentsthatprecededthespreadsheetrevolutionoftenfindthemselvesat alossastowheretogoforanintroductiontowhatiscommonlytaughttomost manyundergraduatesinbusinessandsciences. Eachofonetheseobservationshavemotivatedmetowritethisbook.Thefirstsug- geststhatthereisnoforeseeableendtothedemandfortheskillsthatExcelenables; in fact, the need for continuing productivity in all economies guarantees that an individual with proficiency in spreadsheet analysis will be highly prized by an vii viii Preface organization.Ataminimum,theseskillspermityoufreedomfromspecialiststhat candelayorholdyoucaptivewhilewaitingforasolution.Thiswascommoninthe earlydaysofinformationtechnology(IT);yourequestedthattheITgroupprovide you with a solution or tool and you waited, and waited, and waited. Today if you needasolutionyoucandoityourself. Thecombinationofthe2ndand3rdobservationssuggeststhatwhenyoucouple bright and energetic people with powerful tools and a good learning environment, wonderful things can happen. I have seen this throughout my teaching career, as well as in my consulting practice. The trick is to provide a teaching vehicle that makestheanalysisaccessible.Myhopeisthatthisbookissuchateachingvehicle. Ibelievethattherearethreesimplefactorsthatfacilitatelearning—selectexamples thatcontaininterestingquestions,methodicallyleadstudentsthroughtherationale oftheanalysis,andthoroughlyexplaintheExceltoolstoachievetheanalysis. The last observation has fueled my desire to lend a hand to the many students thatpassedthroughtheeducationalsystembeforethespreadsheetanalysisrevolu- tion; to provide them with a book that points them in the right direction. Several yearsago,IencounteredaformerMBAstudentinaCincinnatiAirportbookstore. HeexplainedtomethathewaslookingforagoodExcel-basedbookonDataanal- ysisandmodeling—“Youknowit’sbeenmorethan20yearssinceIwasinaTuck School classroom, and I desperately need to understand what my interns seem to beabletodosoeasily.”Byprovidingabroadvarietyofexemplaryproblems,from graphical/statisticalanalysistomodeling/simulationtooptimization,andtheExcel toolstoaccomplishtheseanalyses,mostreadersshouldbeabletoachievesuccess intheirself-studyattemptstomasterspreadsheetanalysis.Besidesagoodcompass, students also need to be made aware of the possible. It is not usual to hear from students“CanyouuseExceltodothis?”or“Ididn’tknowyoucoulddothatwith Excel!” WhoBenefitsfromthisBook? This book is targeted at the student or practitioner that is looking for a single introductoryExcel-basedresourcethatcoversthreeessentialbusinessskills—Data Analysis,BusinessModeling,andSimulation.Ihavesuccessfullyusedthismaterial with undergraduates, MBAs, Executive MBAs and in Executive Education pro- grams. For my students, the book has been the main teaching resource for both semester and half-semester long courses. The examples used inthe books aresuf- ficiently flexible to guide teaching goals in many directions. For executives, the book has served as a compliment to classroom lectures, as well as an excellent post-program,self-studyresource.Finally,Ibelievethatitwillservepractitioners, likethatformerstudentImetinCincinnati,thathavethedesireandmotivationto refurbish their understanding of data analysis, modeling, and simulation concepts throughself-study. Preface ix KeyFeaturesofthisBook IhaveusedanumberofexamplesinthisbookthatIhavedevelopedovermanyyears ofteachingandconsulting.Somearebriefandtothepoint;othersaremorecomplex and require considerable effort to digest. I urge you to not become frustrated with themorecomplexexamples.Thereismuchtobelearnedfromtheseexamples,not only the analytical techniques, but also approaches to solving complex problems. Theseexamples,asisalwaysthecaseinreal-world,messyproblems,requiremak- ing reasonable assumptions and some concession to simplification if a solution is to be obtained. My hope is that the approach will be as valuable to the reader as theanalyticaltechniques.Ihavealsotakengreatpainstoprovideanabundanceof Excelscreenshotsthatshouldgivethereaderasolidunderstandingofthechapter examples. But, let me vigorously warn you of one thing—this is not an Excel how-to book. Excel how-to books concentrate on the Excel tools and not on analysis—it isassumedthatyouwillfillintheanalysisblanks.TherearemanyexcellentExcel how-tobooksonthemarketandanumberofexcellentwebsites(e.g.MrExcel.com) whereyoucanfindhelpwiththedetailsofspecificExcelissues.Ihaveattempted to write a book that is about analysis, analysis that can be easily and thoroughly handled withExcel. Keep thisinmindasyouproceed. Soinsummary, remember that the analysis is the primary focus and that Excel simply serves as an excellent vehiclebywhichtoachievetheanalysis. Acknowledgements I would like to thank the editorial staff of Springer for their invaluable support— Dr. Niels Peter Thomas, Ms. Alice Blanck, and Ms. Ulrike Stricker. Thanks to Ms. Elizabeth Bowman for her excellent editing effort over many years. Special thanks to the countless students I have taught over the years, in particular Bill Jelen, the world-wide-web’s Mr. Excel that made a believer out of me. Finally, thanks to my family and friends that took a back seat to the book over the years ofdevelopment—Tere,Rob,Brandy,Mac,Lili,PT,andScout.