Ordering Stock
This is an example of a spreadsheet to calculate the best time interval to order stock.
Scenario | |
A garage fits exhaust systems. | |
The manager orders the exhausts on a regular basis. | |
Each time an order is made for new stock, there is a fixed administrative cost. | |
The exhausts are kept in stock until needed. | |
Keeping the exhausts in stock incurs a cost due to capital tied up and warehouse costs. | |
The supplier of the Exhausts gives a discount on large orders. | |
Objective | |
Find the time interval to order stock which will result in the lowest Admin and Warehouse costs. | |
Input Data |
Cost of a single Exhaust system : | £75 | |
Cost of keeping Exhaust in stock. (As a % of the stock value) : | 12% | |
Quantity of Exhausts used per day : | 10 | |
Admin cost each time new Exhausts are ordered : | £25 | |
Average quantity of Exhausts in stock (As % of ordered quantity) : | 0.5 | |
Ordering Intervals to evaluate. (Expressed in Days) : | ||
Suppliers first Price Break and Discount% offered : | 200 | 1% |
Suppliers second Price Break and Discount% offered : | 750 | 5% |
Output | ||||||||
Ordering Interval In Days | Quantity Per Order | Order Value | Order Discount | Orders Per Year | Annual Admin Cost | Annual Ware house Costs | Annual Total | The Best Ordering Interval |
1 | 10 | £ 750 | £ - | 365 | £ 9,125 | £ 45 | £ 9,170 | - |
2 | 20 | £ 1,500 | £ - | 183 | £ 4,575 | £ 90 | - | |
4 | 40 | £ 3,000 | £ - | 92 | £ 2,300 | £ 180 | £ 2,480 | - |
6 | 60 | £ 4,500 | £ - | 61 | £ 1,525 | £ 270 | £ 1,795 | - |
8 | 80 | £ 6,000 | £ - | 46 | £ 1,150 | £ 360 | £ 1,510 | - |
10 | 100 | £ 7,500 | £ - | 37 | £ 925 | £ 450 | £ 1,375 | - |
12 | 120 | £ 9,000 | £ - | 31 | £ 775 | £ 540 | £ 1,315 | - |
14 | 140 | £ 10,500 | £ - | 27 | £ 675 | £ 630 | £ 1,305 | - |
16 | 160 | £ 12,000 | £ - | 23 | £ 575 | £ 720 | £ 1,295 | - |
18 | 180 | £ 13,500 | £ - | 21 | £ 525 | £ 810 | £ 1,335 | - |
20 | 200 | £ 15,000 | £ 150 | 19 | £ 475 | £ 900 | £ 1,225 | Best |
22 | 220 | £ 16,500 | £ 165 | 17 | £ 425 | £ 990 | £ 1,250 | - |
24 | 240 | £ 18,000 | £ 180 | 16 | £ 400 | £ 1,080 | £ 1,300 | - |
26 | 260 | £ 19,500 | £ 195 | 15 | £ 375 | £ 1,170 | £ 1,350 | - |
28 | 280 | £ 21,000 | £ 210 | 14 | £ 350 | £ 1,260 | £ 1,400 | - |
30 | 300 | £ 22,500 | £ 225 | 13 | £ 325 | £ 1,350 | £ 1,450 | - |
32 | 320 | £ 24,000 | £ 240 | 12 | £ 300 | £ 1,440 | £ 1,500 | - |
34 | 340 | £ 25,500 | £ 255 | 11 | £ 275 | £ 1,530 | £ 1,550 | - |
36 | 360 | £ 27,000 | £ 270 | 11 | £ 275 | £ 1,620 | £ 1,625 | - |
38 | 380 | £ 28,500 | £ 285 | 10 | £ 250 | £ 1,710 | £ 1,675 | - |
40 | 400 | £ 30,000 | £ 300 | 10 | £ 250 | £ 1,800 | £ 1,750 | - |
42 | 420 | £ 31,500 | £ 315 | 9 | £ 225 | £ 1,890 | £ 1,800 | - |
44 | 440 | £ 33,000 | £ 330 | 9 | £ 225 | £ 1,980 | £ 1,875 | - |
46 | 460 | £ 34,500 | £ 345 | 8 | £ 200 | £ 2,070 | £ 1,925 | - |
48 | 480 | £ 36,000 | £ 360 | 8 | £ 200 | £ 2,160 | £ 2,000 | - |
50 | 500 | £ 37,500 | £ 375 | 8 | £ 200 | £ 2,250 | £ 2,075 | - |
52 | 520 | £ 39,000 | £ 390 | 8 | £ 200 | £ 2,340 | £ 2,150 | - |
54 | 540 | £ 40,500 | £ 405 | 7 | £ 175 | £ 2,430 | £ 2,200 | - |
56 | 560 | £ 42,000 | £ 420 | 7 | £ 175 | £ 2,520 | £ 2,275 | - |
58 | 580 | £ 43,500 | £ 435 | 7 | £ 175 | £ 2,610 | £ 2,350 | - |
60 | 600 | £ 45,000 | £ 450 | 7 | £ 175 | £ 2,700 | £ 2,425 | - |
Things To Try | |
Change the Discount % to 0% and 0%. | |
Change the Ordering Interval to 1 or 30. | |
Change the Cost of the Exhaust making it cheaper or more expensive. | |
Change the Quantity used per day to a larger or smaller number. | |
Explanation | |
Column A | Ordering Interval In Days |
The first of these cells has the value 1 entered in it. | |
This is the smallest ordering period, which would require stock to be ordered every day. | |
The second cell picks the ordering interval from the Input Data table. | |
The third and subsequent cells add the ordering interval to the previous cell to create | |
a list of values of the same interval. | |
Column B | Quantity Per Order | |
This is the number of Exhausts which will need to be ordered. | ||
Calculation : | OrderingInterval * QuantityUsedPerDay | |
Column C | Order Value | |
This is the value of the Order before any discount. | ||
Calculation : | QuantityOrdered * CostOfExhaust | |
Column D | Order Discount | |
The discount which can be subtracted from the order value. | ||
The discount is only given on orders which are equal to or greater than the | ||
Price Break values set by the supplier. | ||
Calculation : | OrderValue * SupplierDiscount | |
The supplier discount is calculated using the =IF() and the =AND() functions. | ||
If the OrderQuantity is equal to or above the first Price Break, but below | ||
the second Price Break, then the first Price Break discount is used. | ||
=C29*IF(AND(B29>=$G$24,B29<$G$25),$H$24,IF(B29>=$G$25,$H$25,0)) | ||
If the OrderQuantity is equal to or above the second Price Break, | ||
the second Price Break discount is used. | ||
=C29*IF(AND(B29>=$G$24,B29<$G$25),$H$24,IF(B29>=$G$25,$H$25,0)) | ||
If the OrderQuantity does not qualify for a discount, zero discount is used. | ||
=C29*IF(AND(B29>=$G$24,B29<$G$25),$H$24,IF(B29>=$G$25,$H$25,0)) | ||
Column E | Orders Per Year | |
This is how many orders will need to be made based upon the ordering interval. | ||
With an interval of 1, there will have to be 365 orders. | ||
Calculation : | 365/OrderingInterval | |
This calculation may give results which are decimal, such as 2.3 | ||
This decimal will cause problems, due to the fact that the number of | ||
orders must always be a whole number. | ||
The =CEILING() function has been used to 'round up' any decimals to | ||
the next highest whole number. | ||
=CEILING(365/A29,1) | ||
Column F | Annual Admin Costs | |
This is the administration costs involved in making the orders. | ||
Calculation : | OrdersPerYear * AdminCost | |
=E29*$G$20 | ||
Column G | Annual Warehouse Costs | |
This is the cost of keeping the stock in the warehouse. | ||
It is based on the managers knowledge that on average the stock level is 50% of the | ||
quantity ordered. | ||
Calculation : | QuantityOrdered * AverageStockLevel) * ExhaustCost * WarehousingCost | |
=(B29*$G$21)*$G$17*$G$18 | ||
Column H | Annual Total | |
This is the full yearly cost of ordering the Exhausts, based upon how frequently the | ||
orders are made. | ||
It does not take in to account the actual costs of the Exhausts, as the manager only | ||
wants to know what the lowest values for the overheads associated with ordering and | ||
storing the exhaust systems. | ||
However, the Discount figure is taken into account as this can be used to offset some | ||
of the overheads. | ||
Calculation : | AnnualAdminCosts + AnnualWarehouseCosts - OrderDiscount | |
=F29+G29-D29 | ||
Column I | The Best Ordering Interval | |
This shows the Best ordering interval, giving the lowest annual overheads. | ||
It compares the value in column H against the minimum value for all of column H. | ||
If the two values match the word Best is shown, otherwise a dash is shown. | ||
=IF(H29=MIN($H$29:$H$59),"Best","-") | ||
0 comments:
Post a Comment