Example: air traffic controller

Excel Models for Business and Operations …

JWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0 Excel Models forBusiness and OperationsManagementSecond EditionJohn F. BarlowiiiJWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0 CopyrightC 2005 John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester,West Sussex PO19 8SQ, EnglandTelephone (+44) 1243 779777 Email (for orders and customer service enquiries): our Home Page on or 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 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 or faxed to (+44) 1243 publication is designed to provide accurate and authoritative information in regard tothe subject matter covered.

JWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0 Excel Models for Business and Operations Management Second Edition John F. Barlow iii

Tags:

  Business, Operations, Model, Management, Models for business and operations, Models for business and operations management

Information

Domain:

Source:

Link to this page:

Please notify us if you found a problem with this document:

Other abuse

Transcription of Excel Models for Business and Operations …

1 JWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0 Excel Models forBusiness and OperationsManagementSecond EditionJohn F. BarlowiiiJWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0 CopyrightC 2005 John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester,West Sussex PO19 8SQ, EnglandTelephone (+44) 1243 779777 Email (for orders and customer service enquiries): our Home Page on or 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 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 or faxed to (+44) 1243 publication is designed to provide accurate and authoritative information in regard tothe subject matter covered.

2 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 Wiley Editorial OfficesJohn Wiley & Sons Inc., 111 River Street, Hoboken, NJ 07030, USAJ ossey-Bass, 989 Market Street, San Francisco, CA 94103-1741, USAW iley-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 129809 John Wiley & Sons Canada Ltd, 22 Worcester Road, Etobicoke, Ontario, Canada M9W 1L1 Wiley also publishes its books in a variety of electronic formats. Some content that appears in printmay not be available in electronic of Congress Cataloging-in-Publication DataBarlow, John Models for Business and Operations management / John F.

3 Barlow. 2nd 978-0-470-01509-4 ISBN-10 0-470-01509-81. Microsoft Excel (Computer file) 2. Business Mathematical Models Computer programs. 3. Business Decision making Mathematical Models Computer programs. I. 2005658 . 05554 dc222005003239 British Library Cataloguing in Publication DataA catalogue record for this book is available from the British LibraryISBN-13 978-0-470-01509-4 ISBN-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 JWBK022-Barlow March 18, 2005 7:52 Char Count= 0 ContentsPrefacexiii1 A systems view of business1 Overview1A systems view of Business operations2A manufacturing Business model3 Finance and cost accounting3 The marketing function5 The production function5 management decision-making12 Enterprise resource planning (ERP)15 References and further reading162 model -building tools17 Overview17 Modelling characteristics18 Risk and uncertainty in decision-making20 Linear programming (LP)21 Using Excel s Analysis ToolPak 26 Statistical methods27 Decision analysis34 Simulation42 Excel functions used in model -building46 Exercises49 References and further reading51viiJWBK022-FM JWBK022-Barlow March 18, 2005 7.

4 52 Char Count= 0viiiContentsPART 1 Business MODELS533 Financial models55 Overview55 Financial statements56 Ratio analysis56 Net present value (NPV)59 Investment appraisal61 Portfolio management64 Capital budgeting using decision trees68 Cash flow analysis69 Investment financing: a simulation model74 Financial planning78 Commercial add-in products for Excel82 Excel functions used in model -building82 Exercises86 References and further reading884 Investment analysis models89 Overview89 Risk preference attitudes90 Utility theory91 Portfolio theory: the Markowitz model94 Portfolio analysis: the efficient frontier97 Single index model (SIM)101 The capital asset pricing model (CAPM)106 Bond valuation108 Duration and bond volatility113 The Black Scholes option pricing model117 Excel functions used in model -building120 Exercises124 References and further reading1265 Worksheet applications in cost accounting127 Overview127 Cost-volume-profit analysis128 Depreciation130 Equipment replacement132 Statistical replacement analysis136 Simulation model for replacement/repairs140 Comparison between simulation and statistical results144 Budgeting144 JWBK022-FM JWBK022-Barlow March 18, 2005 7.

5 52 Char Count= 0 ContentsixJob costing150 The learning curve155 Checking the accuracy of learning curves158 Excel functions used in model -building160 Exercises163 References and further reading1666 Marketing models167 Overview167 Organising and presenting data167 Correlation analysis and linear regression170 Forecasting time series and exponential smoothing174 Forecasting exponential smoothing178 Salesforce models186 Goal programming190 Excel functions used in model -building200 Exercises201 References and further reading2057 Purchase order processing: a database application206 Overview206 Creating a simple macro207 Purchase order processing209 Creating the title screen210 Products and suppliers worksheets214 Creating the purchase order form215 Creating the database and its associated macros219 Macros for transferring data into the database221 Adding macros to buttons224 Amending purchase orders225 Printing purchase orders230 Protecting the POP database application232 Excel functions used in model -building236 Exercises237 References and further reading239 PART 2 Models FOR Operations MANAGEMENT2418 Statistical applications in quality control243 Overview243 Probability distributions244 Acceptance sampling249 JWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0xContentsEstimation drawing conclusions from samples253 Hypothesis testing checking out a claim!

6 257 Analysis of variance (ANOVA)258 Statistical process control263 Excel functions used in model -building272 Exercises275 References and further reading2789 Inventory control models279 Overview279 Glossary of inventory terms280 Characteristics of inventory models281 Deterministic models282 Production order quantity model284 Inventory Models with constraints291 Probabilistic models293 Inventory control: a simulation approach304 Material requirements planning307 Lot-sizing methods315 Just-in-time (JIT) approach to inventory management318 Excel functions used in model -building318 Exercises319 References and further reading32310 Models for production operations324 Overview324 Logistics models325 Other network flow applications331 Production planning and scheduling339 Queuing models353 Excel functions used in model -building362 Exercises364 References and further reading36911 Project management370 Overview370 Project management techniques371 The project network371 Simulation model for project management392 Exercises396 References and further reading399 JWBK022-FM JWBK022-Barlow March 18, 2005 7.

7 52 Char Count= 0 ContentsxiAppendix Excel refresher notes400 Basic Excel commands400 Drawing charts with ChartWizard405 Object linking and embedding (OLE)407 Index409 JWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0 PrefaceThe popularity of spreadsheets in both academia and the Business world has increased consid-erably 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 objective of the new edition, however, remains the same: to help readers develop their ownworksheet ( Excel s term for a spreadsheet) book takes a structured view of management decision-making by integrating the ac-tivities of a manufacturing organisation.

8 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 mod-els. 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 en-courages the reader to focus on developing skills in how to build a model while summarisingthe mathematical logic as to why the model is so 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, , the model s formulation. Allowing students tothink through the question of why a model is built in a particular way helps to increaseanalytical skills.

9 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 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 model -building 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 ,finance,accounting,andoperationsmanagem entcourses which offer practical computing skills as an integral part of the course syllabus.

10 ModelsxiiiJWBK022-FM JWBK022-Barlow March 18, 2005 7:52 Char Count= 0xivPrefacethat 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 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) 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.


Related search queries