Mathematical Modeling with Excel

371 Pages·2019·7.107 MB·\371

by  Brian Albright (Author), William P Fox (Author)

Mathematical Modeling
with Excel
Second Edition

Mathematical Modeling
with Excel
Second Edition
Brian Albright
William P. Fox
CRCPress
Taylor&FrancisGroup
6000BrokenSoundParkwayNW,Suite300
BocaRaton,FL33487-2742
(cid:2)c 2020byTaylor&FrancisGroup,LLC
CRCPressisanimprintofTaylor&FrancisGroup,anInformabusiness
NoclaimtooriginalU.S.Governmentworks
Printedonacid-freepaper
InternationalStandardBookNumber-13:978-1-138-59707-5(Hardback)
This book contains information obtained from authentic and highly regarded sources. Reasonable efforts
have been made to publish reliable data and information, but the author and publisher cannot assume
responsibilityforthevalidityofallmaterialsortheconsequencesoftheiruse.Theauthorsandpublishers
haveattemptedtotracethecopyrightholdersofallmaterialreproducedinthispublicationandapologize
tocopyrightholdersifpermissiontopublishinthisformhasnotbeenobtained.Ifanycopyrightmaterial
hasnotbeenacknowledgedpleasewriteandletusknowsowemayrectifyinanyfuturereprint.
ExceptaspermittedunderU.S.CopyrightLaw,nopartofthisbookmaybereprinted,reproduced,trans-
mitted, or utilized in any form by any electronic, mechanical, or other means, now known or hereafter
invented, including photocopying, microfilming, and recording, or in any information storage or retrieval
system,withoutwrittenpermissionfromthepublishers.
Forpermissiontophotocopyorusematerialelectronicallyfromthiswork,pleaseaccesswww.copyright.com
(http://www.copyright.com/)orcontacttheCopyrightClearanceCenter,Inc.(CCC),222RosewoodDrive,
Danvers, MA 01923, 978-750-8400. CCC is a not-for-profit organization that provides licenses and regis-
trationforavarietyofusers.FororganizationsthathavebeengrantedaphotocopylicensebytheCCC,a
separatesystemofpaymenthasbeenarranged.
Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and are
usedonlyforidentificationandexplanationwithoutintenttoinfringe.
Visit the Taylor & Francis Web site at
http://www.taylorandfrancis.com
and the CRC Press Web site at
http://www.crcpress.com
Contents
Preface vii
1 What is Mathematical Modeling? 1
1.1 Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 The Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.4 Assumptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2 Proportionality and Geometric Similarity 11
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
2.2 Using Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
2.3 Modeling with Proportionality . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.4 Fitting Straight Lines Analytically . . . . . . . . . . . . . . . . . . . . . . . 28
2.5 Geometric Similarity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
2.6 Linearizable Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
2.7 Coefficient of Determination . . . . . . . . . . . . . . . . . . . . . . . . . . 58
3 Linear Algebra 67
3.1 Linear Algebra Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
3.2 Modeling with Systems of Equations . . . . . . . . . . . . . . . . . . . . . . 81
3.3 Polynomials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
3.4 Multiple Regression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
3.5 Spline Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
4 Discrete Dynamical Systems 117
4.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
4.2 Long–Term Behavior and Equilibria . . . . . . . . . . . . . . . . . . . . . . 118
4.3 Discrete Logistic Equation . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
4.4 A Linear Predator–Prey Model . . . . . . . . . . . . . . . . . . . . . . . . . 132
4.5 A Nonlinear Predator–Prey Model . . . . . . . . . . . . . . . . . . . . . . . 137
4.6 Epidemics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
5 Differential Equations 149
5.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
5.2 Euler’s Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
5.3 Mixing Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
5.4 Systems of Differential Equations . . . . . . . . . . . . . . . . . . . . . . . 165
5.5 Quadratic Population Model . . . . . . . . . . . . . . . . . . . . . . . . . . 172
5.6 Volterra’s Principle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
5.7 Lanchester Combat Models . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
5.8 Runge-Kutta Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
v
vi Contents
6 Simulations 199
6.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
6.2 Basic Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
6.3 Three Famous Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
6.4 The Poker Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
6.5 Random Number Generators . . . . . . . . . . . . . . . . . . . . . . . . . . 219
6.6 Modeling Random Variables . . . . . . . . . . . . . . . . . . . . . . . . . . 224
6.7 A Theoretical Queuing Model . . . . . . . . . . . . . . . . . . . . . . . . . 234
6.8 A Scheduling Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
6.9 An Inventory Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
7 Linear Optimization 251
7.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
7.2 Linear Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
7.3 The Transportation Problem . . . . . . . . . . . . . . . . . . . . . . . . . . 259
7.4 The Assignment Problem and Binary Constraints . . . . . . . . . . . . . . 269
7.5 Solving Linear Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
7.6 The Simplex Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
7.7 Sensitivity Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
8 Nonlinear Optimization 297
8.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
8.2 Newton’s Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
8.3 The Golden Section Method . . . . . . . . . . . . . . . . . . . . . . . . . . 306
8.4 The One-Dimensional Gradient Method . . . . . . . . . . . . . . . . . . . . 311
8.5 Two-Dimensional Gradient Method . . . . . . . . . . . . . . . . . . . . . . 316
8.6 Lagrange Multipliers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
8.7 Branch and Bound . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
8.8 The Traveling Salesman Problem . . . . . . . . . . . . . . . . . . . . . . . . 338
A Spreadsheet Basics 347
A.1 Basic Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
A.2 Entering Text, Data, and Formulas . . . . . . . . . . . . . . . . . . . . . . 348
A.2.1 Understanding Cell References . . . . . . . . . . . . . . . . . . . . . 349
A.2.2 Formatting Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
A.3 Creating Charts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . 351
A.3.1 Adding Data to a Chart . . . . . . . . . . . . . . . . . . . . . . . . . 352
A.3.2 Graphing Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
A.4 Scroll Bars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
A.5 Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Index 357
Preface
The main goal of this text is to present different ways of building and analyzing mathe-
matical models in a format that can be read by students, not just instructors. This is not a
text on how to use Excel. Rather, Excel is seen as a tool to further the goal of building and
analyzing mathematical models. No prior knowledge or experience with Excel is required
to use this text.
Excelischosenastheonlysoftwareusedtoimplementandanalyzemodelsfortwomain
reasons:
1. It is easy to use and most everyone is familiar with it, so it takes very little time to
become comfortable with the software.
2. It is everywhere. Students will have access to Excel for every mathematical modeling
project they encounter inside and outside of academics.
Each section contains step-by-step instructions for building the models in Excel. These
instructions were originally written for usewith OfficeExcel 2016. Some of theinstructions
may be slightly different for other versions of Excel.
Pedagogical Approach
This text presents a wide variety of common types of models found in other mathematical
modeling texts, as well as some new types. However, the models are presented in a very
unique format. A typical section begins with a general description of the scenario being
modeled. The model is then built using the appropriate mathematical tools. Then it is
implemented and analyzed in Excel via step-by-step instructions. In the exercises, we ask
students to modify or refine the existing model, analyze it further, or adapt it to similar
scenarios.
In each section, we try to focus on the main mathematical modeling concept being
illustratedandnotgettooboggeddownindetails.Wealsofocusontheanalysisofmodels,
and in each case try to address the question, “What does this mean?”
Thisisnota“plug-and-chug”textbook.Wedonotaskstudentstosimplyplugnumbers
into some “black-box” Excel formula and accept the results. Rather, we discuss the mathe-
matics behind the analysis of the models and, where appropriate, build the analytical tools
in Excel from scratch.
Each section ends with several exercises of varying degree of difficulty. In addition, each
chapter ends with a “For Further Reading” section which contains resources for additional
information.
vii
viii Preface
Audience/Prerequisites
This text is appropriate for mathematics majors (including secondary mathematics educa-
tionmajors)whoneedanintroductorymathematicalmodelingcourse.Somesectionsrequire
calculus, linear algebra, differential equations, or basic statistics, so this text is appropri-
ate for use with junior or senior level students. However, many other sections require only
mathematical maturity, so this text could also be used with sophomore level students.
The Flow of Material
Thistextcontainsawidevarietyofmodelingtechniques,mathematicalconcepts,andtypes
of applications. Here we give a brief overview of the highlights of each chapter.
Chapter 1 – What is Mathematical Modeling? This chapter begins with the defini-
tionsofthetermsmodel andmathematical modeling.Itthendiscussesthestepsinvolvedin
mathematical modeling, and concludes with a discussion of the importance of assumptions
in the process of mathematical modeling.
Chapter 2 – Proportionality and Geometric Similarity This chapter begins with
an introduction to graphing and working with data in Excel which includes a discussion of
fitting straight lines to data. Then the geometric concepts of proportionality and similarity
are used to model systems such as free-falling objects. We stress the point that data are
used to test the validity of the models. The chapter ends with an introduction to fitting
straight lines to data, empirical modeling, and the coefficient of determination.
Chapter 3 – Linear Algebra This chapter begins with a brief introduction to topics in
linear algebra used throughout this book including matrices, vectors, and systems of linear
equations. We give a few examples of using linear equations to create models. We then use
linear equations to fit polynomials, multiple regression, and spline models to data.
Chapter 4 – Discrete Dynamical Systems This chapter begins with the definitions of
a discrete dynamical system, a solution, and an equilibrium value. We stress the point that
we are usually interested in the long–term behavior of the system, not necessarily the value
at a single point in time, and how equilibrium values are important in the analysis. The
chapter includes several different types of applications modeled with discrete dynamical
systems including population growth, predator–prey systems, and simple epidemics.
Chapter 5 – Differential Equations This chapter begins with a discussion of the fact
thatitisofteneasiertodescribehowaquantitychangesovertimethanitistodescribethe
valueofthequantityatanyparticulartime.Thismotivatestheuseofdifferentialequations
for modeling dynamical systems. We focus on finding approximate numerical solutions to
differential equations rather than finding exact analytical solutions. To this end, we discuss
Euler’s method for approximating solutions to differential equations and apply it to several
applications of systems of differential equations. We also introduce Runge-Kutta methods
for approximating solutions to differential equations.
Chapter 6 – Simulations We cover the topic of simulations more extensively than most
other mathematical modeling text books. The main goal of this chapter is to illustrate
several different types of simulation models including games of chance, queuing models,
Preface ix
inventory models, and scheduling models. We also discuss how pseudo-random number
generators work and how to model random variables using density functions.
Chapter 7 – Linear OptimizationThemainfocusofthischapterislinearprogramming
and the simplex method. We do not discuss much theory; rather we try to give students
an overview of the basic ideas behind the simplex method. We introduce the assignment
problem and the transportation problem as examples of linear programs and how to model
with linear programs.
Chapter 8 – Nonlinear Optimization In this chapter we cover several numeric tech-
niques for approximating solutions to nonlinear optimization problems including Newton’s
method, gradient methods, and Lagrange multipliers. We also discuss the inherent difficul-
ties of solving nonlinear problems.
Selection of Material
Thereismorethanasemester’sworthofmaterialinthistext.Aninstructorcaneasilypick
and choose the sections that are appropriate for the students. Most sections can easily be
covered in one 50-minute class period. We give three suggestions for choosing material:
1. We suggest starting with Chapters 1, 2, and 3. Chapter 1 gives an essential overview
of the mathematical modeling process which can be covered in one class period. We
haveembeddedanintroductiontoworkingwithExcelthroughoutChapter2.Chapter
2 also introduces many concepts used throughout the rest of the text. Chapter 3
is a continuation of Chapter 2 focused on linear algebra. If students have a solid
background in linear algebra, Sections 3.1 and 3.2 can be skipped. Section 3.5 can be
skipped without loss of continuity.
2. Iftheinstructorwishestofocusondynamicalsystems,wesuggestcoveringChapters4
and 5. In Chapter 4 we model systems using discrete time increments, and in Chapter
5 we model time continuously. Sections 4.6, 5.6, 5.7, and 5.8 can be skipped without
loss of continuity. Selected topics from Chapters 6 and 7 can round out the semester.
3. Iftheinstructorwantstofocusontopicsfromoperationsresearch,wesuggestcovering
Chapters6,7,and8.ThesechaptersdonotdependonChapters4and5.Sections7.7,
8.6, 8.7, and 8.8 can be skipped without loss of continuity.
What’s New in the Second Edition
Work on the second edition began as soon as the first edition was published. The authors
would like to thank their students for finding mistakes and suggesting improvements and
new exercises. Changes made from the first edition include:
• All directions for using Excel have been updated for Excel 2016.
• Numerous new exercises have been added throughout the text.
• Chapter3onlinearalgebrahasbeenadded.ThischapterincludestopicsfromChapters
2 and 3 in the first edition as well as new material.