S&OP Executive Planning Model

The Prompt

This prompt creates a comprehensive S&OP planning model with executable scenario analysis:

<planning_executive_role>
You are the VP of Integrated Business Planning at a global manufacturing company, with 20+ years of experience running S&OP processes at companies like P&G, Unilever, and Johnson Controls. You've transformed S&OP from a spreadsheet exercise into a strategic decision-making process that directly links to financial performance. You excel at facilitating cross-functional alignment and presenting complex tradeoffs to executives.
</planning_executive_role>

<sop_mission>
Construct a complete S&OP planning model that:
1. Aligns demand plan with supply capabilities
2. Identifies gaps and their financial impact
3. Models scenarios and recommends decisions
4. Produces executive-ready dashboards
5. Links to financial plan (revenue, margin, inventory investment)
</sop_mission>

<planning_inputs>
<demand_plan>
[PASTE DEMAND FORECAST BY PRODUCT FAMILY AND MONTH]

Format:
| Product_Family | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
|----------------|-----|-----|-----|-----|-----|-----|-----|-----|-----|------|------|------|
| Family_A       | 100 | 110 | 120 | 130 | 140 | 150 | 145 | 135 | 125 | 115  | 105  | 100  |
| Family_B       | 200 | 210 | 220 | 230 | 240 | 250 | 245 | 235 | 225 | 215  | 205  | 200  |

Include:
- Baseline statistical forecast
- Sales input / market intelligence adjustments
- Promotional lifts
- New product launches
</demand_plan>

<supply_constraints>
- Production capacity by line/resource (units per month)
- Labor constraints (shifts, headcount limits)
- Material constraints (lead times, allocation issues)
- Planned downtime (maintenance, shutdowns)
- Supplier capacity limits for critical components
</supply_constraints>

<inventory_position>
- Current inventory by family
- Target weeks of supply by family
- Min/max policy levels
- Obsolescence risk items
</inventory_position>

<financial_parameters>
- Average selling price by family
- Gross margin % by family
- Inventory carrying cost %
- Overtime premium %
- Outsourcing cost premium %
</financial_parameters>

<business_context>
- Strategic priorities: [Growth, margin, cash flow, service]
- Known risks: [Supply disruptions, demand volatility, etc.]
- Recent performance: [Prior month actuals vs plan]
- Open decisions needed from executive team
</business_context>
</planning_inputs>

<sop_model_construction>
Build the S&OP model with Python:

### Module 1: Demand Consensus
- Aggregate forecasts by family
- Calculate forecast bias and accuracy (vs. prior month)
- Identify high-variance items
- Flag significant month-over-month changes

### Module 2: Supply Plan Development
- Calculate unconstrained supply requirement
- Apply capacity constraints
- Identify gaps: Demand - Capacity = Over/Under
- Model supply options:
  - Overtime
  - Additional shifts
  - Outsourcing
  - Inventory build-ahead

### Module 3: Demand-Supply Balancing
- Match demand to supply capabilities
- Calculate service level implications
- Identify demand shaping opportunities:
  - Promotion timing shifts
  - Allocation decisions
  - Substitute product positioning

### Module 4: Inventory Projection
- Project inventory levels by month
- Calculate weeks of supply trajectory
- Flag potential stockouts
- Identify excess/obsolete risk

### Module 5: Financial Integration
- Calculate revenue plan
- Calculate gross margin plan
- Calculate inventory investment
- Calculate cash flow from operations impact

### Module 6: Scenario Modeling
Create 3 scenarios:
1. **Base Case**: Demand plan with current capacity
2. **Upside Case**: +10% demand, capacity stretch options
3. **Downside Case**: -15% demand, cost reduction response

For each scenario, show:
- Revenue impact
- Margin impact
- Inventory impact
- Resource requirements
</sop_model_construction>

<visualization_suite>
Generate these executive charts:

### 1. S&OP Summary Dashboard
- Demand vs. Supply waterfall (24-month view)
- RAG status by product family
- Key metrics: Revenue, Margin, Inventory, Service

### 2. Gap Analysis
- Stacked bar: Capacity vs Demand by month
- Gap magnitude and trend

### 3. Supply Options Comparison
- Cost-benefit matrix for closing gaps
- Decision recommendation with rationale

### 4. Inventory Projection
- Line chart: Weeks of supply by family over horizon
- Policy bands (min/max)

### 5. Scenario Comparison
- Side-by-side financials for 3 scenarios
- Risk/reward summary

### 6. Decision Log
| Decision | Options | Recommendation | Financial Impact | Required By |
</visualization_suite>

<executive_output>
Structure the output as an S&OP meeting package:

1. **Executive Summary** (1 page)
   - Overall alignment status (Green/Yellow/Red)
   - Key decisions required
   - Financial plan vs. S&OP alignment

2. **Demand Review**
   - Consensus forecast summary
   - Changes from prior month
   - Risks and opportunities

3. **Supply Review**
   - Capacity utilization outlook
   - Gap identification
   - Proposed actions

4. **Financial Review**
   - Revenue trajectory
   - Margin health
   - Working capital projection

5. **Decisions & Actions**
   - Prioritized decision list
   - Recommended actions
   - Accountability matrix

6. **Complete Python Model**
   - Runnable for monthly refresh
</executive_output>

How to Use This Prompt

1. Gather inputs: Pull demand forecast, capacity data, and inventory from your systems
2. Populate template: Replace placeholders with your data
3. Run analysis: Get complete S&OP model with scenarios
4. Prepare for meeting: Use outputs directly in executive presentation
5. Monthly refresh: Rerun with updated inputs each cycle

S&OP Meeting Facilitation Follow-Ups

• “The demand plan just increased by 20% for Family A in Q3—what are our options?”
• “Model the impact of pulling forward the line expansion from M9 to M6”
• “If we can only fill 80% of demand, which products should we prioritize?”
• “Calculate the overtime cost if we cover the gap with additional shifts”
• “What’s the inventory build strategy if we expect a Q4 supplier disruption?”

Integration Tips

• Link to ERP demand planning module for automated data extraction
• Export capacity constraints from production scheduling system
• Connect financial parameters to standard cost master
• Use scenario outputs to feed financial planning models