Applied Statistics for Business and Management using Microsoft Excel

426 Pages·2013·17.566 MB·English

by  Linda Herkenhoff, John Fogli (auth.)

Linda Herkenhoff
John Fogli
Applied Statistics
for Business and
Management using
Microsoft Excel
Applied Statistics for Business and
Management using Microsoft Excel

Linda Herkenhoff (cid:129) John Fogli
Applied Statistics
for Business and
Management using
Microsoft Excel
LindaHerkenhoff JohnFogli
SaintMary’sCollege SenteniumInc.
Orinda,California PleasantHill,California
USA USA
ISBN978-1-4614-8422-6 ISBN978-1-4614-8423-3(eBook)
DOI10.1007/978-1-4614-8423-3
SpringerNewYorkHeidelbergDordrechtLondon
LibraryofCongressControlNumber:2013946938
©SpringerScience+BusinessMediaNewYork2013
Thisworkissubjecttocopyright.AllrightsarereservedbythePublisher,whetherthewholeorpart
of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations,
recitation,broadcasting,reproductiononmicrofilmsorinanyotherphysicalway,andtransmissionor
informationstorageandretrieval,electronicadaptation,computersoftware,orbysimilarordissimilar
methodologynowknownorhereafterdeveloped.Exemptedfromthislegalreservationarebriefexcerpts
inconnectionwithreviewsorscholarlyanalysisormaterialsuppliedspecificallyforthepurposeofbeing
enteredandexecutedonacomputersystem,forexclusiveusebythepurchaserofthework.Duplication
ofthispublicationorpartsthereofispermittedonlyundertheprovisionsoftheCopyrightLawofthe
Publisher’s location, in its current version, and permission for use must always be obtained from
Springer.PermissionsforusemaybeobtainedthroughRightsLinkattheCopyrightClearanceCenter.
ViolationsareliabletoprosecutionundertherespectiveCopyrightLaw.
The use of general descriptive names, registered names, trademarks, service marks, etc. in this
publicationdoesnotimply,evenintheabsenceofaspecificstatement,thatsuchnamesareexempt
fromtherelevantprotectivelawsandregulationsandthereforefreeforgeneraluse.
While the advice and information in this book are believed to be true and accurate at the date of
publication,neithertheauthorsnortheeditorsnorthepublishercanacceptanylegalresponsibilityfor
anyerrorsoromissionsthatmaybemade.Thepublishermakesnowarranty,expressorimplied,with
respecttothematerialcontainedherein.
Printedonacid-freepaper
SpringerispartofSpringerScience+BusinessMedia(www.springer.com)
Preface
Mathematical formulas and Greek letters seem to intimidate many people, so
this book focuses on providing clear word descriptions and step-by-step Excel
instructions,ratherthanincludinglotsofx’sandy’severywhere.Wehavespecifi-
cally avoided including a lot of technical information. This book was written for
thosewho wantbasicunderstandingofhow touse statistics inthe business world
without all the details of statistical theory. It is designed to enhance a practical
learning of statistics by nonstatisticians and to make the experience a little easier
andmaybeevenfun.
Thisbookhasalsobeenwrittenwithmanagersinmindwhomayneedaquick
refresheronhowtocompleteExcelanalysiswithouthavingtowadethroughpages
oftechnicalexplanations.Thosemoredetailedtechnicalexplanationscanbefound
intraditionalstatisticstextbooks.Thekeyconceptsinthisbookaremorecomplex
than the presentations in this book might suggest. We have aimed at concisely
definingtheconceptsandprovidingsimpledescriptionsandexplanationsonhowto
usethem.
For instructors, this publication is designed as a companion guide to your core
text. Students can practice their skills that have been introduced to them in the
classroom. However, at the beginning of each chapter, the key concepts are
summarizedtoremindthemofwhatisimportantwiththetopic.
We believe thatproblems andcase studies should involve actual data from the
businesscommunity.Therefore,thereisacontinuinganalysisthroughoutseveralof
thechaptersusingarealbusinesscasefromInfinityAutoInsurance.Thedatasets
fortheotherpracticeproblemsineachchapterhavebeenkeptintentionallysmallso
that the user can more easily maneuver within the data. The rigorous sample size
rules have been put aside to accommodate this design goal. The same Excel
proceduresapplytoallsizesofdatasets,smallorlarge.
This book was written using Excel 2010; the screenshots may appear slightly
differentifyouareusinganolderversionofExcelorifyouareusingaMac.Ifyou
arecomfortableinExcel,thesedifferencesshouldbeminor.
WehaveincludedonlythoseanalyticExceltoolsthataremostcommonlyused
intheworkplacetokeepthingssimple.Thisapproachshouldworkwellforthose
v
vi Preface
havingtoruntheirownanalysis,aswellasforthoseneedingtobetterunderstand
statisticalreports,soastomakeinformedbusinessdecisions.
No prior Excel experience is necessary, although it may prove helpful.
Instructionsareprovided withthemindsetthatthisisthefirsttimeyouhaveused
this particular Excel functionality. In other words, each chapter provides simple
instructionsforfirst-timeusers.
SomeofthetraditionalstatisticaltextsincludeExcelinstructions.Buttheseare
typicallyembeddeddeepinthechapterasoneofseveraldifferentsoftwareinstruc-
tion packets. This book focuses only on Excel and provides easy-to-find Excel
instructionsineachchapter.
Inadditiontoofferingthisbookintraditionaltextbookform,itisalsoavailable
asane-bookinrecognitionofbeingenvironmentallyresponsible.
TheauthorsbringauniqueperspectiveasguidesthroughtheWorldofStatistics.
Theircombinedskillsandexpertiseintheworkplaceandtheclassroomequipthem
tounderstandthechallengesstudentsandmanagersfaceastheynavigatetheWorld
ofStatistics.Theiractiveconsultingandteachingliveskeepthemuptodateonthe
commonreal-worldissuesinunderstandingandapplyingstatisticalconcepts.
Wehaveworkeddiligentlytomakesurethatthisbookisaccurateanderrorfree.
Butifyouhappentofindanyerrorsorhavesuggestionsforimprovements,please
[email protected]@stmarys-ca.edu.
Many of you have survived statistics courses through pure memorization, but
hopefullythisbookwillencouragebothlogicalreasoningandintuitiveunderstand-
ing.Webelievethatlogicalreasoningisfundamentaltoretaininganalyticalskills
thatcanbepracticedintherealworld.Enjoy....
Orinda,CA,USA LindaHerkenhoff
PleasantHill,CA,USA JohnFogli
Acknowledgments
The foundation of this book was laid with numerous statistical discussions on the
tenniscourt.ThankstoDr.LindaHerkenhoffforconvincingmetoworkwithher
onthisideathatfinallyresultedintothefirsteditionofourbook.Thankyoutoour
reviewers as well as several Diablo Valley College students in Pleasant Hill,
California. A big thank you to Eva Meng from Sentenium, Inc., who spent hours
helping with screenshots and editing this text. Finally, thanks to my parents and
especiallytomywifewhostoodbesidemetotakethisbookforward.
PleasantHill,CA,USA JohnFogli
We would like to thank several of our colleagues who tirelessly reviewed each
and every word and number in our book: Dr. Michael Wagner, Dr. Diana Borova,
Margery Connor, and Eva Meng. Your fastidiousreviews let ussleep at night.But
additionallywehadevenmorecolleagueswhogenerouslyreviewedvarioussections
ofthebook,andtothemalsoweoweourthanks:Dr.PeterHosie,Dr.MadelineWu,
Dr.JoAnnHeydenfeldt,Dr.BrianSholey,andDr.RodolfoMartell.
WeexpressappreciationtoallofourpastStatisticsstudentswhoallowedusto
betterunderstandthechallengesinbringingStatisticsaliveasapowerfulbusiness
tool.Thisbookreflectsourmanyyearsofstudentinputandobservationbothinthe
UnitedStatesandabroad.
SpecialrecognitionispaidtoInfinityLifeInsuranceCompanyforallowingusto
usetheirdataforourreal-worldcasestudy.
vii
viii Acknowledgments
Thanks to my husband Frederic and three sons Brett, Kyle and Eric for their
patienceandsupportthroughoutthewritingprocess.
Orinda,CA,USA LindaHerkenhoff
We have done our best to make this book accessible online for comments and
suggestions.Inaddition,wehavemadeitconvenientforstudentsandprofessionals
to download our data files available at http://www.sentenium.com/springer.html.
Thesedatafilesareusedinvariouspracticeproblemsattheendofcertainchapters.
Ourintentionistogiveyoupracticewithdataanalysisandinterpretationofresults
usingMicrosoftExcel.
Wewelcomecommentsaboutthebookandsuggestionsforimprovement.
Email:[email protected]
Contents
1 DataandStatistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
KeyConcepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Discussion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
CommonPitfalls.. . . .. . . .. . . .. . . .. . . .. . . .. . .. . . .. . . .. . . .. 5
FinalThoughtsandActivities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
PracticeProblems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
DiscussionBoards.. . . .. . . .. . . .. . . .. . . .. . . .. . . .. . . .. . . .. 6
GroupActivity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
PartingThought. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
ProblemSolutions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2 IntroductiontoExcelandBasicCharts. . . . . . . . . . . . . . . . . . . . . . 11
KeyConcepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Discussion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
BasicConcepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
BarandColumnCharts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
PieCharts. . . . . . .. . . . . . . .. . . . . . .. . . . . . .. . . . . . .. . . . . . .. 27
LineChartsandAreaCharts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
OtherCharts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
PivotTables(AkaCrosstabs). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
CommonPitfalls. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
FinalThoughtsandActivities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
PracticeProblems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
DiscussionBoards.. . . .. . . .. . . .. . . .. . . .. . . .. . . .. . . .. . . .. 43
GroupActivity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
PartingThought. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
ProblemSolutions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
ix