๐ฎ Practitioner Hub ยท EPM Enterprise Cloud ยท EPM 25.11+
Scenario Planning &
Strategic Modeling
The complete Oracle EPM practitioner guide โ from Scenario vs Version architecture, Sandbox, and what-if patterns, to the Strategic Modeling module, Goal Seek, and Monte Carlo simulation. Everything you need to build, configured correctly.
The Foundation โ Scenario vs Version
Before building any what-if capability, you need to understand the two dimensions that make it possible. Most EPM practitioners know that Scenario and Version exist. Very few understand why they exist as two separate dimensions, and what each one is actually for. Getting this wrong at design time means rebuilding your entire planning structure.
๐ฎThe Core Distinction: Scenario = what type of data is this? (Budget, Actual, Forecast). Version = which iteration or perspective of that type? (Working, Final, Approved, Upside, Downside). A single Scenario can have multiple Versions. You never create multiple Scenarios to represent the same data type at different stages โ that's what Versions are for.
| Dimension | What It Represents | Examples | Who Configures It |
|---|---|---|---|
| Scenario | The type of plan โ the fundamental nature of the data. Scenarios don't inherit from each other. Each is completely independent. | Budget, Actual, Forecast, Long_Range_Plan, Acquisition_Model | Admin โ at application creation. Adding Scenarios mid-cycle requires database refresh. |
| Version | The stage or perspective of a Scenario. Versions can be shared across Scenarios or Scenario-specific. Supports the approval workflow lifecycle. | Working, Draft, Final, Approved, Upside, Downside, Base, Mgmt_Adj | Admin โ at application creation or via outline. Adding Versions is lower-risk than adding Scenarios. |
Why Both Are Sparse โ And Why It Matters
Both Scenario and Version are sparse dimensions. Essbase only allocates blocks at Scenario ร Version combinations where data actually exists. This is the key architectural fact: adding 3 new Scenario members and 5 new Version members to your outline doesn't balloon your database by 15ร โ it only increases storage when data is actually written at those intersections. This is why what-if analysis in EPBCS can be fast even with many scenarios: you only pay storage cost for the combinations that matter.
โ The Design Trap: Teams new to EPBCS often create a new Scenario for every planning iteration โ "Budget_v1", "Budget_v2_CFO", "Budget_FINAL". This is wrong. Scenarios are types; Versions are iterations. The correct structure: one Budget Scenario with Working, CFO_Review, and Final Versions. The budget cycle progression lives in Version, not Scenario. Violating this rule creates a cube that grows uncontrollably and makes reporting across versions impossible.
Vision Corp โ Correct Scenario ร Version Architecture
Scenarios โ
Actual
Budget
Forecast
Long_Range
Acquisition
Budget
Forecast
Long_Range
Acquisition
Versions (per scenario where active) โ
| Version | Actual | Budget | Forecast | Long_Range | Acquisition |
|---|---|---|---|---|---|
| Working | โ | โ | โ | โ | โ |
| Final | โ | โ | โ | โ | โ |
| Upside | โ | โ | โ | โ | โ |
| Downside | โ | โ | โ | โ | โ |
| Management_Adj | โ | โ | โ | โ | โ |
| Sandbox | โ | โ | โ | โ | โ |
โ = active combination. โ = blocks not created, no storage cost. Actual/Final rarely exists โ actuals are loaded as-is from ERP, no approval cycle needed.
The Four What-If Tools
Oracle EPM Cloud provides four distinct mechanisms for scenario modeling, each solving a different planning problem. Understanding when to use which tool is the difference between an elegant implementation and a messy one.
Scenario + Version Dimension
The foundational EPBCS mechanism. Create Base, Upside, Downside as Versions under Forecast Scenario. Planners work in Working; Finance promotes to Final. Full approval workflow, security, and audit trail. Lives in BSO cube alongside all operational data.
Best for: Budget iterations, Forecast cycles, Base/Upside/Downside that need approval workflow and long-term storage.
Sandbox
Personal what-if scratchpad. Each user gets their own isolated Sandbox copy of a plan โ changes are visible only to them until explicitly promoted. No approval workflow. Automatically available when enabled at application creation. Separate from the main Version hierarchy.
Best for: Individual analyst exploration, "what if we hired 20 more people" quick-play before any team discussion.
Strategic Modeling Module
A dedicated long-range planning module separate from the standard Planning dimensions. Has its own chart of accounts, scenario engine (Base + inheriting scenarios), Goal Seek, and Monte Carlo simulation. Built for 3โ10 year integrated P&L / BS / Cash Flow models, M&A analysis, and debt/capital structure modeling.
Best for: 3โ10 year strategic plans, M&A modeling, capital structure what-ifs, sensitivity analysis with probability distributions.
Freeform (EPM Enterprise)
No required dimension constraints. Build any cube structure you need โ custom dimensions, custom relationships. The most flexible option, used for highly custom analytical models or legacy Essbase application migrations to cloud. No Predictive Planning features (requires standard Scenario/Version/Period dimensions).
Best for: Highly custom operational models, Essbase-to-cloud migrations, non-standard planning processes that don't fit module templates.
| Decision | Recommended Tool | Why |
|---|---|---|
| 3 forecast scenarios (Base/Up/Down) for quarterly Board | Versions under Forecast | Same Scenario, different Versions. Comparison reports work natively. Approval workflow controls who can edit which version. |
| Annual Budget with Management Adjustments on top | Budget/Working + Budget/Mgmt_Adj Version | Management overlays live in a separate Version. Comparison = Working vs Mgmt_Adj. Planners can't see or edit Mgmt_Adj. |
| Analyst wants to test "what if headcount grows 15%" | Sandbox | Personal, isolated, instant. No impact on working data. No approval needed. Promote to Working if the analysis is worth sharing. |
| CFO wants 5-year integrated P&L model for acquisition target | Strategic Modeling | Integrated three-statement model with built-in financial logic. Cross-scenario consolidation. M&A deal period feature. Monte Carlo for probability ranges. |
| Quantify probability of hitting revenue guidance | Monte Carlo in Strategic Modeling | Run 1,000+ simulations across uncertain assumptions. Output: probability distribution, percentile confidence intervals, target achievement probability. |
| "What revenue growth rate do we need to hit $300M EBITDA?" | Goal Seek in Strategic Modeling | Reverse-engineer: specify a target output, let Goal Seek solve for the required input driver value. Instant iteration. |
Sandbox โ Personal What-If Scratchpad
Sandbox is the most underused feature in EPBCS. It gives every analyst their own personal copy of a form's data to experiment with โ completely isolated from the working budget โ without needing admin rights, Groovy scripts, or a new Version member.
1
Enable at Application Creation
During the Planning application creation wizard: Details page โ Enable Sandboxes = Yes. This cannot be changed after the application is created. It adds a Sandbox dimension to the cube. Once enabled, all standard forms automatically have Sandbox capability.
2
Enable Sandbox on Specific Forms
In Form Designer โ Properties โ enable "Allow Sandbox". Not all forms need Sandbox โ enable it selectively on high-value what-if forms (revenue planning, headcount, driver adjustment forms) to avoid unnecessary complexity.
3
Planner Opens Sandbox Mode
On a Sandbox-enabled form, the planner sees a Sandbox toolbar. Click Create Sandbox or select an existing personal Sandbox. The form header indicates which Sandbox is active. Data from Working is copied into the personal Sandbox on first open.
4
Experiment Freely
Change any driver โ headcount, price, volume, growth rate. Calculated accounts update instantly (Dynamic Calc members) or on Save (stored members). Compare Sandbox vs Working in a split view. The planner owns this space completely.
5
Promote or Discard
Promote: pushes Sandbox changes to Working version โ triggers normal save/validation/Groovy rules. Discard: resets Sandbox to current Working values. Multiple Sandboxes per user are possible โ create "Scenario A" and "Scenario B" Sandboxes to compare two approaches side-by-side.
๐กProduction Pattern โ Multi-Sandbox Comparison: Give analysts two Sandbox slots per form: "Conservative_Assumptions" and "Aggressive_Assumptions". Both display in a comparison dashboard alongside the Working version. The CFO sees all three views simultaneously and makes the call on which assumptions to promote. This eliminates the "email me your scenario spreadsheet" workflow permanently.
โ Sandbox + Groovy Rules: When a planner Promotes a Sandbox, it triggers the form's
beforeSave and afterSave Groovy rules exactly as a normal save would. If you have validation guards in those rules (e.g. checking approval status or valid intersections), test that they work correctly with Sandbox promotions. A rule that blocks all saves with a hard-coded "wrong Version" check will also block Sandbox promotions.Vision Corp โ The Complete Scenario Matrix
How Vision Corporation ($2.8B, 12 entities, 6 currencies) structures its scenario and version dimensions across the full planning landscape. This is the reference architecture for a mid-to-large enterprise EPBCS implementation.
Active Scenario ร Version Combinations โ Vision Corp OEP_FS Cube
Actual
Working
GL actuals loaded nightly from SAP via DI. No planner write access. Read-only for all reports.
Budget
Working
Live budget input. Planners write here. Approval workflow: First Pass โ Under Review โ Approved โ Locked.
Budget
Mgmt_Adj
Finance overlay on top of planner Working. CFO adjustments. Planners cannot see this version.
Budget
Final
Board-approved budget. Locked after CFO sign-off. Source for variance reporting vs Actual.
Forecast
Working
Rolling 12-month forecast. Advances monthly via SubVar. Planners update monthly assumptions.
Forecast
Upside
+10% revenue assumption across all entities. Seeded from Working via DATACOPY, then adjusted. Owned by CFO office.
Forecast
Downside
-15% revenue scenario. Supply chain disruption model. Used for covenant testing and cash stress testing.
Forecast
Final
Board submission version. Promoted from Working by Finance at quarter-end. Basis for external guidance.
Long_Range
Working
3-year strategic plan. Annual update cycle. Drives target-setting for next year's Budget.
Long_Range
Upside
Strategic acceleration scenario. New market entry, M&A. Reviewed by Board annually.
Acquisition
Working
M&A target model. Temporary Scenario created during deal analysis. Feeds into Strategic Modeling consolidation.
Budget / Forecast
Sandbox
Per-analyst personal what-if. 2 Sandbox slots per planner. Promote to Working on approval.
๐FP&A Reality โ Why Three Forecast Versions Matter: During a Board Prep cycle, Vision's CFO needs to present three views simultaneously: the Working (current planner estimate), the Upside (management's stretch case), and the Downside (stress scenario for covenant testing with the CFO's lender). Without Upside and Downside Versions properly structured in EPBCS, Finance is maintaining parallel Excel models โ creating exactly the reconciliation chaos EPBCS was deployed to eliminate.
Strategic Modeling Module
What Makes It Different from Standard Planning Scenarios
Built-in Chart of Accounts
Strategic Modeling ships with a hierarchical chart of accounts for a complete three-statement model: Income Statement, Balance Sheet, Cash Flow (direct and indirect), and Treasury accounts. No dimension design required โ use the templates and customize from there. Account codes follow the v-series convention (v0300 = Unit Volume, v0305 = Product Price, etc.).
Inheriting Scenarios
Strategic Modeling's scenario engine is different from Versions. A scenario can be Standalone (fully independent set of assumptions) or Inheriting (inherits values from a parent scenario except for explicitly overridden accounts). The Upside scenario inherits Base's COGS assumptions but overrides Revenue drivers โ only 2 account changes needed to model the full P&L impact.
Flexible Time Structure
Models can span any time horizon โ annual, quarterly, or mixed frequency. A common pattern: annual data for Years 1โ5, quarterly for Year 1 (matching the operating forecast). Strategic Modeling handles both in the same model, with rollup from quarterly to annual automatic.
Multi-Model Consolidation
Each business unit has its own Strategic Modeling model. A parent consolidation structure blends scenarios across models โ Vision US in its "Expansion" scenario, Vision EU in its "Conservative" scenario, combining to produce a consolidated enterprise view. Business unit models don't require matching structures.
Treasury & Debt Modeling
Built-in debt modeling: term loans, revolving credit, bonds, equity. Automatically calculates interest expense, principal payments, amortization of issuance costs. Model credit rating impact of leverage scenarios. Covenant testing (Debt/EBITDA, Interest Coverage) with breach alerts. No Groovy scripting required โ all out-of-box.
Forecast Methods
Each account in a Strategic Modeling model has an assigned forecast method that determines how future periods are calculated: % of Revenue, % growth over prior year, absolute value, % of a driver account. Change the forecast method for an account and all future periods recalculate instantly. Supports rapid re-modelling of structural assumptions.
Setting Up Your First Strategic Model โ Step by Step
1
Enable Strategic Modeling on the Application
Home โ Application โ Configure โ Strategic Modeling โ Enable. Select your industry template (Generic, Retail, Banking, Insurance, etc.). The template pre-loads the chart of accounts relevant to your industry. You can add/remove accounts after setup.
2
Create a Model
Home โ Strategic Modeling โ Models โ New Model. Name it (e.g., "Vision_Corp_LRP"), set the time structure (start year, end year, frequency), select the base currency, and enable multicurrency if required. The model is created with the template chart of accounts populated.
3
Populate the Base Scenario
Open the model โ Account View โ POV: Scenario = Base, Dataview = Standard. Enter historical actuals for context years (last 2โ3 years). Enter the Base forecast assumptions for projected years. Set forecast methods per account group: Revenue accounts = % growth, COGS = % of Revenue, Operating expenses = mixed.
4
Create What-If Scenarios
Scenario Manager โ New Scenario. Type = Standalone (independent) or Inherits (from Base or another scenario). For Inheriting scenarios: specify which accounts differ from the parent. Example: Upside Case inherits all Base accounts except v0300 (Unit Volume) and v0305 (Product Price) โ override just those two drivers to see full P&L impact instantly.
5
Run Calculate and Compare
Calculate โ Current Scenario (or All Scenarios). View side-by-side scenario comparisons via Account View with the Scenario POV dropdown, or via Strategic Modeling's built-in reports (Income Statement, Balance Sheet, Cash Flow, KPI dashboards). Export to Smart View for offline analysis.
6
Integrate with Operational Plan
Actions โ Push to Financials: maps Strategic Modeling targets (strategic revenue, EBITDA targets) to the Financials module as top-down targets that planners see when building the bottom-up operational budget. Creates the strategy-to-execution alignment loop that prevents "the budget doesn't match the LRP" conversations.
๐ฎThe M&A Use Case: Strategic Modeling's deal period feature is purpose-built for acquisition analysis. Define the acquisition closing date, model the combined entity financials including synergies, purchase accounting adjustments, and acquisition financing. Run multiple deal structures (all-cash vs. debt-financed vs. equity) as separate scenarios and compare EPS accretion/dilution, pro forma leverage ratios, and combined cash flow. This replaces the CFO's Excel merger model entirely โ with a live, auditable, multi-scenario tool.
Goal Seek โ Working Backwards
Goal Seek answers the question Finance always asks but Excel can barely handle: "We need $300M EBITDA โ what does that require from our revenue growth rate?" Rather than iterating assumptions manually until the output hits the target, Goal Seek reverse-engineers the required driver value automatically.
How Goal Seek Works
You specify: (1) a target output account and a target value (e.g. EBITDA = $300M), (2) the driver account Goal Seek should adjust (e.g. Unit Volume), (3) the time range and adjustment method (same amount across all periods, same percentage, or front-loaded). Goal Seek uses iterative calculation to find the driver value that produces the target output. Impacted cells turn yellow โ review before accepting.
Adjustment Methods
Same Amount: adds the same absolute value to each period (e.g. +$50K revenue per month). Same Percentage: grows each period by the same % (compound growth). Front-Loaded: adjusts earlier periods more heavily. Each method answers a different question โ "how many units per month?" vs "what CAGR?" vs "where do we need to accelerate early?"
Goal Seek โ Vision Corp Example Sequence
// Scenario: CFO wants to test if a $300M EBITDA target is achievable
// Current Base EBITDA forecast: $241M
// Question: What Unit Volume growth rate closes the $59M gap?
// Step 1: Open Strategic Model โ Account View โ Income Statement tab
// Step 2: Right-click EBITDA cell for Year 3 ($241.58M)
// Step 3: Select Goal Seek
Goal: EBITDA = 300,000 // $300M target
Adjust By: v0300 (Unit Volume) // the driver to change
Start: FY2024
End: FY2026
Method: Same Percentage // compound growth rate
// Result: Unit Volume must grow by 17.26% annually to hit $300M EBITDA
// Impacted cells turn yellow โ includes COGS, Operating Expenses (via % of Revenue formulas)
// CFO can see: is 17.26% CAGR achievable? What does it imply for market share?
// Click Accept to update the scenario, or Close to discard
๐กFP&A Board Prep Pattern: Vision's CFO uses Goal Seek in the week before every Board meeting to answer exactly one question: "What operating leverage assumptions are embedded in our guidance?" Run Goal Seek with Revenue Growth as the driver against EPS as the target. The answer โ the breakeven revenue growth rate for EPS guidance โ becomes the key slide in the Board deck. Previously this took two days in Excel; in Strategic Modeling it takes 4 minutes.
Monte Carlo Simulation
Monte Carlo simulation in Strategic Modeling quantifies risk by running thousands of scenario iterations automatically. Instead of three point estimates (Base/Up/Down), you get a full probability distribution โ "there is a 73% chance of achieving $250M EBITDA or higher." This is the most rigorous scenario planning tool in the EPM suite.
๐ฒHow Oracle's Monte Carlo Works: You define uncertain inputs as Assumptions with a probability distribution (Normal, Uniform, Triangular, Log-Normal) and a range. You define target outputs as Forecasts. Strategic Modeling runs 500โ1,000 trials (each trial samples each assumption randomly from its distribution, recalculates the full model, records the output). The result: a histogram of all possible outcomes with percentile confidence intervals.
Monte Carlo Output โ Vision Corp FY2026 Net Income ($M)
Target: $250M net income guidance โ 72% probability of achieving or exceeding ยท 1,000 trials ยท Assumptions: FX rate (Normal ยฑ10%), Unit Volume (Triangular ยฑ15%), Commodity costs (Log-Normal +5%/โ8%)
Sampling Methods โ Monte Carlo vs Latin Hypercube
| Method | How It Works | Best For | Trial Count |
|---|---|---|---|
| Monte Carlo | Pure random sampling โ each trial draws independently from each assumption's distribution. Natural, random-feel outputs. | General scenario analysis. When you want "what does this distribution actually look like?" | 500โ1,000+ for stable results |
| Latin Hypercube | Stratified sampling โ divides each distribution into equal-probability bands and samples once per band. More even coverage of the distribution space. | Accurate percentile statistics. When you need 95th/5th percentile confidence with fewer trials. | 200โ500 sufficient for stable percentiles |
Monte Carlo Setup โ Vision Corp FX Risk Model
// Define Assumptions (uncertain inputs)
// Right-click each driver account โ Define as Assumption
Assumption 1: EUR/USD exchange rate
Distribution: Normal
Mean: 1.08 (current forecast rate)
Std Dev: 0.08 (ยฑ10% range at 1ฯ)
Assumption 2: Unit Volume growth rate
Distribution: Triangular
Minimum: -5% (worst case: market softness)
Most Likely: +8% (base forecast)
Maximum: +20% (bull case: market share gain)
Assumption 3: Commodity cost inflation
Distribution: Log-Normal (skewed right โ costs rarely fall but can spike)
Mean: +4%
Std Dev: 6%
// Define Forecasts (outputs to track)
// Right-click each output account โ Define as Forecast
Forecast 1: Net Income (v2050)
Forecast 2: EBITDA Margin % (v2040 / v1010)
Forecast 3: Free Cash Flow (v3200)
// Run: Strategic Modeling โ Simulation โ Run
// Trials: 1,000 ยท Method: Latin Hypercube
// Review: histogram + percentile table per Forecast account
Integration with Financials & Operational Plan
Strategic Modeling is most powerful when connected to the operational plan in the Financials module. The integration creates a two-directional alignment: strategic targets flow down to operational planners as constraints; operational actuals and forecasts flow up to refresh the strategic model.
โ
Strategic Targets โ Operational Budget (Top-Down)
Strategic Modeling โ Actions โ Push to Financials. Maps Strategic Modeling scenario values (Revenue target, EBITDA target) to the Financials module. Planners see their budget targets in context of the strategic plan โ "your revenue target is $284M, which achieves the LRP goal." Eliminates the disconnect between "what Finance agreed in the LRP" and "what planners were told to budget."
โ
Operational Actuals โ Strategic Model (Bottom-Up Refresh)
Data Integration pipeline or manual refresh: push current-year actuals from OEP_FS cube to the Strategic Modeling historical input accounts. The strategic model's context years update automatically. Goal Seek and Monte Carlo results always reflect actual performance, not stale point-in-time assumptions.
โ
Job Console Monitoring
Strategic Modeling integration jobs (Push to Financials, Consolidation rollups) appear in Application โ Jobs โ Job Console with full run status and error details. This is how you monitor scheduled Strategic Modeling jobs in production โ same Job Console as all other EPM operations.
๐กSmart View Integration: Strategic Modeling has a dedicated Smart View extension (download from EPM Cloud โ Smart View โ Strategic Modeling extension). Finance can work directly in Excel with live Strategic Modeling data โ building Board presentations where numbers auto-refresh, running Goal Seek from Excel, and building custom scenario comparison reports. The extension supports both account view grids and report views.
Groovy Patterns for Scenario Automation
Most scenario automation tasks in EPBCS โ seeding new versions, running what-if rule chains, detecting scenario state โ are done via Groovy rules or EPM Automate scripts. These are the production patterns Vision Corp uses.
Seed Upside/Downside Versions from Working via Groovy
โผ
Vision Corp runs this pattern at the start of each quarterly forecast cycle. Working is seeded from the prior quarter's Forecast/Final. Upside and Downside are then seeded from Working and adjusted by a global assumption set. The entire operation is triggered from an Action Menu button on the Forecast form.
/* Vision Corp โ Quarterly Scenario Seeding
Triggered from Action Menu on Forecast input form
Step 1: This Groovy rule validates state and triggers the calc chain
Step 2: A calc script runs DATACOPY for each scenario (see below)
Step 3: A second Groovy rule applies the scenario adjustments */
// Read current period from SubVar
def curPeriod = operation.application.getSubstitutionVariableValue("CurPeriod")
def curYear = operation.application.getSubstitutionVariableValue("CurYear")
println("Seeding Upside/Downside for ${curPeriod} / ${curYear}")
// Guard: don't seed if Forecast/Final is locked
def cube = operation.application.getCube("OEP_FS")
def entities = cube.getOutline().getDimension("Entity")
.findMember("Total_Entity").getLevelZeroMembers()
entities.each { e ->
def status = cube.getPlanningContext()
.getPlanningUnitStatus(e.getName(), "Forecast", curYear)
if (status == "LOCKED") {
throwVetoException("${e.getName()} Forecast is Locked โ cannot seed scenarios. Contact Finance Admin.")
}
}
// Trigger the DATACOPY calc script via job factory
def jobDef = operation.application.getJobFactory()
.createRuleJobDefinition("Seed_Scenario_Versions", [:])
def result = operation.application.executeJob(jobDef)
if (!result.isSuccessful()) {
throwVetoException("Scenario seeding failed. Check Job Console.")
}
println("Seeding complete. Upside and Downside ready for adjustment.")
โ Calc Script Required for DATACOPY: The actual block creation (DATACOPY from Forecast/Working โ Forecast/Upside, Forecast/Downside) must happen in a native calc script โ Groovy cannot create blocks. The Groovy rule above triggers the calc script via
createRuleJobDefinition. Never try to do DATACOPY in Groovy itself.
Apply Global Scenario Adjustments (Upside +10%, Downside -15%)
โผ
After seeding, apply global multipliers to revenue driver accounts. Upside = Working ร 1.10, Downside = Working ร 0.85. Batch read all entities at once, compute adjustments, write in one call.
/* Apply Scenario Multipliers โ runs after DATACOPY seeding complete */
def cube = operation.application.getCube("OEP_FS")
def entities = cube.getOutline().getDimension("Entity")
.findMember("Total_Entity").getLevelZeroMembers()
def curYear = operation.application.getSubstitutionVariableValue("CurYear")
def UPSIDE_MULT = 1.10 // +10% revenue drivers for Upside scenario
def DOWNSIDE_MULT = 0.85 // -15% revenue drivers for Downside scenario
def REVENUE_ACCTS = ["Product_Revenue", "Service_Revenue", "License_Revenue"]
def PERIODS = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
def VERSIONS = ["Upside": UPSIDE_MULT, "Downside": DOWNSIDE_MULT]
def writes = []
def writeCount = 0
VERSIONS.each { version, mult ->
entities.each { e ->
REVENUE_ACCTS.each { acct ->
PERIODS.each { period ->
// Read from Working (source), write to this Version
def baseVal = getCell(e.getName(), acct, period, curYear, "Forecast", "Working") ?: 0
writes << [e.getName(), acct, period, curYear, "Forecast", version, baseVal * mult]
writeCount++
}
}
}
}
println("Staging ${writeCount} scenario adjustment writes")
operation.grid.setDataCellValues(writes) // 1 network call for all writes
println("Upside (+10%) and Downside (-15%) applied. Run CALC ALL to aggregate.")
EPM Automate โ Schedule Quarterly Scenario Refresh
โผ
#!/bin/bash
# Vision Corp โ Quarterly Scenario Refresh Script
# Runs on first day of new forecast quarter (scheduled via cron)
# Prerequisites: Working version is closed, Final version locked
EPM="/opt/Oracle/EPMAutomate/bin/epmautomate"
LOG="/var/log/epm/scenario_refresh_$(date +%Y%m%d).log"
$EPM login [email protected] $PASSWORD $ENV_URL >> $LOG 2>&1
# 1. Advance the rolling forecast period
$EPM setSubstitutionVariable OEP_FS CurPeriod $(date +%b) >> $LOG
$EPM setSubstitutionVariable OEP_FS CurYear FY$(date +%Y) >> $LOG
# 2. Seed Working from prior quarter Final
$EPM runBusinessRule "Seed_Working_From_Final" >> $LOG 2>&1
if [ $? -ne 0 ]; then
echo "ERROR: Working seed failed" >> $LOG
exit 1
fi
# 3. DATACOPY Working โ Upside and Downside (via calc script Business Rule)
$EPM runBusinessRule "Seed_Scenario_Versions" >> $LOG 2>&1
# 4. Apply scenario multipliers (the Groovy rule above)
$EPM runBusinessRule "Apply_Scenario_Multipliers" >> $LOG 2>&1
# 5. Run CALC ALL to aggregate all three versions
$EPM runBusinessRule "CALC_ALL_FORECAST" >> $LOG 2>&1
# 6. Run scenario comparison report package for CFO
$EPM runReportingObjectJobset "CFO_Scenario_Pack" >> $LOG 2>&1
$EPM logout >> $LOG 2>&1
echo "Scenario refresh complete: $(date)" >> $LOG
Build Checklist โ Scenario Architecture
๐ Design Phase โ Before You Build
Document all required Scenarios (types of plans) and Versions (iterations within each type) โ get CFO sign-off before building
Confirm: does this application need Sandbox? Decide at creation time โ it cannot be enabled retroactively
Confirm: does this use case require Strategic Modeling (long-range, M&A, Monte Carlo)? Verify EPM Enterprise license
Design Scenario ร Version matrix (like Vision Corp's above) โ document which combinations are active vs not
Map Versions to approval workflow: which Versions need Planning Unit Hierarchy? (Working does; Sandbox does not)
Design Valid Intersections to block planner write access to Final, Approved, Actual scenarios
๐จ Build Phase
Add Scenario and Version members to outline with correct properties (Start, End year, current period, security)
Configure dimension security: planners read-only on Actual, no access to Mgmt_Adj, write access to Working only
Build DATACOPY calc script for seeding Working โ Upside/Downside/scenario copies
Build Groovy rule for applying scenario multipliers (batch reads, staged writes, zero-division guard)
Test Sandbox: enable on target forms, verify isolation from Working, test Promote and Discard flows
Build scenario comparison report (Forecast/Working vs Forecast/Upside vs Forecast/Downside in one FRS report)
If Strategic Modeling: enable module, select industry template, configure model structure, verify integration with Financials
Build EPM Automate script for quarterly scenario refresh โ test in non-prod before scheduling in prod
โ
UAT / Production Readiness
Test seeding with empty data (new year, no blocks) โ verify DATACOPY runs before Groovy adjustments
Test Sandbox Promote with beforeSave Groovy rules โ confirm validation guards fire correctly on promotion
Verify security: log in as a Planner role user and confirm they cannot see or edit Actual/Final/Mgmt_Adj
Performance test: run CALC ALL across all three scenario versions, verify it completes within acceptable time
Document the scenario naming convention and Version purpose in the Finance team runbook
FP&A Context โ Why This Changes the CFO Conversation
๐The Before State at Every Client: Without proper scenario architecture in EPBCS, Vision Corp's FP&A team maintains three separate Excel workbooks: "Q3_Forecast_BASE.xlsx", "Q3_Forecast_UPSIDE_CFO_v3_FINAL.xlsx", and "Q3_Forecast_DOWNSIDE_STRESS.xlsx". Each takes 2 days to update when assumptions change. The three workbooks disagree by month 3 because someone forgot to propagate a formula change. The CFO presentation takes a separate Excel consolidation model to produce the three-scenario comparison table.
Standard Implementation
Scenario Versions in Planning
Base/Upside/Downside as Versions under Forecast Scenario. Seeded from Working via DATACOPY. Scenario comparison report built natively. Update time: 2 hours โ 15 minutes when assumptions change.
CFO gets:
Live three-way scenario comparison ยท Audit trail per version ยท Approval workflow preserved ยท No Excel reconciliation
Advanced Implementation
Sandbox + Version Architecture
Planners explore individually in Sandbox, propose changes for review, promote approved changes to Working. Finance runs scenario comparison across Working + promoted Sandbox analyses. Multiple analysts can run parallel what-ifs without touching the locked forecast.
CFO gets:
Bottom-up scenario contributions ยท Planner ownership ยท Working data integrity maintained ยท Rapid iteration cycle
Full Strategic Implementation
Strategic Modeling + Monte Carlo
3-5 year integrated P&L/BS/CF model with Base + Upside + Downside scenarios. Monte Carlo quantifies guidance risk โ board presentation includes a probability distribution, not three point estimates. Goal Seek answers "what growth rate closes the gap to guidance?"
CFO gets:
Guidance with confidence interval ยท Probability of miss quantified ยท Integrated three-statement LRP ยท M&A scenario modeling
๐ฎThe CFO Conversation That Changes: Before EPBCS scenario architecture, the CFO presentation is: "Here are three Excel files. The numbers may not tie exactly." After: "Here are your Base, Upside, and Downside scenarios. There is a 73% probability of meeting guidance. The scenario that closes the gap requires 17% unit volume growth. Shall I run Goal Seek on a 14% assumption to test the downside?"
๐ฎ
The Oracle โ Scenario Planning Expert
Strategic Modeling ยท What-If Architecture ยท Monte Carlo ยท Sandbox
Ask anything about scenario planning, Strategic Modeling, Monte Carlo simulation, Sandbox architecture, or Version design in Oracle EPM Cloud.
Oracle