In this assignment, you’ll formulate a product mix problem, solve it with Excel’s Solver add-in, and then do some sensitivity analysis.
JetPack Corp. manufactures five high-tech components (A, B, C, D, E) that it supplies to aerospace firms who have contracts with NASA. Each product must pass through four departments for various amounts of time (in hours) before it can be shipped, as seen in the table below, e.g., producing one unit of product A requires 0.5 hours of wiring, 3 hours of drilling, 2 hours of assembly, and 1 hour of inspection. Each product has a different unit profit, as well as a minimum and maximum production level over the next month to satisfy contract commitments.
Product
A B C D E
Wiring Dept. 0.5 1.5 2 1.5 0.5
Drilling Dept. 3 1 2 3 1
Assembly Dept. 2 3 2.5 1 2
Inspection Dept. 1 1 1 1 1
Unit Profit $90 $120 $130 $110 $100
Minimum Production Quantity 100 200 100 200 100
Maximum Production Quantity 300 400 400 300 200
Finally, the time available to make products in each department over the next month is limited: 1,500 hours are available in Wiring; 2,500 hours in Drilling; 2,600 hours in Assembly; and 1,200 in Inspection. Formulate this problem as a linear programming model in Excel.
Objective: Maximize the total profit from the production of the five products.
Constraints:
• JetPack cannot use more time than is available in each department over the next month
• JetPack must produce at least the minimum production quantity of each product
• JetPack must produce no more than the maximum production quantity of each product
Variables: How many of each product should JetPack make this month?
• Do not constrain the decision variables to be integer-valued.
Before having Solver find the optimal solution, make sure the direction of the inequality sign ( vs. ) in each constraint is correct. Also, in the Solver Parameters dialog: (1) carefully specify the Objective, Changing Variable, and Constraints cells; (2) check the box that says “Make Unconstrained Variables Non- Negative”; and (3) choose “Simplex LP” from the “Select a Solving Method” menu.
After finding the optimal solution, create a one-way SolverTable showing how the optimal product mix and its total profit changes as the drilling time per E increases from 1 hour to 3 hours in increments of 0.25 hours. If you can’t get SolverTable to run, you can still get the requested output by repeatedly solving the model with Solver, and copying/pasting the results into a table after each run.
To Do:
0. Please name your Excel file “A3-LastnameFirstname” and submit on iLearn by the due date.
1. Sheet 1 contains your LP model and shows the optimal solution found by Solver.
1. Sheet 2 contains the Sensitivity Report generated by Solver after finding the optimal solution.
2. Sheet 3 contains SolverTable output (or equivalent generated manually).
3. Sheet 4 holds brief answers to the questions below.
1a) What’s the optimal production plan, i.e., how many units of each product should JetPack make?
A* =
B*=
C*=
D*=
E*=
1b) What’s the total profit of the optimal production plan? $
1c) How many hours of drilling are used by the optimal production plan?
Answer questions 2-5:
2) Would the optimal production plan (i.e., the mix of products, not the objective function value) of (1a) change if the unit profit of product C increased from $130 to $138? Yes No
How can you predict this based on information available in the (original) Sensitivity Report?
3) Would the optimal production plan (i.e., the mix of products, not the objective function value) of (1a) change if the unit profit of product E fell from $100 to $94? Yes No
How can you predict this based on information available in the (original) Sensitivity Report?
4) By how many dollars would the optimal profit of (1b) change if JetPack had 1,600 hours of wiring time available this month rather than 1,500 hours?
How can you predict this based on information available in the (original) Sensitivity Report?
5) By how many dollars would the optimal profit of (1b) change if JetPack had only 1,150 hours of inspection time available this month rather than 1,200 hours? How can you predict this based on information available in the (original) Sensitivity Report?
6) Look at your SolverTable output and describe in a 3-4 sentences how the optimal product mix and its total profit changes as the drilling time per E increases from 1 to 3 hours in increments of 0.25 hours.
7) Write a constraint (using cell references) that would require the number of Cs produced to be at least twice the number of Es produced. (Do not add actually this constraint to your model.)
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
