Models for Life: An Introduction to Discrete Mathematical Modeling with Microsoft Office Excel

Preface xiii

Acknowledgments xvii

1 Density-Independent Population Models 1

1.1 Exponential Growth, 1

1.2 Exponential Growth with Stocking or Harvesting, 22

1.3 Two Fundamental Excel Techniques, 32

1.4 Explicit Formulas, 40

1.5 Equilibrium Values and Stability, 50

2 Personal Finance 59

2.1 Compound Interest and Savings, 60

2.2 Borrowing for Major Purchases, 77

2.3 Credit Cards, 92

2.4 The Time Value of Money: Present Value, 104

2.5 Car Leases, 112

3 Combat Models 119

3.1 Lanchester Combat Model, 120

3.2 Phase Plane Graphs, 140

3.3 The Lanchester Model with Reinforcements, 146

3.4 Hughes Aimed Fire Salvo Model, 153

3.5 Armstrong Salvo Model with Area Fire, 169

4 The Spread of Infectious Diseases 183

4.1 The S–I–R Model, 184

4.2 S–I–R with Vital Dynamics, 203

4.3 Determining Parameters from Real Data, 216

4.4 S–I–R with Vital Dynamics and Routine Vaccinations, 226

5 Density-Dependent Population Models 235

5.1 The Discrete Logistic Model, 235

5.2 Logistic Growth with Allee Effects, 248

5.3 Logistic Growth with Harvesting, 254

5.4 The Discrete Logistic Model and Chaos, 263

5.5 The Ricker Model, 266

6 Blood Alcohol Concentration and Pharmacokinetics 273

6.1 Blood Alcohol Concentration, 273

6.2 The Widmark Model, 280

6.3 The Wagner Model, 283

6.4 Alcohol Consumption Patterns, 289

6.5 More General Drug Elimination, 301

6.6 The Volume of Distribution, 319

6.7 Common Drugs, 321

7 Ranking Methods 329

7.1 Introduction to Markov Models, 329

7.2 Ranking Sports Teams, 342

7.3 Google PageRank, 361

8 Body Weight and Body Composition 381

8.1 Constant Calorie Expenditure, 382

8.2 Variable Calorie Expenditure, 385

8.3 Health Metrics, 394

8.4 Body Composition, 397

8.5 The Body Composition Model for Body Weight, 406

8.6 Points-based Systems: The Weight Watchers Model, 419

Appendix A: The Geometric Series Formula 431

Appendix B: Lanchester’s Square Law and the Fractional Exchange Ratio 433

Appendix C: Derivation of the FER = 1 Line for the Hughes Salvo Model 439

Appendix D: The Waiting Time Principle 441

Appendix E: Creating Cobweb Diagrams in Excel 445

Appendix F: Proportion of Total Credit Distributed Does Not Exceed 1 449

Bibliography 451

Index 459

EXCEL TABLE OF CONTENTS

E.1 Introduction to Excel 5

E.2 Absolute Addressing 12

E.3 Multiple Formulas 15

E.4 Graphing in Excel 32

E.5 Goal Seek 35

E.6 Working with Multiple Columns 53

E.7 Improving Spreadsheet Readability 66

E.8 The SUM Command and Working with Dates 99

E.9 The AVERAGE and COUNT Commands 101

E.10 The MAX Command 102

E.11 IF Statements 123

E.12 Phase Plane Graphs 141

E.13 Phase Planes with Multiple Trajectories 143

E.14 Nested IF Statements 155

E.15 Date Format and Plotting Data Points 193

E.16 Graphing Functions 243

E.17 Exponential and Logarithmic Functions 268

E.18 Data Validation 275

E.19 Setting the Error Tolerance for Goal Seek 295

E.20 Exponents 303

E.21 The MOD Command 311

E.22 Rounding 421