Smith Corporation produces bucket loader assemblies for the tractor industry. The product has a long-term life expectancy. Smith has decided to implement a JIT inventory system. Smith is deciding whether to use ABC Co. or XYZ Co. as the supplier. The following relevant information is available for analysis:

One time cost to rearrange the shop floor into manufacturing cells is $305,000

Monthly demand is 1,000 units.

Purchase orders from ABC will cost $7.50 each. Purchase orders from XYZ will cost $7.00 each.

ABC will charge $110 per unit. XYZ will charge $100 per unit.

ABCs units will be inspected at a cost of 30 cents per unit.

Smiths required annual rate of return is 5%.

Stock-out costs are expected to be $3.50 per unit for ABC and $3.00 per unit for XYZ.

Stock-out units are expected to be 5% of the order quantity for ABC and 5% for XYZ. These stockouts are anticipated to occur during each reorder period.

Other annual carrying costs are 1% of the unit purchase cost for ABC and for XYZ.

Questions:

What is the EOQ if Smith chooses ABC Co.?

What is the EOQ if Smith chooses XYZ Co.?

From a quantitative perspective, which supplier should Jones use to implement the JIT system? Show the computations supporting your decision. Note that your solution should be in professional format, and should include a proper heading, date, consistent formatting, etc.

What is the minimum amount of cost savings (in dollars) that Smith would need to save by implementing the JIT system in order for the project to yield an acceptable return on investment?

Please show answer in Excel format