Question

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 $310. The objective in developing a production schedule is to minimize 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 1 55 38 2 55 38 3 44 30 4 0 0 45 48 5 6 45 48 7 36 58 57 8 35 9 35 58

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 an Excel file and a report.

Answer 1

ANSWER

Following Formulas were used:

1. Column E (80% P-Head Demand for Next Week): =C4*80%
2. Column F (80% H-Head Demand for Next Week): =D4*80%
3. Column H (P-Head Unit Balance) Row 4: =C2-C4+(IF(B4="P-Heads",G4,0)) Column H (P-Head Unit Balance) Row 5:=H4-C5+(IF(B5="P-Heads",G5,0))
4. Column I (H-Head Unit Balance) Row 4: =F2-D4+(IF(B4="H-Heads",G4,0)), Column I (H-Head Unit Balance) Row 5: =I4-D5+(IF(B5="H-Heads",G5,0))
5. Column J (Production Cost): =IF(B4="P-Heads",G4*225,IF(B4="H-Heads",G4*310,0))
6. Column K (Inventory Carry Cost): =SUM(H4*225,I4*310)*19.5/(100*12*4)

_____________________________________________

If you have any query or any Explanation please ask me in the comment box, i am here to helps you.please give me positive rating.

*****************THANK YOU**************