Reference no: EM133266362

Please complete the case study using Excel Solver setup created in order to solve for the economic lot size to minimize ordering costs, inventory carrying costs, and transportation costs In the case study below:

Looking at these statistics, develop plots to show how optimal order quantities, inventory turnover ratio, average inventory cost per unit, and average transportation cost per unit change across the different values of annual demand. What relationships do you observe (be specific in these answers)?

Joanne Smith had just started her position in the logistics planning group at Retail Co. after graduating with her degree in Supply Chain Management. Retail Co. was a large retailer specializing in the distribution of hardware goods (e.g., lumber, lawnmowers, hand tools, etc.). One of their biggest challenges Retail Co. faces is determining the economic lot size for products for which demand is relatively low. Joanne had been told that there would be a project coming up that would require the examination of this topic, and she was excited to have the opportunity to work on such a project. The morning of her third week on the job she got to work and opened her email and noticed a message from Gary Wilcox (her boss) that asked for her to meet with him in her office at 9:30. When the time came, Gary swung by her office. 

"Joanne," began Gary as she walked in the door. "I'm not sure how much they told you, but we have been having some issues with our sole supplier of specialty hardwood flooring. The supplier, which is domestic, has been sending defective flooring. We've tried to work with them, but the quality problems have persisted. As a result, we will be replacing them with a new supplier. As part of this transition, we need to conduct an economic lot sizing analysis to determine the best order quantity that will minimize the total transportation, inventory holding, and ordering costs for our orders that we will be placing for specialty hardwood flooring."

"Quick question," asked Joanne, "will the new specialty hardwood flooring supplier give us quantity discount if we purchase above a minimum threshold."

"Not as far as I'm aware," replied Gary. "I believe corporate has struck a contract price that is fixed based on order quantity." 

"Another question then, as this will have a major impact on how we think of ordering this item: are we ordering a variety of other products from this supplier?"

"Since it is a new supplier, I don't believe so," replied Gary. "I'm curious why you ask."

"Because if we were ordering multiple products, any type of order quantity would need to be considered in conjunction with the optimal quantities for the other products. For example, say that we find the optimal quantity would result in 12 orders a year (so once a month), but for other items the optimal quantity results in more orders a year. We would then want to consider potential benefits for ordering more frequently, especially when order quantities are less-thank-truckload (LTL)." Joanne paused and waited for Gary to respond.

"That shouldn't be necessary in this case. While we will obviously be ordering some mix of hardwood flooring from the supplier, the broader goal is to get a ballpark idea of how frequently we should be checking the inventory for these products so we aren't ordering too much (and paying excess for holding inventory) versus too little (and paying excess for transportation," replied Gary.

"Ah, gotcha," said Joanne. "To me, it seems like I would summarize this exercise as follows: we are going to be ordering a variety of hardwood flooring products from the supplier, and because of this, we want to use a periodic inventory management system with a review interval. However, we don't know what the optimal review interval is. In order to get a sense of this, what we can do is using an estimate of the aggregate price of a unit of hardwood flooring, its weight, a rough estimate of annual demand, an estimate of our ordering cost, an estimate of our carrying cost, and less-than-truckload tariff table prices for this commodity category given the supplier's plant to solve an EOQ model that minimizes the sum total of ordering, holding, and transportation. We can use the days of supply from the EOQ to back out what the best review period is."

"Said much more eloquently than I could have tried," smiled Gary. "Let me send you the information you will need."

Gary returned to his desk and sent Joanne an email containing the required information that she would need to complete the analysis. First, she received the following table of freight rates[1]:

Freight rate table for a representative motor carrier. 

Additional information Gary gave her:

• The company's projection was that it would sell 4,000 units of specialty hardwood flooring a year. Each unit of flooring weighs 20 pounds.
• Each unit of specialty flooring costs Retail Co. $75 per unit. They sold the flooring at an average price of $100 per unit.
• The price to place an order is $25, excluding transportation cost.
• The company's annual inventory carrying cost for specialty hardwood flooring in stock is 25%.
• The upper limit on the size of any shipment is 40,000 pounds.
• The company currently receives a 50% discount on the transportation costs from the freight rate table.
• No safety stock will be carried.

Taking in all this information, Joanne realized that her next step would be to construct an Excel spreadsheet to find the optimal level of Q. Specifically, she would need to find a way to make sure that the order quantity determined the weight of the shipment. The weight of the shipment would, in turn, determine the freight rate for the shipment. She could then multiple the freight rate per hundredweight by the hundredweights of the shipment to obtain a transportation cost per shipment. She could then multiply the transportation cost per shipment by the number of shipments per year. Also, she would need to establish a constraint that the weight of the shipment could not exceed the 40,000 pound full truckload limit. With this in mind:

Question 1: Identify an algebraic function that can adequately approximate the freight rates for the flooring to serve as an input into the optimization program to determine order quantity and safety stock.

Question 2: Identify the optimal order quantity for the flooring. What are you (i) number of orders per year, (ii) annual inventory carrying costs, (iii) total annual transportation cost, and (iv) total annual logistics costs (i.e., annual administration, transportation, and inventory carrying cost summed together)? 

Question 3: Assuming there are 360 days in a year, calculate the days of supply [Q/daily demand] for the optimal lot size calculated in Question 2.

Question 4: Calculate the (i) inventory turnover ratio [annual demand/average inventory], (ii) average inventory cost per unit sold [(Ordering Cost + Holding Cost)/annual demand], and (iii) average transportation cost per unit sold [transportation cost/annual demand] for the optimal solution for flooring. Remember, you are only holding cycle stock, so average inventory is Q/2.

Question 5: Gary wants you to conduct a sensitivity analysis to how a change in annual demand for flooring will influence optimal order quantities, inventory turnover ratio, average inventory cost per unit, and average transportation cost per unit. To do this, vary the annual demand from 1,000 units to 20,000 in sensible increments (e.g., 1,000 units at a time) and resolve the optimization for different values of annual demand. For example, change the annual demand to 1,00 units, solve for the optimal order quantity, and report the statistics that Gary requested. Then change annual demand to 2,000 units and resolve, etc. 

[1] Data adopted from Thomas & Tyworth (2007).