MSDA 628 Supply Chain Business Analytics
Module 1 Assignment
General requirement
• Make sure that each problem/sub-problem is on its own worksheet.
• Your Excel model must be executable. If your excel model is ran, it should generate the same results you submit. That means the spreadsheet has correct embedded formulae and Solver dialog boxes have all the inputs. If ‘Solve’ is clicked and your spreadsheet does not produce the reported results, a zero score will be assigned to that problem.
• You should use lecture examples as templates to solve the assignment problems. • Name the Excel file as “Homework 1_Last Name.xlsx”.
• Please contact the instructor if you have any questions regarding the assignment.
Problem 1
The Weinberger Electronics Corporation manufactures four highly technical products that it supplies to aerospace firms that hold NASA contracts. Each of the products must pass through the following departments before being shipped: wiring, drilling, assembly, and inspection. The time requirement in hours for each unit produced and its corresponding profit value are summarized in the following table:
PRODUCT
DEPARTMENT XJ201 XM897 TR29 BR788
Wiring 0.5 1.5 1.5 1.0
Drilling 0.3 1.0 2.0 3.0
Assembly 0.2 4.0 1.0 2.0
Inspection 0.5 1.0 0.5 0.5
Unit Profit ($) 8.0 10.0 11.0 9.0
The production available in each department each month and the minimum monthly production requirement to fulfill contracts are as follows:
DEPARTMENT CAPACITY
(HOURS) PRODUCT
MINIMUM PRODUCTION LEVEL
Wiring 16,000 XJ201 5000 Drilling 17,500 XM897 3000 Assembly 27,000 TR29 2000 Inspection 10,000 BR788 500
1
The production manager has the responsibility of specifying production levels for each product for the coming month. Use Excel spreadsheet to solve Weinberger’s profit-maximization problem using LP.
Problem 2
A sudden increase in the demand for smoke detectors has left Hanson Alarms with insufficient capacity to meet demand. The company has seen its monthly demand for electronic and battery powered smoke detectors rise to 25,000 and 12,500, respectively. Tommy Hanson, the CEO of Hanson Alarms, has told his COO Julia Hanson that the company must always meet demand for its products. Hanson’s production process involves three departments: fabrication, assembly, and shipping. The data for production and prices is summarized below.
Hours required to manufacture 1 unit
DEPARTMENT Electronic powered Battery powered Monthly Hours Available
Fabrication 0.25 0.15 3,000 Assembly 0.30 0.25 4,500 Shipping 0.18 0.20 3,200 Variable cost/unit $18.00 $15.80
Retail price $29.50 $27.50
The company also has the option to obtain additional units from a subcontractor, The ChrisJones Company, who has offered to supply up to 21,000 units per month in any combination of electronic and battery-powered models, at a cost of $21.5 per unit. For this price, ChrisJones will assembly, test, and ship its models directly to the distributors without using Hanson’s production process. Use Excel spreadsheet to determine the optimal quantities to make and/or buy that will maximize profit.
(Hint: For making or buying a product, Unit profit = Unit retail price – Unit Cost)
2