PLEASE SHOW ALL THE FORMULAS IN AN EXCEL FORMAT AND ANSWER ALL QUESTIONS All pro

Question

PLEASE SHOW ALL THE FORMULAS IN AN EXCEL FORMAT AND ANSWER ALL QUESTIONS

All problems must be solved using Excel formulas to receive credit.

Question Set 1

A manufacturing operation must periodically purchase bulk quantities of washers. The washers are purchased in boxes of 1000 and are consumed at a constant rate. The operation expects to purchase 20,000 boxes over the coming year. Each box costs $120, the annual holding cost per box is $15, and the cost of placing an order is $120 (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 1200 (use a step size of 50). You must use a data table structure to receive full credit for this problem. If you have any doubt as to what a data table is, please ask your lab TA. (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)

Explanation / Answer

The EOQ can be computed as-

Q*=sqrt(2DS/H)

D is the Annual requirement - 20000

S is the cost incurred to place a single order - $120

H is the carrying cost per unit - $15

By applying the following function-

=SQRT(2*20000*15/120)

70.71 orders

B.

Annual Inventory holding costs = Annual Inventory*Holding Costs

Annual Inventory holding costs =20000*$15

Annual Inventory holding costs =$300000

C.

The Annual Inventory Ordering Costs is $8485.28.

This is based on the no. of orders.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.