Warehouse Locations
Output
Q1. Which Store and Item have the most unmet supply?
Q2. Which Item had the most units allocated across the retail network?
Q3. Based on cumulative distance traveled (# items assign to location multiplied by the distance traveled), which items collectively traveled less than 400,000 miles?
Input
iVBORw0KGgoAAAANSUhEUgAAAXoAAAA0CAIAAAAL52RvAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnEAABJxAfPceo4AAAASdEVYdFNvZnR3YXJlAEdyZWVuc2hvdF5VCAUAAA0cSURBVHhe7Z3fjhXHEYf9Bs4bxG+A38B+A/sN7DdI3sBccYcSKXBhCQkuLPliibGEfcNeJGKvFiFhNgoKijFai+BVEPJCIrAlS+TbU72V2uo/02dn5uyedX1qjU7XVPd0D1O/qZ4zZ3nrTRAEwUoIuQmCYEWE3ARBsCJORm7+8uQ/Nx7tp0qwDjx58uTu3bu7u7upHgTLs2q5efzi5/e/+PatP31DSaZgHQi5CcazUrm59+zVbz79m2jNsnLz40+/nN/eQ6oQrGQKVkjITTCelcrNJ9s/qNYsJTdWp1iIJWuwQkJugvGsh9y8/emOtgq5ORFCboLxrIfc2FYhNydCyE0wnpCboIuQm2A8ITdBFyE3wXhCboIuQm6C8YTcBF2E3ATjGZCbH3/65Y/fPPt48/v3v/jWvjJDeffzh9jZe+/Zq+Q9xMnKDeM8v733+9v/Yi5s+fzloxdpXzBEyE0wngG5+eCr72yo18o71x4QwIMv4C0lN3+49290gYKu2VZoHEphy6AAXX3w3GmlFuz0gKom1xLs/euT/0pJpsXvMES5RLySdQG71L/ds6JNrHbzWftJpgp2hDO9BhlyE4xnQG4+2tx18dkohG5qVmEpubGe7fLen/+Z2mQQh06tigWfhi5YzUUE8URi1CIluS6wdk5gstZBDdX/t9f+nqxH3zb63e0nyZrh5thzxGMQchOMZ0BuuOty0RPPhBxiYQsWGw9S2okGraxzslawnu3SkJtzn/8jd5bi7ERsapNhnZl1nii5AVh5GpRgsIPkFCXrIr9TO6V2blEi67bW2c3+/j6HAD4kU5PXr1+LPyRTnU43RQbDIVJ9wbKd7O3tuU7EwjbVSxR9BluBjNmdPSzWKNUexH9Cxj4qdgoyodzkSlErNkQt7nBUbQrDZxeotX5ybXLFJRQ3Hu3bve3fvqMO1tmJhT0JrFjzFIwTrg6U2hTGM6vcEAk3b968fPnyBQPVzc1NF+0Cxq2trStXriTXQ7Ds7OwkpwzxuXXrVqo3efjwofi7KYuR0aZ6k9u3b4u/7eSzzz7DwjbVSxR9Fj1duH//fqqXkCOyTfUF0lCNUu1BTsLFixcbMscucb5z504y1RkrN+5yn1BulKUOobgwJlNIO45iFYd4Ttaj5HJDWofEMJJ7z16xzVWANZE6t1c3NoVxWRLQv+6luCUVx2XMuhdtSjtmYD65IX7kei3Ctc5Fn1wXcH1jTLsXEJbWgugUwyPtvnBhMEeAS5cuiXNRbmDwVKCJOqoJ5aYd/NPKDc6Dox10sJxZubE6Yh+IOIhYdaMUO3dyQ1TbB7pFXN6U65Fihenqg+fJanAnzY7QHWVwVGOYSW64JS4u7AOIBxtIHIskAvvGxkYyHdUa9lp/UiR6kL3FmJRWMBgbVgFrcjPYiUS+MKHcAHpaTPpADqrKIkgrZ1QYmzgU/3E5q7K3mFXpiWoooOXMyo19rlRLbQSrJowwWQ3WgW57otrlVkUdAZu80HNNlYpLKndaiiOfkDnkhmtULtbGHRu7DS1dQLmUR8FfFAfPZDoEo0pVYyKalUiC4zwXrVM/jXVNo5ORciPd1lZzk8sNSJ+sbZ3G6Rw716dwNuXm2KP64KvvktVg5SZf79SwGlHsFmx60lhzuenIksr2T4rUSKAmYQ65kaCCznuj3ktrkSNo/Dg5ECOxwZbgSdYMiS7cZHhuygddLAbAttGJ5GVs806KUuIo+mABPQlFsZOBufMj/rWTpqer8Y8roul6kGOhOE6GGpxNuXHf6SRrBTuqopocT27s19uUohzYFKw9L7du+vDrx7bac05GMrncaJZeu1HnSGrTc303wlXvycVwlb1yCOmkKDfaSTGGZWo48CHvpDg2R9EHC/BBtAxymRYJcKMS5+JQoUdu1IcZiUX/+WppZpGzKTdkCrZJslaYSW7QF21FyddT9gusxtMlgd7sUx5bJNmZm8nlRu/SndcrES7+PfKkj4RSfYFaGrdliWSJzFwp4KCLRScy/mN0UpQSR9EHC/CBI4ry5gscmZpTlkW7UXIDGxsb+Fy/fl2qPbPI6ZIbrvUvH704v73HTVXeo9XiXqI7JXJjBYLixuyK/XJnQrkBq3ocKFkPsXt7JMOdBykrWEYJk8uNBAZ0rqQ0KmphYymGkFjkc3F1IHdsdkkMS0S5KR90cbQT9+RCU5taJz2BWvTBAvJZH1Fp/AtyVt28Fu3Gyg3zkiPipk002elkQG4ev/j5483v3SXeKKdTbvrLtHLjXsCx79S43Me9blPDLakoK/sPLeaTm1QfQi/xWthY1NkOWCzyWXMTGzByA9dFVq4UcNDFYSd6FNuJtNKXUPJOilLiKPpggVSpPMmSs+pOUe5mKZ6rIpIzMioZXs8/hKMlN+T/tZ8a1coccuMegswqN8XntceWG7ArIPsFmZ3Uue73ZZzy9ryyPBXzyU3nHVKjoucqL4aQWFLlMKR1aSZNSFikCuLgpnzQRdaJphg9nYiFbaqXKPpggVRZII+9QfuXs+pOkfjUzpuMGdxMi+g3g5oDLkVVbrjfOq0hKtALCndUrnst1odqal/ieHLjWrUPIViBYNg61MGS2h9ljNzYwdvfSdg+88c6NZiLtpJC/2nfzMwnN519snwQ/x65kddhwWqZWFLFhJms5mq64Ia3aPH/TnRU4iZNND+CWrdsU71E0QcLpMoh7vH53HKjJ3apJ8RKVW7sD3/aL5uoG6UWscKJyM34FGCM3LgXcGTR5IydD1/cedAy69t9yuRyoxduj3wI8qwkf6EmR277NsuAxdGOhKusnohqWZi48JaYd1M+6OJoJ/JgGGeJ28FO9KCpXkJaOR8skCqHoKfySEVOy9xys5RzTlVu7AXdfk3Oep5CuaEk63EZIzdgm8uZtN/TN163sdgXAl1p/Lh0QiaXG5A4aby94pDAHrzWuc9Lz7pQEqRtqiwgVsUo/u6hda4UsHD3nehE2Dr/vBNRBJqkegkR1qJqpIpBhRuRXUu5IaTtBd2O8H7PlcmNazLyu5uRcmMf08jPsuyaqPNZr21CsuleLGK+yW825pAbiQ2oRYJD1YE7eePBgaqSXUmBGFPlEH384bQJOuUGdCIuH4G8E11/2TWXRUPayZ8YU+UoOgtZW6233CRrBeu5lNwcbxHRIzdu/P0PR4qMlBumaV/ns+oz+LqN4MRFUiQ7KsrcS6o55EbTEKj9nphAskKggU1cOTUBOrx+/bo45B2KPVUOkTFA3lu/3CzbiRjxz88nEoOdvblyHRy4IjcgfQrrLTftCO/3PIZwgAu2nnTAfc2cv/OyFCPlBtxrh1p6Xrdxz+x1ANitis29pJpDbkCjC1iMoBEcQuCzLE9cGGvyAojL1tYWqwm22LWrPFUB2ZUqBsQCUsXQLzewVCdMR4fKFHZ2dhYz3t3c3BQje11qA7IrVTI4uva5ZnLjnhQ0sgP31LOtIPbGTul8bOG0r7OVi/AxCc54uXFT0CJPjtvYo6Mvtkkx65mJmeQGiCtZAhQhXPPAYxkiTzdysNcWKeKQKh0sJTc1ip1AY9ZMIZ8yyN5UKaFasGZyA/bO+c61B3lgIEn5G4DcY+XvB5NQ5BGeP+/EU3omH5EXl/NFgVM0iv5dZLb9rXArLt8ahxbGyw3kP0E41/G6jROU/BmNHRtJkJyWOZhPbgQyFFISgpNbNPHGB5ZR7cPRhBDa2NjAmS2fsaR9JXCDVOmAAeDvIn+SThSUkZGL7jBrPte0EnoOzRnIO5GGtZ4ZmzjUBmlZyjmnKjf5C6woiPwHBh9+/di++F8rxW+gaz/80TL4m+xiKS4l3NpNCp4yEflBhp1IbT0yidzkgxlMuNAOu4wqypNT1ZHLxgZzy03wa6AqN9zzB6WBgk9DC/J8obas0FIUqTwtyktyPUrtoUmtFNOfSeQmz7aKx7K4E1tLvpyQzbSkCrkJxlOVG+D6zl9jtYUMiJghkOzKS4t70KBwVy/6S6n9aZgbj/YbrRoLkzxNa5TigCeRG7D9DD6Bcs+50JS0o4T9Z5ppSRVyE4ynJTcC1z0SoJkOMU/YcAu1N2c+Ez/qw9VPkDcuevzpgW7pSjqkEFG1G7hQbMWBBr+rYiQMr6FWjLwxYAYmbdkOroAaMHI94uCYmalMUOaYrBU4b+pMaZ/G4xFyE4xnWG7OEsQhcY58SCGkWdzNkQvkcBTVGmQrWdeHkJtgPL8uuTlB7JpuMFs5hYTcBOMJuVkFLAPtd0yryaemJeQmGE/IzSpg4aZa896Ih80nSMhNMJ6Qm9m59+yVTW3aL16fWkJugvGE3MwIa6jz23tWa9Y0tYGQm2A8ITcTg6AgK+8e/YvxUt6u/8d1p5+Qm2A8ITcT4yRGC1ozx+swKyPkJhhPyM3EOJWR8tHm7vrmNULITTCekJuJ0fee2X6y/cPVB8/X8WvvnJcvXz59+jT/81FB0E/ITRAEKyLkJgiCFRFyEwTBSnjz5n+Bsg0wC+hiuAAAAABJRU5ErkJggg==
Weekly Challenge #48
Use Case:
There are 3 warehouses across the US that supply 26 retail locations. The retailers have products/items that are in high demand and, as a result, out of supply within their stores.
Your job as a distribution manager is to allocate as much product from the warehouses as possible, but there are a few constraints:
1. A retailer location can only be supplied by the nearest warehouse.
2. Prioritization of product allocation across retailers is dependent on required product/item need at the location. For example higher priority is the where there is the highest required.
Goal:
Allocate as much product from the 3 warehouses as possible to the 26 retail locations
X
9
68
7
1114
4
343391.9
6
384310.3
9
303567.3
10
385761.6
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_c0810250903845029127256e8ae4be2f_.yxdb
Single
Profile
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_b1690fd0b576420da449dcb08230bd90_.yxdb
Single
Profile
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_14eeafa6f29d4655aeb6b02a9e41270d_.yxdb
Single
Profile
A
Houston
TX
-95.562207
29.839187
B
Birmingham
AL
-86.625591
33.410321
C
Seattle
WA
-122.291399
47.603993
D
Detroit
MI
-83.255088
42.412144
E
Nashville
TN
-86.729174
36.135464
F
Chicago
IL
-87.597287
41.734
G
Lansing
IA
-91.228717
43.361018
H
Fayetteville
NC
-78.907738
35.024302
I
Miami
FL
-80.31047
25.792304
J
Tampa
FL
-82.539427
27.886595
K
Charleston
SC
-80.063175
32.931254
L
Los Angeles
CA
-118.386826
33.945722
M
Sacramento
CA
-121.40379
38.568865
N
Tucson
AZ
-110.845502
32.311728
O
Dallas
TX
-96.770293
32.84673
P
Austin
TX
-97.713678
30.19727
Q
Tulsa
OK
-95.861086
36.177745
R
New York
NY
-74.002527
40.734408
S
Baltimore
MD
-76.467296
39.3277
T
Portland
OR
-122.889658
45.563461
U
Philadelphia
PA
-75.171584
39.955317
V
Boston
MA
-71.066428
42.315352
W
Helena
MT
-112.00339
46.593143
X
Denver
CO
-104.943955
39.709101
Y
Milwaukee
WI
-87.925026
42.96156
Z
Las Vegas
NV
-115.242869
36.177034
Retail Location
Main
1
824
Main
2
1145
Main
3
1354
Main
4
916
Main
5
1120
Main
6
635
Main
7
1056
Main
8
824
Main
9
616
Main
10
679
Second
1
432
Second
2
589
Second
3
111
Second
4
123
Second
5
954
Second
6
376
Second
7
1124
Second
8
260
Second
9
88
Second
10
700
Third
1
145
Third
2
998
Third
3
1037
Third
4
34
Third
5
45
Third
6
732
Third
7
856
Third
8
621
Third
9
21
Third
10
1345
Warehouse Supply
Main
Huntsville
AL
34.737177
-86.603266
Second
El Paso
TX
31.767002
-106.492058
Third
Colorado Springs
CO
38.825775
-104.831478
A
1
47
A
2
29
A
3
50
A
4
12
A
5
19
A
6
72
A
7
71
A
8
2
A
10
40
B
2
46
B
3
36
B
4
7
B
5
48
B
6
71
B
7
19
B
10
4
C
1
34
C
2
52
C
3
66
C
4
17
C
6
3
C
7
57
C
8
77
C
10
13
D
2
1
D
3
76
D
4
46
D
6
21
D
7
40
D
10
41
E
1
44
E
2
14
E
5
75
E
6
32
E
7
14
E
8
76
E
10
42
F
1
71
F
2
28
F
3
46
F
4
54
F
5
9
F
6
52
F
7
43
F
8
33
F
9
59
F
10
16
G
1
60
G
2
2
G
3
51
G
4
66
G
5
47
G
6
28
G
7
66
G
9
30
G
10
55
H
1
11
H
2
69
H
3
5
H
4
41
H
5
54
H
7
28
H
8
57
H
9
17
H
10
36
I
2
6
I
4
73
I
5
43
I
6
3
I
7
70
I
8
70
I
9
16
I
10
17
J
1
71
J
2
35
J
4
5
J
5
79
J
6
9
J
7
38
J
9
55
J
10
72
K
1
44
K
2
13
K
3
63
K
4
73
K
6
62
K
7
61
K
8
20
K
10
25
L
1
26
L
3
10
L
4
35
L
5
62
L
6
16
L
7
73
L
8
35
L
9
78
L
10
33
M
2
12
M
3
23
M
4
55
M
5
46
M
6
9
M
7
4
M
8
8
M
9
76
M
10
60
N
2
9
N
4
29
N
5
12
N
6
4
N
7
36
N
8
3
N
10
8
O
1
15
O
2
35
O
5
30
O
6
43
O
8
78
O
9
48
O
10
64
P
2
60
P
3
33
P
4
5
P
5
9
P
6
27
P
7
62
P
8
71
P
9
30
P
10
50
Q
2
22
Q
3
32
Q
4
11
Q
5
4
Q
6
46
Q
7
69
Q
8
46
Q
9
12
Q
10
53
R
2
76
R
3
15
R
4
13
R
5
69
R
6
41
R
7
49
R
8
50
R
10
9
S
1
23
S
2
9
S
4
27
S
5
31
S
6
31
S
7
17
S
8
69
S
9
53
S
10
21
T
1
19
T
2
66
T
3
76
T
4
56
T
5
78
T
6
53
T
7
62
T
8
2
T
9
12
U
1
71
U
2
54
U
4
3
U
5
54
U
6
3
U
7
48
U
8
6
V
1
65
V
2
30
V
3
4
V
4
12
V
5
50
V
7
37
V
8
10
V
9
76
W
1
76
W
2
68
W
3
10
W
4
48
W
5
65
W
7
60
W
8
15
W
9
31
X
1
8
X
2
49
X
3
57
X
4
23
X
5
25
X
7
39
X
8
45
X
9
68
Y
1
6
Y
2
47
Y
4
1
Y
5
26
Y
6
64
Y
7
33
Y
8
41
Y
9
33
Y
10
7
Z
1
60
Z
2
25
Z
3
39
Z
4
45
Z
5
75
Z
6
18
Z
7
18
Z
8
25
Z
9
2
Z
10
10
Retail Demand
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_fa55e129c7874c6ead9a81cf1088d0e9_.yxdb
Single
Profile
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_3634c8289e884797a0e81ce26fc0e7cb_.yxdb
Single
Profile
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_08330001cfdd41258e0f2a089e79fa63_.yxdb
Single
Profile
Double
Double
Miles
Latest
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_ff6c9966a6c9426496cbbd26fd03cfc2_.yxdb
Single
Profile
Item - Ascending
Warehouse - Ascending
Required - Descending
Delta = IIF([Count]-[RunTot_Required]>=0,0, IIF([Count]-[RunTot_Required]>-1*[Re...
Delta - Descending
Allocated_Units - Descending
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_bd0a724f02004910a4cf54eb4d11e8bf_.yxdb
Single
Profile
Double
Double
Centroid
Centroid2
Latest
Miles
C:\Users\irbrtp\AppData\Local\Temp\Engine_10544_630f7ec6ddc240bbb648eb3e863e2aae_\Engine_7008_798e17f311a3476287bc6794e83dd074_.yxdb
Single
Profile
Simple
Sum_Distance_Travelled
<
400000
[Sum_Distance_Travelled] < 400000
Horizontal
challenge_48_start_file