MINI CASE: Musical Chairs at Home Depot Just about everyone knows that the Home Depot has stores almost everywhere and sells a wide array of things. In fact, the firm operates more than 2,050 full-service stores in the United States, Canada, Puerto Rico, and Mexico. Each store stocks approximately 40,000 different kinds of building materials, home improvement supplies, and lawn and garden products. Note long ago, Home Depot was considering a new deluxe toilet unit in its bathroom fixtures department. It was trying to broaden its product line to appeal to home-improvement contractors, and this item has recently been featured in Architectural Digest. The toilet seat contains a special feature. Sitting on the seat activates a system that sprays up a fine mist of air freshener and plays music. You can choose the scent and whatever musical selection moves you (We are not making this up!) The new toilet has been test-marketed successfully and Home Depot estimated that sales would average 1,000 units per year. It would cost $100 to keep a unit in inventory for a year, and the fixed reordering cost would be $80. Home Depot's cost would be $500 per unit. The following Table 1 shows the expected stockoul costs: TABLE 1. SAFETY STOCKS AND EXPECTED STOCKOUT COSTS Directions: Answer the following case questions using Excel. Please number your responses and order your answers so that they can be easily tracked and graded. Also, be sure to label your answers. You must show all your mathematical calculations as an algebraic or Excel formula function to receive credit. Currency amounts can be rounded to the nearest dollar. Upload your Excel file in Canvas; please do not upload more than one file. University cheating and plagiarism regulations apply to this assignment. Questions: Upload your Excel file in Canvas; please do not upload more than one file. University cheating and plagiarism regulations apply to this assignment. Questions: 1. What is Home Depot's EOQ for the deluxe toilet units? Using the EOQ, what is the average inventory, number of orders per year, and the total annual cost of maintaining the inventory of deluxe toilet units? 2. The average lead-time demand is 10 units. The exhibit above shows how the expected annual stockout cost depends on the amount of safety stock. What is the optimal safety stock and reorder point? (Hint: Calculate the remaining three columns in TABLE 1 above to determine the least expensive alternative (the minimum total cost).) 3. The supplier of the deluxe toilet units has offered Home Depot a 0.5% (half a percent) discount if it orders 50 or more units each time. Should Home Depot order 50 units each time it buys? (Hint: Calculate the total cost with the discount with an order quantity of 50 units and compare your answer to #1. 4. Suppose Home Depot can sell 1,000 units per year at a price of $750 per unit. But it estimates that it could sell 1,100 units per year if it dropped the price to $700, and that it would sell only 900 units if it raised the price to $800 per unit. Which price, $700,$750, or $800 per unit, maximizes the annual profit Home Depot can expect to earn from selling the deluxe toilet units? You should already have the EOQ at 1,000 units from your answer to \#1. (Hint: First, calculate the optimal EOQ at 900,1000 , and 1100 units, rounding to the nearest whole number of unit). Then calculate total costs as the sum of cost of the goods (using the purchase price in the problem) and inventory cost (including ordering costs, carrying costs, and stockout costs). For stockout costs, assume a zero safety stock, and the accompanying stockout costs, as shown in the above table. Then Profit = Revenue - Cost of Goods Sold - Total Inventory Costs. Finally, complete the template below with the data you calculate
