Problems: ABC Analysis, EOQ, Reorder Point, & Safety Stock

Sample Solution

Unfortunately, I cannot directly access or process data attached to prompts. However, I can guide you through the steps involved in completing the assignment and provide insights for analyzing the results.

Step 1: ABC Analysis:

1. Data Preparation: Enter your data into an Excel spreadsheet, including part number, annual demand, and unit cost. Ensure all values are entered correctly.
2. Calculate Total Annual Dollar Value: Multiply annual demand by unit cost for each part to obtain the total annual dollar value.
3. Sort Data: Sort the data in descending order of total annual dollar value.
4. Calculate Cumulative Dollar Value and Percentage: Use Excel formulas to calculate the cumulative dollar value and percentage for each part.
5. Set A-Item Percentage: Apply the desired percentage for A-items (80% in this case) and adjust the following percentages accordingly.
6. Classify Parts: Based on the calculated cumulative percentages, assign each part to an A, B, or C category.

Full Answer Section

Analysis and Interpretation:

• Identify the number of parts in each category and their corresponding percentage contribution to the total annual dollar value.
• Discuss the implications of the categorization for inventory management strategies.
• For A-items, justify their high contribution despite potentially being fewer in number.
• For B and C-items, suggest appropriate inventory control methods.

Step 2: EOQ and Reorder Point:

1. Enter Data: Input the following data into the EOQ model in Excel OM:
   • Annual demand for part 1359
   • Unit cost
   • Lead time
   • Setup/ordering cost
   • Holding cost (ensure it's per unit per year, not per month)
   • Safety stock (50 for now)
2. Calculate EOQ: Run the model to obtain the Economic Order Quantity.
3. Calculate Reorder Point: Use the lead time and average daily demand (calculated from annual demand and working days) to determine the reorder point.

Analysis and Interpretation:

• Explain the meaning of EOQ and its significance in inventory management.
• Discuss the impact of lead time and safety stock on the reorder point.
• Analyze the trade-off between ordering costs and holding costs reflected in the EOQ calculation.

Step 3: Safety Stock Calculation:

1. Data Input: Enter the average daily demand and standard deviation into the Safety Stock model in Excel OM.
2. Set Service Level: Specify the desired service level (95% in this case, corresponding to 5% stockout risk).
3. Calculate Safety Stock: Run the model to obtain the recommended safety stock level.

Analysis and Comparison:

• Compare the calculated safety stock with the initially used level of 50.
• Explain the factors influencing the difference (e.g., demand variability, service level).
• Discuss the implications of using a more accurate safety stock calculation for inventory management.

Remember:

• Replace placeholders with your actual data and adjust formulas as needed.
• Include clear and concise analysis sections for each step, explaining your calculations and interpreting the results.
• Save your Excel file with the specified naming convention.

By following these steps and incorporating your specific data, you can complete the assignment effectively and demonstrate your understanding of inventory management concepts.