Please answer the questions below the three Excel SOLVER reports shown.

Answer Report:

Limits Report:

Sensitivity Report:

1) Based on the results of your SOLVER run, what is the Return on Sales (Ratio of Total Net Profit to Total Revenues)?

Enter your result as a pure number (not %) rounded to 3 decimal places.

2) What is the total NET Profits in the optimized model? Round your answers to the nearest dollar.

3) The product manager of the Crunch product line is concerned about the production numbers. Based on the Sensitivity Report, determine the minimum increase or decrease in the unit sales price of the product that should alleviate his concerns. Round your answers to the nearest penny.

4) The receiving realized that the nut supplier shipped them 1,000 more pounds of nuts than they were allocated. What is the impact to the contribution margin if the entire shipment was consumed for manufacturing? Enter your result to the nearest penny.

Please show how you got the answers as well. Thank you for your help.

14 Objective Cell (Max) 15 16 $G$10 Contribution Margins Total CM $ 17 18 19 Variable Cells 20 21 C$5 Quantity Whole 22 SD$5 Quantity Cluster 23 SE$5 Quantity Crunch 24 F$5 Quantity Roasted 25 26 27 Constraints 28 29 $G$14 Available Labor Required 30 $G$17 Hulling Required 31 $G$18 Roasting Required 32 $G$19 Coating Required 33 SG$20 Packaging Required 34 $G$24 Nuts Required 35 $G$25 Chocolate Required 36 $G$27 Max Whole Required 37 $G$28 Max Cluster Required 38 $G$29 Max Crunch Required 39 $G$30 Max Roasted Required 40 $G$32 Min Whole Required 41 $G$33 Min Crunch Required 42 $G$34 Min Roasted Required 43 Cell Name Original Value Final Value 7.333 $ 643,838.431 Cell Original Value 1.00 1.00 1.00 1.00 Name Final Value Integer 200,000.00 Contin 24,507.01 Contin 500.00 Contin 10,765.42 Contin Cell Name Cell Value Formula Status Slack 1256399.35 $G$14-SIS32 Not Binding 198000.00 0.00 4265.42 456000.0000 $G$18-SI$18 Binding 138000.0000 $G$24-SIS33 Binding 10765.42 $G$34>-SIS34 Not Binding A B 4 6 Objective Name Cell SG$10 Contribution Margins Total CM Value 8 $643,838.431 10 Variable Lower Objective Limit pper Objective 12 13 $C$5 Quantity Whole 14 DS5 Quantity Cluster 15 SE$5 Quantity Crunch 16 $F$5 Quantity Roasted 17 Cell Name Value Result Limit Result 200,000.00 2,000.00 67,444.79200,000.00 643,838.43 0.00 609,423.59 24,507.01 643,838.43 500.00 643,838.43 10,765.426,500.00 633,160.2410,765.42 643,838.43 24,507.0:1 500.00 500.00 643,838.43 6 Variable Cells Final Value Reduced Objective Allowable Allowable Coefficient Increase Cell Name Cost Decrease 9$C$5 Quantity Whole 10 DS5 Quantity Cluster 11 E$5 Quantity Crunch 12 $F$5 Quantity Roasted 13 14 Constraints 15 16 17 $G$14 Available Labor Required 1256399.346 18 $G$17 Huling Required 19 $G$18 Roasting Required 20 $G$19 Coating Required 21 $G$20 Packaging Required 22 $G$24 Nuts Required 23 $G$25 Chocolate Required 24 $G$27 Max Whole Required 25 $G$28 Max Cluster Required 24507.00935 26 $G$29 Max Crunch Required 27 $G$30 Max Roasted Required 10765.42056 28 $G$32 Min Whole Required 29 $G$33 Min Crunch Required 30 $G$34 Min Roasted Required 10765.42056 200000 24507.00935 500 10765.42056 0 2.911078977 1E+30 0.812316716 0 1.404285714 0.741514286 0.151144434 1E+30 0.8651 0.51456 0.106648643 0 2.503433333 0.452957644 Final Value Shadow Constraint Allowable Allowable Cell Name Price R.H. Side Increase Decrease 0 1800000 1E+30 543600.6542 1E+30 16227.57009 456000 6005.263158 16389.0625 1E+30 34745.09346 1E+30 72627.99065 138000 7492.142857 1521.333333 1E+30 40656.00467 16300 56011.29032 1E+30 75492.99065 49500 1E+30 189234.5794 1E+30 500 1E+30 235772.4299 252000 456000 0.321726925 0 0 138000 2.425514019 0 200000 0.812316716 0 0 0 217254.9065 347372.0093 252000 420000 84343.99533 125000 200000 100000 50000 200000 2000 500 1E+30 200000 198000 500 34731.78808 6500 4265.420561 500 -0.106648643 0 WHOLE Per Unit Whole Adj. CLUSTER Per Unit Cluster Adj. CRUNCH Per UnitCrunch Ad. ROASTEDPer Unit Roasted AdJ TOTAL 2 Units Sold 88,000 70,000 10,000 198,000 4 Sales Revenue 397,340.00 $4.515 S4.741 $182,000.00 2.600 S28,000 $2.800 S3.080 $108,500.00 $3.617 715,840.00 6 Variable Costs 7 Direct materials 8 Direct labor 83,100.00 $0.944 59,00.00 $0.673 9 Variable manufacturing overhead $6,330.00 $0.072 $8,400.00 $0.095 157,030.00 $1.784 50.82 S14,400.00 $1.440 S0.59 $9,600.00 0.960 $360.00 $0.036 $620.00$0.062 $1.830 $99,200.00 $1.417$1.456 $24,980.00 $2.498 0.97 S56,000.00 0.800 $0.69 $40,000.00 0,571 0.07 $1,400. $ 0.10 $1,800.00 0.026 28,000 $0.933 $0.99 $13,000,00 $0,433 30 0.06 $1,200.00 $0.040 37 185,500.00 141,800.00 $8,990.00 $12,020.00 $348,310.00 0.020$0.020 0.026 S0.036 $900.00 $0.0 10 Variable selling & administrative $2.565 $43,100.00 $1.4 13 Contribution Margin 240,310.00 $2.731 $2.911 $82,800.00$1.183 1.404 3,020 50.302 $0.515$65,400.00 $2.180 367,530.00 15 Allocated Fixed Costs 16 Manufacturing overhead 17 General & Administrative S3,555.56$0.040 $1,750.00 0.020 S5,305.56 $0.060 0.042 $2,828.28 $0.040 0.044 $404.04 0.040 $0.021$1,750.0 0.025 0.063 $4,578.28 0.065 0.044 $1,212.12 $0.040 0.193 $1,750.0 $0.058 S0.237 $2,962.12 $0.099 $8,000 $0.028 $1,750.0 $0.175 0.072 $2,154.04 $0.215 S78,221.72 62,437.88 352,530.00 21 Net Profit 22 Net Profit Per Unit $235,004.44 14 Objective Cell (Max) 15 16 $G$10 Contribution Margins Total CM $ 17 18 19 Variable Cells 20 21 C$5 Quantity Whole 22 SD$5 Quantity Cluster 23 SE$5 Quantity Crunch 24 F$5 Quantity Roasted 25 26 27 Constraints 28 29 $G$14 Available Labor Required 30 $G$17 Hulling Required 31 $G$18 Roasting Required 32 $G$19 Coating Required 33 SG$20 Packaging Required 34 $G$24 Nuts Required 35 $G$25 Chocolate Required 36 $G$27 Max Whole Required 37 $G$28 Max Cluster Required 38 $G$29 Max Crunch Required 39 $G$30 Max Roasted Required 40 $G$32 Min Whole Required 41 $G$33 Min Crunch Required 42 $G$34 Min Roasted Required 43 Cell Name Original Value Final Value 7.333 $ 643,838.431 Cell Original Value 1.00 1.00 1.00 1.00 Name Final Value Integer 200,000.00 Contin 24,507.01 Contin 500.00 Contin 10,765.42 Contin Cell Name Cell Value Formula Status Slack 1256399.35 $G$14-SIS32 Not Binding 198000.00 0.00 4265.42 456000.0000 $G$18-SI$18 Binding 138000.0000 $G$24-SIS33 Binding 10765.42 $G$34>-SIS34 Not Binding A B 4 6 Objective Name Cell SG$10 Contribution Margins Total CM Value 8 $643,838.431 10 Variable Lower Objective Limit pper Objective 12 13 $C$5 Quantity Whole 14 DS5 Quantity Cluster 15 SE$5 Quantity Crunch 16 $F$5 Quantity Roasted 17 Cell Name Value Result Limit Result 200,000.00 2,000.00 67,444.79200,000.00 643,838.43 0.00 609,423.59 24,507.01 643,838.43 500.00 643,838.43 10,765.426,500.00 633,160.2410,765.42 643,838.43 24,507.0:1 500.00 500.00 643,838.43 6 Variable Cells Final Value Reduced Objective Allowable Allowable Coefficient Increase Cell Name Cost Decrease 9$C$5 Quantity Whole 10 DS5 Quantity Cluster 11 E$5 Quantity Crunch 12 $F$5 Quantity Roasted 13 14 Constraints 15 16 17 $G$14 Available Labor Required 1256399.346 18 $G$17 Huling Required 19 $G$18 Roasting Required 20 $G$19 Coating Required 21 $G$20 Packaging Required 22 $G$24 Nuts Required 23 $G$25 Chocolate Required 24 $G$27 Max Whole Required 25 $G$28 Max Cluster Required 24507.00935 26 $G$29 Max Crunch Required 27 $G$30 Max Roasted Required 10765.42056 28 $G$32 Min Whole Required 29 $G$33 Min Crunch Required 30 $G$34 Min Roasted Required 10765.42056 200000 24507.00935 500 10765.42056 0 2.911078977 1E+30 0.812316716 0 1.404285714 0.741514286 0.151144434 1E+30 0.8651 0.51456 0.106648643 0 2.503433333 0.452957644 Final Value Shadow Constraint Allowable Allowable Cell Name Price R.H. Side Increase Decrease 0 1800000 1E+30 543600.6542 1E+30 16227.57009 456000 6005.263158 16389.0625 1E+30 34745.09346 1E+30 72627.99065 138000 7492.142857 1521.333333 1E+30 40656.00467 16300 56011.29032 1E+30 75492.99065 49500 1E+30 189234.5794 1E+30 500 1E+30 235772.4299 252000 456000 0.321726925 0 0 138000 2.425514019 0 200000 0.812316716 0 0 0 217254.9065 347372.0093 252000 420000 84343.99533 125000 200000 100000 50000 200000 2000 500 1E+30 200000 198000 500 34731.78808 6500 4265.420561 500 -0.106648643 0 WHOLE Per Unit Whole Adj. CLUSTER Per Unit Cluster Adj. CRUNCH Per UnitCrunch Ad. ROASTEDPer Unit Roasted AdJ TOTAL 2 Units Sold 88,000 70,000 10,000 198,000 4 Sales Revenue 397,340.00 $4.515 S4.741 $182,000.00 2.600 S28,000 $2.800 S3.080 $108,500.00 $3.617 715,840.00 6 Variable Costs 7 Direct materials 8 Direct labor 83,100.00 $0.944 59,00.00 $0.673 9 Variable manufacturing overhead $6,330.00 $0.072 $8,400.00 $0.095 157,030.00 $1.784 50.82 S14,400.00 $1.440 S0.59 $9,600.00 0.960 $360.00 $0.036 $620.00$0.062 $1.830 $99,200.00 $1.417$1.456 $24,980.00 $2.498 0.97 S56,000.00 0.800 $0.69 $40,000.00 0,571 0.07 $1,400. $ 0.10 $1,800.00 0.026 28,000 $0.933 $0.99 $13,000,00 $0,433 30 0.06 $1,200.00 $0.040 37 185,500.00 141,800.00 $8,990.00 $12,020.00 $348,310.00 0.020$0.020 0.026 S0.036 $900.00 $0.0 10 Variable selling & administrative $2.565 $43,100.00 $1.4 13 Contribution Margin 240,310.00 $2.731 $2.911 $82,800.00$1.183 1.404 3,020 50.302 $0.515$65,400.00 $2.180 367,530.00 15 Allocated Fixed Costs 16 Manufacturing overhead 17 General & Administrative S3,555.56$0.040 $1,750.00 0.020 S5,305.56 $0.060 0.042 $2,828.28 $0.040 0.044 $404.04 0.040 $0.021$1,750.0 0.025 0.063 $4,578.28 0.065 0.044 $1,212.12 $0.040 0.193 $1,750.0 $0.058 S0.237 $2,962.12 $0.099 $8,000 $0.028 $1,750.0 $0.175 0.072 $2,154.04 $0.215 S78,221.72 62,437.88 352,530.00 21 Net Profit 22 Net Profit Per Unit $235,004.44