See Attachment For Scenario Step 1 Conduct An ABC Analysis

See Attachment For Scenariostep 1 Conduct An Abc Analysisuse Excel

Step 1: Conduct an ABC Analysis using Excel OM, and the chart to categorize parts into A, B, or C categories. Provide analysis and interpretation of the results. Adjust the default percentage for A items from 75% to 80% as requested by your supervisor.

Step 2: Calculate EOQ and Reorder Point for part 1359 using Excel OM. The lead time is 30 days, setup cost is $1,000, fixed monthly holding cost is $125, and the factory operates 365 days annually. Maintain a safety stock of 50 units. Interpret these calculations.

Step 3: Recalculate the safety stock for part 1359 based on demand data. Given an average daily demand of 30 units with a standard deviation of 2, and a desired stockout probability of 5%, determine the appropriate safety stock using Excel OM. Compare this to the safety stock in step 2 and analyze the difference.

Step 4: Compile your work into a single Excel spreadsheet with each activity on a separate tab. Include detailed analysis on each tab. Save the file using your name and activity description, avoiding special characters or punctuation, and submit as specified.

Paper For Above instruction

The tasks outlined involve comprehensive inventory and supply chain analysis utilizing Excel's Operations Management (OM) software. These steps encompass ABC classification, EOQ and reorder point calculations, and safety stock determination, all critical in effective inventory management. This analysis not only ensures operational efficiency but also minimizes costs associated with overstocking or stockouts, aligning with best practices in supply chain management.

ABC Analysis

ABC analysis is a vital inventory categorization technique used to prioritize management focus based on the significance of items. It classifies inventory into three categories:

  • Category A: Items that contribute approximately 80% of the total inventory value, typically representing a small percentage of total items (around 10-20%).
  • Category B: Items that account for the next 15%, representing a moderate portion.
  • Category C: Items that make up the remaining 5%, yet comprise the majority of the number of items.

In this analysis, the traditional cutoff of 75% for A items has been adjusted to 80%, which influences how strictly items are categorized. Using Excel OM's chart, data is analyzed to classify parts accordingly, allowing for focused resource allocation on high-value items. Precise categorization assists inventory managers in prioritizing control efforts and optimizing inventory levels for each class.

Interpreting the results involves examining the cumulative contribution of the parts and verifying the categories against the set thresholds. This strategic focus reduces holding costs on less critical items while ensuring sufficient control over high-value A items.

EOQ and Reorder Point Calculation

The Economic Order Quantity (EOQ) model is fundamental for minimizing the total inventory costs, which include order costs and holding costs. The EOQ formula accounts for demand rate, setup costs, and holding costs:

EOQ = √(2  D  S / H)

Where:

  • D = Annual demand
  • S = Setup or ordering cost per order
  • H = Holding cost per unit annually

Given data: Daily demand is 30 units, so annual demand D = 30 365 = 10,950 units. The setup cost S is $1,000, and the holding cost is $125 monthly ($125 12 = $1,500 annually). Plugging in these values yields:

EOQ = √(2  10950  1000 / 1500) ≈ √(14,600,000 / 1500) ≈ √9746.67 ≈ 98.7 units

The Reorder Point (ROP) incorporates lead time and safety stock:

ROP = Demand during lead time + Safety Stock

Demand during 30 days:

30 units/day * 30 days = 900 units

Adding safety stock yields the total reorder point, which ensures inventory availability during lead time, considering variability in demand or supply delays.

Interpretation of these calculations helps determine optimal order quantities to minimize total costs and prevent stockouts, contributing to efficient inventory management.

Safety Stock Calculation and Comparison

For a more precise safety stock calculation, demand variability and desired service levels are considered. Using the given data: average demand of 30 units/day and standard deviation of 2 units/day, with a 95% service level (5% stockout risk).

The safety stock (SS) is computed via:

SS = Z  σd  √lead time

Where Z is the Z-score associated with the service level (for 95%, Z ≈ 1.645). σd is the standard deviation of daily demand (2 units). Lead time is 30 days. Therefore:

SS = 1.645  2  √30 ≈ 1.645  2  5.477 ≈ 1.645 * 10.954 ≈ 18 units

This safety stock of approximately 18 units is significantly lower than the previous safety stock of 50 units, indicating that a safety stock of 50 units provides an additional buffer margin, potentially reducing stockout risks further. This comparison emphasizes how demand variability and desired service levels influence inventory safety buffers.

Implementing this statistical approach results in more balanced inventory management, ensuring cost-efficiency while maintaining high service levels.

In conclusion, applying these methods systematically allows organizations to fine-tune their inventory policies, aligning stock levels with actual demand variability and service objectives. The strategic adjustment of safety stocks, careful ABC classification, and EOQ optimization collectively contribute to sustainable and cost-effective operations.

References

  • Chopra, S., & Meindl, P. (2016). Supply Chain Management: Strategy, Planning, and Operation. Pearson.
  • Heizer, J., Render, B., & Munson, C. (2017). Operations Management. Pearson.
  • Silver, E. A., Pyke, D. F., & Peterson, R. (2016). Inventory Management and Production Planning and Scheduling. Wiley.
  • Ballou, R. H. (2004). Business Logistics/Supply Chain Management. Pearson Education.
  • Slack, N., Brandon-Jones, A., & Burgess, N. (2019). Operations Management. Pearson.
  • Nahmias, S., & Olsen, T. (2015). Production and Operations Analysis. Waveland Press.
  • Mentzer, J. T. (2004). Supply Chain Management. Sage Publications.
  • Simchi-Levi, D., Kaminsky, P., & Simchi-Levi, E. (2008). Managing the Supply Chain: The Definitive Guide for the Business Profession. McGraw-Hill.
  • Golicic, S. L., & Smith, C. D. (2013). A Review of Warehousing Research: Future Opportunities. International Journal of Physical Distribution & Logistics Management.
  • Rajagopalan, S. (2014). Inventory Management in Manufacturing. Springer.

Related Assignments