Formula Adjustments in Seeding

Depending 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 based on History columns are not transposed; instead, History column formulas are copied to the target scenario.

• 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.