G
GD
Hi, I use What's Best! 6.0.0.1 as an optimisation tool within Excel for some
stock flows. I have a model constructed, which is tried and tested however
one of the pricing criteria have changed.
Previously one of the costings was a fixed amount per unit of stock, with
What's Best able to adjust the unit of stock to find a best total cost figure
(this included various other costs)
However now there is not a fixed figure, there is a strata - so the first
100,000 the storage location is priced at 0.10 per unit, per week. The next
100,000 is 0.07, the next 0.05 etc etc etc.
I have used =IF(R7>100000, 100000, R7) in Q10, with R7 being the closing
stock figure equal to the adjustments the optimiser makes, then
=IF(R7<200000, R7-Q10, 100000) in Q11, so with a total figure of 136000 in R7
I get results of 100000 in Q10 and 36000 in Q11, which can then be multiplied
by the corresponding 0.10, 0.70 (then by 52) to achieve the costs which are
fed into the minimised cell...
The issue I have is Whats Best doesn't like these IF formulas, it reports it
requires a non-linear solve as a direct consequence..aside from putting in
manual calculations up to 100000 then 200000 as the optimisations are run,
can anyone think of a way round this, ideally with experience in WB?
stock flows. I have a model constructed, which is tried and tested however
one of the pricing criteria have changed.
Previously one of the costings was a fixed amount per unit of stock, with
What's Best able to adjust the unit of stock to find a best total cost figure
(this included various other costs)
However now there is not a fixed figure, there is a strata - so the first
100,000 the storage location is priced at 0.10 per unit, per week. The next
100,000 is 0.07, the next 0.05 etc etc etc.
I have used =IF(R7>100000, 100000, R7) in Q10, with R7 being the closing
stock figure equal to the adjustments the optimiser makes, then
=IF(R7<200000, R7-Q10, 100000) in Q11, so with a total figure of 136000 in R7
I get results of 100000 in Q10 and 36000 in Q11, which can then be multiplied
by the corresponding 0.10, 0.70 (then by 52) to achieve the costs which are
fed into the minimised cell...
The issue I have is Whats Best doesn't like these IF formulas, it reports it
requires a non-linear solve as a direct consequence..aside from putting in
manual calculations up to 100000 then 200000 as the optimisations are run,
can anyone think of a way round this, ideally with experience in WB?