robertscory2
New Member
- Joined
- Jun 29, 2015
- Messages
- 7
I work in demand planning have been creating an excel tool to make one of my functions a little easier. It basically looks at all the items a distribution center is requesting/forecasted to request, makes sure current production inventory can cover it, and then suggests rail car/truck combinations to get the items shipped out to the distribution center. Currently I am on the final stage of the project, where it suggests how to get the product shipped.
Attached is a sample of my current setup and thinking. Column C, titled "% of Car" is a calculation of the percentage of a rail car that will be taken up by the quantity demanded of the item listed in column B. My thought was to use solver to look through column C and find unique combinations that added to 1 (with a .05 buffer, so between .95 & 1). This would continue until the # Cars needed value is reached. I am having a hard time getting this to work. I don't know if there is a better way to approach it or if you could help me get it working in the current format but I would appreciate any help.
# Cars Needed = 2
<colgroup><col><col></colgroup><tbody>
</tbody>
Attached is a sample of my current setup and thinking. Column C, titled "% of Car" is a calculation of the percentage of a rail car that will be taken up by the quantity demanded of the item listed in column B. My thought was to use solver to look through column C and find unique combinations that added to 1 (with a .05 buffer, so between .95 & 1). This would continue until the # Cars needed value is reached. I am having a hard time getting this to work. I don't know if there is a better way to approach it or if you could help me get it working in the current format but I would appreciate any help.
# Cars Needed = 2
Item | % of Car |
22935 | 0.325347059 |
26568 | 0.05 |
26569 | 0.0125 |
40070 | 0.025 |
40497 | 0.0125 |
41883 | 0.0125 |
41884 | 0.0125 |
41886 | 0.0125 |
A0062 | 0.05 |
AX001 | 0.0125 |
AX003 | 0.025 |
AX016 | 0.0125 |
AX025 | 0.025 |
AX032 | 0.0125 |
AX033 | 0.1875 |
AX042 | 0.2625 |
AX081 | 0.0125 |
AX102 | 0.05 |
AX202 | 0.0125 |
AX833 | 0.0125 |
D0017 | 0.2875 |
K0120 | 0.0875 |
MP804 | 0.053758824 |
OG101 | 0.053741176 |
S0601 | 1.2125 |
S19 | 0.0125 |
W3628 | 0.025 |
<colgroup><col><col></colgroup><tbody>
</tbody>