Please help! Confused on how to solve this problem. EMachine4All.com assembles a
ID: 2747140 • Letter: P
Question
Please help! Confused on how to solve this problem.
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. Copy the three tables, above, into an Excel worksheet. This document is available on Blackboard. 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 a transportation table in Excel. Copy the summary contributions table, above, 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
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?
Deliverable: Your Project Report should have, in order:
A cover sheet
The answer to part 9
All the Excel tables you created, in order
Solved Output by Excel Solver – The final optimized table
Print Out of the screen shot of the Solver pop-up window that shows the cells and the constraints
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
Explanation / Answer
Asheville
1)Particulars:::::::::::::::::::NE:::::::::::SE::::::::::::NC:::::::::::SC::::::::::::NW:::::::::::::SW
Sales:::::::: :::::::::$660,000:::$495,000::::$700,000::$525,000::$617,500:::$750,000
Less
Labor cost::::::::::::$20,400:::::::$18,700::::::$23,800:::::$17,000::::$22,100::::$25,500
Parts cost::::::::::$450,000::::$412,500::::$525,000::::::$375,000:::$487,500::$562,500
Transportation::::::$20,400:::::$12,100:::::$25,200:::::::::$16,000::::$29,900:::::$31,500
________________________________________________________________________
Contribution:::::::$169,200::::$51,700::::::::$126,000:::::$117,000:::$78,000::::$130,500
Sales:
NE = 1,200*550=$660,000
SE=1,100*450=$495,000
NC=1,400*500=$700,000
SC=1,000*525=$525,000
NW=1,300*475=$617,500
South West=1,500*500=$750,000
Labor Cost
NE = 1,200*$17=$20,400
SE=1,100*$17=$18,700
NC=1,400*$17=$23,800
SC=1,000*$17=$17,000
NW=1,300*$17=$22,100
South West=1,500*$17=$25,500
Parts cost:
NE = 1,200*375=$450,000
SE=1,100*375=$412,500
NC=1,400*375=$525,000
SC=1,000*375=$375,000
NW=1,300*375=$487,500
South West=1,500*375=$562,500
Transportation:
NE = 1,200*17=$20,400
SE=1,100*$11=$12,100
NC=1,400*$18=$25,200
SC=1,000*$16=$16,000
NW=1,300*$23=$29,900
South West=1,500*$21=$31,500
__________________________________________________________________________________
Meridian
Particulars:::::::::::::::::::NE:::::::::::SE::::::::::::NC:::::::::::SC::::::::::::NW:::::::::::::SW
Sales:::::::: :::::::::$660,000:::$495,000::::$700,000::$525,000::$617,500:::$750,000
Less
Labor cost::::::::::::$16,800::::::$15,400::::::$19,600::::$14,000::::::$18,200:::::$21,000
Parts cost::::::::::$450,000:::$412,500:::::$525,000::::$375,000::::$487,500::::$562,500
Transportation::::::$21,600:::$13,200::::::$26,600::::::$14,000:::::::$27,300::::::$$30,000
________________________________________________________________________
Contribution:::::::$171,000::::$53,900:::::::$128,800::::$122,000::::$84,500:::::$136,500
____________________________________________________________________________
Sales:
NE = 1,200*550=$660,000
SE=1,100*450=$495,000
NC=1,400*500=$700,000
SC=1,000*525=$525,000
NW=1,300*475=$617,500
South West=1,500*500=$750,000
Labor Cost
NE = 1,200*14=$16,800
SE=1,100*14=$15,400
NC=1,400*$14=$19,600
SC=1,000*$14=$14,000
NW=1,300*$14=$18,200
South West=1,500*$14=$21,000
Parts cost:
NE = 1,200*$360=$450,000
SE=1,100*$360=$412,500
NC=1,400*$360=$525,000
SC=1,000*$360=$375,000
NW=1,300*$360=$487,500
South West=1,500*$360=$562,500
Transportation:
NE = 1,200*18=$21,600
SE=1,100*$12=$13,200
NC=1,400*$19=$26,600
SC=1,000*$15=$15,000
NW=1,300*$21=$27,300
South West=1,500*$20=$30,000
_____________________________________________________________________________
Round Rock, TX
Particulars:::::::::::::::::::NE:::::::::::SE::::::::::::NC:::::::::::SC::::::::::::NW:::::::::::::SW
Sales:::::::: :::::::::$660,000:::$495,000::::$700,000::$525,000::$617,500:::$750,000
Less
Labor cost::::::::::::$21,600:::::$19,800::::::$25,200:::$18,000::::::$23,400:::::$27,000
Parts cost::::::::::$420,000:::$385,000:::::$490,000::::$350,000::::$487,500::::$525,000
Transportation:::::$24,000:::$19,800::::::$26,600::::::$13,000::::::28,600::::::$25,500
________________________________________________________________________
Contribution:::::::$194,400::::$70,400:::::::$158,200::::$144,000::::$78,000:::::$172,500
____________________________________________________________________________
Sales:
NE = 1,200*550=$660,000
SE=1,100*450=$495,000
NC=1,400*500=$700,000
SC=1,000*525=$525,000
NW=1,300*475=$617,500
South West=1,500*500=$750,000
Labor Cost
NE = 1,200*$18=$21,600
SE=1,100*$18=$19,800
NC=1,400*$18=$25,200
SC=1,000*$18=$18,000
NW=1,300*$18=$23,400
South West=1,500*$18=$27,000
Parts cost:
NE = 1,200*$350=$420,000
SE=1,100*$350=$385,000
NC=1,400*$350=$490,000
SC=1,000*$350=$350,000
NW=1,300*$350=$487,500
South West=1,500*$350=$525,000
Transportation:
NE = 1,200*$20=$24,000
SE=1,100*$18=$19,800
NC=1,400*$19=$26,600
SC=1,000*$13=$13,000
NW=1,300*$22=$28,600
South West=1,500*$17=$25,500
____________________________________________________________________________
Guadalajara
Particulars:::::::::::::::::::NE:::::::::::SE::::::::::::NC:::::::::::SC::::::::::::NW:::::::::::::SW
Sales:::::::: :::::::::$660,000:::$495,000::::$700,000::$525,000::$617,500:::$750,000
Less
Labor cost::::::::::::$12,000:::::$11,000::::::$14,000:::$10,000::::::$13,000:::::$15,000
Parts cost::::::::::$384,000:::$352,000:::::$448,000:::$320,000::::$416,000::::$480,000
Transportation:::::$38,400:::::$33,000:::::$46,200:::::$25,000::::::$40,300:::::::::$36,000
________________________________________________________________________
Contribution:::::::$225,600::::$99,000:::::::$191,800::::$170,000::::$148,200:::::$219,000
____________________________________________________________________________
Sales:
NE = 1,200*550=$660,000
SE=1,100*450=$495,000
NC=1,400*500=$700,000
SC=1,000*525=$525,000
NW=1,300*475=$617,500
South West=1,500*500=$750,000
Labor Cost
NE = 1,200*$10=$12,000
SE=1,100*$10=$11,000
NC=1,400*$10=$14,000
SC=1,000*$10=$10,000
NW=1,300*$10=$13,000
South West=1,500*$10=$15,000
Parts cost:
NE = 1,200*$320=$384,000
SE=1,100*$320=$352,000
NC=1,400*$320=$448,000
SC=1,000*$320=$320,000
NW=1,300*$320=$416,000
South West=1,500*$320=$480,000
Transportation:
NE = 1,200*$32=$38,400
SE=1,100*$30=$33,000
NC=1,400*$33=$46,200
SC=1,000*$25=$25,000
NW=1,300*$31=$40,300
South West=1,500*$24=$36,000
__________________________________________________________________________________
Subcontractor
Particulars:::::::::::::::::::NE:::::::::::SE::::::::::::NC:::::::::::SC::::::::::::NW:::::::::::::SW
Sales:::::::: :::::::::$660,000:::$495,000::::$700,000::$525,000::$617,500:::$750,000
Less
Labor cost::::::::::::$222,000:::$203,500::::$259,000:::$185,000::::$240,500:::$277,500
Parts cost::::::::::$450,000::::$412,500::::$525,000::::::$375,000:::$487,500::$562,500
Transportation::::::$20,400:::::$12,100:::::$25,200:::::::::$16,000::::$29,900:::::$31,500
________________________________________________________________________
Contribution::::(324,400):::($133,100):::($109,200)::($51,000)::($140,400)::($121,500)
____________________________________________________________________________
Sales:
NE = 1,200*550=$660,000
SE=1,100*450=$495,000
NC=1,400*500=$700,000
SC=1,000*525=$525,000
NW=1,300*475=$617,500
South West=1,500*500=$750,000
Labor Cost
NE = 1,200*185=$222,000
SE=1,100*185=$203,500
NC=1,400*185=$259,000
SC=1,000*185=$185,000
NW=1,300*185=$240,500
South West=1,500*185=$277,500
Parts cost:
NE = 1,200*375=$450,000
SE=1,100*375=$412,500
NC=1,400*375=$525,000
SC=1,000*375=$375,000
NW=1,300*375=$487,500
South West=1,500*375=$562,500
Transportation:
NE = 1,200*17=$20,400
SE=1,100*$11=$12,100
NC=1,400*$18=$25,200
SC=1,000*$16=$16,000
NW=1,300*$23=$29,900
South West=1,500*$21=$31,500