Locking Key figures (or cells) in planning books/data views is an important activity of forecast planning process across SAP clients in APO DP.
Locking is usually done to keep the planning results and situation updated by DP planners to remain undisturbed in the planning books.
Incoming New CVC values to planning area / planning books has the potential to derail the locked cells and in turn disturb the planning situation
This document would focus on how to lock the important key figures in planning book for incoming New CVC using a Macro.
Challenges – Saved Planning results Vs New Incoming CVC values
New CVC for forecasting would come into Demand planning either weekly or monthly based on different business needs. CVC values are bought to planning area/books through Info cubes. Business could plan at the detailed level of CVC and arrive at final consensus forecast for a month.
DP business planners would have to lock the key figures for CVC value combinations for future time buckets after planning and save the planning results for important key figures such as Proportional factor, Final Forecast, Consensus Forecast etc.
This practice is followed, since Locked cells don’t have the risk of getting overwritten when aggregation or disaggregation happens inside the planning books.
Challenge faced for example – the “Proportional factor” key figure is manually maintained by regional DP planners at detailed level in the units of %.
This distribution % gets disturbed post new CVC values are bought into the system, as new CVC comes Unlocked.
Incoming New CVC values comes to planning area as Initial (blank values) and remain as unlocked cells across time buckets and this could disturb the planning results saved already.
Below screen shot and explanation would further illustrate the challenge faced
For instance planners have locked the Key Figure “Prop Factor”, this KF is used by planners to manually key in Proportional factors (%) based on past sales data. The New CVC values that come in would come in unlocked and would receive forecast volumes, which is not intended.
Rather Planners should decide if this new CVC needs to be planned for this forecast cycle, if yes they would edit the % manually to manage the new CVC instead of auto value population to those cells since they are not locked.
Challenges – Locking New CVC in foreground
If planners are expected to lock the new CVC values inside the planning books through foreground option. They would have to load data at detailed level and look for new CVC with blank (initial values).
Post this they can lock the cells manually by highlighting them in foreground through right click option.
- Identifying the new CVC in data view would be a challenge & this process could result in manual errors.
- To load the data in planning books from live cache memory usually takes longer processing times.
This would result the users waiting for the data load in dialog processor mode throughout the data retrieval process.
- Macro created to lock new incoming CVC for a Key figure say for example “Proportional factor” Refer to next section in this document.
- Then Assign the macro to a planning job through activity and run them in background through the Process chain as a step.
Please Refer to Document – http://scn.sap.com/docs/DOC-58121 on “Macro execution in background through process Chain
Overall logic of the macro:
- With the help of control statement (IF) & a Condition statement, macro would first decide if the CVC values are new (Initial) and then would populate those blank cells with 0 value.
- Subsequently lock those cells for the future time horizon defined in the macro.
We need to populate blank new cvc cells with 0, because to lock a cell it should have a non-blank values.
Transaction to create Macro: /SAPAPO/ADVM (Macro work bench) – Chose the planning book and data view, where you would want to create the macro for locking the New CVC.
Final look of the Macro that would lock the new CVC’s for key figure “Proportional Factor” as below:
The above steps/lines are created one after the other by right clicking in the Macro workbench itself.
Section wise – Key pointers for the creation:
Divided the Macro into 3 sections as shown in below image and we would see details and key pointers for each of the 3 sections as shown below
Section 1: Result step (Yellow Box)
Section 2: Condition Statement (Green Box)
Section 3: Locking Step (Red Box)
Section 1: Result Step – Pointers
- The period for which the Macro is applicable is defined here.
- Say manual Prop factor KF % is entered for future periods, hence selected the Future periods in this case.
- But based on need this could be applied to Past periods or user defined time buckets.
- Below image would be the detailed pop-up for this step.
Section 2: Condition Statement – Pointers
- Post introducing the IF statement, one should invoke the Condition statement to determine if the CVC is a new CVC or not.
- Enter the description of the condition step as “New CVC Values Y/N”
- One should use “IS INITIAL (Key Figure)” statement to decide if it’s a new CVC value or not.
**Please note while invoking the IS INITIAL statement remove the closing bracket to insert the key figure one wishes to lock:
- Post adding the key figure that needs to be locked for new cvc values, close the brackets and set the True condition for the statement value = 1
Section 3: Locking Step – Pointers
- This is the section where you mention lock the key figure Proportional factor by assigning value 0 – If they are new CVC values
- Blow up of statement “Row: Proportional factor (From 09/2014)” shown below:
Post creation of this macro, it could be run in background in automated way during non-business hours. This can be achieved by assigning the above macro to an activity, subsequently assign the activity to a planning job and finally add the planning job as a step to a Process chain that brings the CVC on a weekly basis.
To know detailed steps on how to assign and execute a macro through background planning job or through a process chain
Please refer to the document: http://scn.sap.com/docs/DOC-58121 titled “Macro Execution in Background through Process chain”