I am creating a P+L and need a formula for this instance. I am reasonably profficient on this software but have never had to generate a spreadsheet like this.

Could someone advise a formula for this instance:

I have a units sold column which generates the rest of the P+L, but there are some variable fields. 40,000 units are ordered no matter what, and then any amount over this would require a reorder, but this can only be done in multiples of 2,500. so if I enter 51,000 in the units sold field, i need the order costs to calculate an order of 52,500 as it would not be possible to order any less. The unit costs are not variable (ie there are no economies of scale benefits) so it is just a matter of making sure the units sold amount generates an order amount which is possible due to the minimum reorder level.

Any assistance on this, even a link for further advice would be much appreciated.

Lewis