Question

1. Better Products manufactures three products on two machines. There is $30 profit associated with Product 1, $50 profit with Product 2, and $20 profit with Product 3. Each product requires labor time using machine A and machine B. Product 1 requires 1 hour from machine A and 1 hour from machine B. Product 2 requires 4 hours from machine A and 1 hour from machine B. Product 3 requires 1.5 hours from machine A and .5 hours from machine B. Up to 100 labor hours are available for each machine. Product 1 cannot account for more than 50% of the total units produced, and Product 3 must account for at least 20% of the total units produced. The company wants to maximize profit, and produce only complete products at the end of each production run. Format and solve the linear program in Excel.

2. The current profit is $2000. Suppose that 10 more labor hours for machine A could be bought for $150. Would this be a good idea in terms of the new profitability? Explain your answer and show your work.

3. Reset the linear program to the original solution from problem 1. The labor for machine A will now be 100. What if 10 more hours of labor could be bought for machine B at a cost of $150 . Would this be a good idea in terms of the new profitability? Explain your answer and show your work.

5. Reset the linear program to the original solution from problem 2. What if the company was able to reduce its cost to produce Product 1, and the new profit was $35. How would that change overall profitability? Show your work.

6. Reset the linear program to the original solution from problem 2. What if the company received an order for 15 of Product 2 and had to fill that order. There would be no more Product 2 to fill. How many of each product would now be produced and what would the new profitability be?

Answer 1

1) This is a linear programming problem and can be solved and modeled in excel as below

the formulas used are as below

Available -SUM(C5:E5) 100 100 Product3 Product1 Product2 5 Produced 6 Machine1 7 Machine2 8 Profit 1.5 0.5 20 -SUMPRODUCT(CS:ES,C6:E6) =SUM PRODUCT(C5:E5,C7:E7) 30 50 10 Objective 11 Profit 12 UMPRODUCT(C5:E5,C8:E8)

Then go to the data tab and under that solver section and click

The objective function is to maximize the profit and the constraints are feeded as below

HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER From Access From Web Solver Data Analysis - Reapply From Othe xisti ng Refresh Ungroup Subtotal Era From Text Sources Connections All·aEdit Links | Sort F Solver Parameters Get External Data Connections Outline Analysis C11 Set Objective: SCS11 F G (● Max OMin Oyalue of: By Changing Variable Cells: SCSS:SES5 Product1 Product2 Product3 Used Available Subject to the Constraints: Produced Machine1 Machine2 Profit 100 100 0.5 0 SES5 >= 0.2*SHS5 Change 30 50 Delete objective- Profit 10 Reset All 12 13 14 15 Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options 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. 18 21 Help Close 23

Then click on solve and keep solver solution

Available 4 Product1 Product2 Product3 Used Produced Machine1 Machine2 Profit 4 1 50 1.5 100仁 0.5 60 20 100 100 Objective Profit 10 12 13

Thus we see that 40 units of product 1 and 40 units of product 3 are optimum to maximize profits.

2) Machine 2 is unused because 100 hours are available on machine 2 but only 60 hours are used.

3) We have to again repeat the process shown in step 1 with one constraint changed which is Machine 1 labour hours now become 110 instead of 100.

And the final outcome with this constraints is as below

FILE HOME INSER PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER From Access o Welb [의 Connections AUETA Properties はEdit Links Clear Reapply Advanced Columns Fill Dupli From Other Existing Refresh All ▼ Sort Filter Text to Flash Remo From Text Sources ▼ Connections Get External Data Connections Sort & Filter G10 F G 4 Product1 Product2 Product3 Used Available Produced Machine1 Machine2 Profit 1.5 110 0.5 66仁 20 110 100 4 1 10 Objective Profit 12 13

So the net profitability = 2200-150 = 2050 which is slightly more than 2000 which we got in part1

4) sicne machine 2 is under utilized ( 60 instead of available 100 hours), adding capacity to mchine 2 will not be any help in increasing profitability instead it will add to cost only.

you can confirm with the below output by repeating steps in problem1 and changing constraint of Machine 2

H7 110 F G 4 Product1 Product2 Product3 Used Available Produced Machine1 Machine2 Profit 0 4 1 1.5 100仁 0.5 60仁 20 100 110 Obiective Profit 12 13 14

Actual profitability will be 2000-150 = $1850 which will be less than the original