Automating FP&A Analytics Using SAP Visual Intelligence and Predictive Analysis

September 9–11, 2013

Anaheim, California

Automating FP&A Analytics Using SAP Visual Intelligence and Predictive Analysis

Learning Points



Create management insight tool using SAP Visual Intelligence



Develop visualizations that facilitate decision making and analysis

Agenda



Business Context



FP&A Margin Analytics



Linking Analytics to Value Creation



Developing visualizations for decision making and analysis



Data progression



Waterfall charts



Scatter plots and Bubble Charts



Box and Whiskers Plot & Stacked charts



Management Insight Tool :-Automation Approach



Short term vs. Long Term



Mathematical Forecasting using historical trends



Forecasting using Multiple Linear Regression and ARIMA



Steps in SAP Predictive Analytics – DEMO

Business Context

Management Insight Tool

• Different systems for business consolidation,

planning and forecasting and financial

reporting.

• The Data required for analysis is at a lot of

different places and its highly detailed.

• Desire for more commentary around drivers of

margins and variance with focus on what

numbers mean vs. what they are.

Automation of P&L Forecasting

• Fields forecasting process takes significant time

and effort to update after the financials close.

• Business Units use different standards to

update forecasting in the planning and

forecasting application.

• Provide mathematical baseline to increase

accuracy of the field forecasting function.

Linking analytics to value creation – Key Metrics for Margin Analysis

Value

Creation

Growth

Net Sales

Cases

(volume)

Rate

($/Case)

Margin

Gross Profit

Rate

($/Case)

Cause of

Change

Operating

Leverage

Expenses

from Ops

Corporate

expenses

Developing displays that facilitates decision making and analysis

Seq.

Key Business Questions :

-Margin Analysis

Metrics

Visualizations

1. Are Sales / Volumes / Margins Growing ? Volume, Rate of change.

Data progression and possible patterns, 12 months trend chart

2. What is driving the growth/change in Sales/ Volumes/ Margins compared to Last year, Plan, forecasts?

Change by customers, products

Waterfall Charts , TY vs. LY, TY vs. Plan, TY vs. forecast

3. Are we realizing higher margins compared to Plan, LY and forecasts ?

% change, point change

Vertical bar chart, horizontal bar chart

4. What aspects of our operations are contributing to improving/lowering margins?

Cause of Change Percentage of parts or as ratios to a whole represented using a pie chart. Waterfall Chart, cause of change analysis – cost, price , Volume, mix calculations 5. Are we meeting our customers expectations across

distribution centers ?

Service level %, volume & sales

Box and whiskers Plot

6.

Expenses, TY , LY, Plan, Forecasts

Vertical bar chart, horizontal bar chart, bar chart with two y axis, Stacked Column charts and 12 months trend charts

7. How is our portfolio doing in terms of delivering profitable growth? Are we Growing at the expense of pricing ? Are we growing with the customer we have ?

% Change , point change, ratios

Scatter Plots/ Bubble Charts Operating income Point change vs. Net Sales Growth

Scatter Plots/ Bubble Charts Gross Margin Point change vs. Net Sales Growth Scatter Plots/Bubble Charts New Lost business Ratio vs. growth from

Are sales, margins & volumes growing?

Data Progression, Trends

Are we realizing higher margins compared to

Last year, Plan, forecasts?

What is driving this growth/change in volume, sales,

margins compared to Last year, Plan, forecasts?

Waterfall Charts & CVP Analysis

What aspects of our operations (cost basis, pricing,

volume, mix change etc.) are contributing to

improving/lowering margins?

Scatter Plots and Bubble Charts

Are we growing profitably ?

Are we growing at the expense of pricing ?

Box and Whiskers Plots & Stacked Columns Charts

Are expenses growing ? Are we making progress in

leveraging our scale?

Are we meeting our customers expectations across

distribution centers ?

Management insight Tool : Automation Approach

Short Term solution

:-streamline data gathering to

feed into reporting process

Set up retrieves to streamline data gathering from readily accessible systems.

Identify alternative approaches to access data not in current systems (e.g. emailed

spreadsheets, csv files, freehand SQL)

Create consolidated

database/spreadsheets to drive reporting and visualizations

Longer term solution

:-pull data from transaction

systems and warehouse

Identify alternative approaches to pull data for a more

automated solution

Online Report generation and delivery with click thru

capabilities and collaboration options.

Identify alternative warehousing systems/architecture to support solution development and deployment.

Scope of Today’s

Discussion

Short Term Solution Cont.…

Gain a broader understanding of the various

source system and requirements for data

munging & custom calculations.

All financial reporting systems have an

interface to Excel, to run retrieves.

Use freehand SQL or CSV files for various

database sources that do not support

retrieves.

Financial

Consolidation

Financial

Reporting

Data Mart

Planning &

Forecasting

Others

Source

Data files

_{Consolidated reporting}

backup

Generated and distributed manually

SAP Predictive

Analytics

Spreadsheets organized for reporting

Automatically refreshes when new retrieves are run

Short Term approach detailed

Identify granularity of data

needed for each metric

Investigate sources for each

metric at the desired level of

granularity

Estimate effort needed to

streamline gathering of data

available in readily accessible

systems

Catalogue data not available in

readily accessible systems for

further investigation

Automation of P&L Forecasting



Mathematical Forecasting using historical trends



Forecasting using Multiple Linear Regression and ARIMA

Mathematical Forecasting using Historical trends

Forecasting

Causal

Multiple

Linear

Regression

Time Series

Moving

Average

Exponential

Smoothing

ARIMA

Field Forecast

Planning and

Forecasting

System

Native algorithms and desktop R

algorithms can be used.

P&L Forecasting solution works

with limited data volumes and

hence usage of HANA PAL

algorithms or HANA integration

with R may not be required.

Native algorithms: which logic is

implemented natively in PA's core.

Desktop R algorithms:

implemented with R scripts that

are run against a local (desktop) R

installation

R Desktop

Customization

Native SAP-PA

Algorithm

Desktop R

algorithms

‘math models’ to predict performance

16

Forecast Approach



Goal

•

Create base mathematic model to predict the

future trend of Sales and P&L with minimal

variance



Drivers

•

External variables: Inflation (PPI)

•

Internal variables (input and output):

–

Input drivers: cases sold, margin

compression, expenses per case

–

Output variables: Sales ($), Net COGS ($)

and net Opex ($)



Key Assumptions

•

Exclude impact of Strategic initiatives

•

Trend model based on 5 years of historical data

Cases Sold Inflation (PPI) Inflation Pass Thru Expenses Per Case Net COGS ($) Sales ($) Net OPEX ($)

‘math models’ to predict performance …

Methodology



Time Series (ARIMA)

•

Predict future values of input drivers base on

observed value to describe trend, seasonality

and randomness

–

Volume forecast



Multiple Linear Regression

•

To describe the change of dependent variables

to the change of independent variables

–

(Cases Sold, Inflation Pass-thru, Expenses

per Case

–

i.e. the relationship to sales to cogs,

price to cogs



Input Variables

•

Inflation (PPI index) : USDA Food Inflation

forecast



Net COGS = Volume + PPI Index



Sales = COGS + Impact of margin

compression (inflation pass thru)



Margin = Sales – Net COGS



Expense = Volume + Expense per

case

Steps in SAP Predictive Analytics

Acquire Corporate

data

Perform Time Series

Calculations (ARIMA ,

Exp. Smoothing)

Synthesize and

Recap of SAP Predictive Analysis features

Functionality for drawing

Waterfall charts and box and

whiskers plots is delivered right

out of the box

Leverages investments in SAP

Universes and HANA, Excel

calculations can be pushed

backwards.

Starting SAP Predictive Analysis

1.0.11, it is possible to embed

custom R-Script as new

components.

Pros

_{One cannot create a dashboard that}

would show a trend chart and a

Scatterplot or waterfall chart on the

same page.

End user Interaction with the visuals

and collaboration

It is not possible to save the

visualizations created in “Predict”

panel in the “Visualize” pane.

Next steps for SAP predictive Analytics connected to SAP Backend.

Identify customers

who need sales

force attention

Key Question

Identify customers likely to decrease

spend and prescribe interventions

Surveys

Insights

Transactions

Events

Address early warning signs of

decline and intervene

Salesforce Operational System

• Alert when changes in weekly sales

indicate a statistically unusual

pattern

• Overlay other metrics or aggregate

to higher levels

Key learnings



How to create management insight tools using SAP Visual Intelligence



How to develop visuals that facilitate decision making and analysis

Thank you for participating.

Please provide feedback on this session by

completing a short survey via the event

mobile application.

SESSION CODE: 1105