Answered step by step
Verified Expert Solution
Question
1 Approved Answer
please show the work in excel Problem 3: Bennet and Daughter Case Bennet and Daughter Inc, is a manufacturer of small electric motors. They originally
please show the work in excel
Problem 3: Bennet and Daughter Case Bennet and Daughter Inc, is a manufacturer of small electric motors. They originally started their manufacturing plant in Longbourne, MA. Since inception they have grown to become one of the top suppliers of small electric motors in all of New England. Distribution Centers: They started their first distribution center near New London, RI and eventually started a second one in Harrisburg, PA and third one recently in Peoria, IL. They also recently opened a manufacturing plant near Canton, OH. The Manufacturing plants: The costs of making the motors depends on the plant. The cost of manufacturing a motor in the Canton plant is $11.00. The Longbourne plant uses older technologies and so the cost of manufacturing there is higher and is $11.75/ motor. The product ion capacity at the Longbourne plant is 15,000 motors/quarter and the production capacity at the Canton plant is 25000 motors/quarter. There are no shipments allowed from the Canton plant to the New London distribution center. The shipping costs from the plants to the distribution centers are as follows: Demand Locations: The company serves seven market locations. The expected demand at each location is as follows: The cost of shipping from each distribution center to each customer location is given below. Note that some of the distribution centers can't serve some of the demand locations. Those pairs are indicated by a dash. The problem is to figure out the optimal way to manufacture and distribute the motors to satisfy all the demand. We have to find out which plants serve which distribution centers and which distribution centers serve which demand locations. The Report: Make recommendations to Mr. Bennet and his daughter Elizabeth that optimizes their supply chain. Your report should include the following: 1. If the company doesn't change its supply chain what will the total cost of manufacturing and transportation be the next quarter? Are there any plants that have excess capacity? If so, by how much? 2. If the company is willing to drop the distribution center requirement and let any distribution center serve any customer zone what is the total cost? (The new transportation cost table is given below). Are there any plants that have excess capacity? If so, bv how much Step 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