View the step-by-step solution to:

Question

Hello, I had difficulty understanding this

assignment and my professor didn't even give me a partial grade even though I think I'm on the right path. Can you please review this and provide me some guidance? Specifically explaining the sensitivity report, part b, and shadow prices? Thanks!McDougall_BUSN625WK7.xlsxMcDougall_BUSN625WK7.docx

McDougall_BUSN625WK7.docx

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.

McDougall_BUSN625WK7.xlsx

Klein Industries Number to Make Small
16157 Medium
6200 Large
2600 Projected Sales
Minimum
Maximum 14000
21000 6200
12500 2600
4200 Required Inputs
Bending/Forming
Welding
Painting 0.4
0.6
1.4 0.7
1.0
2.6 0.8
1.2
3.1 Used
12882.9
19014.3
46800.0 Total Profit
Unit profit
$
20.50 $
34.00 $
42.00 $ 651,221.43
Contribution $ 331,221.43 $ 210,800.00 $ 109,200.00 <=
<=
<= Available
23,400
23,400
46,800 Microsoft Excel 16.0 Answer Report
Worksheet: [Book3]Sheet2
Report Created: 9/22/2019 9:57:46 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.063 Seconds.
Iterations: 2 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max)
Cell
Name
$E$17 Unit profit Total Profit Original Value Final Value
$
- $ 651,221.43 Variable Cells
Cell
Name
Original Value
$B$4 Number to Make Small
0
$C$4 Number to Make Medium
0
$D$4 Number to Make Large
0 Constraints
Cell
Name
$E$12 Bending/Forming Used
$E$13 Welding Used
$E$14 Painting Used
$B$4 Number to Make Small
$B$4 Number to Make Small
$C$4 Number to Make Medium
$C$4 Number to Make Medium
$D$4 Number to Make Large
$D$4 Number to Make Large Final Value
Integer
16157 Contin
6200 Contin
2600 Contin Cell Value
Formula
12882.9 $E$12<=$G$12
19014.3 $E$13<=$G$13
46800.0 $E$14<=$G$14
16157 $B$4<=$B$8
16157 $B$4>=$B$7
6200 $C$4<=$C$8
6200 $C$4>=$C$7
2600 $D$4<=$D$8
2600 $D$4>=$D$7 Status
Slack
Not Binding 10517.142857
Not Binding 4385.7142857
Binding
0
Not Binding 4842.8571429
Not Binding
2157
Not Binding
6300
Binding
0
Not Binding
1600
Binding
0 Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Book3]Sheet2
Report Created: 9/22/2019 9:57:47 PM Variable Cells
Final
Reduced
Objective
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
$B$4 Number to Make Small
16157.142857
0
20.5
1E+030
$C$4 Number to Make Medium
6200 -4.0714285714
34 4.0714285714
$D$4 Number to Make Large
2600 -3.3928571429
42 3.3928571429
Constraints
Cell
Name
$E$12 Bending/Forming Used
$E$13 Welding Used
$E$14 Painting Used Final
Shadow
Constraint
Value
Price
R.H. Side
12882.857143
0
23400
19014.285714
0
23400
46800 14.642857143
46800 Allowable
Increase
1E+030
1E+030
6780 Allowable
Decrease
1.5322580645
1E+030
1E+030 Allowable
Decrease
10517.142857
4385.7142857
3020 Memo
The small compressors have the highest profitibabilty margain
so we should produce the minumum amount of medium and
large and focus on small.
Increasing or decreasing the production hours for
bending/forming and welding will not increase our profit as the
shadow is zero. There is additional time available in both
departments, but painting has none. If we increase the time by
14.7 minutes, profit would increase by allowing us to produce
more small compressors. Microsoft Excel 16.0 Limits Report
Worksheet: [Book3]Sheet2
Report Created: 9/22/2019 9:57:47 PM Objective
Cell
Name
$E$17 Unit profit Total Profit Cell
$B$4
$C$4
$D$4 Variable
Name
Number to Make Small
Number to Make Medium
Number to Make Large $ Value
651,221.43 Value
16157
6200
2600 Lower Objective
Limit
Result
14000 607000
6200 651221
2600 651221 Upper Objective
Limit
Result
16157 651221
6200 651221
2600 651221 Klein Industries Number to Make Small
16157 Medium
6200 Large
2600 Projected Sales
Minimum
Maximum 14000
21000 6200
12500 2600
4200 Required Inputs
Bending/Forming
Welding
Painting Unit profit 0.4
0.6
1.4 0.7
1.0
2.6 0.8
1.2
3.1 Used
12882.9
19014.3
46800.0 Total Profit
$ 20.50 $ 34.00 $ 42.00 $
651,221.43 <=
<=
<= Available
23,400
23,400
46,800 Microsoft Excel 16.0 Answer Report
Worksheet: [McDougall_BUSN625_WK7.xlsx]Part 2
Report Created: 9/23/2019 8:49:17 AM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.25 Seconds.
Iterations: 2 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max)
Cell
Name
$E$17 Unit profit Total Profit Original Value
$
- Variable Cells
Cell
Name
$B$4 Number to Make Small
$C$4 Number to Make Medium
$D$4 Number to Make Large Original Value
0
0
0 Constraints
Cell
Name
$E$12 Bending/Forming Used
$E$13 Welding Used
$E$14 Painting Used
$B$4 Number to Make Small
$C$4 Number to Make Medium
$D$4 Number to Make Large Final Value
$ 651,221.43 Final Value
Integer
16157 Contin
6200 Contin
2600 Contin Cell Value
Formula
12882.9 $E$12<=$G$12
19014.3 $E$13<=$G$13
46800.0 $E$14<=$G$14
16157 $B$4>=$B$7
6200 $C$4>=$C$7
2600 $D$4>=$D$7 Status
Not Binding
Not Binding
Binding
Not Binding
Binding
Binding Slack
10517.142857
4385.7142857
0
2157
0
0 NonNegative Microsoft Excel 16.0 Sensitivity Report
Worksheet: [McDougall_BUSN625_WK7.xlsx]Part 2
Report Created: 9/23/2019 8:49:23 AM Variable Cells
Cell
Name
$B$4 Number to Make Small
$C$4 Number to Make Medium
$D$4 Number to Make Large Final
Reduced
Objective
Allowable
Value
Cost
Coefficient
Increase
16157.142857
0
20.5
1E+030
6200 -4.0714285714
34 4.0714285714
2600 -3.3928571429
42 3.3928571429 Constraints
Cell
Name
$E$12 Bending/Forming Used
$E$13 Welding Used
$E$14 Painting Used Final
Shadow
Constraint
Allowable
Value
Price
R.H. Side
Increase
12882.857143
0
23400
1E+030
19014.285714
0
23400
1E+030
46800 14.642857143
46800 10233.333333 Allowable
Decrease
1.5322580645
1E+030
1E+030 Allowable
Decrease
10517.142857
4385.7142857
3020 Memo
After relaxing the contstraints to reflect only the minimum sales
required, the reporting still gives the same results.
If we look at the reduced cost, it tells us how much we need to
reduce the objective coefficient by to meet the minimum
production requirements. If we can increase the medium by
$4.07, and the large by $3.39, then it would make sense to
produce more than the minimum. Microsoft Excel 16.0 Limits Report
Worksheet: [McDougall_BUSN625_WK7.xlsx]Part 2
Report Created: 9/23/2019 8:49:28 AM Objective
Cell
Name
$E$17 Unit profit Total Profit Cell
$B$4
$C$4
$D$4 Variable
Name
Number to Make Small
Number to Make Medium
Number to Make Large Value
$ 651,221.43 Value
16157
6200
2600 Lower Objective
Limit
Result
14000
607000
6200
651221
2600
651221 Upper Objective
Limit
Result
16157
651221
6200
651221
2600
651221

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes