Simple algebra/ inventory order

[Guitarmageddon]

*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))>

 

[istiasztalos]

Re: Help with simple algebra/ inventory order

Hi Guitarmageddon,

I'm looking at your screenshot but can't figure out what you're looking for. Both AA and AE are filled with values, not sure which one do you need the formula for...

Are you trying to come up with a prediction model to find what level of stock you need to "Fill-to" based on previous months sales, expected sales and stock level?

 

[oldbrewer]

Re: Help with simple algebra/ inventory order

you can't have 288=if(............

do you mean roundup (x-(af2+ag2))/v2 = 288

 

[Guitarmageddon]

Re: Help with simple algebra/ inventory order

So basically, the formula in AC is showing be the quantity ordered, based on the current "fill-to" level in AE. However, I'm trying to basically work backwards and get it to ORDER whats in AA by manually altering the "fill-to" level. I'm thinking this could be a job for solver, but again....I cant wrap my brain around this.

 

[oldbrewer]

Re: Help with simple algebra/ inventory order

please answer my post 3

 

[istiasztalos]

Re: Help with simple algebra/ inventory order

Hmm if I understand you correctly you want to set AE to a level so that AC would equal to AA?

If so, why not just add up AA+AF+AG? (not sure though where AA comes from, it doesn't seem to depend on AF:AG since you have items - like row 4 - where AA is high even though "fill-to" is less then AF...)

 

[oldbrewer]

Re: Help with simple algebra/ inventory order

25000	117	0	72
x	af2	ag2	v2							x	answer	increase
										10	-1.48
						345.5972	####			117	0
										150	0.46
						=(ROUNDUP((A1-B1-C1),0))/D1		250	1.85
										1000	12.26
										5000	67.8
										10000	137.2	69.4
										15000	206.7	69.5
	plugging your numbers in							20000	276.1	69.4
	we can see that there is a relationship						25000	345.6	69.5
	between x and the answer
	not sure your equation is right…

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>