Mathematical Modeling with Excel

Brian Albright, William P Fox

• Publisher: Chapman and Hall/CRC
• Year: 2019
• Print ISBN: 9781138597075
• eBook ISBN: 9780429945052
• Edition: 2
• Topic: Business
• Subtopic: Operations
• Index: Business

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.

1

What is Mathematical Modeling?

Chapter Objectives

• Define the terms model, mathematical model, and mathematical modeling
• Understand the purpose and process of mathematical modeling
• Understand the importance and significance of assumptions behind a mathematical model

Every student of mathematics has done some “mathematical modeling” in his/her educational career. These instances of mathematical modeling are typically called “applications” and are used to illustrate how mathematics is implemented in the “real world.”

In most math classes, the main goal is to learn the theory of some particular mathematical discipline. The applications are used to help achieve this goal by providing a more concrete context in which to study and understand the theory. For instance, in Calculus I, the real goal is to understand the idea of the limit and the derivative. An applied maximization problem is used to motivate the idea of the derivative and to provide practice in calculating and interpreting derivatives.

In mathematical modeling, the opposite is true. Here we will start with some “real world” problem and use mathematical theory and techniques to better understand the phenomena behind the problem.

1.1 Definitions

To define the phrase mathematical modeling, we will first define the term model. The word model is used frequently in everyday language. We talk about model airplanes, model houses, models on a runway, etc. What does the term model mean in a mathematical sense?

Lucas (Lucas, William F., The Impact and Benefits of Mathematical Modeling, in Applied Mathematical Modeling (D.R Shier and K.T. Wallenius eds.), Chapman and Hall/CRC, 1999, pg. 5) defines a model as “a simpler realization or idealization of some more complex reality.” The real world is a very complex place. To better understand it, we need to try to simplify it to a reasonable degree, describe the simplification in ways we can understand and work with, and then study the simplification. This is what we call modeling.

A mathematical model then can be defined as a model constructed using mathematical terms, symbols, and ideas. Giordano et. al. (Frank. R. Giordano, M. D. Weir, and W. P. Fox, A First Course in Mathematical Modeling, Third ed., Thomson Brooks/Cole, 2003, pg. 54) defines a mathematical model as “a mathematical construct designed to study a particular real world system or phenomenon.” Mathematical models can take many different forms. They may involve equations, inequalities, differential equations, matrices, logic, or any other type of “mathematical” idea.

The key idea is that we use mathematics to describe a portion of the real world. Therefore, a very simple but general definition of the process of mathematical modeling is:

Definition 1.1.1. Mathematical modeling is the application of mathematics to real world problems.

1.2 Purpose

Why do we do mathematical modeling? Since we want to answer a question about real world phenomena, we could just sit back, observe, and take notes. Suppose we put 500 bacteria in a Petri dish. The next day we count 525 in the dish, and the next we count 551.

Obviously, the number of bacteria is growing. Based on this observation, we might ask these questions:

1. How long will it be until there are 600 bacteria in the dish?
2. If we need 900 bacteria for an experiment in 3 days, how many must we put into the dish today?

We could answer each question as follows:

1. Wait until we count 600 bacteria in the dish.
2. Put 1 bacterium in a dish, 2 in a second dish, 3 in a third, etc. up to 900, wait 3 days, and determine which dish contains 900 bacteria.

These solutions only require us to make simple observations of this real world phenomenon of bacteria growing in a Petri dish. However, these solutions are obviously impractical for they might require too much time or too many resources (the second solution requires 900 Petri dishes and a total of 1 + 2 + · · · + 900 = 405, 450 bacteria).

A much more practical approach to answering these questions is to construct a function that gives the number of bacteria in the dish in terms of time (i.e. construct a mathematical model of the bacteria growth).

In other situations, making observations may itself be a complicated ordeal. For instance, suppose we wanted to find the optimal mixture of doctors and nurses (i.e. the number of doctors and number of nurses) to staff a hospital emergency room. The concept of “optimal” may take into account several factors, including:

1. Quality of patient care. (Do they get the care they need?)
2. Patient waiting time. (Do they have to wait a long time?)
3. Time spent with patients. (Are the doctors and nurses over-worked, or do they have too much “free time?”)
4. Resources. (Is there enough floor space or are people running into each other?)

One approach to finding an optimal number is to choose some mixture of doctors and nurses (say 3 doctors and 8 nurses), put them to work, and have a team of people record data for a series of weeks or months. Then choose another mixture (say 2 doctors and 7 nurses) and repeat the process. Repeat this until all possible combinations of doctors and nurses have been tried, analyze the data, and pick the optimal mi...

Table of contents

• Cover
• Half Title
• Series Page
• Title Page
• Copyright Page
• Contents
• Preface
• 1. What is Mathematical Modeling?
• 2. Proportionality and Geometric Similarity
• 3. Linear Algebra
• 4. Discrete Dynamical Systems
• 5. Differential Equations
• 6. Simulations
• 7. Linear Optimization
• 8. Nonlinear Optimization
• Appendix A: Spreadsheet Basics
• Index