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.
- 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:
The production available in each department each month and the minimum monthly production requirement to fulfill contracts are as follows:
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 27,500 and 14,450, 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.
The company also has the option to obtain additional units from a subcontractor, The ChrisJones Company, who has offered to supply up to 25,000 units per month in any combination of electronic and battery-powered models, at a cost of $22.75 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)