logo Use SA10RAM to get 10%* Discount.
Order Now logo

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

expert
Dawn WardManagement
(5/5)

694 Answers

Hire Me
expert
Kyle BaileyFinance
(4/5)

659 Answers

Hire Me
expert
Ishanat TripathiTechnical writing
(5/5)

707 Answers

Hire Me
expert
Vishal FogatComputer science
(5/5)

588 Answers

Hire Me
Excel
(5/5)

In this assignment, you’ll formulate a product mix problem, solve it with Excel’s Solver add-in, and then do some sensitivity analysis.

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

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.)

 

(5/5)
Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

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

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme

Get Free Quote!

405 Experts Online