i need help putting this in excel

7-3 is a solved problem that needs to be put into an excel spread sheet

LUU yas. LO 7-3 SOLVEI SOLVED PROBLEM 2 manufacturer makes a product for a customer that consists of two items, a cable ndard RCA connectors and a cable with a mini-plug, which are then packaged together as the final product (each product sold contains one RCA and one mini-plug cable). The man- ufacturer makes both cables on the same assembly line and can only make one type at a time: ther it can make RCA cables or it can make mini-plug cables. There is a setup time when switching from one cable to the other. The assembly line costs $500/hour to operate, and this rate is charged whether it is being set up or actually making cables. 162 Manufacturing and Service Processes of the mini-plug Current plans are to make 100 units of the RCA cable, then 100 units of the mini cable, then 100 units of the RCA cable, then 100 units of the mini-plug cable, and so where the setup and run times for each cable are given as follows. Component Run Time/Unit Setup/Changeover Time RCA cable 5 minutes 0.2 minute Mini-plug cable 10 minutes 0. 1 minute Assume the packaging of the two cables is totally automated and takes only two seconds per unit of the final product and is done as a separate step from the assembly line. Since the packaging step is quick and the time required does not depend on the assembly-line batch size its cost does not vary and need not be considered in the analysis. What is the average hourly output in terms of the number of units of packaged product (which includes one RCA cable and one mini-plug cable)? What is the average cost per unit Tor assembling the product? If the batch size were changed from 100 to 200 units, what would be the impact on the assembly cost per unit? Solution The average hourly output rate when the batch size is 100 units is calculated by first calculating the total time to produce a batch of cable. The time consists of the setup + the run time for a batch 5 + 10+ 0.2(100) + 0.1(100) = 15 + 30 = 45 minutes/100 units So if we can produce 100 units in 45 minutes, we need to calculate how many units can be produced in 60 minutes, we can find this with the following ratio: 45/100 = 60/X Solving for X: X = 133.3 units/hour The cost per unit is then $500/133.3 = $3.75/unit If the batch size were increased to 200 units: 5 + 10 +0.2(200) + 0.1(200) = 15 + 60 = 75 minutes/200 units 75/200 = 60/X X = 160/hour $500/160 = $3.125/unit Questions 7-1 1. What is meant by a process? Describe its important features. 2. What is a customer order decoupling point? Why is it important? 3. What's the relationship between the design of manuf tegic competitive dimana Marturing Pro LO7-3 Analyze simple manufacturing processes. Summary Vie charts can be manufactur ing process nous. Some common charts are assem by drawings sembly charts, route shorts and Flowcharts are of very informative in business endeavors. They provide a simple but insightful analy- sis of the capacity of a process and the variable cost to produce each unit of product Solved Problems LO - SOLVED PROBLEM 1 An mobile manufacturer is considering a change in an assembly line that should save money by reducing labor and material cost. The change involves the installation of four new robots that will automatically install windshields. The cost of the four robots, including instal lation and initial programming is $400,000. Current practice is to amortize the initial cost of robots over two years on straight line basis. The process engineer estimates that one full- time technician will be needed to monitor, maintain and reprogram the robots on an ongoing basis. This person will be paid approximately $60,000 per year. Currently, the company uses four full-time employees on this job and each makes about 552.000 per year. One of these employees is a material handler and this person will still be needed with the new process. To complicate matters, the process engineer estimates that the robots will apply the windshield sealing material in a manner that will result in a wavings of $0.25 per windshield installed. How many automobiles need to be produced over the next two years to make the new robots an attractive investment? Due to the relatively short time horizon, do not consider the time value of money VE Solution The cost of the current process over the next two years is just the cost of the four full-time employees 552.000 employce x 4 employees x 2 years = $416.000 The cost of the new process over the next two years, assuming the robot is completely costed over that time, is the following: (552,000/material handler + $60,000/technician) x 2 + $400,000/robots - $0.25 x autos Equating the two alternatives: 5416,000 = $624.000 - 50.25 x autos Solving for the break-even point: -$208,000 - 50.25 = 832,000 autos This indicates that, to break even, 832,000 autos would need to be produced with the robots over the next two years. LO 7-3 SOLVED PROBLEM 2 A contract manufacturer makes a product for a customer that consists of two items, a cable with standard RCA connectors and a cable with a mini-plug, which are then packaged together as the final product (each product sold contains one RCA and one mini-plug cable). The man- ufacturer makes both cables on the same assembly line and can only make one type at a time: either it can make RCA cables or it can make mini-plug cables. There is a setup time when switching from one cable to the other. The assembly line costs $500/hour to operate, and this rate is charged whether it is being set up or actually making cables. Manufaturi und Service Process of the mini-pl cable, and so on Current plans are to make 100 units of the RCA cable, then h 100 units of the RCA cable, then 100 units of the mini-plug cable where the setup and run times for each cable are given as follows. Se Changeover Time takes only two seconds assembly line. Since the y-line batch size Run Time Unit Component 0.2 minute RCA Cable 5 minutes 0 minute M ugabe 10 minutes Assume the packaging of the two cables is totally automated and takes only two per of the final product and is done as a separate step from the assembly line si packaging step is quick and the time required does not depend on the assembly-line batch its cost does not vary and need not be considered in the analysis. What is the average hourly output in terms of the number of units of packaged prex (which includes one RCA cable and one mini-plug cable)? What is the average cost per un for assembling the product? If the batch size were changed from 100 to 200 units, what woul he the impact on the assembly cost per unit? Solution The average hourly output rate when the batch size is 100 units is calculated by first calculating the total time to produce a batch of cable. The time consists of the setup + the run time for a batch: 5 + 10+ 0.2(100) + 0.1(100) = 15+ 30 = 45 minutes/100 units So if we can produce 100 units in 45 minutes, we need to calculate how many units can be produced in 60 minutes, we can find this with the following ratio: 45/100 = 60/X Solving for X X = 133.3 units/hour The cost per unit is then S500/133.3 $3.75/unit If the batch size were increased to 200 units: 5 +10 +0.2(200) +0.1(200) = 15 + 60 = 75 minutes/200 units 75/200 = 60/X X = 160/hour $500/160 = $3.125/unit Discussion Questions LO 7- 1. What is meant by a process? Describe its important features 2. What is a customer order decoupling point? Why is it important? 3. What's the relationship between the design of a manufacturing process and the firm's stra tegic competitive dimensions (Chapter 2)? 07-Z 4. What does the product-process matrix tell us? How should Witch nese restau- Tant be structured? 5. It has been noted that, during World War II, Germany its formidable Tiger tanks produced by dable IS 1