EOQ for manufacturer. Beaumont Corporation makes air conditioners. It purchases 14,400 units of a particular type of compressor part, CU29, each year at a cost of $60 per unit.

Beaumont requires a 12% annual return on investment. In addition, relevant carrying costs

(for insurance, materials-handling, breakage, and so on) are $2.40 per unit per year. Relevant costs per purchase order are $144.

Required 1. Calculate Beaumont’s EOQ for CU29.

2. Calculate Beaumont’s total ordering and carrying costs using EOQ.

3. Assume that demand is uniform throughout the year and is known with certainty. The purchasing lead time is half a month. Calculate Beaumont’s reorder point for CU29.

Excel Application For students who wish to practise their spreadsheet skills, the following is a step-by-step approach to creating an Excel spreadsheet to work this problem.

Step-by-Step 1.Open a new spreadsheet. At the top, create an “Original Data” section for the data pro¬

vided by Beaumont Corporation, with rows for “Annual Demand, Purchase Cost per Unit, Required Return on Investment (%), and Relevant Ordering Costs per Purchase Order.”

(Program your spreadsheet to perform all necessary calculations. Do not “hard-code” any amounts, such as economic order quantity, requiring addition, subtraction, multiplication, or division operations.)

2. $kip two rows, create a “Relevant Carrying Cost per Unit per Year” section in a format similar to the top of page 785. Create rows and enter calculations for “Required Annual Return on Investment,” “Relevant Insurance, Materials Handling, and Breakage Costs per Year,” and “Total Carrying Costs per Unit per Year.”

3. $kip two rows, create a “Problem 1” section, with rows for each of the inputs into the EOQ formula: D, P, and C. Include a calculation for the “Economic Order Quantity” on a separate row.

4. $kip two rows, create a “Problem 2” section. Create rows and enter calculations for

“Annual Relevant Ordering Costs,” “Annual Relevant Carrying Costs,” and “Relevant Total Costs.”

5. $kip two rows, create a “Problem 3” section. Create rows and enter calculations for

“Monthly Demand,” “Purchasing Lead Time (in months),” and the “Reorder Point.”

6. Check the accuracy ofyour spreadsheet: Go to your Original Data section and change the required return on investment from 12% to 15%. If you programmed your spreadsheet correctly, total carrying cost per unit per year should increase to $11.40, and your economic order quantity should decrease to 603 units.