Let us assume that you are the store manager of a store, stocking speciality products. Your responsibility is it to maintain the optimum stock for 10 different products in store. From your past experience you know the stock level to be maintained for these products, given the fluctuations in demand, the lead time for arrival of these products and the safety stock desired. Just before being promoted to this position, you would make a physical inventory check of these products everyday, pull out from your memory as to how many have been sold or committed to your customer, how many have arrived in store that day, how many have been ordered and are due to arrive and finally calculate what is the net quantity to be ordered. So you just scribble all the information in the pocket notebook that you carry with you all the time, and use a simple calculator to make your calculations as you go. Remember you have been dealing with your suppliers directly, and know by heart the part numbers / descriptions of these 10 different products and so have no difficulty placing an order over phone at the last minute before the supplier’s office closes.
However as your business grows, the management thought it fit to promote you to the senior level making you responsible for several functions like sales, administration, shipping etc apart from the inventory control / purchasing that you have been handling so far. Also your mandate is to automate the processes as much as possible and be willing to take more load ASAP.
As a first step in the process you thought it best to hire an assistant to your position, train him/her and delegate your inventory control / purchasing function. Since the assistant is new to this line, to prevent mistakes you decide to use a spreadsheet, which he/she can fill in and is possible for you to oversee from anywhere in the office / warehouse.
So before you delegate, by way of planning you list all your inputs and your desired output and draw a simple table on a sheet of paper outlining your requirement and ask your assistant to prepare a spreadsheet based on this.
Inputs to the spreadsheet:
- Part number of product to be ordered.
- Part description of the product.
- Quantity in stock
- Quantity sold / committed to customer that day.
- Quantity arrived that day.
- Quantity on order / due to arrive
- Safety stock desired for each product.
Output from spreadsheet:
- Net quantity.
- Net quantity to order.
Formulas for calculating the output :
Net Quantity = Qty in stock – Qty Sold + Qty Arrived + Qty Ordered
and
Net Quantity to order = Net Quantity – Safety Stock
Sheet 1:
Purpose of the spreadsheet:
To maintain optimum inventory level of products in store.
Drawn and updated by: Your assistant
Date prepared: Whatever date this spreadsheet is prepared.
Guidelines / Assumptions :
- Check physical stock and update the spreadsheet every day.
- Place order only when the net quantity to order turns negative and the quantitiy to order is the negative quantity.
- If the net quantity to order is either zero or positive, there is no need to order.
- To save on shipping bunch orders together as much as possible.
- Lead time for orders with suppliers fairly predictable and stable.
- There are no damage to goods in transit / storage and there are no rejects in consignment received.
Sheet 2:
| Part # | Product Description | Qty in Stock | Qty Sold | Qty Arrived | Qty Ordered | Net Qty | Safety Stock | Net Qty to Order |
|---|---|---|---|---|---|---|---|---|
| 1-11 | Product 1 | 4 | 3 | 1 | 2 | 4 | 3 | 1 |
| 2-22 | Product 2 | 2 | 5 | 4 | 0 | 1 | 4 | -3 |
| ….. | ….. | ….. | ….. | ….. | ….. | ….. | ….. | ….. |
| ….. | ….. | ….. | ….. | ….. | ….. | ….. | ….. | ….. |
| 9-99 | Product 9 | 1 | 1 | 1 | 1 | 2 | 2 | 0 |
| 10-100 | Product 10 | 2 | 2 | 2 | 0 | 2 | 3 | -1 |
<--BackTo: Creating a Worksheet that means business
Next: Create a skeleton worksheet –>