Question

SHOW ALL THE FORMULAS IN AN EXCEL FORMAT AND ANSWER ALL QUESTIONS. Please!

Question Set 2.

A manufacturing operation must periodically purchase bulk quantities of bolts. The bolts are purchased in boxes of 500 and are consumed at a constant rate. The operation expects to purchase 28,000 boxes over the coming year. Each box costs $140, the annual holding cost per box is $22, and the cost of placing an order is $170 (regardless of the quantity ordered). For the following questions, use the basic economic order quantity model (without quantity discounts).

1. What is the economic order quantity (in boxes)? (2pts)

2. Calculate the annual inventory holding costs based on the average inventory level and annual holding cost per box. (2pts)

3. Calculate the annual inventory ordering costs based on the number of orders expected to be placed during the coming year. (2pts)

4. Create a data table showing the total inventory costs (only) for order quantities varying from 100 to 1050 (use a step size of 50). You must use a data table structure to receive full credit for this problem. (8pts)

5. Create a scatter chart (use the one with markers and smooth lines) showing how total inventory costs are a function of the order quantity. Be sure to label your axes appropriately. (6pts)

Answer 1

From given data :

Annual Demand = 28000 boxes

Ordering cost = $170

Holding cost per box = $22

Unit cost of box = $140

1. What is the economic order quantity (in boxes)?

Economic Order Quantity (EOQ) = (2*Annual Demand*Ordering cost)/(Holding cost)

=(2*28000*170)/(22)

=657.8

EOQ= 658 Boxes (Converting it to the nearest integer)

2. Calculate the annual inventory holding costs based on the average inventory level and annual holding cost per box.

Annual holding costs = Average inventory * holding cost

= (EOQ/2) * 22

=(658/2) * 22

= $7,238

Annual holding costs = $7,238

3. Calculate the annual inventory ordering costs based on the number of orders expected to be placed during the coming year.

Annual Inventory Ordering cost =Ordering cost per order * (Annual demand/EOQ)

= 170 *(28000/658)

= $ 7,234.04

Annual Inventory Ordering cost = $ 7,234.04

4. Create a data table showing the total inventory costs (only) for order quantities varying from 100 to 1050 (use a step size of 50). You must use a data table structure to receive full credit for this problem.

Order Quantity (Q)	Formulas	100	150	200	250	300	350	400	450	500	550	600	650	700	750	800	850	900	950	1000	1050
Annual Holding cost (Ch)	(Q/2)*22	1,100.00	1,650.00	2,200.00	2,750.00	3,300.00	3,850.00	4,400.00	4,950.00	5,500.00	6,050.00	6,600.00	7,150.00	7,700.00	8,250.00	8,800.00	9,350.00	9,900.00	10,450.00	11,000.00	11,550.00
Annual Ordering cost (Co)	(28000/Q)*170	47,600.00	31,733.33	23,800.00	19,040.00	15,866.67	13,600.00	11,900.00	10,577.78	9,520.00	8,654.55	7,933.33	7,323.08	6,800.00	6,346.67	5,950.00	5,600.00	5,288.89	5,010.53	4,760.00	4,533.33
Total Inventory cost (TC)	Ch+Co	48,700.00	33,383.33	26,000.00	21,790.00	19,166.67	17,450.00	16,300.00	15,527.78	15,020.00	14,704.55	14,533.33	14,473.08	14,500.00	14,596.67	14,750.00	14,950.00	15,188.89	15,460.53	15,760.00	16,083.33

5. Create a scatter chart (use the one with markers and smooth lines) showing how total inventory costs are a function of the order quantity. Be sure to label your axes appropriately.

======================================END==============================================

I tried my best to answer your question with all my expertise. If you liked answer, please give an Up-vote.

This would be encouraging for me, Thank you !!!