Answered step by step
Verified Expert Solution
Question
1 Approved Answer
This doesn't intend to submit a tutor's work as my own work. I just like a step-by-step guide formula to solve the color yellow texts
This doesn't intend to submit a tutor's work as my own work. I just like a step-by-step guide formula to solve the color yellow texts that are shown in excel. I have trouble finding out how.
Here is the screenshot from excel.
A B C D E F G H K L IN Production Demand safety stock P N weeks P H P H the cost of production 225 310 A W 38 44 30.4 maximum weekly rate 100 80 55 38 35.2 24 changover cost 500 500 6 44 30 0 Weekly Inv.rate 0.00375 0.00375 UPWN 0 0 36 38.4 Weekly Inv.cost 0.84375 1.1625 40 00 45 48 36 38.4 Beginning Inv. 125 143 6 45 48 28.8 46.4 10 7 36 58 28 45.6 11 35 57 28 46.4 12 35 58 13 14 The Model 15 Week P H Inv.P Inv.H Setup P The Changeover 16 18.00 0.00 88.00 105.00 1.00 0.00 17 100.00 0.00 133.00 67.00 1.00 0.00 production cost 117374 W N 18 100 0.00 189.00 37.00 1.00 0.00 Inv.Cost 1280.35 19 4 0.00 1.40 189.00 38.40 0.00 1.00 changeover costs 500 20 5 0.0 48.00 144.00 38.40 0.00 0.00 The minimum total cost 119154 21 6 0.00 56.00 99.00 46.40 0.00 0.00 22 7 0.00 57.20 63.00 45.60 0.00 0.00 23 0.00 57.80 28.00 46.40 0.00 0.00 24 Inventory Balance 25 Beginning Inv. +Production 26 Production Capacity Ending Inv 27 Weeks P H P H Week To P if 1 To H if 1 28 100 55 38 1 0.00 0.00 29 100 55 38 0.00 0.00 30 W N 100 0 44 30 3 0.00 0.00 31 0 80 0 0 -1.00 1.00 32 80 45 48 5 0.00 0.00 33 80 45 48 0.00 0.00 34 80 36 58 0.00 0.00 35 80 35 57 0.00 0.00Production Sd'Ieduling and Change over Costs 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 HHead. The PHead is used in heavy duty tnscks ad the H-Head is used in smaller trucks. Because only one type of head can he produced at a time, the line is either set up to manufacture the Phead or the H-Head but not both. Changeovers are made oer a weekend; costs are 550:: in going from a setup for the P-l-Iead to a setup for the H-Head, and vice versa. il'll'hen set up for the Phead, the maximum production rate is 100 units per week and when set up for the H-Head, the maximum production rate is 30 per week. Buckeye Just shut down for the week after using the line to produce the PI-lead. The manager wants to plan production and changeovers for the next eight weeks. Currently, Buckeye's inventory consists of 125 PI-lead and 143 H-l-leads. Inventory carrying costs are charged at annual rate of 19.5% of the value of the inventory. The production cost for the PHead Is 5225, and the production cost for the l-I-Head Is 5310. The objective is developing a production schedule is to minimize the sum of production oost, plus inventory carrying cost, plus changeover cost. Buckeye received the following requirements schedule form Its customer {an engine assembly plant} for the I'IEitt nine weeks Product Demand Week P-l-lead I-I-I-lead 1 55 SB 2 55 BB 3 44 3t} 4 D 0 5 45 4B 6 45 4B 3* 36 53 B 35 5? 9 35 53 Safety stock requirements are such that week-ending inventory must provide for at least 80% of the next week's demand. Prepare a report for Buckeye's management with a production and changeover schedule for the next eight weeks. He 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 changeoverStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started