Imagine you're running items a manufacturing plant, producing hats, shirts, gloves, and other similar t You know the following The number of each item you are now manufacturing. The number of labor units you need to produce each item. The number of raw material units you need to produce each item. The unit price, unit cost, and unit profit for each item. Maximum demand for each item. Your total available labor units. Your total available raw material units. Here is a MS Excel worksheet that summarizes your data A C E G 1 Product Shirts Scarfs Gloves Hats Coats Gowns 2 No. Produced 120 197 89 372 400 56 3 Labor 4 2 3 1 2 4 Raw Material 2 1.5 4.8 0.7 2.5 9.7 5 Unit Price 49.99 17.49 110 9.99 19.95 250.95 6 Unit Cost 48.99 6.59 25.35 3.45 9.95 105.55 7 Unit Profit 10.9 24.64 61.01 6.54 10 145.4 8 Demand 250 356 400 1680 900 390 9 10 11 USED AVAILABLE PROFIT 12 Labor 26167.25 2639 4500 13 Raw Materials 2766.3 7430 QUESTION: How would you know how many to manufacture from each item in order to maximize your profit WITHOUT going over your total labor and raw material limits? SOLUTION: This is generally referred to as an OPTIMAL PRODUCT MIX problem and Excel Solver solves these kinds of problems really well, especially when the solution conforms to a "linear solution" model. Instead of the "GRG Nonlinear" method shown in the practice exercise, here you must use the "Simplex LP" method. STEP 1 Calculate unit profit in cells B7 to G7 by entering the formula =B5-B6 in cell B7, -C5-C6 in cell C7 etc. up to cell G7 STEP 2 Calculate the total labor used by entering this formula into the total labor cell (B12, in our example) SUMPRODUCT (SB$2:SG$2,SB$3:$G$3) This formula multiplies each cell of the Numbers Produced and Labor Required rows (arrays) with one another STEP 3 Calculate the total raw materials used by entering this formula into the total raw materials cell (B13, in our example): -SUMPRODUCT(SBS2:SG$2,$B$4:$G$4) This formula multiplies each cell of the Numbers Produced and Raw Materials Required (arrays) with one another. rows IMPORTANT: In cell G12, you must enter the correct formula for calculating the total profit realized by the firm, that formula is not given to you in these instructions. If you enter the correct formula, you will see the figure 26167.25 shown in the screen shot STEP 4 Click the Solver icon on the ribbon to display the Solver Parameters dialog box X Solver Parameters Set Objective: To Mio Value Of Max By Changing Variable Cells: Sybject to the Constraints: Add Change Delete Beset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: GRG Nonlinear Ogtions Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Cgse If you instant. This is the part you must figure using the knowledge you gained by doing the practice ises and by using your critical thinking skills. To do that, you must enter the right content in the following boxes -here are a few hints to help you along, but you must figure out exactly what to enter can fill in this box correctly and click the SOLVE button, you can find the solution in an exe SET OBJECTIVE is the PROFIT CELL (G12). That's what trying to MAXIMIZE. we are That's the FINAL OBJECTIVE of this whole exercise. BY CHANGING VARIABLE CELLS is the # Produced row (B2:G2). That's how we will maximize our profits- by varying the number produced from each item Make sure: MAX option button is selected since we are trying to maximize our profit. Simplex LP is selected from the drop-down list in the "Select a Solving Method" area, for solving relatively-simpler linear problems. Make Unconstrained Variables Non-Negative check box is checked. And we have SEVERAL SETS OF CONSTRAINTS: (1) The total labor and total raw material used cannot be more than what's available. Thus the value of the cell B12 must be equal to or less than cell D12. Similarly, the value of the cell B13 must be equal to or less than cell D13 (2) Also, the total number of items produced you'd be wasting precious resources cannot exceed the total demand. If it does Why produce items for which there is no demand? Back at SOLVER PARAMETERS dialog box, click the SOLVE button to calculate the OPTIMUM NUMBER OF PRODUCT-MIX that would maximize your profit. What is the result? Look at the results and answer the following questions in the same worksheet 5e: (1) How many units of each product must be produced to maximize profit? (2) What is the profit generated by producing these quantities? (3) What are the total labor and raw materials used? (4) If we need to generate more profits then the maximization has indicated, what actions must you take? CAUTION: With this analysis, MS Excel cannot tell us if any other product mix could've generated the same amount of profit CAUTION: With this analysis, MS Excel cannot tell us if any other product mix could've generated the same amount of profit Could you have guessed the answers without using Solver? Probably not I hope now you can easily see the power of the SOLVER functionality VARIATION: What if you need to make exactly $50,000 profit? What happens to your product mix then? Easy In the SOLVER PARAMETERS dialog box, instead of selecting the MAX option button, select the VALUE OF option button and enter "50000 for value All else remains the same. Click SOLVE for the result, then answer the following question: (1) What must be produced and in what quantity or quantities to generate exactly $ 50,000 profit? You must show both solutions in worksheet 5d