Running head: EXCEL SOLVER 1 Excel Solver

Amanda McDougall

American Public University EXCEL SOLVER 2

Excel Solver To begin using Excel Solver, the add-in must be active. To check this, go to the data tab and look

on the far right. If “solver” is not an option, it will need to be added manually. From file, choose options,

and then add-ins. Once this tool is located, select it and click go – it should now appear in the analyzer

section. Now that the tool is available, one can begin to enter their data on a blank sheet. The first box,

A1, will have the name of the project which in this case is the business, Klein Industries. The example

problem gives two sets of data and asks to solve for the decision variables. The data provided is as

follows:

Projected Sales

Minimum

Maximum Small

1400

2100 Medium

6200

12500 Large

2600

4200 Required Inputs

Bending/Formin Small

.4 Medium

.7 Large

.8 Available

23400 g

Welding

Painting .6

1.4 1

2.6 1.2

3.1 23400

46800 Unit Profit $20.50 $34 $42 Excel needs to be organized in a way that makes sense to the person and the program.

The attached file shows the first section which is “number to make” so that way once the

calculations are completed, the answer is at the top. The next section shows projected sales, then

required inputs, then unit profit. Once the information was added, a column was added to the left

of “available” as shown in a YouTube example and will show the total minutes used. In order

calculate this, a formula was entered using =SUMPRODUCT(variables, minutes for each size). EXCEL SOLVER 3 From here, solver was selected with the information needed to find a solution. The set

objective is the cell that will display “total profit” which is on the same line as “unit profit”.

Next, the cells for “changing variable” are the ones created to show to answer to the problem.

Then, several constraints are added including total sales being between the minimum and

maximum projections, as well as ensuring that the used minutes do not exceed the allowable

minutes. The type of solving method will be Simplex LP, and from there just click solve and it

will calculate. Be sure to choose all three reports, answer, sensitivity, and limits, as it will not

automatically do it. This same process was used for Part B, but the maximum constraints were

removed to relax the equation.

Once the reports were ran, the most useful information in this case will be found on the

sensitivity tab. It provides the final value and shows how many of each size the company should

make. In this case, the number of small compressors far outweighed the medium and large. In

fact, to be the most profitable, they should only make the minimum of each and focus their

efforts on the small ones. The report also shows that in two of the labor categories,

bending/forming and welding, that making a positive or negative change to the time would have

no impact on the category. This is shown as 1E+30 which is infinity because the shadow price is

0. In contrast, painting used 100% of the allotted time and does have flexibility to increase or

decrease. It also gives a shallow price value which means if the time is increased by 14.7

minutes, the profit would also increase.

Other useful information can be found by looking at the reduced cost which shows how

much the objective coefficient needs to change by in order to meet the minimum requirements of

production. In this case, if the medium increases by $4.07, and the large by $3.39, then it would

make sense to focus more production on those areas. At the present time though, it will not EXCEL SOLVER 4 maximize the profit as is. Once the constraints were relaxed and solver was ran again, the

information came up the same. References

Evans, J. R., & Basu, A. (2013). Statistics, data analysis, and decision modeling. Harlow:

Pearson Education.