machinery manufacturer

Company XYZ is a machinery manufacturer, with a catalogue of 1000 items that it makes and sells.  The company has seen some changes its product mix and market demand and wants to manage its production scheduling and inventory management more efficiently going forward.

On the attached spreadsheet, titled “ABC Inventory Analysis.xlsx”, you will find the company’s product listing, along with key information for each of its products:

Base unit of measure

Weight per unit

Average sales price for the last 12 months

Gross margin % for the last 12 months

Expected gross margin % for the next 12 months

Quantity sold for the last 12 months

Forecast for the next 12 months

Monthly Mean Absolute Percentage Error (MAPE) of the forecast

The company has heard about ABC inventory analysis and would like to investigate if this might be advantageous.  Historically, all products have been produced on average once per month, such that the planned production should result in opening the next month with enough supply to satisfy that month’s demand, plus half of the next month’s demand.  In other words, the plan is to open each month with 1.5 months of supply.  For instance, if the company’s forecast for a given product is 100 units per month, then it should open each month with 150 units of supply.

Your task is to develop an ABC analysis for the company that would prove beneficial.


All products have a lot-for-lot production lot sizing strategy.  That is, whatever quantity is required to be produced is the quantity that is produced.

This is in contrast to a fixed lot size, whereby production must be in multiples of the base lot size.  For instance, if product requirements are 175 units, in a lot-for-lot approach, the company would produce 175 units.  In a fixed lot size approach however, say 50 units, then the company could only produce quantities in multiples of 50 units, and therefore if 175 units are required, then 200 would be produced.

The standard costs for the next 12 months will be the same as for the last 12 months

It is estimated that the holding cost for finished goods inventory is 2% per month of the average inventory value.

It is estimated that the average setup cost for a production run of any given product is about $300

The company needs to create safety stock targets for each product.  Safety stock is to be based on aiming for 99% service levels for A-items, 98% service level for B-items, and 95% service level for C-items.

Assume that the average month has 21.67 days

Assume that the company operates 5 days per week, 52 weeks per year.

Your task:

You have been asked to develop a report to deliver to the senior management team.  This report is to contain the following:

An ABC analysis for the company’s products, based on expected gross margin value (i.e., total dollars of gross margin) for the next 12 months.  It is up to you to determine where the break points are between the item categories of A, B, and C.  Justify why you chose those particular break points.  Your ABC analysis should also include a summary table and appropriate charts for the executives.  You may find that the best approach for this begins with a pivot table.

Different strategies for how frequently to produce A, B, and C-items.  Develop three scenarios for overall approaches on how to manage the planning of A, B, and C items.

For each scenario, develop planning approaches for A, B, and C items from among the following:







For each scenario, calculate for each item

Safety stock requirement to achieve the indicated service level for that item’s ABC category

Average expected inventory

Number of production runs expected for the 12 month period.

The total average inventory level for A, B, and C categories, and overall for the company

The total number of production runs for A, B, and C categories, and overall for the company

For each scenario, develop appropriate executive-level description/commentary and charts/graphs to explain the scenario, its advantages, and its disadvantages.

An executive level summary of your overall analysis, with recommendations.  Make sure your summary has appropriate tables, charts, and discussion.  Remember, you are preparing this report for executives.