steven owen nicole robinson nina trujillo. background/problem coca-cola bottling company of north...
TRANSCRIPT
Steven OwenNicole Robinson
Nina Trujillo
Background/Problem
Coca-Cola Bottling Company of North Texas
Inefficient Forecasting Model Simple linear regression Distributor guesswork
Relatively High Refill Rate on Machines Ave. of 55.13% Capacity refill ( 37 machines)
On average Machines are over ½ full upon refill
Background/Problem
Central Insurance 486 0.4279 21 9.902828571Ceramic Tile International 624 0.3461 10.5 20.56822857ColorWeb 440 0.3818 12.6 13.33269841Dallas Tile Corporation 376 0.4468 31.5 5.333231746Dallas Tile Corporation 336 0.6428 63 3.428266667DNS Electronics 486 0.5432 63 4.1904Dominos 1 196 0.5306 21 4.952266667Dominos 2 196 0.5306 21 4.952266667Dynamex Incorporated 368 0.1304 63 0.761701587
Objective
Estimate cost saving effectiveness of implementing real time inventory technologyDevelop a Linear Program to minimize costs in regard to stocking soft drink vending machinesUse simulation models to demonstrate effectiveness of new methods
Methodology
Pick location of numerous vending machinesGather Data Machine capacity Distance between machines Actual days between service Average cans per day Costs
Fuel Labor
Methodology
Simulations Over a period of 90 days Five Simulations
Constant Demand/Daily Refill/Previous Refill Rate Constant Demand/Daily Refill/New Refill Rate Constant Demand/Weekly Refill/New Refill Rate Variable Demand/Daily Refill/Previous Refill Rate Variable Demand/Daily Refill/New Refill Rate
Staggered Starting Inventory Uniform Distribution between .15 and 1
Methodology-Simulation
Constant Demand Average Cans/Day = (Machine Capacity*Actual% Capacity
Fill)Actual Days Between
Service
Variable Demand Normal Distribution
μ = Average Cans/Day σ = 20% of Average Cans/Day
Methodology – Refill Period
Refill Rate The percentage of inventory remaining that
determines the need for refilling on a specific machine. (Previous 55.13%, New 10%)
Daily Refill Machine would be refilled the day that it was
estimated to drop below the respective refill rate.
Weekly Refill Machine would be refilled at the beginning of the
week in which it was estimated to drop below the estimated refill rate.
Methodology
Linear Programming Models Developed to optimize route distances on specific
days with more than two service locations
Methodology -Linear Programming Model
Objective function:Minimize X01 + X02 +…+Xij ij = traveling from i ending at j.
(Respective distances as coefficients).
Subject to: X01 + X02 +…+X0j = 2 only two segments connecting to distributor.
X10 + X12 +…+X1j = 2 only two segments connecting to location #1. X20 + X21 +…+X2j = 2 only two segments connecting to location #2
. . = 2 . .
. . = 2 . .X60 + X61+…+X6j = 2 only two segments connecting to location #6
#Central Insurance, Color Web, Dallas Tile, Dominos 2, Dynamex#dist_ccddd.lp minimize 17.9x01 + 17.9X10 + 9.0x02 + 9.0x20 + 20.7x03 + 20.7x30 +21.1x04 +21.1x40 + 18.9x05 + 18.9x50 + 6.0x21 + 6.0x12 + 19.8x31+ 19.8x13 + 6.5x41 + 6.5x14 + 13.2x51 + 13.2x15 + 25.5x32 + 25.5x23 + 12.1x42 + 12.1x24 +10.9x52 +10.9x25 +13.7x43 + 13.7x34 + 21.9x53 + 21.9x35 + 13.9x54 +13.9x45 subject tox01 + x02 + x03 + x04 + x05 = 2x01 + x12 + x13 + x14 + x15 = 2x02 + x12 + x23 + x24 + x25 = 2x03 + x13 + x23 + x34 + x35 = 2x04 + x14 + x24 + x34 + x45 = 2x05 + x15 + x25 + x35 + x45 = 2x01 + x13 + x35 + x05 <= 3 binary x01 x02 x03 x04 x05x12 x13 x14 x15x23 x24 x25x34 x35x45end
Linear Programming Example
Methodology - Calculations
Travel Cost = Distance of Route * $0.15 10 mpg * 1.50/gallon of fuel = $0.15/mileTravel Time = Distance of Route / 30mph Refill Time = .333 hours to refill each machine * number of machines on routeLabor Cost = $10/hour for labor * (Travel Time + Refill Time)
Analysis-ComparisonConstant Demand
ConstantPrevious-Daily New-Daily New-Weekly
Distance 2240.1 1424.6 662.5# of Machines 270 130 130
Travel Time 74.67 47.49 22.08Loading Time 89.91 43.29 43.29
Total Time 164.58 90.78 65.37
Travel Cost 336.015 213.69 99.375Labor Cost 1645.8 907.77 653.73Total Cost $1,981.82 $1,121.46 $753.11% Savings ---------------- 43.40% 61.99%
$336.02
$213.69
$99.38
$1645.80
$907.77
$653.73
0
200
400
600
800
1000
1200
1400
1600
1800
Travel Cost Labor Cost
Constant Demand Travel/Loading
Previous-DailyNew-DailyNew-Weekly
Constant Demand Cost/Savings
$753.11
$1121.46
$1981.82
0
43.40%
61.99%
0
500
1000
1500
2000
2500
Previous-Daily
New-Daily New-Weekly
0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
Total Cost% Savings
Analysis-Comparison
Variable Demand Variation
Previous-Daily New-DailyDistance 3404.2 2576.8
# of Machines 483 267Travel Time 113.47 85.89
Loading Time 160.84 88.91Total Time 274.31 174.8
Travel Cost 503.63 386.52Labor Cost 2743.12 1748.04
Total Cost $3,253.75 $2,134.56
% Savings ---------------- 34.40%
$503.63$386.52
$2743.12
$1748.04
0
500
1000
1500
2000
2500
3000
Travel Cost Labor Cost
Demand Variation Travel/Labor
Previous-Daily
New-Daily
Demand Variation Costs/Savings
$2134.56
$3253.75
0
34.40%
0
500
1000
1500
2000
2500
3000
3500
Previous-Daily New-Daily
0
0.05
0.1
0.15
0.2
0.25
0.3
0.35
0.4
Total Cost
% Savings
Demand Variation Costs/Savings
$2134.56
$3253.75
0
34.40%
0
500
1000
1500
2000
2500
3000
3500
Previous-Daily New-Daily
0
0.05
0.1
0.15
0.2
0.25
0.3
0.35
0.4
Total Cost
% Savings
Conclusion/Recommendation
As our preliminary hypothesis suggested, there is considerable room for improvement in the efficiency in soft drink distribution.We recommend implementing a real time inventory system to capitalize on this cost saving opportunity.