Sales and Operations Plans:

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.

1. You can schedule all of the employees or a lesser number but, not less than 67 in any week.
2. Each employee works only one shift per day.
3. 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).
4. All employees must work that week if you schedule an overtime day.
5. 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).
6. 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.

1. What are your projected Sales for the coming 5 weeks (units)?
2. What is your projected Revenue for the coming 5 weeks ($)?
3. What is your projected average inventory (both units and $)?
4. What was your net average inventory reduction (both units and %)?
5. Did you have to work any overtime days to meet your plan? Why or, why not?