Need help for an Excel formula

[marabona]

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

 

[Gordon]

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.

Re-order of goods for re-sale comes from a STOCKS record, NOT the P&L......
In your stock sheet you just say "if the quantity of free stock is less than
a certain amount, re-order" Of course a time calculation also comes in
because you can't get delivery instantly (well not usually anyway).
I think you'll find this is a lot more complicated than you think.....

 

[marabona]

This is just a P+L for internal purposes, therefore general accounting
principles do not matter. If someone could answer with formulae it would be
much appreciated

 

[Roger Govier]

Hi

=(INT(A1/2500)+1)*2500

 

[Gordon]

This is just a P+L for internal purposes, therefore general accounting
principles do not matter. If someone could answer with formulae it would
be
much appreciated

Well just do what I said - have a cell on the P&L sheet that says no of
units sold. On another sheet in the same workbook, you have your opening
stock in one cell, the amount sold in another cell, (data from the P&L
sheet), and a Stock remaining cell. (Opening - sold). Then in another cell
you say "if the Total remaining is LESS than a certain number, do the
re-ordering process".

I'm sure someone of your calibre can work out the formula from that - if you
can't then I suggest you find someone locally to help you...

 

[Gordon]

This is just a P+L for internal purposes, therefore general accounting
principles do not matter. If someone could answer with formulae it would
be
much appreciated

Also be aware that the COST of goods sold will NOT always equal number x
cost. The COST of goods sold is Opening Stock for the period plus Purchases
in the period less closing stock at the end of the period....