Develop an Excel based solution to the following inventory problem In class, we discussed an inventory model used to determine the optimal spares order quantity Q to minimize total inventory costs over a period of time (typically a year). Consider the scenario in which you are the engineer responsible for maintaining the spares inventory for a small manufacturing company. The table below details the spare parts estimated to be needed over a typical year, with the respective per unit dollar costs for each item. Item Item Cost Demand 1 $200.00 85 2 $3,500.00 4. 3 $10.00 1000 4 $0.50 2200 5 $1.00 820 6 $0.50 1620 7 $1.50 500 00 $0.60 1000 9 $0.15 2000 10 $0.90 200 11 $0.20 740 12 $3.00) 30 13 $0.02. 4500 14 $0.25 320 15 $14.00 5 16 $0.01 6000 17 $0.10 500 18 $0.05 1000 19. $2.00 20 20 $5.00 8 21 | $0.15 200 22. $1.00) 20 23 $0.01 1000 In addition, other costs associated with ordering and storing spare parts inventory are: Cp = $110/order Ch = 0.25 C + 1.50 $/unit/year Where, Ch inventory holding cost, $/unit/year Gitem cost, $/unit Cp order processing cost, $/order The inventory holding cost model includes a fixed cost regardless of unit type, and a variable cost that reflects financing and insurance costs associated with holding inventory. As you would expect, the variable costs are a function of the item cost. The General Manager has asked that you investigate several ordering policies so as to minimize annual inventory costs. You must use Excel files to respond to the following questions (part a and B. a. The first policy is to consider each part independently from the others. Please find the optimal spares order quantity (Q;") for each of the above parts and determine the total yearly inventory cost associated with this policy. Please make sure to round up so that all Qi* are integers (Excel has a ROUNDUP function). Spare parts only come in integer values; i.e., 1, 2, 3, ... b. Upon further reflection and examination of the data, you determine that a small number of parts contribute a very large percentage of the annual part usage costs (the Pareto Principle at work). Thus, you develop the table below where you group items as follows: Group A{1, 2, 3) Group B {4, 5, 6, 7, 8, 9, 10, 11) Group C (12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24). Based on this grouping your inventory policy is to Group A items: order based on each item's respective optimal Q* Group B items: order each item in this group two times a year Group C items: order each item in this group once a year Determine the total yearly inventory cost associated with the above policy. Notes: You must use Excel to respond to both Part a and Part b. Your Excel printouts must: Clearly indicate your answer to the question posed Be professionally formatted Item Item Cost Cumulative Proportion Demand Cost/year Cost/year of Costs 1 $200.00 85 $17,000 $17,000 0.37 2 $3,500.00 4 $14,000 $31.000 0.67 3 $10.00 1000 $10,000 $41,000 0.89 4 $0.50 2200 $1,100 $42,100 0.91 5 $1.00 820 $820 $42,920 0.93 6 20.50 1620 $810 $43,730 0.95 7 $1.50 500 $750 $44,480 0.96 8 $0.60 1000 2600 $45,080 0.97 ON 20.15 2000 $300 $45,380 0.98 10 $0.90 200 $180 $45,560 0.98 11 20.20 740 2148 $45,708 0.99 12 $3.00 30 $90 $45,798 0.99 13 $0.02 4500 $90 $45,888 0.99 14 $0.25 320 $80 $45,968 0.99 15 $14.00 5 $70 $46,038 0.99 16 $0.01 6000 $60 $46,098 0.99 17 $0.10 500 $50 846,148 1.00 18. $0.05 1000 $50 $46,198 1.00 19 $2.00 20 940 $46,238 1,000 20 $5.00 8. $40 $46,278 1.00 21 $0.15 200 $30 A$46,308 1.00 22 91.00 20) $20 $46,328 1.00 23 $0.01 1000 $10 $46,338 1.00 24 $0.01 1000 $10 $46,348 1.00