Optimization Of Resources - Using Excel Solver To Aid Decision Making

When an Excel data model is created and some cells require input from the user, the scenario outcome can change for better or worse. Here is a simple example of an organic baked goods production model where there are constrained resources available to make apple pies, boxes of cookies and cinnamon buns.

The limited available organic ingredients for tomorrow’s production is 350 cups of sugar, 450 cups of flour and 400 cups of butter.

An apple pie requires 1 cup of sugar, 0.5 cups of flour and 1 cup of butter. A minimum of 10 pies must be made to fill the shelf and each pie produces $1.00 of profit.

A box of cookies requires 2 cups of sugar, 4 cups of flour and 1 cup of butter. A minimum of 30 boxes must be produced and each box yields $5.00 of profit.

A box of cinnamon buns requires 1 cup of sugar, 3 cups of flour and 2 cups of butter. A minimum of 15 boxes is required and each will produce $4.00 in profit.

Setting Up The Model

The decision to be made is how many apple pies, boxes of cookies and cinnamon rolls to produce. These decisions are input by the user in the yellow cells C2:E2. In order to respect the minimum presentation requirement, these production numbers should be at least as big as the values in cells C4:E4. Cells C6:E8 detail how many cups of each organic ingredient are required to produce one unit of each baked good. The values in cells C10:E12 multiply the per-unit ingredient requirements by the number of units to produce in cells C2:E2 (40 boxes of cookies require 80 cups of sugar, 160 cups of flour and 80 cups of butter). Cells C14:E14 displays the per-unit profit of each baked good and C15:E15 multiplies these values by the number of units produced. Since there are only 350 cups of sugar available, this maximum is displayed in cell I10 alongside the total sugar consumed by the three products in G10. Cells G11:I12 display the cups of flour and butter used as well as the cups available.

The Initial Solutions

With a production schedule of 20 apple pies, 40 boxes of cookies and 30 cinnamon rolls, the presentation requirements are met, the three constrained ingredients are available and $340 of profit is earned.

Of course, more profit could be earned if additional baked goods are produced, but how many units of each should be added to yield the greatest profit? Since cookies have the highest profit per unit, we can change the production to 70 units. Cinnamon rolls also produce a good profit but use less flour, maybe we should increase production to 50 units. At this point we are constrained on flour and can only make a total of 40 apple pies. All of the flour has been used up but sugar and butter are still available while the profit earned is $590. Is there another solution that makes better use of the ingredients and yields more profit?

Excel Solver

This is a type of problem that is difficult to solve using trial and error but simple with Excel’s Solver Add-In. The Solver button appears in the Data tab of Excel’s ribbon. If you don’t see the Solver button, you need to enable the Add-In. Click on the File Tab then select Options and when the Excel Options dialog appears, select the Add-Ins tab. Click on the Go button to bring up a menu of Add-Ins that can be enabled or disabled, check the box for the Solver Add-In then click on OK. The Solver Add-In should be added to the Data tab in Excel, if not then quit Excel and open the Excel application again.

The Optimal Production Schedule

Click on the Solver button to set up the optimization model. The Set Objective box should be cell G15 and the To button should be Max because we want to maximize the value in cell G15, the total profit. The By Changing Variable Cells box should be cells C2:E2, the decision input cells that can

change their values. Next, click the Add button to make the Add Constraint dialog box appear. The Cell Reference of C2:E2 should be set to greater than or equal to the Constraint of C4:E4. Notice that three constraints are being specified at once instead of one cell at a time. Click Add to add another constraint. The next constraint is that G10:G12 should be less than or equal to I10:I12. The last constraint is the fact that C2:E2 must be integers; we can’t create half of a pie. In order to set this constraint, the Cell Reference is C2:E2 and the drop-down menu is set to “int” (which will then place the word “integer” in the Constraint box). Click OK to close the Add Constraint dialog box. The Solver model is ready to choose the optimal solution, so click the Solve button and when the solution is ready, click OK to return to the model.

The Optimal Solution

Excel Solver has found an interesting solution where 216 apple pies, 30 boxes of cookies and 74 cinnamon rolls are made. This solution makes good use of the ingredients as only 6 cups of butter will remain. The profit of this solution is $662, higher than what we could likely produce with trial and error. It seems counterintuitive that production of the highest per-unit profit item (cookies) is low but that makes sense considering that cookies use more flour than the other baked goods. If making 216 apple pies is unreasonable and risks spoilage, a new constraint could be added to this model that specifies a maximum level of production for each baked good.

Whether you need to allocate different products to stores at varying prices or allocate hours to employees with varying wage costs and sales performance, models such as this one can be created to maximize profits or minimize costs given a set of constraints.

If you have any questions for future articles, would like to know more about using Solver, Excel, VBA, Charts, need a dashboard, data model, database solution or analytics training, please contact Scott Cuthbertson:

Email: scottjcuthbertson@gmail.com
LinkedIn: http://ca.linkedin.com/in/scottcuthbertson
Blog: http://theanalyticspro.blogspot.ca/

Scott Cuthbertson BBA

Scott is an Analytics Professional who has worked with over 100 clients in the retail and financial services industries. He has helped organizations improve their labour scheduling, inventory replenishment, performance management, organization design and pricing. He has been a practitioner of analytics since childhood, using data models to win Hockey Pools. His expertise lies in developing data models, reporting, algorithms and solutions for any organization that wants to use their data to improve decision making. He also teaches at the CMA Professional Development Institute.

© Scott Cuthbertson 2014 Blog: Words From The Analytics Pro