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
Output Data
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","-")
No comments:
Post a Comment