Reference no: EM132337318
Assignment -
Purpose - To assess your ability to:
- Solve basic linear programming problems by using Excel Solver.
- Solve basic linear programming problems by using a graphical solution.
- Perform sensitivity analysis on linear programming solutions.
Overview - You will use the following information to complete this assignment.
The Ohio Metal Works (OMW) produces three main products from steel bars-- P1, P2 and P3. The features of these products are given below.
|
|
Profit (excluding labor cost) per foot of product
|
Minimum Weekly Production Requirement (feet)
|
|
P1
|
$0.017
|
218,000
|
|
P2
|
$0.019
|
114,000
|
|
P3
|
$0.020
|
111,000
|
One of the initial steps is a shaping operation performed by rolling machines. There are three machines available for this purpose-- RM1, RM2 and RM3. Their features are given by the following table.
|
Machine
|
Speed in Feet/Min
|
Products Processed in the Machine
|
Available Hours Per Week
|
Labor Cost/Operating Hour
|
|
RM1
|
150
|
P1, P2
|
35
|
$10
|
|
RM2
|
100
|
P2, P3
|
35
|
$15
|
|
RM3
|
75
|
P2, P3
|
35
|
$17
|
Also, the shipping department has a limit of 600,000 feet per week, regardless of thickness.
Action Items -
1. Determine the objective functions and constraints of this case if the objective of the OMW is to maximize the profits.
2. Create an Excel Solver model to solve the problem and answer the questions in Action Item 3. A help document and template for setting up the problem/case study have been provided here:
- Help Document
- Solution Template
3. Write a one- to two-page paper answering the following questions.
a. What are the objective functions and constraints of this case if the objective of the OMW is to maximize the profits?
b. What is the value of an additional hour of capacity on the rolling machine RM2?
c. What is the value of an additional two hours of capacity on the rolling machine RM1?
d. If the speed of rolling machine RM3 could be doubled without changing the labor cost, what would it be worth per week?
Hint:
i. Convert labor costs from "$/hour" to "feet/hour" when determining objective function.
ii. There are three main products, but since these products are processed in three different machines, there are actually six different products with different net profit values.
iii. Convert speed from "feet/min" to "hours/feet" when dealing with time constraints (available hours per week).
Attachment:- Case Study Assignment Files.rar