Transcription
Excel Models forBusiness and OperationsManagementSecond EditionJohn F. Barlow
Excel Models for Business and OperationsManagementSecond Edition
About the authorDr Barlow holds degrees in mathematics, computer science, and mechanical engineering. Aswell as extensive teaching experience – previously at the Universities of Cape Town, SouthAfrica and Wollongong, Australia – he has held various positions in computer consultancy andthe petroleum industry. He has published numerous papers in the areas of computer applicationsand systems management.
Excel Models forBusiness and OperationsManagementSecond EditionJohn F. Barlow
C 2005Copyright John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester,West Sussex PO19 8SQ, EnglandTelephone ( 44) 1243 779777Email (for orders and customer service enquiries): cs-books@wiley.co.ukVisit our Home Page on www.wileyeurope.com or www.wiley.comAll Rights Reserved. No part of this publication may be reproduced, stored in a retrieval systemor transmitted in any form or by any means, electronic, mechanical, photocopying, recording,scanning or otherwise, except under the terms of the Copyright, Designs and Patents Act 1988or under the terms of a licence issued by the Copyright Licensing Agency Ltd, 90 TottenhamCourt Road, London W1T 4LP, UK, without the permission in writing of the Publisher.Requests to the Publisher should be addressed to the Permissions Department, John Wiley &Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex PO19 8SQ, England, or emailedto permreq@wiley.co.uk, or faxed to ( 44) 1243 770620.This publication is designed to provide accurate and authoritative information in regard tothe subject matter covered. It is sold on the understanding that the Publisher is not engagedin rendering professional services. If professional advice or other expert assistance isrequired, the services of a competent professional should be sought.Other Wiley Editorial OfficesJohn Wiley & Sons Inc., 111 River Street, Hoboken, NJ 07030, USAJossey-Bass, 989 Market Street, San Francisco, CA 94103-1741, USAWiley-VCH Verlag GmbH, Boschstr. 12, D-69469 Weinheim, GermanyJohn Wiley & Sons Australia Ltd, 33 Park Road, Milton, Queensland 4064, AustraliaJohn Wiley & Sons (Asia) Pte Ltd, 2 Clementi Loop #02-01, Jin Xing Distripark, Singapore 129809John Wiley & Sons Canada Ltd, 22 Worcester Road, Etobicoke, Ontario, Canada M9W 1L1Wiley also publishes its books in a variety of electronic formats. Some content that appears in printmay not be available in electronic books.Library of Congress Cataloging-in-Publication DataBarlow, John F.Excel models for business and operations management / John F. Barlow.– 2nd ed.p. cm.ISBN-13 978-0-470-01509-4ISBN-10 0-470-01509-81. Microsoft Excel (Computer file) 2. Business–Mathematical models–Computer programs. 3. Business–Decision making–Mathematical models–Computer programs. I. Title.HF5548.4.M523B37658 . 05554—dc2220052005003239British Library Cataloguing in Publication DataA catalogue record for this book is available from the British LibraryISBN-13 978-0-470-01509-4ISBN-10 0-470-01509-8 (PB)Typeset in 10/12pt Times by TechBooks, New Delhi, IndiaPrinted and bound in Great Britain by Biddles Ltd, King’s Lynn,This book is printed on acid-free paper responsibly manufactured from sustainable forestryin which at least two trees are planted for each one used for paper production.
To Maryand ‘the Gang of 4’
ContentsPreface1xiiiA systems view of business1OverviewA systems view of business operationsA manufacturing business modelFinance and cost accountingThe marketing functionThe production functionManagement decision-makingEnterprise resource planning (ERP)References and further reading123355121516217Model-building toolsOverviewModelling characteristicsRisk and uncertainty in decision-makingLinear programming (LP)Using Excel’s ‘Analysis ToolPak’Statistical methodsDecision analysisSimulationExcel functions used in model-buildingExercisesReferences and further reading1718202126273442464951
viiiContentsPART 13BUSINESS MODELSFinancial models5355OverviewFinancial statementsRatio analysisNet present value (NPV)Investment appraisalPortfolio managementCapital budgeting using decision treesCash flow analysisInvestment financing: a simulation modelFinancial planningCommercial add-in products for ExcelExcel functions used in model-buildingExercisesReferences and further reading5556565961646869747882828688489Investment analysis modelsOverviewRisk preference attitudesUtility theoryPortfolio theory: the Markowitz modelPortfolio analysis: the efficient frontierSingle index model (SIM)The capital asset pricing model (CAPM)Bond valuationDuration and bond volatilityThe Black–Scholes option pricing modelExcel functions used in model-buildingExercisesReferences and further t applications in cost accounting127OverviewCost-volume-profit analysisDepreciationEquipment replacementStatistical replacement analysisSimulation model for replacement/repairsComparison between simulation and statistical resultsBudgeting127128130132136140144144
ContentsJob costingThe learning curveChecking the accuracy of learning curvesExcel functions used in model-buildingExercisesReferences and further reading1501551581601631666167Marketing modelsOverviewOrganising and presenting dataCorrelation analysis and linear regressionForecasting – time series and exponential smoothingForecasting – exponential smoothingSalesforce modelsGoal programmingExcel functions used in model-buildingExercisesReferences and further reading1671671701741781861902002012057206Purchase order processing: a database applicationOverviewCreating a simple macroPurchase order processingCreating the title screenProducts and suppliers worksheetsCreating the purchase order formCreating the database and its associated macrosMacros for transferring data into the databaseAdding macros to buttonsAmending purchase ordersPrinting purchase ordersProtecting the POP database applicationExcel functions used in model-buildingExercisesReferences and further 39PART 22418MODELS FOR OPERATIONS MANAGEMENTStatistical applications in quality controlOverviewProbability distributionsAcceptance sampling243243244249ix
xContentsEstimation – drawing conclusions from samplesHypothesis testing – checking out a claim!Analysis of variance (ANOVA)Statistical process controlExcel functions used in model-buildingExercisesReferences and further reading2532572582632722752789279Inventory control modelsOverviewGlossary of inventory termsCharacteristics of inventory modelsDeterministic modelsProduction order quantity modelInventory models with constraintsProbabilistic modelsInventory control: a simulation approachMaterial requirements planningLot-sizing methodsJust-in-time (JIT) approach to inventory managementExcel functions used in model-buildingExercisesReferences and further 0 Models for production operations324OverviewLogistics modelsOther network flow applicationsProduction planning and schedulingQueuing modelsExcel functions used in model-buildingExercisesReferences and further reading32432533133935336236436911 Project management370OverviewProject management techniquesThe project networkSimulation model for project managementExercisesReferences and further reading370371371392396399
ContentsAppendixExcel refresher notes400Basic Excel commandsDrawing charts with ChartWizardObject linking and embedding (OLE)400405407Index409xi
PrefaceThe popularity of spreadsheets in both academia and the business world has increased considerably over the past six years. Microsoft’s Office, which includes Excel as a core element, isnow one of the most widely used software products around. It is therefore no surprise to see thatOffice has undergone several revisions since the first edition of this textbook was published.The objective of the new edition, however, remains the same: to help readers develop their ownworksheet (Excel’s term for a spreadsheet) models.The book takes a structured view of management decision-making by integrating the activities of a manufacturing organisation. Everyday examples from finance, marketing, andoperations management form the basis of the book’s ‘hands-on’ development models. The textis entirely assignment-based and uses Microsoft’s Excel software to develop over eighty models. As in the previous edition, the emphasis is on the practical implementation of real-worldmodels rather than traditional theoretical concepts. The book’s active learning approach encourages the reader to focus on developing skills in ‘how’ to build a model while summarisingthe mathematical logic as to ‘why’ the model is so constructed.The book’s primary objective is to help the reader ‘put theory into practice’. In order to createan effective spreadsheet, a student must understand what variables should be included and whatrelationship exists between the variables, i.e., the model’s formulation. Allowing students tothink through the question of ‘why’ a model is built in a particular way helps to increaseanalytical skills. If students are encouraged to use their own initiative through groundworkassignments and class discussions, they are better placed to transfer decision-making andproblem-solving skills into the workplace.The book’s format is subject-focused following standard business/operations managementtexts. The mathematical concepts of management science/operations research provide thetools for model building. There are two introductory chapters, the first showing how thebook’s chapter topics are interrelated while the second chapter explains the main modelbuilding techniques that are used throughout the book. The rest of the book’s nine chapters aredivided into two main parts, each containing models for business and operations managementrespectively. End-of-chapter assignments (with answers) allow the student to develop theiranalytical skills by (i) modifying existing models, and (ii) building new applications.The text is ideally suited to business studies, finance, accounting, and operations managementcourses which offer practical computing skills as an integral part of the course syllabus. Models
xivPrefacethat are developed cover the areas of finance, accounting, marketing and forecasting, statisticalquality control, logistics and distribution, production planning, job scheduling and sequencing,inventory control including material requirements planning (MRP), and project management.In addition, a chapter is devoted entirely to the development of an internal database applicationusing Excel’s Visual Basic for Applications (VBA) language. This chapter includes a completelisting of the eleven macros used to build the purchase order processing (POP) system.Formulae templates for all models are provided throughout the text. While it is assumed thatthe reader is already familiar with Excel’s basic concepts, brief refresher notes are included asan appendix. Where Excel’s functions are introduced for the first time, a full description foreach function is given at the end of the relevant chapter. For readers who want to pursue themathematical techniques behind specific models in greater depth, a bibliography is providedat the end of each chapter.There is an accompanying website at http://www.wiley.com/go/barlow2e containing allworksheet models developed throughout the text. The website contains ten workbooks, eachworkbook file corresponding to a chapter in the book. The workbook files are named asChapter2, Chapter3, . . . Chapter11. (Note: Chapter 1 does not contain any spreadsheets.) Withineach workbook, worksheets represent the figures that appear in that chapter, e.g., Chapter3contains worksheets Fig. 3.1, Fig. 3.2, Fig 3.3, etc. Solutions to all end-of-chapter exercisesare available to adopters of the text on the website.NEW FOR THE SECOND EDITIONr A new chapter entitled Investment Analysis Models has been added. As well as expandingthe subject of portfolio theory introduced in Chapter 3, asset pricing models including thecapital asset pricing model (CAPM) and single index model (SIM) are covered. Other topicsdiscussed are bond valuation, duration, and the Black–Scholes option pricing model. Thisnew chapter should be of particular interest to both students and practitioners in the area offinance.r The ‘Time Series and Exponential Smoothing’ section in Chapter 6 now provides a modelfor the Holt-Winters’ triple-parameter method for multiplicative seasonal effects.r The ‘Production Planning and Scheduling’ section in Chapter 10 has been expanded to coverpriority job sequencing. A VBA routine is included for Johnson’s Rule for sequencing aseries of jobs through two facilities.r All models and Appendix notes have been upgraded to reflect the latest versions of applications software and operating systems, namely Microsoft’s Office 2003 and Windows XP.r New topics such as enterprise resource planning have been introduced where appropriate,and end-of-chapter bibliographies have been updated.ACKNOWLEDGEMENTSI am grateful to the reviewers for their many helpful comments and suggestions for improvingthe book. These views have been incorporated wherever practicable. It is also encouraging to
Prefacesee that other authors find merit in adopting an active learning approach to problem solving,and their important contributions have been recognised in the end-of-chapter bibliographies.I would like to thank editors Sarah Booth, Rachel Goodyear and David Barnard for all theirsupport throughout the book’s production. Finally, with a text involving so many formulae, theresponsibility for any errors or omissions that remain undetected is mine alone. Feedback oncorrections, questions, or ideas is always welcome and will be duly acknowledged.xv
1A systems view of businessOVERVIEWThis textbook develops models for various aspects o
10 Models for production operations 324. Overview 324 Logistics models 325 Other network flow applications 331 Production planning and scheduling 339 Queuing models 353
