for mathematicsexpert only

RES 351 EAG

The Art of Optimization*

Optimization is simply defined as the best resource allocation given a set of definable objectives. There are many types of models that apply in this aspect of management science. Some “classes” of this are product mix, ingredients mix (blend), personnel assignment, transportation and warehousing, inventory purchases, and networks. If you notice, these factors have a lot to do with supply chains and logistics management. Therefore there is much application that can be done in assisting a business environment. Perhaps this is not directly on a “glorious” web page of E-Business/M-Commerce, but it sure might make a difference in the “ behind the scenes” in regular Business or E-Business management. In truth, regardless of storefront, any business needs solid decisions. Managers need to maximize profits at minimal cost and use the resources to the best competitive advantage. Business managers often know how to define business objectives in written language. The key to modeling this business acumen is to write out the statement in plain English, then translate it into the mathematical equivalent. The case below is a simple entrepreneur making some clay bowls and mugs given a week of production. First, start out with an objective: For a week of production, the company can make four dollars profit ($4) per bowl produced and five dollars ($5) per mug. Declaring two types of products means two mathematical unknowns. Since one does not know how many to make yet, the goal is to decide. Typically this would mean “x” bowls and “y” mugs. To keep in a ready to expand form, use X1 and X2. This can expand to mean Xi where i could represent any number of products so you can see the flexibility now introduced (e.g. i=1,2,3,4…) Now, what does this mean from a mathematical standpoint? Let maximum profit = Z (total profit to solve) Decision variables: X1 = number of bowls made X2 = number of mugs made This starts out Z= $4 times X1 + $5 times X2 Next our model needs to know limits or constraints on the objective. Constraints are simply when the given resource will be used to capacity. In the storage shed the entrepreneur notices there are 120 lbs of clay, and there is one work week (40 hours) to make a production run.

RES 351 EAG

The constraints in written language that the entrepreneur knows from work experience are stated in sentence form first.

1. It takes four pounds of clay per bowl and three pounds of clay per mug

2. It takes one hour to make a bowl and two hours to make a mug. The mathematical equivalent of the above statements on the left hand side of the constraint equations are: 1. 4 times X1 + 3 times X2 2. 1 times X1 + 2 times X2 The right side of the equation is the value the constraint equation must not exceed. That is, the total amount of the resources, 120 lbs of clay for constraint 1 and 40 hours for constraint 2. This means less than or equal to the constraint limit using the symbol <= as the notation. The final version of the constraint equations follows: 1. 4 times X1 + 3 times X2 <= 120 2. 1 times X1 + 2 times X2 <= 40 Now the simple model is defined in its entirety Z= $4 times X1 + $5 times X2 Subject to (s.t.) 1. 4 times X1 + 3 times X2 <= 120 2. 1 times X1 + 2 times X2 <= 40 Now the input into Excel is accomplished by declaring cells as certain values and formulas to accommodate the optimizer in Excel called Solver (under the Tools in Excel 2003 menu, Data tab in Excel 2007/ 2010). The following translations are used: The Target Cell is the objective equation Changing Cells are the declared variables (X1, X2) Constraint cells are the formulas for the constraint equations to be less than or equal a constraint value cell. Given this, translate the model on paper first prior to inputting into Excel

RES 351 EAG

Changing cell declarations: C2 and D2 in excel, Target Cell B2 Note the Algebra notation is a placeholder to remind you of the math, the “cells” is what Excel uses in the Solver. ( Excel row number)

Column A Column B Column “C” Column “D”

1 X1 X2 2 Z= Target Cell(B2) C2 D2

Objective: Z= $4 times X1 + $5 times X2 Target Cell: B2 Excel Formula: Subject to (s.t.) 1. 4 times X1 + 3 times X2 <= 120 Excel Formula: 2. 1 times X1 + 2 times X2 <= 40 Excel Formula: Hopefully you understand the translation from an “X” to a cell SO the Excel formula in Target Cell B2 should be =4*C2+5*D2 Constraint equations ( depends on location in Sheet) formulations Constraint equation Left hand side <= Right hand side supply =4*C2+3*D2 <= 120 =1*C2+2*D2 <= 40 Notes: Do a simple graph to solidify the idea. In the next page , the graphic is how this should look when set up properly. When the formulas are set properly you are at the graph origin of (0,0). This makes sense because at the beginning of the work week nothing has started.

RES 351 EAG

To add solver: the following pictures should guide you to install the Excel solver. This is a built in add-in that manually loaded.

1. Go to the Excel drop menu and get to Excel options

RES 351 EAG

2. Click on Excel options, then the next screen for add ins appears.

Excel options

RES 351 EAG

3. Clicking Add-Ins results in the next screen.

Excel Add-Ins

RES 351 EAG

After “Go” the add in scripts for the solver appear. I also recommend adding the other Analysis tool packs

Excel Add-Ins, Click “Go”

RES 351 EAG

also.

4.Click the check marks and click “OK, This adds in the modules to Excel. Next, the Solver is located in the Data tab at the top of Excel.

Excel Add-Ins, Check and click “OK”

RES 351 EAG

5. The Solver activated by clicking the solver function.

Excel Data tab, Solver location

RES 351 EAG

6. Re-check the entries that the cells you declared as Target, Changing Cells, and Constraints are correct to the Excel sheet ( it is easy to click a wrong cell sometimes). Then Click “Solve” and the optimization kicks in, produces a solution , and ask if you the answer. Highlight the Answer in blue and click OK.

7. This produces an Answer Sheet before Sheet 1( see next page)

RES 351 EAG

Excel Answer Report Sheet