How would I set this up in Microsoft excel and use solver to

How would I set this up in Microsoft excel and use solver to find the solution?

Solution

Essential Steps

To define an optimization model in Excel you\'ll follow these essential steps:

Within this overall structure, you have a great deal of flexibility in how you choose cells to hold your model\'s decision variables and constraints, and which formulas and built-in functions you use.  In general, your goal should be to create a spreadsheet that communicates its purpose in a clear and understandable manner.

Using the Solver Dialogs

To let the Excel Solver know which cells on the worksheet represent the decision variables, constraints and objective function, we click Solver button on the Excel Data tab, or the Premium Solver button on the Add-Ins tab, which displays the Solver Parameters dialog. In the Set Objective (or Set Target Cell) edit box, we type or click on cell F5, the objective function. In the By Changing Variable Cells edit box, we type B4:E4 or select these cells with the mouse.

To add the constraints, we click on the Add button in the Solver Parameters dialog and select cells F8:F11 in the Cell Reference edit box (the left hand side), and select cells G8:G11 in the Constraint edit box (the right hand side); the default relation <= is OK.

We choose the Add button again (either from the Add Constraint dialog above, or from the main Solver Parameters dialog) to define the non-negativity constraint on the decision variables. (Alternatively, we can check the Make Unconstrainted Variables Non-Negative option in the Solver Parameters dialog.)

Frontline\'s Premium Solver products can emulate either style, and they also offer a new Ribbon-based user interface.