This video shows how you, as a planner, might use the SAP Analytics Cloud
add-in for Microsoft Office to enter data and perform planning activities while connected
in real time with SAP Analytics Cloud.
You'll start operational planning in Excel for Sales Organizations by taking a deeper
look at bicycle production and expected Revenue for Q1 2022.
First, you'll get bike prices from an external system and feed them into SAP Analytics Cloud
by using a VBA macro to copy and paste the data in the Add-in grid.
The cells are now light blue because you’re in what is known as “Mass data entry mode.”
This means that data will not be sent to the SAP Analytics Cloud backend and calculations
will not be performed until you specify by processing the data.
The purpose here is to improve system performance if there’s a large amount of data to enter.
You can use the ribbon menu or Keyboard shortcuts to Process.
In the next steps of the process, you’ll use the shortcuts.
On the left is pure Excel Calculation and on the right, the Add-in grid.
After processing the data, you can see the changed cells highlighted in yellow with the updated prices.
Next, you want to use this information to calculate a target margin for Q1 2022 of 21%.
You’ll use a “what-if” Excel feature called “Goal seek” to enter the target
margin and return the target Volume of the product.
To do this, you’ll go to Open Data > What If Analysis > Goal Seek, enter a target margin
for March 2022 of 21%,
select “Volume” as parameter to be adjusted by the calculation to reach the Target Margin, and click OK.
The result is retrieved in Excel.
It’s also visible on the SAP Analytics Cloud Add- in grid as those cells are referenced
by a formula.
The Volume Data is entered in mass data entry mode, and then processed.
You now need to check if you can produce such a Volume based on supply chain constraints
on raw material.
To do so, you’ll use another advanced Excel capability called “Solver” on the SAP
analytics Data.
This enables you to perform What if analysis with multiple constraints applied.
Again, you need to check if your planned scenario is realistic, based on the capacity constraints
for production and requirements for the raw materials – steel and iron – to produce
the bikes.
You enter the constraints in the Solver tool and get a result of “Ok” – you could
produce even more bikes than planned.
Since the scenario is OK, the Planned gross revenue can be used to feed the Profit and
Loss Planning, which is stored in a different model in SAP Analytics Cloud.
To do this, you point to a Report showing overall Data, and check the Aggregated planned
Gross revenue that will be fed back to the model to ensure the data granularity matches.
You then create a formula in the Financial Model grid, referencing the data from the
Operational Planning Model on the more aggregated level that the P&L presentation requires.
You can see that the cell is now light blue color, as the data is being added in Mass
Data Entry Mode.
When you “process” the data, you can also see the impact on Total for Q1 2022.
Now you need to complete Operational planning for March 2022 to enter planned Volume.
Because you don’t have any detailed information for the remaining regions, you’ll use local
aggregated data.
You can use your favorite template -- the pixel perfect native Excel Report template
-- to complete the data entry.
You’ll enter values in the top table, which is a native Excel report, and the new entries
will feed SAP Analytics Cloud, as the bottom table is from the Add-in and contains formulas
referencing the top table.
Now the data is in mass data entry mode, you can process it to see all the SAP Analytics
Cloud back-end calculations applied.
Success!
The planning process is complete; data can be now made public from Excel to SAP Analytics
Cloud in real time.
To find out more about how you can use the SAP Analytics Cloud add-in for Microsoft Office
to support your planning processes, try it for yourself today!