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!
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. 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 =$B$7 6200 $C$4=$C$7 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 =$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
