I am trying to introduce re-order levels and re-order quantities for a small business and I am getting nowhere and would welcome advice from somebody who could understand and help. I have tried to attach a simple excel spreadsheet but the site won't let me update as it is not the right format ? so I have cut and pasted below. The formula seems to work in a simple form but when it comes to re-order quantity the simple formula doesn't stand up. I am sure I am not making any sense but have a look below 
| ACTUAL CALCULATION | re-order level is driven by lead time demand and safety stock | |||||||||||||||||
| lead time demand = lead time in days x daily sales | ||||||||||||||||||
| stock on hand | 40.00 | |||||||||||||||||
| allocated | 10.00 | If you hover over the inputs there are comments. Where I am running into trouble is | ||||||||||||||||
| back order | 0.00 | setting the re-order quantity. So in the example here we have 40 in stock and | ||||||||||||||||
| forward order | 0.00 | 10 allocated so free stock is 30. Using Lead time Demand and safety stock which is the | ||||||||||||||||
| available stock | 30.00 | normal formula used it works out that you have now fallen below rol which is set at 37 | ||||||||||||||||
| qty on order | 0.00 | If things go as predicted you will use 1.33 x 7 days before the stock arrives so you are back to expected | ||||||||||||||||
| lead time | 7.00 | stock. My problem is where somebody now orders another 20 after the original 10. We now have 10 left in | ||||||||||||||||
| monthly usage | 40.00 | available stock and only 10 on order but using the fomula below it would now only order the re-order qty again | ||||||||||||||||
| daily usage | 1.33 | which is set to 10 (9.33) thus now allowing for the full 20 ordered so the formula it too simplistic. | ||||||||||||||||
| lead time demand | 9.33 | does that make any sense at all to you !!!!!! | ||||||||||||||||
| safety stock | 27.69 | |||||||||||||||||
| the formula needs somehow to get the stock back up to the re-order level | ||||||||||||||||||
| re-order level | 37.03 | |||||||||||||||||
| re-order qty | 9.33 |