Instructions:
Create a model in Microsoft Excel and use it to solve the problem shown below based on the following narrative.
Narrative
Bakken Industries Corp. manufactures industrial valves for oil refineries. Three valves are scheduled for production this month: 102-A,102-B, and 102-C. The production of each of these valves consists of three parts. In the first part (casting) the body of the valve is created. In the second part (machining) the cast surfaces of the valve are finished and the inner flange is created. In the last part (assembly), the valve is assembled and inspected.
Key data regarding these values and the manufacturing process is shown in the following table:
|
Vabte
|
List pit.
per UM
|
VMS* costs
per unit
|
Fixed set-up
cats
|
Csstini
time Vass)
|
Mad:ring
time ChM)
|
Amen*
Uwe Mies)
|
|
102-A
|
$ 106.00
|
$ 36.00
|
$ 975.00
|
1.10
|
1.80
|
0.30
|
|
102-8
|
135.00
|
45.00
|
1,100.00
|
3.30
|
1.50
|
0.30
|
|
102-C
|
126.00
|
42.00
|
1,150.00
|
3.00
|
1.20
|
0.30
|
The plant manager has informed you that manufacturing time will be limited in the upcoming month. A maximum of 500 hours is available in the Casting Department, 500 hours in the Machining Department, and 100 hours in the Assembly Department.
A memorandum from the sales manager states that a minimum of 25 102-B valves must be manufactured to meet a contractual commitment.
Problem
1. Create a Microsoft Excel model in good form that captures the appropriate inputs and performs the necessary calculations. How many of each valve should be produced this month to maximize profits?
2. Create an answer report for your model.