the cfo’s guide to enterpriseexplore.venasolutions.com/rs/304-ezf-539/images/slide deck -...

® Copyright Vena Solutions Inc.® Copyright Vena Solutions Inc. 1• ® Copyright VenaTM

THE CFO’S GUIDE TO ENTERPRISE

BUDGETING IN EXCEL

Presented by Rishi Grover

SVP Solutions Engineering, Vena Solutions

REVIEW & VALIDATIONS

® Copyright Vena Solutions Inc.

RISHI GROVER

Rishi is responsible for the day-to-day operations and continued

success of client implementations at Vena. He has helped many

Fortune 500 companies re-engineer and optimize financial

processes including:

- Budgeting and Planning

- Rolling Forecasts

- Management Reporting & Analysis

- Strategic Planning and Balance Scorecards

- Regulatory Reporting and Disclosure Management

2

SVP Solutions Engineering


AGENDA

3

• Why do we use Excel for Budgeting?

• Problems with Review & Validation in Excel

• Review & Validation Best Practices in Excel

• Inside Excel

• Beyond Excel

• Internal Processes

• Vena Technology Overview

® Copyright Vena Solutions Inc.® Copyright Vena Solutions Inc.

VENA… THE WAY YOU THINK

4

Created to make businesses work smarter, Vena uses an innovative approach to

simplify complex enterprise processes.

Trust Radius:

Top Rated CPM Firm for

Customer Satisfaction

PwC and CIO Review: Most

Promising, Up-and-coming and

People’s Choice Award

Nucleus Research:

2014-16 Value Matrix

Leadership Quadrant

Gartner:

Market Guide for

Specialty Budgeting,

Planning and

Forecasting

Applications; Notable

Mention in 2016 CPM

Magic Quadrant

E&Y: Nominee for

2014 Entrepreneur of

the Year Award

BPM Partners:

2013-16 Core Vendor

and Top Customer

Satisfaction Rating


WHY DO WE USE EXCEL FOR

BUDGETING?

5


WHY DO WE USE EXCEL FOR BUDGETING?

Pros:

• High user adoption

• Highly configurable

• Flexible

• Strong modeling

• Powerful calculations

• Strong data tables

More Pros:

• Conditional formatting

• Icon sets

• Spark lines/trend icons

• Top X rules

• Advanced charting

• Pivot tables and Power BI

• Sorting & filtering


WHY DO WE USE EXCEL FOR BUDGETING?


PROBLEMS WITH REVIEW & VALIDATION

IN EXCEL

8

G/L

Sub-Ledgers

Other Systems

Cons:

Version control

Data inconsistencies

Manual consolidations

Late submissions

No workflow

Source Data Numerous e-mails and

versions of each

document

Budget

Inputs

Review

and Approval

Manuallyextract data& re-key intospreadsheets

PROBLEMS WITH REVIEW & VALIDATION



REVIEW & VALIDATION BEST PRACTICES

IN EXCEL

10


INSIDE EXCEL

11

Recommendation Description

Data Validations To ensure entries are in the right format, and within an acceptable set of

values/ranges. (Data > Data Validations)

Conditional Formatting To flag any exceptions or missing information.

(Home > Conditional Formatting)

Checklist / Homepage Include a ‘Home’ worksheet that summarizes all validation rule results and

includes a checklist the preparer must complete.

IFERROR() Avoid #REF, #NA calculation results, which can have a ripple effect of

errors.

Workbook Protection /

Range Protection

Password protect the workbook, worksheet and/or ranges as required.

Create ranges for calculations versus input and protect accordingly.

(Review > Protect Sheet, Protect Workbook, Allow Users to Edit Ranges)


INSIDE EXCEL

12


Standardize Worksheets Input Excel templates should have the main worksheets (either input or

summary) that is always standards for each submission.

‘start’ and ‘end’

Worksheets

Create a ‘Consolidating’ workbook that has a ‘start’ and ‘end’ worksheet.

Copy user submitted worksheets between these two worksheets.

=Sum(start:end!G20)

Track Changes Enable Track Changes. This will track who changes what, when.

(Review > Track Changes)


INTERNAL PROCESSES

13


Checklist / Homepage Include a ‘Home’ worksheet that summarizes all validation rule results and

includes a checklist the preparer must complete.

Change Table Create a worksheet called ‘Change Table’. This should be an Excel table

with the fields User, Date/Time, Change Description.

Standardized Naming

Convention

Ensure there is a consistent standard for naming Excel files when being

submitted.

I.e. Dept 101 Budget v2_YYY_MM_DD.xlsx

The final version should always be a certain name as well.

I.e. Dept 101 Budget FINAL.xlsx


BEYOND EXCEL

14

THROUGH DATABASE INTEGRATION AND AUTOMATED DATA COLLECTION


INTERESTING STATISTICS…PWC AND ABERDEEN GROUP

15

“Organizations that automate financial planning create 20% more accurate forecasts.”

“Organizations with automated processes are 62% more likely to have a centralized

repository of performance data.”

“Those with CPM/EPM create budgets that are 36% more accurate."

“Both IT and the line of business are able to update processes 42% more quickly in

organizations with cloud BPM.”

“Financial data contains 20% fewer errors for companies with a centralized, automated

financial close process.”


WHAT ARE OUR OPTIONS?

16

Pros:

• Flexible

• Strong modeling


• Rich presentation


Cons:

• Data integrity issues

• No source system integration

• Manual consolidations

• Lack of audit-ability

• No process management

Spreadsheet Environment

Pros:

• Controlled environment

• High data integrity

• Database centric

• Workflow management

• Auditable

Cons:

• Takes you away from spreadsheets

• Rigid, lack of flexibility

• IT/consulting intensive

• High cost of ownership

Budgeting

Applications

Financial Close

Applications

Reporting

Applications

Consolidation

Applications

“Excel-like Look &

Feel”

“Excel-like

Functionality”

Flexible

“Replace Excel” Application Environment

Controlled


WHAT ARE OUR OPTIONS?

17

Pros:

• Flexible

• Strong modeling


• Rich presentation


Cons:

• Data integrity issues

• No source system integration

• Manual consolidations

• Lack of audit-ability

• No process management

Spreadsheet Environment

Pros:

• Controlled environment

• High data integrity

• Database centric

• Workflow management

• Auditable

Cons:

• Takes you away from spreadsheets

• Rigid, lack of flexibility

• IT/consulting intensive

• High cost of ownership

Budgeting

Applications

Financial Close

Applications

Reporting

Applications

Consolidation

Applications

“Excel-like Look &

Feel”

“Excel-like

Functionality”

Flexible

“Replace Excel” Application Environment

Controlled

81% of companies that have a performance

management solution…supplement it with

spreadsheets.

Pulse Survey


VENA TECHNOLOGY OVERVIEW

18

® Copyright Vena Solutions Inc.® Copyright Vena Solutions Inc. 19

VENA… THE BEST OF BOTH WORLDS


RELATIONAL

• ETL Staging Environment

• Actual TransactionsTemplate & Report Design Optimize your templates

Dynamically map to database

Template-ize

Add application functionality Spreading, line-item details

Business rules

Drill to transactions

Process Designer Intuitive drag-and-drop designer

Role-based data security

Task Management Single point of template access

Status tracking dashboard

User collaboration

ETL

Source Systems .csv, GL, ERP, HR,

CRM, Fixed Assets,

Sales, Consol, etc.

• Dimensions & hierarchies

• Real-time aggregations

• Transactions / line-items

HOW VENA WORKS

Feature Benefit

Process Automation & Rollover Automate user assignments and workflow

Integration to source data No need to re-key data

Feature Benefit

Integration to source data Latest, accurate data

Business Rules Numbers always tie-out

Automated Aggregations System generated results

Data Integrity

Increased confidence in the results Start reviewing the data sooner

Feature Benefit

Security to view or edit data Users only see the data they are supposed to see

Workflow controls access throughout the process

Users only see data when they are allowed to see it

Access to information is controlled

Feature Benefit

Compliance Checklists To ensure that the right steps are being followed

Audit Trail To track who did what, and when they did it

Compliance information always available

Clarity FSR Reduces the Risks

Accelerated Cycle

Security & Collaboration Control & Auditing

IMPROVE THE VALIDATION & REVIEW OF YOUR DATA



COMMUNICATIONDATA COLLECTION

& VALIDATIONCONSOLIDATION

REVIEW &

APPROVAL

REPORTING &

ANALYSIS

Long Term Planning

Budgeting

Forecasting

Management Reporting

Regulatory Reporting

Disclosure Note Collection

Account Reconciliation

Financial Consolidation

Tax Provisioning

Balance Sheet Flux

Regulatory Reporting

External Reporting

Any Excel-intensive process

BU

SIN

ES

S P

RO

CE

SS

ES

Repeated &

interrelated

processes

OPERATIONAL TASKS


THANK YOU!

23

the cfo’s guide to enterpriseexplore.venasolutions.com/rs/304-ezf-539/images/slide deck -...

Documents