maximize revenue
Pettibone Traverse Lift in Baraga is trying to determine if they should be making some of their machine frames in-house versus buying them from external suppliers. Engineering has determined internal consumption rates of steel and labor to produce each frame plus a setup cost required to create the frame production line. There is a demand 26, 26, 52, 12 respectively for the Cary-Lift, Speed Swing, Extendo and Traverse model frames. Labor cost is $36 per hour with a $18 per hour overhead rate. There are 1,200 hours of labor available during the planning period. Currently steel cost $1.98 per pound. The following table describes the revenue per machine; outsource cost per frame and consumption for each frame if made internally.
Cary-Lift | Speed Swing | Extendo | Traverse | |
Selling price | $36,000 | $37,500 | $48,000 | $51,500 |
Sub 1 cost each | $8,900 | $9,200 | ||
Sub 2 cost each | $10,500 | $10,900 | ||
Steel (lbs) | 3600 | 4400 | 5000 | 5400 |
Labor (hrs) | 17 | 21 | 22 | 28 |
Setup cost | $22,100 | $22,100 | $36,000 | $36,000 |
Create an Excel model to determine which models Pettibone should setup to make internally and buy to maximize revenue.
12 years ago
Purchase the answer to view it
maximize_revenue.xlsx
