Download the spreadsheet “SandOP worksheet,” you’ll need to develop your inventory plan.
- Your firm manufactures test kits for swimming pools.
- In the past month (weeks 1-4), you noticed that actual sales did not meet the forecast (1,666 units under). Therefore, inventory has gone up significantly.
- Production has gone well and is slightly higher than planned during the current month.
You must prepare a production and inventory plan to present to management at the upcoming S&OP meeting.
The sales/marketing team has provided you with the following forecast information:
- Base sales are expected to be 25,875 in the coming 5-week period.
- The marketing team is planning a promotion in weeks 6 and 7. They expect a 20% sales lift from this promotion in those weeks.
- They plan to introduce a new test kit in week 7. It is expected to provide incremental sales of 10%
Here are your S&OP plan constraints:
- You have 75 employees on staff.
- You can schedule all of the employees or a lesser number but, not less than 67 in any week.
- Each employee works only one shift per day.
- You can schedule production for 4, 5, or 6 days in any given week. They can only work 6 days once per period (weeks 5-9).
- All employees must work that week if you schedule an overtime day.
- Your CFO wants the average inventory to come down at least 10% but the VP of sales does not want it to come down more than 20% (because they are worried this will impact service levels).
- Inventory cannot go below 3 days of stock on hand in any given week (because this will be below safety stock levels). Inventory cannot go above 6 days on hand in any given week (because you have limited storage capacity).
Part A (20 points):
Using the spreadsheet provided, create an S&OP plan that does not violate any of the above constraints.
Requirements:
-
- Populate forecast cells
- Populate production cells
- Rename your final sheet in a way that indicates the file is yours
- Submit this file that captures the plan you intend to submit to management.
- There is a 3-point deduction for each constraint violation (A through F above) (max 18)
Part B, each question is worth 4 points (20 total):
Submit a Word or PDF document that answers the following questions – make sure it is clear on your spreadsheet plan where your answers originate.
- What are your projected Sales for the coming 5 weeks (units)?
- What is your projected Revenue for the coming 5 weeks ($)?
- What is your projected average inventory (both units and $)?
- What was your net average inventory reduction (both units and %)?
- Did you have to work any overtime days to meet your plan? Why or, why not?