Final Project $(20\%$ of the Final Mark$)$

Randy Smith, Inventory Control Manager.

Randy Smith was very proud of his new position as inventory control manager for the Johnson

Trinket Company. His primary responsibility had been fairly clearly defined.

Maintain an inventory level in the warehouse that ensures that production will not run out

of stock,

Since Randy had recently had a course in materials management, he knew an approach the should help him. He decided to make a list of inventory items in one small section of the control. If it worked in one sma

The following is the data Randy compiled

$\backslash$table$[[$Part Number,Part unit value in $$,\backslash$table$[[$Quantity currently$],[$in inventory$]],\backslash$table$[[$Average annual$],[$usage$]]],[1234,$$$0\mathrm{.}3,110,2100],[1235,$$$0\mathrm{.}20,550,900],[1236,$$$15\mathrm{.}00,400,1000],[1237,$$$0\mathrm{.}75,50,7900],[1238,$$$0\mathrm{.}76,180,2800],[1239,$$$4\mathrm{.}40,20,5000],[1240,$$$1\mathrm{.}80,200,1800],[1241,$$$0\mathrm{.}05,10,1200],[1242,$$$17\mathrm{.}20,950,2000],[1243,$$$9\mathrm{.}00,160,2500],[1244,$$$3\mathrm{.}20,430,7000],[1245,$$$0\mathrm{.}30,500,10000],[1246,$$$1\mathrm{.}10,25,7500],[1247,$$$8\mathrm{.}10,60,2100]]$

$\backslash$table$[[1248,$$$5\mathrm{.}00,390,4000],[1249,$$$0\mathrm{.}90,830,6500],[1250,$$$6\mathrm{.}00,700,3100],[1251,$$$2\mathrm{.}20,80,6000],[1252,$$$1\mathrm{.}20,480,4500],[1253,$$$5\mathrm{.}90,230,900]]$

When Randy scanned the list, he noticed several things that disturbed him, and he asked one of the experienced inventory clerks. The following list summarizes the part number issues the concerned Randy, and the explanation from the clerk:

Part number $1236,$ a very expensive part with almost half a year's worth of inventory This part is used for a product that has very cyclical demand, and the busy time of the yea is about to start.

Part number $1241$ is very inexpensive, yet the inventory is very small. This part has a supplier with an erratic delivery history, and the part also has a very long lead time. A I of $150$ have been on order for some time, and are now several days past due.

Part number $1242,$ like $1236,$ is expensive with almost hall a year's worth of inventorythis part is shipped to a location on the oher side of the country and is being accumulated this part is shipped to a location on the into a large lot to save shipping costs.

Part number $1246$ is not too expensive, with a low inventory. This part is produced inhouse and has a quality tolerance that the older equipment, which was used to produce it$,$ had a difficult time meeting. The last batch was rejected by the quality department,

Part number $1253$ is moderately expensive with a large inventory compared to usage. This part was subject to a recent quality audit, and almost $150$ of the iterms were rejected as a result of that audit.

Once Randy understood some of the issues, suddenty he did not feel quite as confident that he had the best approach in mind to control the inventory to meet the expectations of his boss.

Assignment

$(1)$ Use Microsoft EXCEL and the intormation above to evaluate the current situation. Also, create an ABC Analysis Chart in EXCEL.

$(2)$ Given your evaluation, try to develop an integrated inventory control poicy that Randy should

consider. For example, a$)$ For thems in category B$,$ you can use a specific criterion as follows if the current inventory lev of an item is lower than $20\%$ of annual usege, then your EXCEL table will automatically $($not manually$)$ show an alarm of $\text{'}$To Order' in a different color $($for example red color$).$ Design a EXCEL table wheve the above tunction. Is there other information that you would like to see that might help you to make a more effection Is there other information that you would like to see that might help you to make a more effective policy to manage items in category $C?$ If so$,$ what would that information be and how would you use it to help you? Any other specitic

c$)$ Any other specific methods that are appropriste to this situation.

Instructions:

The research report should be handed in betore week $15($April $11).$

Perscrialize the style of your text. Imagine you are gernerating a report to inform the autience. For

example, to address or to answer the requirements of your maneger.

Be clear and organized.

Teacher will not answer questions such as

"how to get in done with EXCEL? "how to yake an have studied this knowledge previously,

"how to make an ABC analysis?" it wil behas been covered in the class, "Can you check my work before I hand in it$?"$ I only evaluat