The Price is Right - Or is it?

Gary Franchy
Davenport University
This material is replicated on a number of sites as part of the SERC Pedagogic Service Project

Summary

In this Spreadsheets Across the Curriculum activity, students are guided step-by-step to build a spreadsheet that determines optimal revenue and profit. To better illustrate the modeling process, the module begins with the simplest case where all costs are fixed and is then expanded to consider per-unit cost, showing the divergence of maximum revenue from maximum profit. This module should demonstrate both the power of basic mathematics to analyze authentic relevant scenarios as well as the ease at which this can be accomplished using spreadsheets.


Learning Goals


Students will:
  • Recreate spreadsheets in Excel from information in a Powerpoint presentation.
  • Use the spreadsheets to calculate price, sales, profit and revenue of given data.
  • Create an x-y scatter plot.
In the process the students will:
  • Gain knowledge of entering equations in Excel spreadsheets.
  • Understand the mathematics behind maximizing revenue and profit.
  • Understand how graphical representations of numbers can help intensify patterns.

Context for Use

I believe, based on the algebra texts I have seen and used, that we are missing the opportunity for a much clearer transition from linear to quadratic functions. Specifically, the "building" of a quadratic function as a product of two "related" linear functions. Additionally, I also believe that we wait too long to start on quadratic optimization problems which are missing from most, if not all, elementary (aka beginning or introductory) algebra texts. With that in mind, I started with a fairly standard quadratic optimization problem. (Please note that these views may partially be a result of years of teaching to mostly business students for whom the physics-based intercept-focused example is less than inspiring.)

The module is meant to be a first step into real modeling (i.e., thinking beyond the basic textbook story problem), and in that respect is designed for use in an intermediate algebra course. However, given the level of math sophistication needed (and above rant), this module could easily be used in an elementary algebra course. It would also fit nicely into any business course as a lead in to optimization.

Within an Introductory or Intermediate Algebra course, this module is meant for use immediately after covering linear functions. I do assign (and would recommend) a series of short Excel skill-building assignments prior to this module. This way, students can start on the module with full confidence in their spreadsheeting abilities.

Because the module is designed as a stand-alone resource which may be assigned with minimal class discussion, there is overlap between the overview slides and items that would naturally come out during a classroom conversation.

The classroom discussion preceding the module in class in centered around the question "Why do stores have sales?" In all likelihood, the responses will address all the reasons listed in the module overview. From there, I direct the conversation to the point of the module: increasing profit while decreasing price. One assumption implicit in my module is a "real" original price, meaning the "list" price isn't set artificially high (i.e., the store will actually sell the product at full price).

The main point of the second example is to illustrate the difference between maximizing revenue and profit. In class discussions, it will likely become apparent that (some) students see the two as equivalent.

The final question of the End of Module section introduces the parallel idea of raising the product price and determining if that increase is sufficient to offset the resulting decrease in quantity sold. The spreadsheet was designed with not only that kind of analysis in mind, but also so that students can "see" the linear factors contributing to the quadratic. It also allows for discussion of the location and meaning of both the y and x-intercepts.

A possible next step (any business course or within a math course to coincide with the introduction of step functions) would be to explore a volume discount scenario within the per-unit cost.

Description and Teaching Materials

The module is a PowerPoint presentation with embedded spreadsheets. If the embedded spreadsheets are not visible, save the PowerPoint file to disk and open it from there.

The above PowerPoint files are the student version of the module. An instructor version is available by request. The instructor version includes the completed spreadsheet. Send your request to Len Vacher (vacher@usf.edu) by filling out and submitting the Instructor Module Request Form.

Teaching Notes and Tips

The module is constructed to be a stand-alone resource. It can be used as a homework assignment or lab activity. It can also be used as the basis of an interactive classroom activity.

Assessment

The last two slides of the module is a set of questions that can be used for assessment. The instructor version includes a slide of questions that can be used as a pretest.