Question

Could you help me solve this in excel please. Showing the formulas in the cells as well as the answers. Thanks so much. Will leave a like!!

D21 ху fх A B с D E F G H J K L M N. O 10.8. The local fire department uses 10,000 alkaline flashlight batteries per year, which cost $4 each. The cost of ordering batteries is estimated to be $50. The current interest rate suggested by the city council is 25%. The sales rep has recently suggested that you could get a discount of 2% for orders of 2000 batteries at a time. Should you take 10 advantage of this special offer? 11 Annual demand (A) 10,000 12 Cost per unit (c) $4.00 13 Ordering cost (S) $50.00 14 Annual inventory carrying cost rate (i) 25% 15 Volumn discount 2% 16 Discount threshold 2,000 17 Annual Demand Dollars $40,000.00 18 EOQ 19 Order by EOQ (no discount) Order by discount threshold 20 Unit purchasing price (c) 21 Order quantity (Q) 22 Average inventory (Q/2) 23 # of orders per year 24 Purchase cost (A*c) 25 Inventory carrying cost 26 Ordering cost 27 Total cost 29

Answer 1

As per your request, I have given detailed explanation. Answer is attached below.

Please rate my answer

Answer Unit Purchase Price (c) Order Quantity (2) Average Inventory (0/2) # of orders per year Purchase Cost (Ac) Inventory Carrying Cost Ordering Cost Total Cost Order by EOQ Order by (no Discount) Dscount Threshold 4 4 1,000 2,000 500 1,000 10 5 40,000 39,200 500 1,000 500 250 41,000 40,450 EOQ = V(2*A*S)/(i*c) V(2*10,000*50)/(25%*4) 1,000 Batteries As, Total Cost in Order by Discount Threshold reduces by $550 Advantage from special offer should be accepted

A B с D E F G 1 2 Answer 3 4 Order by EOQ (no Discount) 5 6 7 Unit Purchase Price (c) Order Quantity (0) Average Inventory (0/2) # of orders per year Purchase Cost (A*C) Inventory Carrying Cost Ordering Cost Total Cost 8 Order by Dscount Threshold -4*0.98 2000 =G6/2 5 =10000*65 =G7*25%*4 =50*68 =SUM(G9:611) 4 1000 =F6/2 10 =10000*F5 =F7*25%*4 =50*F8 =SUM(F9:F11) 9 10 11 12 13