Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help for an Excel formula
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help for an Excel formula
"marabona" wrote in message
... 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..... |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help for an Excel formula
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 "Gordon" wrote: "marabona" wrote in message ... 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..... |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help for an Excel formula
Hi
=(INT(A1/2500)+1)*2500 -- Regards Roger Govier "marabona" wrote in message ... 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 "Gordon" wrote: "marabona" wrote in message ... 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..... |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help for an Excel formula
"marabona" wrote in message
... 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... |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help for an Excel formula
"marabona" wrote in message
... 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |