Stock Projection (Stock + Input - target)

Options
jahurtado
jahurtado
in Modeling
image.png

If anyone can help me with this: I need to perform a stock simulation by inputting values, as you can see in Mar 24, I do not have enough to reach the Best Estimate (what I need is 261 to reach). But if I input 1000 in Feb-24, I would have 146 + 1000 = 1146. I need to subtract the target for February, which is 261, from this line item, so I'll have 888 + the new input in March, which is 100 = 988. Feb is good but March should be 998 and Apr 1227. I've tried various approaches, but I believe I need some assistance with this. Can anyone help?

image.png
maquette_simulation_dynamic (1).xlsx

Tagged:

Best Answers

  • gaurav
    gaurav
    edited September 2023 Answer ✓
    Options

    Hi @jahurtado

    Use PREVIOUS function in STOCK BOM Simulation to add the input value from previous day and reduce the Target of previous day.

    Your formula will look like:

    STOCK BOM Simulation= Input+ Stock BOM + PREVIOUS(Input+Stock BOM - Target )

  • jahurtado
    jahurtado
    Answer ✓
    Options

    Hi @GauravKumar Thank you very much! Your assistance has been greatly appreciated. I wish you a wonderful day!

  • gaurav
    gaurav
    Answer ✓
    Options

    No Problem! Have a good one. Accept the answer if it resolves your issue.

Categories