Guitarmageddon
Board Regular
- Joined
- Dec 22, 2014
- Messages
- 161
*pic included below*
So I am working through our inventory replenishment report, trying to compute some holiday build orders. Our orders compute based on the difference between the "fill-to" (AE) subtracting current "on hand"/"on order" (AF:AG). The difference is the quantity which will order, which is rounded to the case increment which that item is sold (V). I have a formula in AC which shows me the quantity that would order based on the current inventory positions shown. However in this case, I'm trying to figure out how to back in a number of a fill-to (AE) to generate the order quantity listed in AA (build units).
My formula for the CURRENT fill-to level and its order is in the formula bar of my screen, on the link below.
<ae2,roundup((ae2-(af2+ag2)) V2,0)*V2,0)
<ae2,roundup((ae2-(af2+ag2)) V2,0)*V2,0)
but since excel doesn't really do algebra, I'm mentally stuck.
For example, in row two, the equation for x "would" be
288=IF((AF2+AG2)<X,ROUNDUP((X-(AF2+AG2))/V2,0)*V2,0) **NOTE THE VARIABLE X**
X is the "fill-to" number I'm trying to figure out what I need to fudge it to, to have our system auto order the desired quantities of build units (AA.)
Long and short of it: I work with product buying teams that don't want to take the time to key my suggested quantities in "AA." They would rather get our auto order system to generate it. However, i don't know what formula to use to find what Id need to fudge the "fill-to" level to get that. I'm hoping this makes sense to someone, please let me know if theres any more info or clarification i can provide.
This should be a public link, please let me know if it doesn't work.
<a href="https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk" target="_blank">https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk</ae2,roundup((ae2-(af2+ag2))><a href="https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk" target="_blank"><a href="https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk" target="_blank"></ae2,roundup((ae2-(af2+ag2))>
So I am working through our inventory replenishment report, trying to compute some holiday build orders. Our orders compute based on the difference between the "fill-to" (AE) subtracting current "on hand"/"on order" (AF:AG). The difference is the quantity which will order, which is rounded to the case increment which that item is sold (V). I have a formula in AC which shows me the quantity that would order based on the current inventory positions shown. However in this case, I'm trying to figure out how to back in a number of a fill-to (AE) to generate the order quantity listed in AA (build units).
My formula for the CURRENT fill-to level and its order is in the formula bar of my screen, on the link below.
<ae2,roundup((ae2-(af2+ag2)) V2,0)*V2,0)
<ae2,roundup((ae2-(af2+ag2)) V2,0)*V2,0)
but since excel doesn't really do algebra, I'm mentally stuck.
For example, in row two, the equation for x "would" be
288=IF((AF2+AG2)<X,ROUNDUP((X-(AF2+AG2))/V2,0)*V2,0) **NOTE THE VARIABLE X**
X is the "fill-to" number I'm trying to figure out what I need to fudge it to, to have our system auto order the desired quantities of build units (AA.)
Long and short of it: I work with product buying teams that don't want to take the time to key my suggested quantities in "AA." They would rather get our auto order system to generate it. However, i don't know what formula to use to find what Id need to fudge the "fill-to" level to get that. I'm hoping this makes sense to someone, please let me know if theres any more info or clarification i can provide.
This should be a public link, please let me know if it doesn't work.
<a href="https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk" target="_blank">https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk</ae2,roundup((ae2-(af2+ag2))><a href="https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk" target="_blank"><a href="https://drive.google.com/file/d/0B2z6HIm4YiM_Q0wxYTliRmluM3FxRnhRYmpndjY0c1dtRW5Z/view?usp=drivesdk" target="_blank"></ae2,roundup((ae2-(af2+ag2))>
Last edited:
