How to Use Excel for Optimization Calculations

1. Compute an equation for the optimization. For example, suppose the problem is to create a fence that encloses 100 square feet while using the least amount of fencing. Then, the two equations would be 'area = length * width' and 'total fence = 2*l 2*w.'
2. Enter the formulas into Excel. Enter an example length of 25 feet in box 'A1.' Enter an example width of 4 ft in box 'A2.' Type '=A1*A2' into box 'A3.' Box 'A3' is the area parameter. Type '=(2*A1) (2*A2)' into box 'A4.' Box 'A4' is the perimeter which is to be optimized.
3. Select 'Solver' from the 'Tools' menu.
4. Enter the value to be optimized into the 'Target Cell' box. Type 'A4' into the 'Target Cell' box, as this is the perimeter to be minimized. Click on the 'Min' dial.
5. Enter the values that can be changed into the 'By Changing Cells' box. Type 'A1, A2' into the 'By Changing Cells' box, as the length and width are the parameters that can be changed.
6. Enter the problem constraints into the 'Constraints' box. Type 'A3 = 100' into the 'Constraints' box, as this is the constraint of the problem.
7. Press 'Solve.' In this example, the length and width would both be 10 feet, to produce a minimum fence length of 40 feet.