Question

Question 1:
A local company located in Nizwa assembles engines for customized trekking sport utility vehicle SUV. During the year end 2019 financial review, the company is concerned with its bloated inventory cost for the past five years. The profitability of the company has been declining year on year due to the effect of the increasing inventory costs. The newly hired manager who is a mechanical engineer is tasked to look into this matter and will provide a comprehensive proposal report to the company CEO to reduce the bloating inventory cost. The current consumption or usage of parts of the assembly department is shown in Table Q1. This data shows the typical annual usage or consumption of component parts for its engines. He found out that there is big mismanagement or neglect of the company inventories. Currently, the company is ordering parts from its suppliers locally and abroad once every three months.
(i) Develop an ABC analysis for the company's inventories. Show completely the results in tabular form using excel.
(ii) Create a PARETO analysis for the ABC analysis using excel   
(iii) Considering your 'class A' inventories, determine the economic order quantity EOQ using the following assumptions; zero safety stock for each class A inventories, stocking cost is 10% of the unit price, ordering cost for any class A inventories is 120 OMR per order. Determine the cost savings if EOQ is followed instead of the current ordering system of the company which is once every three months for
'class A' inventories

Table Q1: Annual Usage of Component Parts
Part Number		Annual quantity (unit)					Unit cost (OMR/unit)
111D		30,666					1.00
128H		49,465					0.50
196G		7,076					22.40
205Y		2,828					270.00
216U		2,979					12.80
217J		3,263					122.40
228G		804					98.40
235D		60,098					1.80
249E		13,760					6.30
258L		2,049					62.40
261K		19,091					1.20
272J		4,985					9.00
324H		21,848					1.50
333C		2,235					15.40
334U		7,128					24.00
352S		12,104					19.20
391J		2,149					51.60
421A		4,048					9.60
432S		4,038					7.60
436S		72,968					0.25
452F		3,334					13.50
462R		3,601					16.80
463H		2,820					132.00
478L		26,612					1.20
521I		2,974					13.80
532Q		1,194					144.00
610B		29,407					5.10

Answer 1

(i) ABC Analysis done using MS Excel.

Annual consumption value = Unit cost x Annual Quantity of each item

Percentage Value = Annual Consumption Value of each item x Total Value of Consumption

Category A: All items whose value is above 10%

Category B: All items whose value is between 2 and 10%

Category C: All items whose value is 1% or lower

(ii) Pareto Chart using MS Excel

(iii) EOQ Calculation

D: Annual Quantity Demanded

S: Ordering Cost

H: Holding Cost

EOQ = √ (2SD/H)

For Part 205Y, EOQ is 159 units.

For Part 217J, EOQ is 253 units.

For Part 463H, EOQ is 227 units.