I need help urgently for a work problem.
So basically, the summation of opening stock and production give total stock.
While closing stock is
((opening stock+production)-sale)) and stock cover is Closing stock/Sale.
I need help in creating a formula using excel to make sure my stock cover equates to 1.5 without manually inputting production. For example, I want a formula that determines production with values rounded up to the nearest 500 which result in a stock cover of 1.5.
Substitute the cell reference for the names, so "Sales" below is the cell where this is, which might be something like A1, similarly "Open Stock" might be B1.
production = sales * 2.5 - opening stock
This does not fix the rounding up to the nearest 500. For that try
rounded_up_production = ROUNDUP(production/500,0)*500
And for the zero bounds
Or combine them into one, fairly horrible formula
rounded_up_production = IF(ROUNDUP((sales * 2.5 - open stock)/500,0)*500<0,0,ROUNDUP((sales * 2.5 - open stock)/500,0)*500)