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.
  1. IntroExcel-I Getting started. The worksheet covers entering formulas, absolute and relative cell references and documenting with text boxes.
  2. 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,
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.

  1. 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.
  2. 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.
  3. 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.
  4. 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