Excel Models for Business and Operations Management

Dr Barlow holds degrees in mathematics, computer science, and mechanical engineering. As well as extensive teaching experience – previously at the Universities of Cape Town, South Africa and Wollongong, Australia – he has held various positions in computer consultancy and the petroleum industry. He has published numerous papers in the areas of computer applications and systems management.

• Preface xiii1 A systems view of business 1Overview 1A systems view of business operations 2A manufacturing business model 3Finance and cost accounting 3The marketing function 5The production function 5Management decision-making 12Enterprise resource planning (ERP) 15References and further reading 162 Model-building tools 17Overview 17Modelling characteristics 18Risk and uncertainty in decision-making 20Linear programming (LP) 21Using Excel’s ‘Analysis ToolPak’ 26Statistical methods 27Decision analysis 34Simulation 42Excel functions used in model-building 46Exercises 49References and further reading 51Part 1 Business Models 533 Financial models 55Overview 55Financial statements 56Ratio analysis 56Net present value (NPV) 59Investment appraisal 61Portfolio management 64Capital budgeting using decision trees 68Cash flow analysis 69Investment financing: a simulation model 74Financial planning 78Commercial add-in products for Excel 82Excel functions used in model-building 82Exercises 86References and further reading 884 Investment analysis models 89Overview 89Risk preference attitudes 90Utility theory 91Portfolio theory: the Markowitz model 94Portfolio analysis: the efficient frontier 97Single index model (SIM) 101The capital asset pricing model (CAPM) 106Bond valuation 108Duration and bond volatility 113The Black–Scholes option pricing model 117Excel functions used in model-building 120Exercises 124References and further reading 1265 Worksheet applications in cost accounting 127Overview 127Cost-volume-profit analysis 128Depreciation 130Equipment replacement 132Statistical replacement analysis 136Simulation model for replacement/repairs 140Comparison between simulation and statistical results 144Budgeting 144Job costing 150The learning curve 155Checking the accuracy of learning curves 158Excel functions used in model-building 160Exercises 163References and further reading 1666 Marketing models 167Overview 167Organising and presenting data 167Correlation analysis and linear regression 170Forecasting – time series and exponential smoothing 174Forecasting – exponential smoothing 178Salesforce models 186Goal programming 190Excel functions used in model-building 200Exercises 201References and further reading 2057 Purchase order processing: a database application 206Overview 206Creating a simple macro 207Purchase order processing 209Creating the title screen 210Products and suppliers worksheets 214Creating the purchase order form 215Creating the database and its associated macros 219Macros for transferring data into the database 221Adding macros to buttons 224Amending purchase orders 225Printing purchase orders 230Protecting the POP database application 232Excel functions used in model-building 236Exercises 237References and further reading 239Part 2 Models for Operations Management 2418 Statistical applications in quality control 243Overview 243Probability distributions 244Acceptance sampling 249Estimation – drawing conclusions from samples 253Hypothesis testing – checking out a claim! 257Analysis of variance (ANOVA) 258Statistical process control 263Excel functions used in model-building 272Exercises 275References and further reading 2789 Inventory control models 279Overview 279Glossary of inventory terms 280Characteristics of inventory models 281Deterministic models 282Production order quantity model 284Inventory models with constraints 291Probabilistic models 293Inventory control: a simulation approach 304Material requirements planning 307Lot-sizing methods 315Just-in-time (JIT) approach to inventory management 318Excel functions used in model-building 318Exercises 319References and further reading 32310 Models for production operations 324Overview 324Logistics models 325Other network flow applications 331Production planning and scheduling 339Queuing models 353Excel functions used in model-building 362Exercises 364References and further reading 36911 Project management 370Overview 370Project management techniques 371The project network 371Simulation model for project management 392Exercises 396References and further reading 399Appendix Excel refresher notes 400Basic Excel commands 400Drawing charts with ChartWizard 405Object linking and embedding (OLE) 407Index 409