Sp Opm 352 HW 7 30 Pt Submit As A Single Excel Spreadsheet

20sp Opm 352 Hw 7 30 Ptsubmit As A Single Excel Spreadsheet All C

Analyze multiple inventory management and supply chain problems involving economic order quantity, safety stock calculation, reorder points, service levels, and cost analysis for different scenarios, including regional warehouses and centralized inventory systems. The tasks include calculations based on demand, variability, lead times, and costs, with all work to be demonstrated clearly using Excel spreadsheets.

Paper For Above instruction

This paper addresses various scenario-based questions related to inventory management and supply chain operations, emphasizing the use of Excel for complex calculations and decision-making processes. The focus lies on determining optimal safety stock levels, reorder points, economic order quantities, and evaluating cost implications between decentralized and centralized warehousing structures.

Problem 1: Stockout Risk and Safety Stock Calculation for a Souvenir Shop

The first scenario involves a downtown San Francisco souvenir shop that operates 300 days annually, selling on average 37 Golden Gate Bridge T-shirts per day. The shop estimates their ordering cost at $15 per order and holds an annual inventory holding cost of $0.65 per shirt. The lead time demand (LTD) is normally distributed with a mean of 370 shirts and a standard deviation of 23 shirts.

Part (a): The management desires a maximum of 1% stockout risk. To determine the safety stock, the z-score corresponding to a 1% stockout probability is approximately 2.33. The safety stock (SS) is then calculated as:

SS = z  standard deviation of LTD = 2.33  23 ≈ 53.59 shirts

Part (b): The Reorder Point (ROP) is the sum of the expected demand during lead time and safety stock:

ROP = LTD mean + Safety Stock = 370 + 54 ≈ 424 shirts

Part (c): The annual holding cost of safety stock is calculated as:

Annual Safety Stock Cost = Safety Stock  holding cost per unit = 54  0.65 ≈ $35.10

Part (d): If the stockout risk is increased to 2%, the corresponding z-score decreases to about 2.05, reducing safety stock and thereby decreasing holding costs. The safety stock with 2% risk:

SS = 2.05 * 23 ≈ 47.15 shirts

This demonstrates how increasing acceptable stockout risk reduces safety stock and holding costs.

Problem 2: Towel Inventory Management at the Hard Rock Hotel

The hotel anticipates a mean daily usage of 3,250 towels with a standard deviation of 260. The lead time for replenishment is 4 days with an uncertainty of 2 days. The combined variability in demand over the entire lead time can be calculated by:

LTD = mean daily demand  lead time = 3,250  4 = 13,000 towels

The standard deviation of the Lead Time Demand (σLTD) accounts for variability in both demand and lead time:

σLTD = √[(lead time  demand std dev)^2 + (mean demand  lead time std dev)^2]

= √[(4  260)^2 + (3,250  2)^2]


= √[(1,040)^2 + (6,500)^2]


= √[1,081,600 + 42,250,000]


≈ √[43,331,600] ≈ 6,590 towels

For a 98% service level, the z-score is approximately 2.05. The safety stock is:

Safety Stock = z  σLTD = 2.05  6,590 ≈ 13,514 towels

The Reorder Point (ROP) is thus:

ROP = LTD + Safety Stock = 13,000 + 13,514 ≈ 26,514 towels

Problem 3: Unsold Valentine Hearts and Service Level

The gift shop's Valentine Hearts are purchased at $3.50 and sold at $15 before Valentine's Day. They are discounted to $1.50 afterwards. Given the asymmetrical risk and costs, the optimal service level can be derived by considering the critical ratio:

Customer's willingness to pay during peak = $15

Cost of unsold inventory (after Valentine’s) = $1.50


Purchase cost = $3.50

Optimal service level corresponds to the critical ratio:

CR = (selling price - cost) / (selling price + salvage value - purchase cost) ≈ ($15 - $3.50) / ($15 - $3.50 + $1.50) ≈ 11.50 / 13 ≈ 0.88 or 88%

Therefore, an 88% service level optimizes balancing risk of stockouts against overstocking.

Problem 4: Regional Warehouses vs. Centralized Warehouse Cost Analysis

Demand at each of four warehouses is normally distributed with a mean of 1,800 units/month and a std dev of 250 units. The annual holding cost rate is 35%, and each unit costs $20. The annual demand per warehouse is 21,600 units (1,800 * 12), and the variability considers the standard deviation scaled to annual terms.

Part (a): Independent Warehouse Policy

The EOQ model suggests order quantity (Q), reordering point (ROP), safety stock, and associated costs. The EOQ is calculated as:

EOQ = √(2  demand  ordering cost / holding cost rate) = √(2  21,600  $1,450 / (0.35 * $20))

= √(2  21,600  1,450 / 7)


≈ √(2  21,600  207.14)


≈ √(8,939,904)


≈ 2,989 units

The ROP accounts for demand during lead time plus safety stock, based on the z-score for the desired service level (e.g., 95% corresponding to z=1.645):

ROP = demand during lead time + safety stock

= (monthly demand  lead time in months) + z  demand std dev during lead time


= 1,800  2 + 1.645  2 * 250 ≈ 3,600 + 825 = 4,425 units

The safety stock safeguards against variability, and costs for ordering and holding are derived accordingly.

Part (b): Centralized Warehouse Policy

Centralization reduces variability through pooling, leading to different demand and variability calculations. The combined demand over four warehouses (assuming independence) is 72,000 units annually, with a reduced variability due to aggregation:

Std dev_total = √(4)  250  12 ≈ 2  250  12 = 6,000 units

The EOQ and ROP are recalculated for centralized demand, typically resulting in larger order quantities and potentially lower safety stock per unit due to reduced variability.

Cost implications and recommendations

The comparison reveals that consolidation can decrease total safety stock holdings, reducing inventory costs. However, it may introduce risks like increased transportation costs and reduced responsiveness. The decision depends on the balance of these factors.

Conclusion

Optimizing inventory policies requires a detailed understanding of demand variability, lead times, costs, and service levels. Excel-based calculations support strategic decisions for effective supply chain management. For multiple warehouses, decentralization allows flexibility but increases safety stock and costs; centralization offers economies of scale but may affect service responsiveness.

References

  • Chopra, S., & Meindl, P. (2018). Supply Chain Management: Strategy, Planning, and Operation. Pearson.
  • Heizer, J., Render, B., & Munson, C. (2017). Principles of Operations Management. Pearson.
  • Silver, E.A., Pyke, D.F., & Peterson, R. (1998). Inventory Management and Production Planning and Scheduling. Wiley.
  • Bijvank, M., et al. (2019). Inventory Control with Safety Stock: Analytical Solutions and Practical Applications. European Journal of Operational Research.
  • Nahmias, S. (2013). Production and Operations Analysis. Waveland Press.
  • Gupta, S. M., & Eilon, S. (1984). Inventory Control. Academic Press.
  • Axsäter, S. (2007). Inventory Control. Springer.
  • Zipkin, P. (2000). Foundations of Inventory Management. McGraw-Hill.
  • Simchi-Levi, D., Kaminsky, P., & Simchi-Levi, E. (2003). Designing and Managing the Supply Chain. McGraw-Hill.
  • Patel, S. (2020). Warehouse and Inventory Management Strategies. Logistics Management Journal.

Related Assignments