Modeling College Algebra with Excel
This represents work supported in part by NSF grant
DUE-IUSE-1625222
The goal of the grant was to make math education more effective for
business students.
One of the sub-projects of the grant was to produce a text and
course for a true Business Calculus course, with the assumption
being that students would have laptops and would use an Excel
spreadsheet as their primary computational engine. The open
source, online text, Business
Calculus with Excel is the result.
A second sub-project was to have a flavor of College algebra aimed
at business students. In particular, the course was to have a
greater emphasis on modeling. The idea is that the problems
will routinely have the structure of starting with a collection of
data, choosing an appropriate mode for the situation, finding the
formula that best fits the model to the data, an then answering
questions about the function defined by that formula.
This is consistent with the recommendations of the MAA CRAFTY
committee's Curriculum Foundations Project (CFP). While the
material was developed for business students, the material should
work with minor adaptation for any modeling based College Algebra
course that uses spreadsheets.
Worksheets - The modeling skills were developed with a series of 8
worksheets. With each worksheet, the students spent the class
period working in groups. They finished outside of class and
turned in completed assignments one week later. The course
assumed the students had no prior experience with Excel.
- IntroExcel-I
Getting started. The worksheet covers entering formulas,
absolute and relative cell references and documenting with text
boxes.
- IntroExcel-II
Adding charts and graphs. The worksheet teaches how to add
charts and graphs in Excel. Students need to be trained
that one of the first steps in dealing with data is to look at a
visual representation, looking for important features. A worked key is
provided for instructors who are interested,
- IntroExcel-III
An introduction to Goal Seek. Goal Seek is a tool in Excel
for doing What-If analysis. Function of one or more
variables, Goal Seek lets you designate one of the inputs as a
variable and the others are considered parameters.
You can then specify the output and find the input. The
worksheets starts with a function of one variable, then looks at
intersections of curves, and functions of several
variables. It concludes with with a problem too hard to do
by hand. A worked key is
provided for instructors who are interested.
- IntroExcel-IV
Optimization and using Solver. Solver might be described
as Goal Seek on steroids. It has greater precision, can use
multiple constraints and will look at maximum or minimum as well
as solving for a value. While Goal Seek is available on
other spreadsheets, Solver is must more robust with Excel.
A worked key is
provided for instructors who are interested.
- IntroExcel-V
Linear Modeling. The worksheet goes through fitting a
linear function to data. It covers using the trendline in
Excel. It also looks at other definitions of "best
fitting" that might be considered. It looks at why we may
want to transform data to use reasonable numbers ofr input and
output. A worked key is
provided for instructors who are interested.
- IntroExcel-VI
Polynomial modeling. We move onto situations where the
model is not linear. We start with profit in a standard
business model, which will be a quadratic function in
quantity. We introduce using a larger data set where we
want to provide data that can be copied and pasted rather
than retyped. A data worksheet
is provided. A worked key
is provided for instructors who are interested.
- IntroExcel-VII
Exponential Modeling. This worksheet models exponential
curves using Consumer Price Index and Dow Jones Average data
from the real world. It looks at issues in modeling
concerned with base point and presentations of the
formula. A data
worksheet is provided. A worked
key is provided for instructors who are interested.
This worksheet was used in another workshop. You may be
intersted in the lesson
plan and teacher
worksheet that were developed.
- IntroExcel-VIII
Power function modeling. The final worksheet looks at
fitting data to a power function. A data
worksheet is provided. A worked
key is provided for instructors who are interested.
Test projects - Since students understand we have said something
is important if and only if points are attached, it is important
to have test questions on the modeling skills. Each test for
the semester had a modeling assignment.
- Test
1 - The exercises are looking at basic Excel
skills. The student is asked to make tables for
values of several functions, produce graphs, and specify
interesting features of the functions. The answers are to
be in a clean write-up using word.
- Test
2 - For the second test, students are asked to fit
specified polynomial models to a given data set.
The students then need to find features for the functions
specified by the formulas they have found. The answers are
to be in a clean write-up using word.
- Test
3 - For the third test, students are asked to fit
specified exponential and power
models to a given data set. The
students then need to find features for the functions specified
by the formulas they have found. The answers are to be
in a clean write-up using word.
- Final Exam
- For the final exam, students are asked to take a collection of
data sets corresponding to all the models discussed in the
semester. They are to decide which model is appropriate
from each data set. The
students are asked to produce a best fitting equation for the
model and to find features for the functions specified by the
formulas they have found for each model. The answers are to be
in a clean write-up using word.
If you are interested in using this
material in a class, please let me know.
Mike May
mike.may@slu.edu