Creating Plan Scenario

What is a Plan Scenario?

The Plan Scenario type provides an enhanced method to seed data.

Intelligent Seeding

The primary objective of Seeding is to allow you to get data and/or formulas from a source scenario. You may require different ways to seed data/formulas depending on the specific business case. The main categories of seeding that you can use are as follows:

• Cell to Cell Seeding (Overlapping periods seeding type)

• Period to Period Seeding (Plan Scenario- new scenario type)

Cell to Cell Seeding

Cell to Cell seeding refers to the seeding methodology where you can use the same template structure as the source scenario. This type of seeding is illustrated in the following image.

Here, the formulas defined in period 1 of 2018 in the source scenario are seeded to period 1 of 2019 in the target scenario.

Period to Period Seeding

Period to period seeding provides a setup where data and formulas are seeded based on the matching periods. In this case, formulas defined in Period 1 of 2019 in the source scenario are seeded to Period 1 of 2019 in the target Scenario. This functionality is part of the new seeding feature, which can be enabled via Plan Scenario .

Seeding Logic

When you create a new scenario with Scenario Type as Plan Scenario, the following Seeding logic occurs in the application:

• In the Seeding section, the application automatically maps the overlapping periods based on the selected source scenario.

• For periods that do not overlap, the application maps the last period as the source. You can change this to any of the open periods in the source scenario.

• The application automatically adjusts the formulas, seed formulas, and structure for overlapping periods and out periods.

• If there is any error related to the Seeding process in adjusting the formulas, the application fills the cell with data from the source. In addition, the application records a log entry for the formula that could not be adjusted. This log is provided in the form of an exception report.

  Note:

  The Seeding process does not automatically correct Excel errors caused by circular or misplaced references.

• In case of any exceptions, you can choose to correct the errors manually by replacing the formulas or you can continue with the existing values.

How to Create a Plan Scenario?

1. To seed data using the new Plan scenario type, go to the Scenario List page (accessed by navigating to Maintenance > Administration > Scenario Setup).
2. Click the Add icon. The Add Scenario page is displayed.
3. In the General Information section, select Plan Scenario from the Scenario Type drop-down list.
4. Specify the required details to create a scenario.
5. Click the Data Seeding section; the Enable Data Seeding checkbox is selected by default and is read-only.
6. From the Scenario list, select the reference scenario. All the options in the Copy field are enabled by default and are read-only.
7. The data is seeded from the reference scenario to the target scenario and the overlapping periods are mapped by default.

• For example, if you create a new scenario as Forecast 2023-2024 and select the source as Forecast 2022 – 2023, the application will automatically map the periods of 2023. This is because these periods are overlapping in both the source and target scenarios.
• For the out periods in the Forecast 2023-2024 scenario (periods of the financial year 2024), the application takes the last period of 2023 and maps it as the default source. You can, however, change any of the mappings and choose any specific mapping from the source scenario, if the period is an open period in the source scenario.
• If there are no overlapping periods, the application keeps the Source Period column as blank by default and you can then select the required dates.
• If there are templates (in the Reference scenario) with custom time sets, map them from the Number of Years tab to target periods in the plan scenario
• For lines without account mapping, the data for closed periods is seeded from the selected Reference scenario.
• For lines with account mapping, the data for closed periods is seeded from the selected source scenario in the Closed Period Adjustment section.
• All formulas are automatically adjusted during the Seeding process.

Formula Adjustments in Seeding with the Plan ScenarioDepending on the way the formulas have been defined, the adjustments are done accordingly. For example, let’s say you create a new scenario named Forecast 2019-2020 and select Forecast 2018-2019 as the source scenario. The default mapping in the new scenario matches the 2019 formulas from the source to the target scenario.

Consider a simple template structure to explain the way formulas are adjusted.

• The template structure in the source scenario contains the following columns:
  • Column A: Segment Code
  • Column B: Name
  • Column C: Attribute
  • Columns D-0: History Periods (Year 2017)
  • Column P: History Total
  • Columns Q-AB: Year 1 (2018) Periods
  • Column AC-AN: Year 2 (2019) Periods
  • Column AO: Year 1 Total
  • Column AP: Year 2 Total

This image illustrates the Source Scenario.

• The template structure in the target scenario contains the following columns:

  • Column A: Segment Code

  • Column B: Name

  • Column C: Attribute

  • Columns D-0: History Periods (Year 2018)

  • Column P: History Total

  • Columns Q-AB: Year 1 (2019) Periods

  • Column AC-AN: Year 2 (2020) Periods

  • Column AO: Year 1 Total

  • Column AP: Year 2 Total

This image illustrates the Target Scenario.

• With automatic adjusting, the formulas that are defined for Jan 2019 in the source scenario will now be updated in Jan 2019 of the target scenario.

• This process requires the following adjustments:

  • The formula in Jan 2019 exists in cell AC2 and is based on reference to cell Q2 .

  • When this formula is replaced in the target scenario, it is placed in cell Q2 and is adjusted to refer to cell E2 to ensure that the same relationship is maintained.

  • The adjustments are not limited to a blind transpose of formulas, but as per smart adjustments.

The following example explains the adjustment of formulas based on Attribute columns:

• In the source scenario – Jan 2019 period – row 3, the formula is defined based on the Attribute column in Jan 2019: cell AC3 references cell C3.

  

• When this formula is seeded into the target scenario, the position of Jan 2019 changes, but the formula should still refer to the same attribute column reference: cell Q3 refers to cell C3.
  

Here is information on transpose logic using the image above:

• Formulas based on Cell Reference are transposed and adjusted.

• Formulas on History Periods are transposed and adjusted to point to the respective history periods.

• Formulas based on Compare scenarios are transposed and adjusted to point to the corresponding history periods.

• Value-based formulas are brought forward without any changes. For example, if a cell in the source scenario has a formula as 100+20*30 (purely based on values without reference to any other cells), then in the Target scenario, the same formula is copied without any adjustments.

• Formulas based on Line name, code, or any of the fixed columns remain true and are not transposed.

• Fixed columns include the following:

  • Line Name

  • Line Code

  • Attributes

  • Global Fields

• Spreads: Formulas based on Spreads are not transposed. Spread formulas are replaced with values.

• Formulas based on Forecast Methods are as follows:

  • The Monthly Increase in % and Monthly Increase in $ forecast methods are transposed.

  • The Full Year Growth forecast method is not transposed. Formulas are replaced with values.

• Sub lines: The items associated with Sub lines:

  • Principle: The behavior applicable to main lines is applied to Sub lines.

  • Values: The existing Values are retained.

  • Formulas: The behavior of Formulas applicable to main lines is applied here as well.

  • Simulation Process: The simulation process is as follows:

    • When formula adjustments are completed, they need to be reevaluated.

    • You must execute the Simulation Process to enable the application to recalculate the values.

    • To ensure that sub lines are processed during the simulation process, you must enable the Enable Subline Processing setting from the Template Add/Edit page (as shown in the image). This is to ensure that the sub lines are processed when the simulation process is executed.

    • We recommend that you do not simultaneously try to open and save the template during the execution of the Simulation process.

      
      

• Closed Periods:

  • When the source scenario has closed periods, no formulas are displayed in the target scenario. Mapping takes precedence in this case.

  • If the period to which the mapping is done contains formulas, the application fetches the same formulas.

• C Type Lines:

  • Formulas are adjusted similar to the L Type Lines with Formulas.

• Value-based formulas: For example, =100+10*20 .

  • These formulas are seeded as is, without any adjustments

• Lines without Account Mapping:

  • For overlapping periods:

    • Where the source and target scenarios have open periods – the data is fetched.

    • Where the source scenario has closed periods and the target scenario has open periods – the data is not fetched as closed periods are not allowed to be referred.

    • Where the source scenario has open periods and the target scenario has closed periods:

      • The data is retrieved based on the data in the source (for the same period), irrespective of the seeding.

      • For example, in case of closed periods, you may have mapped to actual periods.

      • However, since open periods do not have any understanding of the data for non-account mapping lines, there is no way to get this data

      • So, you can get the data from the source scenario/template based on the matching period.

• For Out Periods

  • Values are fetched based on the mapped period.

  • Where the application cannot transpose formulas or tries but results in formula errors, the formulas are replaced by values associated to the period.

• CAPEX: Adjustments are not done in CAPEX templates.

• Workforce Planning:

  • No data adjustments are done.

  • You must run the simulation engine to recalculate the accurate values.

• Other template types:

  • Block, Initiative, and Template-based HR follow the same behavior as that of OPEX templates as far as translating and adjusting formulas are concerned.

  • Currently, Custom Time Sets are not supported. Default Time Sets are supported as of now, which is data at the monthly, quarterly, and yearly levels.

• The exceptions for the transpose logic are as follows:

  • If the template has a formula with $, these formulas are not adjusted and are seeded in their existing state.

  • If the template has a formula, which after adjustment results in circular reference, the application tries to adjust; but, the formulas get deleted due to a circular reference error.

  • If the template has formulas, which after adjustment refer to undefined references, the application does not adjust the formulas.

  • For example, a template in a 5-year scenario (2018-2022) has formulas in the year 2022 that refer to the value in the year 2018. The new scenario needs to be created for the years 2021-2025. To adjust formulas in this case, the application tries to keep reference to the column associated with the year 2018 in the new scenario.

  • However, since the application cannot find any column referring to the year 2018, it cannot perform the adjustment process.

Best Practices When Seeding Using the Plan Scenario

• To trigger the Intelligent Seeding functionality, Admin users must schedule a simulation engine process to ensure all the data is transposed properly.
• In addition, Admin users must select the Enable Subline Processing setting and then run a simulation process. This is to ensure that the process runs for Main lines and Sublines together, when the process is run for the first time.
• Once the simulation engine has run successfully, we recommend you to review the Seeding Exception Report to validate that all formulas and data has been transposed properly.
• We recommend that you do not simultaneously try to open and save the template during the execution of the simulation process.

Limitations

If a circular reference error occurs in a cell and there are other cells that depend on this cell, then all such depending cells are also resulting into formula errors and the application replaces formulas with values.