Buckeye Manufacturing produces heads for engines used in the m…
Buckeye Manufacturing produces heads for engines used in the manufacture of trucks. The production line is highly complex, and it measures 900 feet in length. Two types of engine heads are produced on this line: the P-Head and the H-Head. The P-Head is used in heavy duty trucks and the H-Head is used in smaller trucks. Because only one type of head can be produced at a time, the line is set up to manufacture either the P-Head or the H-Head, but not both. Changeovers are made over a weekend; costs are $500 in going from a setup for the P-Head to a setup for the H-Head, and vice versa. When set up for the P-Head, the maximum production rate is 100 units per week and when set up for the H-Head, the maximum production rate is 80 units per week. Buckeye just shut down for the week after using the line to produce the P-Head. The manager wants to plan production and changeovers for the next eight weeks. Currently Buckeye’s inventory consists of 125 P-Heads and 143 H-Heads. Inventory carrying costs are charged at an annual rate of 19.5 percent of the value of inventory. The production cost for the P-Head is $225, and the production cost for the H-Head is S310. The objective in developing a production schedule is to minimise the sum of production cost, plus inventory carrying cost, plus changeover cost. Buckeye received the following requirements schedule from its customer (an engine assembly plant) for the next nine weeks:
|Product Demand Week||P-Head||H-Head|
Safety stock requirements are such that week-ending inventory must provide for at least 80 percent of the next week’s demand.
Managerial Report Prepare a report for Buckeye’s management with a production and changeover schedule for the next eight weeks. Be sure to note how much of the total cost is due to production, how much is due to inventory, and how much is due to changeover. Using Microsoft Excel to solve this problem. Submit a Excel file and a report.
Click the button below to view answer!
Technifi Expert’s Answer:
Details and Formulas to generate the Excel File:
Demand for Next Week
80% P-Head Demand for Next Week -> Column E: =C4*80%
80% H-Head Demand for Next Week -> Column F: =D4*80%
Balance Unit – P-Head Unit Balance
Column H – Row 4: =C2-C4+(IF(B4=”P-Heads”,G4,0))
Column H – Row 5: =H4-C5+(IF(B5=”P-Heads”,G5,0))
Balance Unit – H-Head Unit Balance
Column I – Row 4: =F2-D4+(IF(B4=”H-Heads”,G4,0))
Column I – Row 5: =I4-D5+(IF(B5=”H-Heads”,G5,0))
Column J: =IF(B4=”P-Heads”,G4*225,IF(B4=”H-Heads”,G4*310,0))
Inventory Carry Cost
Column K: =SUM(H4*225,I4*310)*19.5/(100*12*4)
If you happen to run into some problem while following the steps, please make sure to let us know in the comment section below, and we’ll do our best to solve it. Apart from that, you can contact us on Facebook and Twitter, however we can’t guarantee a rapid reaction time over those platform