Charles Watts Electronics manufactures the following six peripheral devices used in computers especially designed for jet fighter planes: internal modems, external modems, graphics circuit boards, USB memory stick, hard disk drives, and memory expansion boards. Each of these technical products requires time, in minutes, on three types of electronic testing equipment as shown in the following table:
The first two test devices are available 130 hours per week. The third (device 3) requires more preventive maintenance and may be used only 100 hours each week. Watts Electronics believes that it cannot sell more than 2000, 1500, 1800, 1200, 1000, 1000 units of each device, respectively. Thus, it does not want to produce more than these units. The table that follows summarizes the revenues and material costs for each product:
In addition, variable labor costs are $16 per hour for test device 1, $12 per hour for test device 2, and $18 per hour for test device 3. Watts Electronics wants to maximize its profits.
a) Write a linear program for this problem.
b) Excel submission Write a spreadsheet for the problem and solve the problem using Excel Solver, following the guidelines given in the Excel Workbook that comes with this problem set.(Hint: the optimal value is $211666.9 .)
c) Use the Excel spreadsheet to answer the following questions:
(i) What is the value of an additional minute of time per week on test device 1? Test device 2? Test device 3? Should Watts Electronics add more test device time? If so, on which equipment?
(ii) Suppose that Watts Electronics is considering to increase the availability time of test device 2 for the next week. What would be the increase in the profit if the availability time increases to t for t = 131, 132, and 133. (Assume that there are still 130 hours of test device 1 and 100 hours of test device 3.) The increase is the difference between the new profit and the profit from Part (a).
(iii) Based on your answer to part (ii), what do you think will be the contribution if the availability time of test deice 2 increases to 135? (Verify that you are correct.) What is the formula for the optimum profit if the availability time increased by 130 + t? (You may assume that t is between 1 and 10).
(iv) Based on your formula in part (iii), what is the contribution if the availability time of test deice 2 increases to 150. Use Excel solver to see if the formula is correct. Use Excel solver to determine the maximum value of t for which your formula is correct (Be accurate to within an hour).
(v) How would the optimal solution in Part (b) change if the labor costs increased to $18 per hour for test device 1, $13 per hour for test device 2, and $20 per hour for test device 3?
(vi) Over what range of the labor cost for test device 3 will the optimal production mix in Part (b) remain optimal? (Be accurate to within one dollar.)