Create an average ups shipping costs

Question

Discuss the below:

Case:

EMachine4All.com assembles and sells inexpensive laptop computers from industry standard parts which are generally purchased from the lowest bidder. Its strategy is to avoid direct competition with major producers such as Dell and Hewlett-Packard by targeting its product at budget-conscious individual consumers and small businesses. EMachine4All.com also promotes and sells its products only through the Web in order to keep marketing costs low and to avoid head-to-head competition with major retail outlets such as Fry's and Best Buy. As a result, most of its sales are made one unit at a time. A laptop is not assembled until after the order is received in order to minimize inventory and obsolescence cost. Each completed laptop is then shipped by UPS from the assembly plant directly to the customer. Lap-Tops-R-Us currently operates four assembly plants, all of which are located in the Southern U.S. and Mexico because of relatively low labor costs.

EMachine4All.com has built its reputation on shipping all orders in a week or less. So, if the company does not have enough overall internal capacity to fulfill an order in any given week, it will subcontract out the assembly task to a small electronics company near Asheville. On short notice the subcontractor picks up parts at the Ashville plant and then transports the assembled computers back to the Ashville plant for shipment to the customer. The short notice requirement expected of the subcontractor makes this option very expensive at $185 per computer.

This cost can be treated as the subcontractor's labor charge for analysis purposes; however, it also covers his overhead, cost of picking up parts and delivering assembled computers to the Ashville plant. It also covers his profit margin. But it does not include the cost of the parts or the shipping cost to the customer, both of which are paid for by EMachine4All.com. The company uses the subcontractor even though it loses money on every laptop they assemble because it knows its reputation will be damaged if it cannot fulfill all customer orders on a timely basis. The four plants and the subcontractor with their average operating costs and capacities are as follows. The subcontractor capacity is whatever is necessary to meet demand.

	Assembly labor cost $/unit	Parts cost, $/unit (including inbound transportation cost)	Fixed cost $/week	Capacity units/week
Asheville, NC	17	375	125,000	1,500
Meridian, MS	14	360	60,000	1,000
Round Rock, TX	18	350	240,000	2,500
Guadalajara, Mexico	10	320	120,000	2,000
Subcontractor	185	375

For marketing and accounting purposes, the company has divided the U.S. market into six geographic regions. These regions along with their average selling prices and forecasted weekly demand levels are as follows:

	Selling price $/unit	Demand units/week
North East (NE)	550	1,200
South East (SE)	450	1,100
North Central (NC)	500	1,400
South Central (SC)	525	1,000
North West (NW)	475	1,300
South West (SW)	500	1,500

 

Average UPS shipping costs, in dollars per unit, from any of the assembly plants (and the subcontractor) to any market area are as shown below. The shipping costs are included "free" to the customer.

 

From/To	NE	SE	NC	SC	NW	SW
Asheville	17	11	18	16	23	21
Meridian	18	12	19	15	21	20
Round Rock	20	18	19	13	22	17
Guadalajara	32	30	33	25	31	24
Subcontractor	17	11	18	16	23	21

1. On the same worksheet create a table like the one below to compute contribution per laptop from each plant and the subcontractor to each market area.

		NE	SE	NC	SC	NW	SW
Asheville	Sales
	Labor cost
	Parts cost
	Transportation
	Contribution
Meridian	Sales
	Labor cost
	Parts cost
	Transportation
	Contribution
Round Rock	Sales
	Labor cost
	Parts cost
	Transportation
	Contribution
Guadalajara	Sales
	Labor cost
	Parts cost
	Transportation
	Contribution
Subcontractor	Sales
	Labor cost
	Parts cost
	Transportation
	Contribution

2.  On the same Excel worksheet as above create a summary table of contributions per laptop, supply and demand like the one shown below.

	NE	SE	NC	SC	NW	SW	Supply
Asheville
Meridian
Round Rock
Guadalajara
Subcontractor
Demand

Set up an appropriate transportation matrix table in Excel. Use the summary contributions table, as above. Solve. Paste the solution table from Excel to create a table of the number of laptops shipped from each plant to each market area as shown below:

	NE	SE	NC	SC	NW	SW	Total
Asheville
Meridian
Round Rock
Guadalajara
Subcontractor
Total

4. Create a table in Excel, like the one below. Fill it in with weekly contribution, fixed cost, and operating profit generated at each of the plants. Also fill in the percent of capacity utilized at each plant.

Plant	Contribution	Fixed Cost	Operating profit	Percent of Capacity
Asheville
Meridian
Round Rock
Guadalajara
Subcontractor		XXXXXX		XXXXXXX
Total

5. Create a table in Excel, like the one below, filled in with the total weekly profit contribution (both in dollars and as a percent of total dollars) generated in each market area.

Market Area	Contribution Dollars	Contribution percent of total $
North East
South East
North Central
South Central
North West
South West
Total

Management is now proposing that capacity be expanded to 3,000 units per week at the Guadalajara plant in order to eliminate the use of the expensive subcontractor and to provide for future growth in demand. Guadalajara was selected because of its low labor and parts costs. The expansion is not expected to change any variable costs; however, weekly fixed costs at Guadalajara are expected to increase to $225,000.

6. Set up another transportation table in Excel. Copy the summary contributions into Excel. Solve. Paste the solution into Excel to create a table of the number of laptops shipped from each plant to each market area as shown below

	NE	SE	NC	SC	NW	SW	Dummy	Total
Asheville
Meridian
Round Rock
Guadalajara
Total

7.  Create another table in Excel, like the one below, after the Guadalajara expansion. Fill in the table with weekly contribution, fixed cost, and operating profit generated at each of the plants. Also fill in the percent of capacity utilized at each plant.

Plant	Contribution	Fixed Cost	Operating profit	Percent of Capacity
Asheville
Meridian
Round Rock
Guadalajara
Total

8. Create another table in Excel, like the one below, after the Guadalajara expansion. Fill in the table with the total weekly profit contribution (both in dollars and as a percent of total dollars) generated in each market area.

Market Area	Contribution Dollars	Contribution percent of total $
North East
South East
North Central
South Central
North West
South West
Total

9.  Based on the analysis, do you recommend expanding the Guadalajara plant? Why or why not?

After reading the Project case carefully, answer the following questions in a Word Document. Each answer should not be more than 700 words.

Q1. What is the business problem and what are you supposed to find out?

Q2. What is the "Objective" under the two different scenarios?

Q3. What would be the formula to calculate the "Objective Function" for the two different scenarios?

Q4. What are the various business constraints for the two different scenarios?

Q5. Describe your step-by-step solution strategy (your solution steps)

Q6. How would you decide and justify your choice between the two different scenarios?