Question

Instructions:

You must complete the questions below. All will be included in a SINGLE PDF file and turned into Blackboard or handed at the start of class. The due date is specified on the syllabus. Remember, late work is not accepted. You will need an Executive Summary, PowerPoint (one to page/color) and Excel spreadsheet. Be prepared to present your findings in class.

Purpose:

You must evaluate the various options to ship freight from Asia to the USA. You MUST find the overall lowest cost solution and evaluate other items that could affect your decision. At the end, you will have a better understanding of the challenges of international routing and costs.

Situation:

Henry Lee is the Vice President of Purchasing for the consumer electronics division of the Major Electric Corporation (MEC). The company recently introduced a new type of ditigal video camcorder that has taken the market by storm. While Henry is pleased with the strong demand for this product in the marketplace, it has been a challenge to keep up with MEC's distributors' orders of this camcorder. His current challenge is how to meet requests from MEC major distributors in Pittsburgh, Denver, Baltimore, and Houston who have placed orders of 15,000, 25,000, 35,000, and 25,000 units, respectively, for delivery in two months. (There is a one-month manufacturing and a one-month shipping lead-time for this product.)

MEC has contracts with companies in Hong Kong and Singapore who manufacture camcorders for the company under the MEC label. These contracts require MEC to order a specified minimum number of units each month at guaranteed per unit cost. The contracts also specify the maximum number of units that may he ordered at this price. The following table summarizes these contracts:

Monthly Purchasing Contract Provisions

Supplier	Unit Cost	Minimum Required	Maximum Allowed
Hong Kong	$370	20,000	40,000
Korea	$390	25,000	50,000
Singapore	$365	15,000	40,000

MEC also has a standing contract with a shipping company to transport product from each of these suppliers to ports in San Francisco and San Diego. The cost of shipping from each supplier to each port is given in the following table along with the minimum required and maximum allowable number of shipping cartons each month: (see top of next page)

Under the terms of this contract, MEC guarantees it will send at least 20 but no more than 65 shipping containers to San Francisco each month and at least 30 but no more than 70 shipping containers to San Diego each month.

Monthly Shipping Contract Provisions

		San Francisco Requirements												San Diego Requirements
			Cost per Cont		Min # Cont		Max # Cont							Cost per Cont		Min # Cont	Max # Cont
	HK			$2,100		5		25					$2,400		5			25
	Korea			$1,900		10		35					$2,200		10			35
	Singapore			$2,500		5		30					$2,300		5			20

Each shipping container can hold 1,000 video cameras and will ultimately be trucked from the seaports to the distributors. Again, MEC has a standing contract with a trucking company to provide trucking services each month. The cost of trucking a shipping container from each port to each distributor is summarized on the next page.

Unit Shipping Cost per Container

	Pittsburgh	Denver	Baltimore	Houston
San Francisco	$1,200	$950	$1,300	$1,100
San Diego	$1,300	$1,100	$1,200	$900

As with the other contracts, to obtain the prices given above, MEC is required to use a certain minimum amount of trucking capacity on each route each month and may not exceed certain maximum shipping amounts without incurring cost penalties. These minimum and maximum shipping restrictions are summarized below:

Minimum Required and Maximum Allowable
Number of Shipping Containers per Month

	Pittsburgh		Denver		Baltimore		Houston
	Min	Max	Min	Max	Min	Max	Min	Max
San Francisco	3	19	6	20	10	24	5	20
San Diego	4	15	5	18	5	25	10	30

So Henry is left with the task of sorting through all this information to determine the least costly purchasing and distribution plan to fill the distributors' requests. But because he and his wife have tickets to the symphony for this evening, he has asked you to take a look at this problem and give him your recommendations at 9:00 tomorrow morning.

What is the optimal solution that minimizes the total cost for the company? Be sure to show all your work. A suggestion is that you review your network modeling problems and use solver of the computer. You will work in your assigned groups to complete this assignment.

Also, be sure to identify other issues that may affect your solution!!

Requirements:

1.Identify the lowest cost solution that meets the requirements:

a.Turn in your PowerPoint and be sure to include a network flow diagram of the problem to include all lanes and costs (a map as part of the answer would be helpful to you and me)

b.Input all the data into Excel and find the lowest cost solution using Solver

c.Print out your “Solver Parameters” portion as part of your packet

d.Include an Executive Summary of the case.

e.NOTE: You are not required to recommend the lowest cost, but must demonstrate that you found it during the process

f.You will turn in ONE PAPER PACKET file with all three requirements. If you wish to attach an additional page for the specific questions or answer them in the above that is your choice.

2.Answer the following questions:

What is the lowest cost total solution?

What other factors would you consider beyond cost given this situation? Country political relationships, routes prone to natural disasters, areas where traffic is a problem

What other steps would you recommend long term to improve your solution?

Are there any unique solutions you might consider?

Answer 1

Network representation of the problem is as follows

Solution using network flow model is as follows

Formula:

J40 =SUMIF($B$39:$B$52,I40,$D$39:$D$52) copy to J40:J44

J45 =SUMIF($B$39:$B$52,I45,$D$39:$D$52)-SUMIF($C$39:$C$52,I45,$D$39:$D$52)   copy to J45:J46

J47 =SUMIF($C$39:$C$52,I47,$D$39:$D$52)   copy to J47:J50

L52 =SUMPRODUCT(D39:D52,E39:E52)

L53 =SUMPRODUCT(J40:J42,M40:M42)

L54 =SUM(L52:L53)

Total cost of the solution = $ 37,630,800