KEY Electronics - Sourcing and Warehouse Analysis T. Kraft, Y.T. Chung, F. Erhun; Stanford University This section provides a brief overview of the entire spreadsheet and an instruction on how to use the interactive decision tool in the Solution tab. Contents 1. Solution (and decision tool) This tab provides the average annual procurement cost calculations for each SKU. The reader can try the interactive decision tool to check the total costs including the average annual procurement cost and the warehousing cost. 2. Appendix data This tab contains all basic information regarding each SKU category such as physical specifications, profit margins, duties, and demand distributions. 3. Appendix categories This tab presents the number of SKUs in each category. 4. Appendix parameters This tab contains all parameters needed for cost calculations of each sourcing option. 5. 3PL parameters This tab contains the cost structure of the 3PL provider. 6. 3PL cost calculations This tab provides the three cost calculations (storage cost, receiving cost, and shipping cost) for the 3PL option. 7. Frequency calculations This tab automatically calculates the number of pallets for each SKU in each week according to the sourcing decision given in the Solution tab. 8. Warehouse parameters This tab contains all information regarding the cost structure of the existing warehouse. The reader can estimate the cost sturcture of the new warehouse option from this information. 9. Warehouse cost calculations This tab automatically calculates the cost of the new n ew warehouse option according to the sourcing decision given in the Solution tab by considering the number of pallets in each week.
Instruction for the Interactive decision tool
The interactive decision tool is found on the Solution tab, from the column CJ to CQ. Please note that the blue-colored cells named "decision variable cells" (CK3:CL12, and CQ3) are the only input cells. All other cells (including cells in other tabs) will be adjusted automatically based on the user's input. The user can input "1" to choose the option and "0" not to choose the option. According to the user's input, all other cells will be updated and the total cost for the given option will appear in cell CR13. The minimum cost sourcing stra tegy can be found in column CH.
The interactive decision tool is found on the Solution tab, from the column CJ to CQ. Please note that the blue-colored cells named "decision variable cells" (CK3:CL12, and CQ3) are the only input cells. All other cells (including cells in other tabs) will be adjusted automatically based on the user's input. The user can input "1" to choose the option and "0" not to choose the option. According to the user's input, all other cells will be updated and the total cost for the given option will appear in cell CR13. The minimum cost sourcing stra tegy can be found in column CH.
SKU 2300105 1609313 2780211 1709786 6300612 1309034 6509036 6109015 4309113 2000234
KM Description BATERIA KY357 T.V. 25" SANYO CONECTOR F-59 TEL CEL SONY Z200 PURIFICADOR DE AIRE KARAOKE JXOK5 CALC.CIENT.SC OL153 REGULADOR KOBLENZ BP1000 TELEFONO ALAMBRICO GONDOL RADIO FM YB400PE
Item Current Source Batteries KEY TV Local Cable/Connector KEY Cellphone KEY Air Purifier KEY Karaoke Machine Local Calculator Local Surge Protector Local Telephone Local Radio KEY
Weight (lbs) 0.1 8.82 0.01 0.6 8.08 16.5 1.23 0.96 1.50 3
Batteries TV able/Connect Cellphone Air Purifier araoke Machi Calculator urge Protecto Telephone Radio
0 0 0 0 1 0 0 1 1 1
ourc ng + sa 1 0 0 0 0 0 0 0 0 0
are ouse ost oca 0 1 1 1 0 1 1 0 0 0
Decision Variable Cells
est Asia Local Cts Local Cts Local Cts US Local Cts Local Cts US US US Total Sourcin
Volume (Cubic ft) 0.016 2.08 0.043 0.13 1.36 3.5 0.2 0.08 0.14 0.16
Units/ Pallet 1,000 8 350 128 8 4 80 200 115 100
oca Sales Price Base Unit ($) Cost ($) 1.33 0.80 176.94 129.17 1.18 0.66 142.22 128.00 166.67 116.67 138.89 108.33 16.28 10.42 20.51 13.33 9.49 6.83 174.39 148.23
Base Unit Cost ($) 0.41 116.78 0.38 108.09 93.33 75.00 8.46 8.41 4.46 120.33
sa Base Unit Cost ($) 0.40 115.01 0.37 106.67 91.67 73.61 8.30 8.21 4.37 118.58
Duty 18% 30% 10% 35% 15% 50% 25% 13% 23% 20%
Service level (Type 1) 0.98 0.98 0.9 0.98 0.95 0.95 0.95 0.95 0.98 0.95
rocureme are ous ng ost #REF! 0 #REF! WHSE #REF! #REF! #REF! #REF! #REF! 1 #REF! #REF! 3PL #REF! #REF! #REF! #REF! Total Whse #REF!
Fixed Cost for WHSE Variable Cost for WHSE TOTAL
#REF! #REF! #REF!
3PL Storage 3PL Receiving 3PL Shipping
#REF! #REF! #REF!
TOTAL
#REF!
TOTAL #REF!
Projected 2012 Mean Demand (Month) 1,800 115 675 152 125 30 108 540 910 38
Std Dev of Demand (Month) 250 95 85 69 45 16 65 270 334 17
Duty & Labeling Unit Costs US*Duty 0.48 151.81 0.42 145.92 107.33 112.50 10.58 9.50 5.49 144.40
Unit Costs Asia * Duty 0.47 149.51 0.41 144.00 105.42 110.42 10.38 9.28 5.38 142.30
Unit Costs (US) + Labeling (US) 0.62 151.95 0.56 146.06 107.47 112.64 10.72 9.64 5.63 144.54
Transportation Unit Costs (Asia) + Unit Costs (US) + Unit Costs (Asia) + Labeling (Asia) Transp (US) Transp (Asia) 0.49 0.64 0.54 149.53 153.19 154.86 0.43 0.56 0.52 144.02 146.15 144.36 105.44 108.60 109.20 110.44 114.95 119.59 10.40 10.89 10.95 9.30 9.78 9.58 5.40 5.84 5.86 142.32 144.96 143.00
EOQ
EOQ (Local) 246 5 166 6 5 3 17 33 60 3
EOQ (US) 1147 19 750 22 23 11 68 161 270 11
T
EOQ (Asia) 2014 30 1257 36 37 17 109 261 433 18
T (Local) - Mos 0.14 0.04 0.25 0.04 0.04 0.09 0.15 0.06 0.07 0.07
T (US) - Mos 0.64 0.16 1.11 0.14 0.19 0.37 0.63 0.30 0.30 0.29
T (Asia) - Mos 1.12 0.26 1.86 0.23 0.30 0.58 1.01 0.48 0.48 0.47
Lead Time Demand (Local) 178 11 67 15 12 3 11 53 90 4
Asia US Local
Lead Time Lead Time StdDev (Local) 79 30 27 22 14 5 20 85 105 5
Lead Time Demand (US) 828 53 311 70 58 14 50 249 419 17
LT (Days) 65 14 3
LT (Mos) 2.137 0.46 0.10
Lead Time StdDev (US) 186 65 64 47 31 11 44 185 230 12
Lead Time Demand (Asia) 3847 246 1442 325 267 64 231 1154 1945 81
LT StdDev (Days) LT StdDev (Mos) 7 0.23 1.3 0.04 -
Lead Time StdDev (Asia) 552 141 199 107 72 24 98 414 531 26
Reorder Point (R)
R (Local) 339 73 101 59 36 11 44 193 305 13
R (US) 1211 186 393 167 109 32 123 552 891 37
Safety Stock
R (Asia) 4981 536 1697 544 385 104 392 1835 3036 125
Safety Stock (Local) 161 61 34 45 23 8 34 139 215 9
Safety Stock (US) 382 133 83 97 51 18 73 304 472 19
Hold Cost Safety Stock (Asia) 1135 290 255 219 118 40 162 681 1091 43
Hold Cost (Local) $57 $2,058 $19 $1,515 $756 $261 $109 $520 $419 $374
Hold Cost (US) $152 $5,442 $64 $3,949 $1,699 $675 $291 $939 $886 $895
Hold Cost (Asia) $287 $11,821 $114 $8,557 $3,732 $1,460 $592 $1,942 $1,917 $1,870
Order Cost (Local) $17,280 $178,255 $5,346 $233,472 $175,005 $38,999 $13,504 $86,378 $74,584 $67,593
Order Cost
Order Cost (US) Order Cost (Asia) $13,776 $11,595 $211,401 $213,700 $4,531 $4,182 $266,569 $263,318 $162,901 $163,797 $41,382 $43,052 $14,110 $14,195 $63,359 $62,056 $63,727 $64,014 $66,100 $65,210
Setup Cost Setup Cost (Local) $25 $79 $14 $90 $78 $37 $22 $55 $51 $49
Setup Cost (US) Setup Cost (Asia) $91 $135 $358 $580 $52 $81 $402 $644 $315 $508 $159 $260 $93 $150 $196 $313 $197 $318 $200 $320
Loss Fxn
L(z) 0.0073 0.0073 0.0473 0.0073 0.0209 0.0209 0.0209 0.0209 0.0073 0.0209
Penalty Cost Penalty Cost (Local) $6 $354 $2 $255 $172 $57 $22 $118 $66 $83
Penalty Cost (US) $11 $909 $4 $634 $352 $131 $50 $189 $123 $176
Penalty Cost (Asia) $27 $2,010 $12 $1,415 $819 $305 $112 $414 $286 $394
Total Cost Cts (Local) $17,367 $180,745 $5,381 $235,332 $176,012 $39,353 $13,657 $87,071 $75,119 $68,098
Total Cost (Q,R) Total Cost Cts (US) $15,031 $219,110 $5,652 $272,554 $166,266 $43,347 $15,544 $65,683 $65,932 $68,371
T Total Cost Cts (Asia) $13,045 $229,111 $5,390 $274,934 $169,856 $46,077 $16,048 $65,724 $67,534 $68,794
T (Local) Optimal 0.14 0.04 0.25 0.04 0.04 0.09 0.15 0.06 0.07 0.07
T Adj (US) 0.46 0.46 0.92 0.46 0.46 0.46 0.46 0.46 0.46 0.46
T Adj (Asia) 0.92 0.92 1.85 0.92 0.92 0.92 0.92 0.92 0.92 0.92
Order Intervals US Asia
Weeks 2.0 4.0
Mos 0.46 0.92
US 0.46 0.92 1.38 1.85 2.31 2.77 3.23 3.69 4.15 4.62
Asia 0.92 1.85 2.77 3.69 4.62 5.54 6.46 7.38 8.31 9.23
EOQ
EOQ (US) 831 53 623 70 58 14 50 249 420 18
Lead Time
EOQ(Asia) 1662 106 1246 140 115 28 100 498 840 35
Lead Time StdDev (Local) 121 36 50 25 17 7 33 108 135 7
Lead Time StdDev (US) 252 91 104 67 44 15 63 260 323 16
Lead Time StdDev (Asia) 602 168 230 126 84 29 116 488 621 31
Safety Stock
Hold Cost
Safety Stock Prd Safety Stock Prd Safety Stock Prd (Local) (US) (Asia) Hold Cost (Local) 249.0 517.7 1237.1 $74 73.3 187.6 345.6 $2,446 63.9 133.3 294.8 $24 52.2 136.7 258.1 $1,761 27.8 71.6 137.9 $891 11.5 25.4 47.4 $347 53.8 102.9 191.4 $162 177.5 428.1 803.3 $646 278.1 663.4 1274.7 $526 11.5 27.0 51.0 $473 S* US 2,177 294 1,067 277 187 53 202 926 1,502 62
Asia 6,745 698 2,983 723 520 139 522 2,456 4,059 167
Hold Cost (US) $149 $8,201 $62 $6,276 $2,727 $928 $348 $1,351 $1,274 $1,296
Order Cost
Hold Cost (Asia) $278 $15,434 $118 $11,847 $5,338 $1,832 $661 $2,520 $2,484 $2,450
Order Cost (Local) $17,280 $178,255 $5,346 $233,472 $175,005 $38,999 $13,504 $86,378 $74,584 $67,593
Order Cost (US) Order Cost (Asia) $13,776 $11,595 $211,401 $213,700 $4,531 $4,182 $266,569 $263,318 $162,901 $163,797 $41,382 $43,052 $14,110 $14,195 $63,359 $62,056 $63,727 $64,014 $66,100 $65,210
Setup Cost (Local) $25 $79 $14 $90 $78 $37 $22 $55 $51 $49
Setup Cost
Setup Cost (US) Setup Cost (Asia) $126 $164 $126 $164 $63 $82 $126 $164 $126 $164 $126 $164 $126 $164 $126 $164 $126 $164 $126 $164
Penalty Cost Penalty Cost (Local) $9 $423 $4 $299 $206 $79 $36 $150 $85 $108
Penalty Cost (US) $15 $1,284 $7 $893 $494 $185 $71 $266 $173 $248
Penalty Cost (Asia) $30 $2,392 $14 $1,665 $956 $360 $133 $489 $334 $463
Total Category Cost (Periodic)
(Continuous)
Total Cost Prd Total Cost Prd Total Cost Prd (Local) (US) (Asia) $869,395 $753,318 $653,294 $1,812,030 $2,220,120 $2,326,905 $285,553 $300,162 $286,034 $4,712,452 $5,497,302 $5,559,876 $2,642,703 $2,508,729 $2,568,831 $591,928 $654,316 $696,120 $205,853 $234,830 $242,283 $8,723,015 $6,610,271 $6,622,806 $902,947 $795,607 $815,946 $682,223 $687,702 $692,865 Single SKU Cost (Periodic) $17,388 $15,066 $13,066 $181,203 $222,012 $232,691 $5,388 $5,663 $5,397 $235,623 $274,865 $277,994 $176,180 $167,249 $171,255 $39,462 $43,621 $46,408 $13,724 $15,655 $16,152 $87,230 $66,103 $66,228 $75,246 $66,301 $67,996 $68,222 $68,770 $69,287
Total Cost Cts (Local) $868,360 $1,807,450 $285,198 $4,706,631 $2,640,177 $590,301 $204,862 $8,707,125 $901,433 $680,983 (Continuous) $17,367 $180,745 $5,381 $235,332 $176,012 $39,353 $13,657 $87,071 $75,119 $68,098
Recomm Recommended (Sourcing only) Asia Local Cts Local Cts Local Cts US Local Cts Local Cts US US Local Cts Total $13,066 $180,745 $5,381 $235,332 $167,249 $39,353 $13,657 $66,103 $66,301 $68,098
endations Sourcing Cost (w/o warehouse) $653,294 $1,807,450 $285,198 $4,706,631 $2,508,729 $590,301 $204,862 $6,610,271 $795,607 $680,983 $18,843,326
Sourcing + Ware
SKU Batteries TV Cable/Connector Cellphone Air Purifier Karaoke Machine Calculator Surge Protector Telephone Radio
US 0 0 0 0 1 0 0 1 1 0
Asia 1 0 0 0 0 0 0 0 0 0
Local 0 1 1 1 0 1 1 0 0 1
Decision Variable Cells Min Unit Cost 0.58 129.17 0.64 128.00 109.27 108.33 10.42 9.73 5.93 145.20
Unit Cost Solution Asia Local Asia Local US Local Local Asia US Asia
house Cost
Best Option Asia Local Cts Local Cts Local Cts US Local Cts Local Cts US US Local Cts Total Sourcing
Procurement Cost $653,294 $1,807,450 $285,198 $4,706,631 $2,508,729 $590,301 $204,862 $6,610,271 $795,607 $680,983 $18,843,326
Warehousing Cost 1 WHSE
$378,107 0
3PL
Total Whse
$0 $378,107 WHSE Avg Utiliz
Fixed Cost for W HSE Variable Cost for WHSE TOTAL
$251,400 $126,707 $378,107
3PL Storage 3PL Receiving 3PL Shipping TOTAL
$186,927 $89,889 $102,375 $379,190
TOTAL $19,221,432 49%
Pallets on-hand
T 0.92 0.04 0.25 0.04 0.46 0.09 0.15 0.46 0.46 0.07
Min 83 83 7 8 188 41 9 276 91 1 787
Max 145 83 30 8 242 41 9 339 113 1 1,012
Avg 114 83 18 8 215 41 9 308 102 1 900
a ets received per year 1,092 1,974 1,274 323 2,834 1,452 312 3,250 1,144 174 13,829
urrent Sourcing Projected Cost $753,318 $1,807,450 $300,162 $5,497,302 $2,508,729 $590,301 $204,862 $8,707,125 $901,433 $687,702 $21,958,384
e ecte Sourcing Projected Cost $653,294 $1,807,450 $285,198 $4,706,631 $2,508,729 $590,301 $204,862 $6,610,271 $795,607 $680,983 $18,843,326
Savings from sourcing $3,115,059
SKU 2300105 1609313 2780211 1709786 6300612 1309034 6509036 6109015 4309113 2000234
KM Description BATERIA KY357 T.V. 25" SANYO CONECTOR F-59 TEL CEL SONY Z200 PURIFICADOR DE AIRE KARAOKE JXOK5 CALC.CIENT.SC OL153 REGULADOR KOBLENZ BP1000 TELEFONO ALAMBRICO GONDOL RADIO FM YB400PE
Item Batteries TV Cable/Connector Cellphone Air Purifier Karaoke Machine Calculator Surge Protector Telephone Radio
Current Source KEY Local KEY KEY KEY Local Local Local Local KEY
Weight (lbs) 0.10 8.82 0.01 0.60 8.08 16.50 1.23 0.96 1.50 3.00
Volume (Cubic ft) 0.016 2.08 0.043 0.13 1.36 3.50 0.20 0.08 0.14 0.16
Units/ Pallet 1,000 8 350 128 8 4 80 200 115 100
Sales Price ($) 1.33 176.94 1.18 142.22 166.67 138.89 16.28 20.51 9.49 174.39
oca Base Unit Cost ($) 0.80 129.17 0.66 128.00 116.67 108.33 10.42 13.33 6.83 148.23
Base Unit Cost ($) 0.41 116.78 0.38 108.09 93.33 75.00 8.46 8.41 4.46 120.33
sa Base Unit Cost ($) 0.40 115.01 0.37 106.67 91.67 73.61 8.30 8.21 4.37 118.58
Duty 18% 30% 10% 35% 15% 50% 25% 13% 23% 20%
erv ce Level (Type 1) 0.98 0.98 0.90 0.98 0.95 0.95 0.95 0.95 0.98 0.95
Projected 2012 ean t ev o Demand Demand (Month) (Month) 1,800 250 115 95 675 85 152 69 125 45 30 16 108 65 540 270 910 334 38 17
SKU Category1 13 16 17 20 23 27 43 61 63 65
Description Music TV, DVD Cell phones Antennas, Short-wave radios Batteries Fuses, adapters Telephones Surge protectors Gadgets Calculators Total 1
Number of SKUs 15 10 20 10 50 53 12 100 15 15 300
SKU Category = First two digits of SKU number
SKU Category (re-order) 2300105 1609313 2780211 1709786 6300612 1309034 6509036 6109015 4309113 2000234 Total
Number of SKUs 50 10 53 20 15 15 15 100 12 10 300
General Annual Holding Cost Interest Rate
Lead Times/Replenishment (Da 25.00%
Ocean Freight $/Cubic ft
$2.05
US Shipping $/lb (OKC to Border)
$0.05
Asia Asia (Std. Deviation) OKC OKC (Std. Deviation) Local Mexico Shipping $/lb (Border to Mexico City) $/lb (Manzanillo to Mexico City)
ys)
Order Costs (per SKU) 65 7 14 1.3 3
Asia US Local Labeling Cost per unit (Asia) Cost per unit (OKC)
$0.09 $0.12
$12.60 $4.86 $0.28
$0.02 $0.14
3PL Pricing Description Receiving cost/pallet1 Shipping cost/replenishment2 Avg. orders per week/per store Storage cost/pallet per week3 Pallets on-hand (x) x < 1000
Value $6.50 $11.25 2.5 Incremental quantity discount $4.00 $2.75 $1.50 $0.75
1000 ≤ x < 1200 1200 ≤ x < 1400 1400 ≤ x 1
includes cost to receive and putaway pallets
2
includes cost to pick, prep, and ship a replenishment (i.e., a store order) 3
weekly inventory snap-shot taken on Friday
Used in formulas 1000 $4,000 1200 $4,550 1400 $4,850
3PL Storage Cost Calculations W eek
1
2
3
4
5
6
7
8
Cost Cost_H Cost_M2 Cost_M1 Cost_L
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
Storage Cost
$186,927
3PL Receiving Cost Calculations
SKU 2300105 1609313 2780211 1709786 6300612 1309034 6509036 6109015 4309113 2000234
Source Asia Local Cts Local Cts Local Cts US Local Cts Local Cts US US Local Cts
Weeks between replens 4.00 0.18 1.06 0.16 2.00 0.39 0.67 2.00 2.00 0.30
Receipts per year 13 282 49 323 26 132 78 26 26 174
3PL Shipping Cost Calculations 70 Stores x 2.5 Replen/wk/store x 52 weeks/year 9100 Replens/yr x $11.25 Cost/Replen $102,375 Shipping Cost
3PL Cost Storage $186,927 Receiving $89,889 Shipping $102,375 Total Cost $379,190
Q 1662 5 166 6 58 3 17 249 420 3
a ets Receipt Pallets Units/ (SKU received Pallet Category) per year 1000 84 1,092 8 7 1,974 350 26 1,274 128 1 323 8 109 2,834 4 11 1,452 80 4 312 200 125 3,250 115 44 1,144 100 1 174 Total pallets received 13,829 Cost/Pallet $6.50 Receiving Cost $89,889
9
10
11
12
13
14
15
16
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
17
$4,032 $4,047 $4,032 $4,268 $4,559
18
19
20
21
22
23
24
25
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
26
$3,316 $3,316 $3,530 $3,994 $4,422
27
28
29
30
31
32
33
34
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
35
$3,881 $3,881 $3,918 $4,205 $4,528
36
37
38
39
40
41
42
43
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
44
$3,150 $3,150 $3,416 $3,931 $4,391
45
46
47
48
49
50
51
52
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
$4,032 $4,047 $4,032 $4,268 $4,559
$3,316 $3,316 $3,530 $3,994 $4,422
$3,881 $3,881 $3,918 $4,205 $4,528
$3,150 $3,150 $3,416 $3,931 $4,391
SKU 2300105 1609313 2780211 1709786 6300612 1309034 6509036 6109015 4309113 2000234
Source Asia Local Cts Local Cts Local Cts US Local Cts Local Cts US US Local Cts
Q 1662 5 166 6 58 3 17 249 420 3
SS 1237 61 34 45 72 8 34 428 663 9
Units/Pallet T (weeks) 1000 4.00 8 0.18 350 1.06 128 0.16 8 2.00 4 0.39 80 0.67 200 2.00 115 2.00 100 0.30
1 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
2 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
3 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
4 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
5 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
6 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
7 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
8 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
9 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
10 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
11 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
12 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
13 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
14 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
15 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
16 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
17 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
18 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
19 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
20 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
21 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
22 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
23 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
24 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
25 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
26 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
27 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
28 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
29 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
30 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
31 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
32 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
33 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
34 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
35 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
36 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
37 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
38 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
39 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
40 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
41 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
42 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
43 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
44 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
45 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
46 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
47 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
48 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
49 2.9 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
50 2.5 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
51 2.1 8.3 0.6 0.4 16.2 2.7 0.6 3.4 9.4 0.1
52 1.7 8.3 0.1 0.4 12.6 2.7 0.6 2.8 7.6 0.1
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.75 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
0.50 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00
0.25 1.00 0.06 1.00 0.50 1.00 1.00 0.50 0.50 1.00
Warehouse Parameters Description
Value
Capacity in pallets
1,850
Square feet
40,000
Rent ($/Sq ft per year)
$3.75
Salary per: Manager (1 in total) Supervisor (4 in total) Benefits (% applied to salary)
Whse Fixed Cost Rent
$150,000
Salaries
$101,400 $251,400
$30,000 $12,000 30%
High Mid 100%
0.85 0.65
0.85 0.65
0.85 0.65
0.85 0.65
0.85 0.65
0.85 0.65
0.85 0.65
0.85 0.65
0.85 0.65
0.85 0.85 0.65 $3,500 0.65
Low
0.45
0.45
0.45
0.45
0.45
0.45
0.45
0.45
0.45
0.45
90%
n o i t a z i l i t U y t i c a p a C
0.45 $3,000
85% Capacity
80%
$2,500
70%
$2,000
65% Capacity
60%
$1,500
50%
$1,000
45% Capacity
40%
$500
30%
$0
1
2
3
4
5
6
7
8
9
10
11
12
Week
Capacity Utilization W eek
Variable Cost Capacity Utilization
1
2
3
4
5
6
$3,200 $2,400 $2,350 $2,400 $1,600 $2,400 90%
75%
68%
80%
55%
68%
Variable Cost
A ril - June
7
8
9
$525
$1,700
$500
40%
58%
42%
10
11
$3,400 $2,350 95%
78%
0.85 0.65 0.45
t s o C e l b a i r a V
Capacity Range
$1,660 58%
Improve
0.85 0.65
$3,300 $2,350
$8,800 $6,267
$7,480 $5,327
.45<=x<.65
0.45
$1,650
$4,400
$3,740
$500
$1,333
$1,133
Scale Improve
12
Scaled
x>=.85 .65<=x<.85 x<.45
2007
Old VC
2.7 15%
SKU 2300105 1609313 2780211 1709786 6300612 1309034 6509036 6109015 4309113 2000234
Description
Best Option Asia Local Cts Local Cts Local Cts US Local Cts Local Cts US US Local Cts
BATERIA KY357 T.V. 25" SANYO CONECTOR F-59 TEL CEL SONY Z200 PURIFICADOR DE AIRE KARAOKE JXOK5 CALC.CIENT.SC OL153 REGULADOR KOBLENZ BP1000 TELEFONO ALAMBRICO GONDOL RADIO FM YB400PE
Review Freq 0.92 0.04 0.25 0.04 0.46 0.09 0.15 0.46 0.46 0.07
$251,400 $126,707 $378,107
Fixed Cost Variable Cost Total Warehouse Cost
Projected Pallets on-hand 1,200 1,000 s t e l l a P
800 600 400 200 0 1
8
15
22
29 Week
36
43
SKUs/Category 50 10 53 20 15 15 15 100 12 10 Total Pallets Weekly Whse VC Utilization
Week 1 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
Pallet Capacity
1,850
50
2 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
3 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
4 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
5 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
6 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
7 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
8 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
9 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
10 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
11 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
12 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
13 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
14 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
15 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
16 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
17 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
18 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
19 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
20 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
21 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
22 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
23 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
24 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
25 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
26 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
27 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
28 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
29 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
30 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
31 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
32 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
33 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
34 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
35 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
36 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
37 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
38 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
39 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
40 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
41 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
42 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
43 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
44 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
45 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
46 124 83 7 8 188 41 9 276 91 1 829 $1,133 45%
47 103 83 30 8 242 41 9 339 113 1 970 $3,740 52%
48 83 83 7 8 188 41 9 276 91 1 787 $1,133 43%
49 145 83 30 8 242 41 9 339 113 1 1,012 $3,740 55%
