Forecasting and inventory optimization model

Project Description

• Student Groups will be provided with raw data in Excel that identifies products, product families, warehouses, transportation costs, product level COGS and margin. Historical demand for each product will also be provided. Groups will be required to analyze the data and develop the following:
• Select a forecasting method and develop a sales forecast.
• Analyze the data and develop an ABC analysis. The analysis might best be performed in more than one way. Scenario 1 would involve optimization of Margin, while scenario 2 is to optimize revenue. An additional segmentation approach incorporating concepts of data fit to forecasting is added to create a multifaceted segmentation approach called ABC/XYZ.
• Develop safety stock strategies and calculate safety stocks, EOQ, and reorder points.
• Evaluate the effect of changes in lead times & cost based upon choice of transportation method (air and sea). Based upon this analysis, we determine the best shipping methods, at the item level to optimize the groups result in terms of effects on inventory, revenue, and margin optimization.
• Develop target inventory levels and assess how various levels of inventory and service levels impact margin and revenue optimization.Key Outputs and Assumptions (Example will be updated prior to Milestone 2)
  • Organizations Gross Margin Objective is 36% to 42%
  • Key statistics of this Base Case:

  This is the Base Case that students need to create and hit tolerances identified in the Rubric and instructions. Essentially, the Student Groups need to develop a base case that comes with +/- .5% of the forecast growth from 2023 to 2024 and develop a target inventory value with +/- 15% of Professor Browns developed target.

  1. The Base requires an increase of 9% in the forecast value of total order opportunity.
  2. Last years Sales Orders Totaled $557.4M and in the base case could not fulfill 5% of these orders.
  3. This Years Forecasted Sales Orders total xxxxxxxM and total net revenue at 95% service level is xxxxxxM
  4. Target inventory in the base case with safety stock at a 95% service level is $xxxxxx for your forecasted revenue at 95% service level.

• Students can assume a 10% increase in Forecast Order Value, as compared to last year. That should be distributed proportionally across the product portfolio. Hint: See price increase assumption and please factor that into your overall sales forecast.
• Revenue Growth (Hint you can manage the level of revenue growth based upon safety stock service levels, and some level of slight lift that is incorporated into the forecast) from last year is a requirement. However, what is the impact of different levels of revenue growth on margin. Sales history is based upon actual orders. At a 95% service level we can assume, for the purposes of your model, that 5% of the revenue based upon last year’s orders was lost. This should be deducted from last year’s revenue and corresponding margin. As you develop your safety stocks at an SKU level you can manage the lost sales vs margin uplift (lower service level means more lost sales, but less holding costs, while higher service levels will produce the opposite reaction.
• Aggregate Target inventory level will be provided by Professor Brown. Each student group needs to develop a base case that represents your calculated target inventory. Professor Brown has identified a notional target inventory for each data set. Students are to develop a base case with their target inventory. The target inventory should be within +/-15% of the target provided by Professor Brown
• Holding costs can be assumed to be 35% of the Cost of Goods Sold, herein referred to as COGS.
• Order costs can be assumed to be $1000 per order.
• Current product price as developed based upon COGS and Margins is to serve as the base case.
• When air is used in place of Sea the lead time can be assumed to be reduced by 42 days and the cost is increased by 30%
• All freight is priced in COGS, with the freight method as designated in the spreadsheet. If you choose to adjust the freight method then COGS will need to be adjusted. NOTE: This is an optimization scenario that the student does not need to model but can receive extra credit if successfully modeled.
• That part of COGS that represents the Freight Cost is identified for each product based upon the indicated freight mode (sea vs air).
• If you model the cost effect of the change in the freight mode assume 4X the cost from Sea to Air and a reduction of 30% from Air to Sea.
• Assumed prices will increase by 4% in the coming year.
• Assumed cost of goods sold will increase by 4% per unit in the coming year.
• Excel Spreadsheet is being provided. It is expected that data analysis techniques such as pivot tables will be needed to summarize the results of different service and inventory strategies.
• Optimization strategies need to include separate analysis for revenue and margin.