this is one whole question

1. 2. Using the College of Business Computer Lab and/or supporting software (MS Excel's SOLVER or LINDO), complete the following problem. Turn-in an electronic copy of your Model, Output Analysis, Drawings and Discussion with a Cover Sheet containing your Name, Student 1.D. Number, Date, Course and Section Number. You will not receive credit for work turned-in without a Cover Sheet. As you proceed with the computer assignment, save your work often on a (USB) storage device and upload it with the electronic copy of your assignment. 3. THE PROBLEM Bell Hardware, Inc. manufactures and distributes hand-held digital televisions. The company has three plants and three distribution facilities. The plants are located in Austin, TX; Seattle, WA; and St. Louis, MO. The distribution facilities are located in Houston, TX; Salt Lake City, UT; and Kansas City, MO. I Manufacturing costs differ between the company's production plants. The cost of each DTV produced at the St. Louis plant is $25.00. The Austin and Seattle plants utilize newer and more efficient equipment; as a result, manufacturing costs are $4.00 and $5.00 per DTV less than the cost at the St. Louis plant respectively. Over time, not much attention had been paid to the efficiency of the distribution system. However, due to the company's rapid demand growth and higher taxes for holding DTVs in its distribution facilities, Bell's management has decided that it is time to address the issue. The cost of shipping a DTV from each plant to each distribution facility and quarterly plant capacities are shown below. PLANTS CAPACITY St. Louis Austin DISTRIBUTION FACILITIES Houston Salt Lake City Kansas City $6 $8 $3 $3 $6 $6 35,000 55,000 Seattle $12 $4 $9 30,000 Holding costs per unit for the distribution facilities are $2.00 for the Salt Lake City facility; $4.00 for the Kansas City facility; and $4.50 for the Houston facility. The company serves four customer zones from the distribution facilities. The customer zones served include Chicago, IL; Dallas, TX; Denver, CO; and Los Angeles, CA. The forecast for the number of DTVs needed in each customer zone for the next quarter is shown below. CUSTOMER ZONE Chicago Dallas Denver Los Angeles FORECASTED DEMAND (in DTVs) 32,000 35,000 18,500 24,500 Shipping costs per DTV from each distribution facility to each customer zone are shown below. DISTRIBUTION FACILITIES Chicago Houston $8 Salt Lake City $6 Kansas City $3 1 CUSTOMER ZONES Dallas Denver $2 $5 $6 $2 $4 $4 Los Angeles $9 $4 $8 000 07 to a SITUATION I (Base Case) If the company continues to elect follow traditional transshipment modeling approach for fulfilling customer zone demand, what will its distribution plan and cost be for the following quarter? SITUATION II (Revised Case) Suppose that shipments between distribution facilities are permitted and that a direct shipment can be made from the Austin plant the Dallas customer Can of to zone. the cost the SITUATION II (Revised Case) Suppose that shipments between distribution facilities are permitted and that a direct shipment can be made from the Austin plant to the Dallas customer zone. Can the cost of the distribution plan be reduced? By how much? If so, what is the new distribution plan? What are the consequences? Can more costs be saved? If so, where? I Costs for the new routes are shown below. ROUTE COST $ 8 $8 PERMITTED ROUTES Houston to Salt Lake City Salt Lake City to Houston Kansas City to Salt Lake City Salt Lake City to Kansas City Houston to Kansas City Kansas City to Houston Austin to Dallas $6 $6 $5 $5 $1 un nos HINTS You should: 1. Develop a network model for the problem. 2. Formulate a linear programming model for the problem. 3. Use a computer software package (e.g. MS Excel's SOLVER or LINDO) to find an optimal distribution plan and cost. 4. Discuss the results of the problem. I 1. 2. Using the College of Business Computer Lab and/or supporting software (MS Excel's SOLVER or LINDO), complete the following problem. Turn-in an electronic copy of your Model, Output Analysis, Drawings and Discussion with a Cover Sheet containing your Name, Student 1.D. Number, Date, Course and Section Number. You will not receive credit for work turned-in without a Cover Sheet. As you proceed with the computer assignment, save your work often on a (USB) storage device and upload it with the electronic copy of your assignment. 3. THE PROBLEM Bell Hardware, Inc. manufactures and distributes hand-held digital televisions. The company has three plants and three distribution facilities. The plants are located in Austin, TX; Seattle, WA; and St. Louis, MO. The distribution facilities are located in Houston, TX; Salt Lake City, UT; and Kansas City, MO. I Manufacturing costs differ between the company's production plants. The cost of each DTV produced at the St. Louis plant is $25.00. The Austin and Seattle plants utilize newer and more efficient equipment; as a result, manufacturing costs are $4.00 and $5.00 per DTV less than the cost at the St. Louis plant respectively. Over time, not much attention had been paid to the efficiency of the distribution system. However, due to the company's rapid demand growth and higher taxes for holding DTVs in its distribution facilities, Bell's management has decided that it is time to address the issue. The cost of shipping a DTV from each plant to each distribution facility and quarterly plant capacities are shown below. PLANTS CAPACITY St. Louis Austin DISTRIBUTION FACILITIES Houston Salt Lake City Kansas City $6 $8 $3 $3 $6 $6 35,000 55,000 Seattle $12 $4 $9 30,000 Holding costs per unit for the distribution facilities are $2.00 for the Salt Lake City facility; $4.00 for the Kansas City facility; and $4.50 for the Houston facility. The company serves four customer zones from the distribution facilities. The customer zones served include Chicago, IL; Dallas, TX; Denver, CO; and Los Angeles, CA. The forecast for the number of DTVs needed in each customer zone for the next quarter is shown below. CUSTOMER ZONE Chicago Dallas Denver Los Angeles FORECASTED DEMAND (in DTVs) 32,000 35,000 18,500 24,500 Shipping costs per DTV from each distribution facility to each customer zone are shown below. DISTRIBUTION FACILITIES Chicago Houston $8 Salt Lake City $6 Kansas City $3 1 CUSTOMER ZONES Dallas Denver $2 $5 $6 $2 $4 $4 Los Angeles $9 $4 $8 000 07 to a SITUATION I (Base Case) If the company continues to elect follow traditional transshipment modeling approach for fulfilling customer zone demand, what will its distribution plan and cost be for the following quarter? SITUATION II (Revised Case) Suppose that shipments between distribution facilities are permitted and that a direct shipment can be made from the Austin plant the Dallas customer Can of to zone. the cost the SITUATION II (Revised Case) Suppose that shipments between distribution facilities are permitted and that a direct shipment can be made from the Austin plant to the Dallas customer zone. Can the cost of the distribution plan be reduced? By how much? If so, what is the new distribution plan? What are the consequences? Can more costs be saved? If so, where? I Costs for the new routes are shown below. ROUTE COST $ 8 $8 PERMITTED ROUTES Houston to Salt Lake City Salt Lake City to Houston Kansas City to Salt Lake City Salt Lake City to Kansas City Houston to Kansas City Kansas City to Houston Austin to Dallas $6 $6 $5 $5 $1 un nos HINTS You should: 1. Develop a network model for the problem. 2. Formulate a linear programming model for the problem. 3. Use a computer software package (e.g. MS Excel's SOLVER or LINDO) to find an optimal distribution plan and cost. 4. Discuss the results of the