Day 2: - Optimizing Data for EPM

Lecture at SP Jain Institute of Management

Executive MBA, Singapore – Oct 2009

Ravi Tirumalai

Oracle Corporation,, Asia Pacific

Agenda

• Understanding the EPM Data challenges

• Master key data management process

• Products and solutions:

– EPM

– Scorecards

– Dashboards & KPI’s

Enterprise Performance Management System

EPM Workspace

OLTP & ODSSystems

Oracle Data WarehouseData Mart

SAP, Oracle, Siebel,PeopleSoft, Custom

ExcelXML

BusinessProcess

OLAP

Middleware

Business Intelligence Foundation

BI ApplicationsPerformance Management

Applications

4

Performance Management Applications

Master Data

Business Rules

Metrics/KPIs

Targets, Plans & Actuals

Strategic Planning

Cost and Profitability Management

Define Costing

Methods

Perform

Allocations

Report & Analyze Define Cost Drivers

Financial Close Process

Data

CleansingConsolidate

Internal

Reporting &

Analysis

External

Reporting &

Compliance

Cascade Targets

Allocate Resources

Forecasting Detailed Budgeting

Planning and Budgeting

Set Strategic

Objectives

Corporate

Development

Treasury

Strategies

Long-Term

Planning

Transparency Challenges

Financial Consolidation At-a-Glance

Collect Data Calculate & Adjust Report

• Gather from multiple & disparate applications

• Store data in a centralized repository

• Generate financial statements, managerial reports, and perform ad hoc analysis

“Consolidations applications are seeing a resurgence after

what was considered a maturing of this market, driven

primarily by compliance issues in the United States (driven

by Sarbanes-Oxley) and worldwide (driven by the pending International Financial

Reporting Standards).” -- Kathleen Wilhide and Henry D. Morris

• Apply FASB and IFRS consolidation rules

• Perform currency translation & aggregation

7

The Financial Consolidation ChallengeDeliver High Value and Reduced Cycle Times

Source: Hackett Group Benchmarking-Solutions Book of Numbers

Collect data Close AdjustmentsCurrency

I/C

Subsidiaries

ReportManagement

Legal

External

Analyze

6 Days 12 DaysAverage

Collect Close Adjustment Report Analyze and Forecast

<2 Days 6 Days

World Class

More time!

Operational Challenges

Lengthy close process with weak internal controls

Rigid systems that cannot exploit new opportunities

Disconnect between strategy and execution

Parent Company

$

Subsidiary 1

¥

Subsidiary 2

£

Joint Venture

Affiliate

Accounting Differences

• Charts of accounts

• Accounting methods

Manual, Error-Prone Processes

• Data extraction, transformation, and load

• Adjustments and eliminations

Rs

€

Consolidation

Complex Ownership Structures

• Minority interest

• Cross holding

Multiple, Fragmented Systems

• Currencies

• Calendars

• Intercompany

transactions

Accounting scandals have

brought financial reporting

into focus as never before

Financial Close and Reporting Today

• Lengthy and cumbersome closes

• Inefficient and disconnected

processes

• Difficulty with reporting and analysis

• Data quality issues

• Inability to keep pace with change

• No control over the process

• Too many spreadsheets

…AUDIT ISSUES

Kenneth Lay

…a controlled process is not easy to achieve!

10

Performance Management Applications

Master Data

Business Rules

Metrics/KPIs

Targets, Plans & Actuals

Strategic Planning

Cost and Profitability Management

Define Costing

Methods

Perform

Allocations

Report & Analyze Define Cost Drivers

Financial Close Process

Data

CleansingConsolidate

Internal

Reporting &

Analysis

External

Reporting &

Compliance

Cascade Targets

Allocate Resources

Forecasting Detailed Budgeting

Planning and Budgeting

Set Strategic

Objectives

Corporate

Development

Treasury

Strategies

Long-Term

Planning

Fundamental Capabilities

Capability Detail

Planning and

Analysis

Fully embedded into BI and Microsoft Office

for real-time reporting and analysis

ModelingThe ability to model at the operational level,

assessing impact to the overall strategic plan

What-ifSee the business impact of changing business drivers

to make intelligent decisions

CollaborationTechnology that allows the entire user community

to communicate and plan in real time as well as offline

Top-down / Bottom-upBi-directional planning capabilities

to promote accuracy and accountability

Challenges - Excel/Manual Based Processes

– Lack of process

– Lack of integration

– Lack of confidence

– Rigid, costly to

maintain

– Inability to act

12

15

Today’s Integrated Planning Process

Sweat and Excel

ERP System

Functional Operational Planning

DemandManagement

CustomerManagement

Human Resources Planning

SupplyPlanning

ProductPerformanceManagement

LogisticsPlanning

Financial Planning

Long-TermFinancial Planning

TreasuryManagement

Financial Planning & Budgeting

WorkingCapitalPlanning

ProfitabilityManagement

PerformanceScorecards

17

Integrated Business PlanningBridging the Finance-Operations Disconnect

ERP System

Functional Operational Planning

DemandManagement

CustomerManagement

Human Resources Planning

SupplyPlanning

ProductPerformanceManagement

LogisticsPlanning

Financial Planning

Long-TermFinancial Planning

TreasuryManagement

Financial Planning & Budgeting

WorkingCapitalPlanning

ProfitabilityManagement

PerformanceScorecards

IntegratedImpactAnalysis

FinancialPerformanceModeling

StrategicOperationalModeling

Integrated Business Planning

Strategic Operational Modeling and Planning

Functional Operational Planning

ERP

Financial Modeling and Planning

GL AccountsAccounting PeriodsEntitiesActual

Budget Data

Profit targets, prices, costs,investment limits, working capital

Constrained Op Plans

Master DataTransactional Data

Execution Plan

Baseline Op Plans Operating Policies

Differences between Strategic Operational

Planning and Financial Planning

1. “Network” models

2. Planning Frequency

3. Concept of “Lead Time”

4. Data Driven Model Relationships

5. Constraint Based

20

Integrated Planning Drives Key RequirementsNeed for Core Technology Innovation

1. Modeling of operational constructs

– Rich expressiveness in modeling & calculation logic

– Complex dependencies, relationships, transformations

– Declarative calculations, not scripted

1

2

3

1

2

3

2. Rapid analysis of cross-functional impact

• “Change anywhere, analyze everywhere”

• Evaluate financial desirability & operational feasibility

• Interactive response times

3. Robust analytic transaction framework

• Scenario management & change isolation

• Reconciliation of concurrent plan changes

• System of record for plan changes: audit trails

Budgeting Process is Highly Inefficient

• Time – takes too long, results

obsolete

• Quality – political/negotiation, not

reflective of business

• Cost – many wasted resources,

dubious benefits

• Flexibility – lack of

responsiveness to changesFP&A Group

Senior Management

Dept A Dept B Dept C

Streamline Planning, Budgeting and Forecasting

• Centralized, not distributed

• Short budget cycles

• Collaboration across organization

• Consistent assumptions and

calculations

• Accurate data and plans

• Accountability

• Align top-down with bottom-up

• Driver-based planning

• Rolling forecasting

Reduce Fragmentation in Financial Modeling

• Complex spreadsheet models

• No consolidated view

• Spreadsheets are error prone

• Limited accounting integrity

• Lack of integrated models

make scenario analysis

cumbersome

• Long simulation times

Treasury

Board of DirectorsSenior Management

Banks Investors AcquisitionsDivestitures

Long-Term PlanningCorporate Development

24

Review Plan Exceptions Evaluate Alternatives

Assess Business ImpactUpdate Plan of Record

Marketing Sales

Finance

Supply Chain

• Create what-if scenario’s on plan revisions

• Rough-cut modeling

• Resolve constraints

Enabling an Iterative Planning Process

Manage collaboration

Assess cross-functional impact

Enable fast decision-making

Perform interactive scenario analysis

• Approve plan revisions

• Submit plan changes back to planning and execution systems

• Analyze critical gaps between financial and operational plans

• Compare impact of alternatives on key financial and operational metrics

25

Performance Management Applications

Master Data

Business Rules

Metrics/KPIs

Targets, Plans & Actuals

Strategic Planning

Cost and Profitability Management

Define Costing

Methods

Perform

Allocations

Report & Analyze Define Cost Drivers

Financial Close Process

Data

CleansingConsolidate

Internal

Reporting &

Analysis

External

Reporting &

Compliance

Cascade Targets

Allocate Resources

Forecasting Detailed Budgeting

Planning and Budgeting

Set Strategic

Objectives

Corporate

Development

Treasury

Strategies

Long-Term

Planning

26

Integrated Corporate Finance Modeling Allows You

to Create a Complete Plan

Covenant/Ratio Analysis

Funding Alternatives

Rating Agencies

Mergers & Acquisitions

Valuation Analysis

Divestitures

Top-Down Target Setting

Contingency Planning

Strategy Screening

Strategic Planning

Value

Management

Corporate

Development

Treasury

Strategies

Long-Term

Planning

27

Challenge

Financial Modeling

Limited cost-benefit analysis

Missed market opportunities

All scenarios not considered

Not enough focus on cash flow

Scenario ModelingExplore Options for Organizational Restructuring

• Copy and modify

existing hierarchies to

easily model options

– Move

– Add

– Remove

• Run consolidation on

modified hierarchy to

see potential outcome

– Balance Sheet

– Income Statement

– Statement of Cash

Flows

– Managerial Reports

Original State

Scenario 3

Scenario 2

Scenario 1

29

Risk Analysis

• Only 3 possible outcomes

• Limited view of risk

• What are most important risk factors?

• What are the odds I’ll miss the target?

• Which outcome is most likely?

• Monte Carlo simulation shows full range of

outcomes

• Illustrates probability of outcomes

• Immediate visibility into inherent risk

• True risk analysis for financial models

PossibilitiesThinking in Single Point Scenarios

ProbabilitiesThinking in Ranges

3 possibilities… No probabilities… All possibilities… Clear probability…

30

Predictive Modeling – Drivers Impact

Integrate Strategic Planning Budgeting & Forecasting

3 – 5 Years1 YearMonths / Quarters

Budget

Strategic Plan

Forecast

Rolling Forecast

YTD

Actuals

Seed targets and assumptions

Seed budget

Update strategic plan

with latest forecast

Cascade BU Targets

Prepare detailed financial plan

Consolidate budgets

Budget Reviews

Long-term planning

Corporate development

Optimize capital structure

Shareholder value

Capture forecast assumptions

Performance reviews

Driver-based planning

Key performance indicators

Profitability Management Questions

• What defines a profitable

customer?

• What is the profit contribution

margin of a product or a service?

• What does it cost to sell or

service customers?

• What proportion of resources

does a customer consume? Why hasn’t reducing costs increased profitability?

32

Enterprise Performance Management System

EPM Workspace

OLTP & ODSSystems

Oracle Data WarehouseData Mart

SAP, Oracle, Siebel,PeopleSoft, Custom

ExcelXML

BusinessProcess

OLAP

Middleware

Business Intelligence Foundation

BI ApplicationsPerformance Management

Applications

Across the Enterprise and Beyond

Corp

Division Division

CustomerSupplier

Partner

Scorecard

Dashboards

EUQR

Data Foundation

Business Intelligence

36

Operational Challenges

• Delayed, inaccurate reporting• Conflicting, departmentally-biased results

SalesData

Marketing Operations FinanceSales

Analyses, ReportsExecutives

IT

Cross-functional analysis only by ITSub-optimal enterprise performance

OperationsData 1

FinanceData N

FinanceData 1

OperationsData N

DataWarehouse

MarketingData

37

Valuable Insights Often Require Data from Multiple

Departments and Sources

How do I proactively manage risks of my receivables portfolio?

How does supplier performance impact customer satisfaction and revenue?

Serv

ice

Customers

Sale

s

Mar

keti

ng

Dis

trib

uti

on

Fin

ance

HR

/Wo

rkfo

rce

Op

erat

ion

s

Pro

cure

men

t

Customers

Customers

Suppliers

Suppliers

Suppliers

How does call center agent tenure, training, & compensation affect efficiency and cross-selling performance?

DASHBOARDS

“Visible”

“PLUMBING”

“Under the Surface”

Mappings to multiple applications

and data transformation all take

expertise and time

Upgrades

The Analytic Application Problem is Below the Surface

Information Delivery

Everything Else

What’s “Above the Surface”?

Reports & DashboardsMetrics and Attributes

So what’s Below the Surface?

Work Flows

& Execution

Plans

Extracts

&

Change

Data

Capture Account ID

Staging

Area

Transform

&

Loads

∑

Unified

Dimensions

Fact Tables &

Aggregates

Metadata

Ph

ysic

al

Lo

gic

al

Pre

se

nta

tion

What Percentage of Each Layer is Configured?

20% + 10% + 10% + 10% + 20% + 20% 40% 50%

Work Flows

& Execution

Plans

Extracts

&

Change

Data

Capture Account ID

Staging

Area

Transform

&

Loads

∑

Unified

Dimensions

Fact Tables &

Aggregates

Metadata

Ph

ysic

al

Lo

gic

al

Pre

se

nta

tion

Data Quality Control Points

WorkspaceData Warehouse BI Foundation

(BI Server)

Data Sources

BI Approach :From Warehouse to Workspace

• User Roles, Preferences

• Simplified View

• Logical SQL Interface

PRESENTATION LAYER

• Dimensions

• Hierarchies

• Measures

• Calculations

• Aggregation Rules

• Time Series

SEMANTIC OBJECT LAYER

PHYSICAL LAYER

• Map Physical Data

• Connections

• Schema

Manage

•Interactive Dashboards

•Ad Hoc Analysis

•Reporting & Publishing

•Disconnected & Mobile

Analytics

•Proactive Detection &

Alerts

•MS Office Integration

Consum

e

Acquire

•Single Database

•Multiple schemas,

logical separation

•Staging Data Layer

(SDL)

•Foundation Data

Layer (FDL)

•Access and

Performance Layer

(APL)

Security

Copyright © 2008, Oracle and / or its affiliates. All rights reserved. - Internal Use Only

Role Based Dashboards

Analytic Workflow

Guided Navigation

Security / Visibility

Alerts & Proactive Delivery

Logical to Physical Abstraction Layer

Calculations and Metrics Definition

Visibility & Personalization

Dynamic SQL Generation

Highly Parallel

Multistage and Customizable

Deployment Modularity

Abstracted Data Model

Conformed Dimensions

Heterogeneous Database support

Database specific indexing

BI Applications ArchitectureA

dm

inis

tra

tio

n

Me

tad

ata

BI

Presentation

Services

Dashboards by Role

Reports, Analysis / Analytic

Workflows

Metrics / KPIs

Logical Model / Subject Areas

Physical Map

BI Server

Direct

Access to

Source

Data

Data Warehouse /Data Model

ETL

Load Process

Staging Area

Extraction Process

DA

C

Federated Data Sources

SiebelOracle SAP R/3 PSFT EDW

Other

48

Dashboards and Scorecards Differing Perspectives

Dashboards

• Business results• Operational and strategic• Root cause analysis• Guided navigation

Past

Dashboards: Why did I miss

my goal?

Scorecards

• Strategy deployment • People & team scoring• Accountability maps• Management processes• Initiative tracking• Cause & effect• Collaboration

Future

Scorecards: How does my goal

support the corporate strategy?

Alerting

Benchmarking

Metrics

Graphics

Reporting

Metadata

Snapshot of Dashboard

50

Snapshot of Dashboard

Snapshot of Scorecard

Snapshot of Scorecard

These steps require significant resources with specialized skills / expertise

These steps

require different

types of BI, ETL

and DB

technology

Process & Steps to build a BI Application1. Research business requirements for processes & users

2. Design the underlying logical model – KPIs, Dimensions etc

3. Architect physical data model – Fact tables, Aggregates etc

4. Buy and configure a database management system to store

information

5. Buy an ETL tool to extract, transform and load data from the

transactional tables

6. Develop ETL programs based on an intimate understanding

of transactional data structures

7. Buy a BI tool for interactive end user access – Ad-Hoc,

Dashboards, Alerts, Offline, MS Office etc.

8. Develop Dashboards & KPI, Hierarchies and prompts

9. Integrate Security: authorization, authentication and auditing

10. Integrate user experience with transactional applications

11. Perform rigorous performance testing and QA

12. Manage changes to BI Apps – because of upgrades and

updates to the transactional systems

These steps

typically take a

long time to

perfect as

knowledge of

best practices is

learned

Receivables

AR Balance

• DSO

• Closing Group Amt

• Credit Limit Used %

• Total AR Overdue Amt

AR Aging

• AR Aging 1-30 Amt

• AR Due 1-30 Amt

• AR Overdue 1-30 Amt

Payment Performance

• AR Payment Days

• AR Weighted Days

• Times Paid Before Due

AR Transactions

• AR Avg Invoice Amt

• AR Credit Memo Amt

Payables

AP Balance

• DPO

• Closing Group Amt

• Total AP Overdue Amt

• Overdue Amt to Total %

AP Aging

• AP Aging 1-30 Amt

• AP Due 1-30 Amt

• AP Overdue 1-30 Amt

Payment Performance

• AP Payment Days

• AP Weighted Days

• Times Paid Before Due

AP Transactions

• AP Avg Invoice Amt

• AP Avg Payment Amt

Profitability

Profitability Returns

• Return on Equity

• Return on Assets

• Return on Capital

Margins

• Gross Margin %

• Operating Margin %

• EBT Margin %

• Net Income Margin %

Product Profitability

• Revenue

• Product Gross Profit

• Product Operating Profit

Customer Profitability

• Revenue

• Customer Gross Margin

Example Financial Analytics Metrics

Sample Pre-Built Dashboards

Financial Controller• Balance Sheet

• Cash Flow

• Budget Vs Actual

• P&L

Department Manager• Budget Vs Actual

• P&L

• Product Profitability

• Customer Profitability

Payables Manager• AP Balance

• Payments Due

• Effectiveness

• Invoice Details

Receivables Manager• AR Balance

• Payments Due

• Effectiveness

• Invoice Details

General Ledger

Balance Sheet

• Cash

• Accounts Receivable

• Debt to Equity Ratio

• Current Ratio

Asset Turnover

• AR Turnover

• AP Turnover

• Inventory Turnover

• Cash Cycle

• Fixed Assets Turnover

Cash Flow

• Operating Cash Flow

• Investing Cash Flow

• Financing Cash Flow

• Net Cash Flow

Built-in Best Practices - Metrics

Ensuring Alignment to Corporate Objectives

Objectives

Critical

Success

Factors

Critical

Success

Factors

Critical

Success

Factors

Actions Actions Actions

Headquarter

Subsidiary 1 Subsidiary 2 Subsidiary 3

56

Aligning to Corporate Strategy

• Accountability Maps

• Strategy Maps

• Cause and Effect

Maps

Building a Scorecard

Financial

Customer

Internal

Learning & Growth

ConsolidatedActuals

BudgetGoals

StrategicLong Term Goals

ERP, CRM, HR, Other (Oracle, SAP…)

Actuals Target YEAR 1 Target YEAR 5

58

E-MAIL ALERT

Send e-mail alert

when a problem is

detected

Analyze

Fix

How the Scorecard System Works

SCORECARD

STRATEGY ANALYSIS

The Business Intelligence Continuum

Which customers spend the most?

What did this customer buy?Which customers are most profitable?

What if demand falls short of forecast?

What if we rolled out this product

nationwide?

Will our cash balances take us

through this crisis?

What is the Q4 revenue forecast?

Future OrientedPast Oriented

Operational Static

Strategic Dynamic

Solve a Continuum of BI Needs

Ad-Hoc Query

& Reporting

Standardized

Reporting

Advanced

Analytics

Modeling

Future Oriented

Operational Static

Strategic Dynamic

Past Oriented

A Fusion of BI & Business Processes

• Visibility & Business Insight

– Understand Business Impact

• Action From Insight

– Empower Business Users

• Insight Driven Business Processes

– Business Intelligence Services for integrated insight and reporting

Business Process

BI & BAM

Business Process

BI

Business Process BI

Driving action from insight – Empowering Decisions

Business Process

Business Intelligence

Dashboards, Alerts, Reports

Enhance Customer

Satisfaction

Drive sales & marketing

promotions

Initiate employee

education initiatives

Remove production

bottlenecks

Escalate and rectify

approvals issues

Technology Value Chain

CollectStruct

ureStore

Synthesis

Use

Where Value is Created

External

Internal

By Decision

By Responsibility

Optimized

for Speed

Filter

Aggregate

Relate

Extract

Digets

Assess

Decide

Act

InsightPerformanceAction

SetGoals

Plan

Monitor

Analyze

Report

Align

• Develop strategies and goals

• Define key initiatives and KPIs

• Model scenarios

• Allocate strategic targets

• Financial budgeting

• Operational planning

• Rolling forecasts

• Financial and operational

• Revenue, profits, KPIs

• Efficiency and Utilization

• Benchmarking and metrics• Variances to budget

• Key trends across LOBs

• Profitability

• Effectiveness

• Financial & Statutory

• Management Reporting

• Compliance

• SDR – GRI Metrics

• Revisit Goals

• Update Models

• Update Plans

• Reallocate Resources

In Summary : Alignment and Accountability are

Key to Driving Enterprise Performance…

Applying Technology to Performance

Management- Fourth time lucky

1960 1970 1980 1990 2000 2010

DATA

INFORMATION

NETWORK

CONVERGENCE

CONVERGENCE

PAYOFF

People

Computing

Information

Communication

Back Up Slides

Performance Management - Best Practices

• Strategic Planning

– Definition of goal

• Tactical Planning

– Tactics, initiatives, resource allocation

• Financial Planning

– Preparation/consolidation of plan

• Management Reporting

• Forecasting

• Business Risk Management

Why Planning Fails

• Planning and reporting the wrong stuff

• Poor ownership and accountability

• Tying plan achievement to compensation

• Incomplete strategy definition

• Inadequate risk recognition

• Poor communication

• Weak Integration (connecting the dots)

• Information overload

• Mistaking detail for accuracy

Best Practice for leveraging Technology

• Integrate business and technology planning

• Break down the functional walls

• Set the right priorities

• Avoid automating inefficiencies

• Implementation is a team effort

• Focus on use, not deployment

• Manage complexity

• Link ROI to business value

75

Common Enterprise Information ModelSingle Consistent View and User Self-Sufficiency

User Roles, PreferencesSimplified ViewLogical SQL Interface

PRESENTATION LAYER

DimensionsHierarchiesMeasuresCalculationsAggregation RulesTime Series

SEMANTIC OBJECT LAYER

PHYSICAL LAYER

Map Physical DataConnectionsSchema

Role-Based Views of the Information Relevant to the User

Consistent Definition of Business Measures, Metrics, Calculations

Model Once, Deploy Everywhere

Across Any Data Sources

Identify the

Initial Data

Source

Identify The

Dimensions

To be Debited

Identify the

Dimensions

To be

Credited

Identify the

varying

Operation to be

performed

Create or Select a

Condition

Create or Select

Components

Simple Source On Mapping Rule

77

Ensuring Financial Data Quality

Business

Processes

Stewardship

Driver

Financial Data

Audit Trail

Trial Balance to Report

Data Reconciliation

Data Accuracy

Data Completeness

Internal Audit Controls

COSO Repository

Business Analysts

Regulatory Compliance

We mean

An Integrated approach to BI & DW

Consume

Acquire

Manage

Insight as a Service

Aligning People,

Processes & Technology

Security, Scalability,

& Performance

Multi-channel

Open API’s

User self-service

Federated query

Data Integration, Business

Processes (BPEL, BAM), MDM,

Content Management

Embedded OLAP, Data Mining RAC,

Clustering

Elements of a BI Initiative Success Factors

Pre-Built, Pre-Mapped, Pre-Packaged Insights

Pre-built warehouse with more than 16

star-schemas designed for analysis and reporting

on Financial Analytics

Pre-mapped metadata, including embedded best

practice calculations and metrics for Financial,

Executives & other Business Users.

1

2

3

4Pre-built ETL to extract data from hundreds of

operational tables and load it into the DW, sourced

from Oracle EBS, PeopleSoft Enterprise, SAP R/3,

and other sources.

A “best practice” library of over 360

pre-built metrics, Intelligent Dashboards, 200+

Reports and alerts for CFO, Finance Controller,

Financial Analyst, AR/AP Managers and Executives

Presentation Layer

Logical Business

Model

Physical Sources

The New BI approach

Workspace

BI Foundation

Infrastructure

Performance

Management

Apps.

BI Apps

Consume

Acquire Manage

visualization Dashboards

ScorecardsAnalytics