C11IP: Demand and Inventory Planning

The Course Assignment
On graduation from Heriot-Watt you take up a new role as the Inventory Manager for petEssentials Ltd., a pet product wholesaler selling to retailers throughout Scotland.
With one warehouse based in Livingston your job is to devise an inventory management and control policy for the domestic product range. In total there are 234 products (mostly for cats and dogs) sourced from 13
different vendors.
The assignment is divided into 3 parts as follows:
Part A: Inventory analysis and segmentation
Part B: Sales forecasting
Part C: Inventory strategy and control policy

Each Part will be introduced during the relevant lectures:
Part A: lecture 2 - inventory segmentation
Part B: lecture 3 - demand planning & forecasting
Part C: lecture 5 - inventory control

You will submit a final individual report in week 10 (Tuesday 4pm) of no more than 1500 words. Your report will make good use of graphs and tables to present your results. All algorithms used will be presented clearly
(using Equation Editor) with supporting references in Appendices (not included in the word count).

Your policy choices must be justified using the relevant academic literature. Please use the Harvard referencing method throughout.

The Course Assignment: PART A
Using the data provided on VISION: petEssentials data.xls.
Warehouse shipments (in units per month) for 234 pet products and other useful data.

  1. Carry out ABC Analysis by ‘dollar-usage’ (based on unit cost value)
  2. Carry out multi-criteria ABC analysis by ‘product profit contribution’ and ‘vendor (supplier) profit contribution’.
  3. Make sure you adhere as close as possible to the 20:30:50 split in % of products and % of Vendors per segment.
  4. Plot the Pareto curves and present the segmentation results in tables, e.g. for your multicriteria results the table should be structured something like this:

The Course Assignment: PART B
Using appropriate statistical (projective) forecasting method/s and the data provided in petEssentials data.xls., produce a forecast of sales for the next 12 months.
You can use the Forecasting spreadsheet model available on VISION if you wish.
The forecast of sales you produce for each individual product will be essential to complete Part C of this assignment.

The Course Assignment: PART C
Design a segmented inventory management strategy for petEssentials.

  1. Using appropriate inventory control models and parameters and using SKU level forecasts (produced in Part B) evaluate the impact of different inventory policies on key inventory performance metrics. A comparative analysis would be useful.
  2. Based on the your findings, and clearly presenting any expected savings, make final recommendations to the petEssentials Board.
    Compile your results from Parts A, B and C into a coherent Final Report.