Inventory Management Assignment 2: Forecasting, EOQ, and ABC Analysis

This document presents a comprehensive solution to an inventory management assignment, addressing various key concepts and calculations. It begins with forecasting techniques, including three-month moving averages and exponential smoothing, providing step-by-step calculations for demand prediction. The assignment then delves into seasonal indices, tracking signals, and transit inventory reduction. Cost analysis is a significant component, encompassing annual carrying cost, inventory turns ratio, and the impact of improved materials management. Furthermore, the solution includes an ABC classification based on annual dollar usage, determining inventory categories. Economic Order Quantity (EOQ) calculations are performed, considering average inventory, ordering costs, and total costs, followed by a discount analysis. Finally, the solution concludes with Material Requirements Planning (MRP) calculations, determining planned order receipts using both EOQ and period order quantity methods, and calculating ending inventory and total inventory carried over a ten-week period. This assignment provides a detailed analysis of inventory management principles and their practical applications.

~ Danita Samuel
1. Given the following data, calculate the three-month moving average forecasts for months 4, 5, 6, and 7.
Month Actual Demand Forecast
1 80
2 90
3 110
4 105 80+90+110/3 = 93.3 = 93
5 120 90+110+105/3 = 101.6 = 102
6 105 110+105+120/3 =111.6 = 112
7 95 105+120+105/3 =110
(4 points)
Average forecast 4 = month1+month2+month3/ 3
2. Using exponential smoothing, calculate the forecasts for months 2, 3, 4, 5, and 6. The smoothing
constant is 0.18, and the old forecast for the month is 155.
Exponential smoothing: Ft= Ft-1+ alpha(At-1 –Ft-1)
Where Ft = The forecast of the next period
Ft-1 = Previously determined forecast of the present period
At-1 = Actual demand in the present period
Alpha= smoothing constant
Month Actual Demand Forecast Demand
1 110 155
2 98 = 155+0.18 (110 – 155) = 147
3 112 = 147+0.18 (98 – 147) = 138
4 126 = 138+0.18 (112 – 138) = 133
5 140 = 133+0.18 (126 – 133) = 131
6 = 131+0.18 (140 – 131) = 133
(4 points)
3. Given the following average demand for each month, calculate the seasonal indices for each month.
BU1283 – Inventory Management
Assignment #2

Seasonal index = Average demand / Average demand for total months
Month Average Demand Seasonal Index
January 30 30/108.75 = 0.2759
February 50 50/108.75 = 0.4598
March 85 85/108.75 = 0.7816
April 110 110/108.75 = 1.0115
May 125 125/108.75 = 1.1494
June 245 245/108.75 = 2.2529
July 255 255/108.75 = 2.3448
August 135 135/108.75 = 1.2414
September 100 100/108.75 = 0.9195
October 90 90/108.75 = 0.8276
November 50 50/108.75 = 0.4598
December 30 30/108.75 = 0.2759
Total 108.75
Note that your answer, if done correctly, should have all the seasonal indices add up to the number of
periods in the entire season, in this case 12.
4. A company uses a tracking signal trigger +/- 4 to decide whether a forecast should be reviewed. Given
the following history, determine in which period the forecast should be reviewed. MAD for the item is
15. Is there any previous indication that the forecast should be reviewed?
Deviation = forecast- actual
Tracking signal = cumulative deviation / 15
Period Forecast Actual Deviation Cumulative
Deviation
Tracking Signal
1 100 110 100-110 = -10 -10 -10/15= -0.6667
2 105 90 105-90 = 15 -10+15 = 5 5/15=0.3334
3 110 85 110-85 = 25 5+25= 30 30/15= 1.2
4 115 110 115-110 = 5 30+5=35 35/15= 2.3
5 120 105 120-105 = 15 35+15= 50 50/15= 3.3334
6 125 95 125-95 = 30 50+30=80 80/15= 5.3334
Since the tracking signal doesn’t remain within +/-4, the forecast need to be reviewed in 6th period

5. A company is using a carrier to deliver goods to a major customer. The annual demand is $6,000,000,
and the average transit time is 12 days. Another carrier promises to deliver in 8 days. What is the
reduction in transit inventory?
Annual Demand Value= $6000000
Daily demand value = Annual demand value /365 = $6000000/365 = $16,438.35
Average transit days = 12 days
New promised transit days = 8 days
Transit days reduction = Average transit days - New promised transit days = 12-8 = 4 days
Reduction in In transit inventory = Transit days reduction X Daily demand value
Reduction in transit inventory = 4 X 16438.35 = $65753.4
6. A bakery carries an average inventory of $3,000 in cakes. The cakes require special storage, as they
need to be kept refrigerated and are perishable. The bakery estimates capital costs at 10%, storage
costs at 25%, and risk costs at 50%. What is the annual carrying cost?
Annual carrying cost = 10% + 25% + 50% = 85%
Carrying cost = 85% x $3000 = $2550
7. If the annual cost of goods sold is $42,000,000 and the average inventory is $7,000,000,
a. What is the inventory turns ratio?
Inventory turns ratio = Annual cost of goods sold /Average inventory
= 42000000/7000000
= 6
b. What would be the reduction in inventory if, through better materials management, inventory
turns were increased to 9 times per year?
Average inventory = Annual cost of goods sold/ inventory turns
= 42000000/9 = 4666666.67
Reduction in average inventory = 7000000 – 4666666.67
= $2333333.33
c. If the cost of carrying inventory is 25% of the average inventory, what is the annual savings?
Annual savings = 25% of average reduction in inventory
= (25/100) X 2333333.33 = 583333.3325

8. Analyze the following data to produce an ABC classification based on annual dollar usage.
Part Number Annual Unit
Usage
Unit Cost ($) Annual $ Usage Annual $ Usage (%)
1 200 10 200 X 10 = 2000 2000/760600% = 0.262950302
2 17,000 4 17000 X 4 = 68000 68000/760600% = 8.940310281
3 60,000 6 60000 X 6 = 360000 360000/760600% = 47.33105443
4 15,000 15 15000 X 15 = 225000 225000/760600% = 29.58190902
5 1500 10 1500 X 10 = 15000 15000/760600% = 1.972127268
6 120 50 120 X 50 = 6000 6000/760600% = 0.788850907
7 25,000 2 25000 X 2 = 50000 50000/760600% = 6.57375756
8 700 3 700 X 3 = 2100 2100/760600% = 0.276097818
9 25,000 1 25000 X 1 = 25000 25000/760600% = 3.28687878
10 7,500 1 7500 X 1 = 7500 7500/760600% = 0.986063634
TOTAL 760600
Largest to smallest count based on annual $ usage
Part
Number
Annual
Unit
Usage
Unit
Cost ($)
Annual $
Usage
Annual $ Usage (%) Cumulative annual $ usage Category
3 60000 6 360000 47.33105443 47.33105443 A
4 15000 15 225000 29.58190902 29.58190902 + 47.33105443 =
76.91296345
A
2 17000 4 68000 8.94031028 8.94031028 + 76.91296345 =
85.85327373
B
7 25000 2 50000 6.57375756 6.57375756 + 85.85327373 =
92.42703129
B
9 25000 1 25000 3.28687878 3.28687878 + 92.42703129 =
95.71391007
B
5 1500 10 15000 1.97212726 1.97212726 + 95.71391007 =
97.68603734
C
10 7500 1 7500 0.98606363 0.98606363 + 97.68603734 =
98.67210097
C
6 120 50 6000 0.78885090 0.78885090 + 98.67210097 =
99.46095188
C
8 700 3 2100 0.27609781 0.27609781 + 99.46095188 =
99.73704969
C
1 200 10 2000 0.26295030 0.26295030 + 99.73704969 =
99.99999999
C
TOTAL 760600

9. An SKU costing $75 is ordered in quantities of 4,800 units, annual demand is 200,000 units. Carrying
costs are 18%, and the cost of placing an order is $47. Calculate the following:
a. Average inventory
Average inventory= Quantity ordered/2 = 4800/2 = 2400
b. Number of orders placed per year
Number of orders placed per year = Annual demand/ Ordered quantity
=200000/4800 = 41.6 = 42 orders
c. Annual inventory carrying cost
Annual inventory carrying cost = Average inventory X carrying cost per unit per annum
= 2400 X 75 X 18% = $32400
d. Annual ordering cost
Annual ordering cost = No. of orders in a year X cost per order
= 42 X 47 = $1974
e. Annual total cost
Annual total cost = Purchase cost + Carrying cost + Ordering cost
= (200000 X 75) + 32400 + 1974 = $15,034,374
10. A company decides to establish an EOQ for an item. The annual demand is 500,000 units, each costing
$11, ordering costs are $50 per order, and inventory carrying costs are 20%. Calculate the following:
a. The EOQ in units.
Annual holding cost per unit = cost per unit X holding cost percent
= 11 X 20% = 2.2
EOQ = SQRT [(2 x Annual demand x Order per cost) / Annual holding cost per unit]
= SQRT 2X 500000 X 50 / 2.2 = 4767.31
b. Number of orders per year.
Number of orders per year = Annual demand/EQQ
= 500000/4767.31
= 104.88 = 105 orders
c. Cost of ordering, cost of carrying inventory, and total cost.
Cost of ordering = (Annual demand / EQQ) X Ordering cost

= 500000/4767) X 50 = $5244.38
Cost of carrying inventory = EQQ/2 X holding cost per unit
= 4767/2 X 2.2 = 5243.7
Annual total cost = Annual inventory carrying cost + Annual ordering cost
= $5243.7 + $5244.38 = $10488.08
11. A company is presently ordering on the basis of an EOQ. The demand is 15,000 units a year, unit cost is
$15, cost of ordering is $40, and the cost of carrying inventory is 20%. The supplier offers a discount of
2.5% on orders of 1000 units or more. What will be the saving or loss of accepting the discount?
p = unit cost
Q = order quantity
H = holding cost
D = annual cost
Holding cost = 0.2 x 15= $3
EOQ= Square root (2*D*S)/H
= Square root (2*15,000*40/3) = 633 units
Total cost = annual carrying cost +annual ordering cost + annual purchase cost
= (Q/2) H + (D/Q) S + pD
= (633/2) x3 + (15,000/633) x 40 + 15x 15,000
= 949.5 + 947.86 +225,000
= $226,897.36
3% discount on 1,000 units
Annual Purchase cost = 15x15,000 x 97.5%
= $219,375
Annual ordering cost = 15,000 x 40/ 1000
= $600
Annual holding cost = q*h/2
= 1000 x 3* 97.5%
= $2,925
Total cost = $219,375 + $600 + $2925
= $222,900
Cost Difference= $226,897.36 - $222,900= $3,997.36
The saving of accepting this discount is $3,997.36
12. Given the following MRP record and an EOQ of 400 units, calculate the planned order receipts using the
economic order quantity. Next, calculate the period order quantities and the planned order receipts. In
both cases, calculate the ending inventory and the total inventory carried over the 10 weeks.

Week 1 2 3 4 5 6 7 8 9 10 Total
Net
Requirements
150 140 120 0 200 160 140 130 0 80 1120
Planned
Order Receipt
400 400 400
Ending
Inventory
250 110 390 390 190 30 290 160 160 80 2050
Period Order Quantity= EOQ/ Weekly Average Quantity
Weekly average demand = 1120/10 = 112 units
PQQ = 400 / 112 = 4 weeks
EOQ total inventory at the end= 2050 units
Week 1 2 3 4 5 6 7 8 9 10 Total
Net
Requirements
150 140 120 0 200 160 140 130 0 80 1120
Planned
Order Receipt
410 500 290
Ending
Inventory
260 120 0 0 300 140 0 160 160 80 1220
The total inventory has reduced from 2050 units to 1220 units over ten-week period