Optimization and Spreadsheets.

  Optimization is looking at something to find the best solution.  It is used in manufacturing, production, inventory control, transportation, scheduling, networks, finance, engineering, mechanics, economics, control engineering, marketing, and policy modeling.  Mathematical optimization is an applied form of mathematics used in so many different ways.

Unfortunately, most times, optimization activities use calculus but it is possible to expose students to trying without students knowing calculus.  The cool thing about using spreadsheets is that it gives students a visual representation.

Optimization has its own set of vocabulary from the objective function which is what you are trying to maximize or minimize, variables which represent those items you can control, and constraints that control the size of the variable.  An example of the vocabulary applied to a problem would be from the football coaches point of view.  He wants his players to gain the most yardage possible each time so that is his objective function.  The factors such as practice time for weight lifting, running, ball protection, etc.  would each be represented by one variable.  The constraints involve the total time available to practice, the number of acceptable fumbles, the number of times a player is tackled, etc.

There are several types of problems easily used in the Algebra classroom with a spreadsheet and minimal knowledge of spreadsheet use.

1.  Maximizing the volume of an open box.  Start by giving students an 18 inch by 18 inch piece of paper.  Have them cut small squares x by x out of each corner.  The value of x is from 1 inch to 8 inches. Once the squares are cut out, students assemble the boxes so they can see the final shape of each. With some discussion they should discover the formula is 18-2x = length = width and x is the height.  Students then fill out a spreadsheet to calculate the volume form the height, length, and depth of each box.  Once they have the results for the volume of each box on the spreadsheet, students create a graph showing the curve of results. 

A variation of this problem would be to create an optimization spreadsheet for the constraints of the post office.  They have a maximum of 108 inches for height and girth so what is the best shipping size of boxes.  That one students out here understand because everything has to come in by air unless it is too big and too heavy in which case it has to be shipped by barge or by air freight or bypass mail.

2.  Maximizing profit, an important optimization if someone is planning to open their own business.  A shoe company makes shoes for $15 per pair and sells them for $45 per pair minus a 5 cent discount for every pair a company buys.  The formula is (45-.05x)x = income and $15x is the cost of making each pair.  The 45-.05x is the cost of the shoes while x represents the number of shoes.  There will be columns for number of pairs, price per pair, cost of shoes, income and profit.  Students will discover that buying 300 pairs is the best buy but if they sell more than 600 pairs, they will no longer make any profit.




For other ideas this paper has some in depth information for optimizing investment among several investments.  The paper includes the constraints, the variables, etc, everything needed to run a scenario.  In addition it also provides all the information to to an optimization of a network system running from Los Angeles, CA to Amarillo, TX.  These are a bit more complex but they can be done on a spreadsheet.




Here are some really nice problems for students to learn about optimization without having to know all the calculus necessary to solve them.  Have fun and enjoy.  Let me know what you think, I'd love to hear.