P108: Leveraging Your Spreadsheets for Business Analytics

1. P108: Leveraging Your Spreadsheets for Business Analytics

2. Challenges Facing Business Leaders Wanted: Better Insight • Unable to readily view and use their information • Over-reliance on spreadsheets • Need solutions to drive competitive advantage • Need better integration of Business Intelligence and Performance Management

3. Spreadsheets Failing “there have been well-documented cases of errors and losses resulting from spreadsheet manipulation errors and accidents.”Source: Gartner, August 2007 Disparate Systems “Average finance organizations operate 11 finance systems per billion dollars of revenue.” Source: Hackett Group, 2007 Business Risks “62 percent of enterprises … encountered a major risk event … 42 percent of these enterprises were not well prepared for the event.”Source: IBM Global CFO Study 2008 Some Common Risks and Challenges CFO Tax /Treasury Controller FP & A IT

4. “….it has lots of limitations…..but it does lend itself to the solution of a broad class of problems where interactive operation and lots of flexibility are desired.” What Product is this Quote About ?

5. Visicalc – the first commercial spreadsheet Advertisement in BYTE Magazine Circa 1979

6. But not the first “spread sheet”!

7. Why Are Spreadsheets So Popular? • Comfort Factors: • Pervasive, Powerful, Personal, Familiar, Flexible • A numerical “Swiss Army Knife” for many companies: • Data Collection, Business Modeling, Analysis, Reporting • But… Spreadsheets are a Breeding Ground for Risk! Inaccurate:Almost certain to contain unintended errors Untrustworthy:No confidence in source and accuracy Insecure:Security is monolithic, no audit trail Fragile:Lack of collaboration and repeatability for continuous processes (e.g. budgeting and planning) Cumbersome: No process automation, long cycles, unintuitive analytics, manual hierarchies

8. Calculation errors are guaranteed Two dimensions cannot encompass business data “Copy and Paste” is “Hit or Miss” • Research shows 94% of spreadsheets contain unintended errors • Most FPM processes require a minimum of four, often many more • Asymmetrical ranges, accidental overwriting, missing rows/columns Single accurate business model Multi-dimensional cubes with meaningful identifiers Immutable business rules that apply to the entire cube • Centrally managed server for data and business rules • NOT “$A$1 * “\\BudFiles\2007\May\Budget.xls!C3” • Margin = Revenue – Cost, always. Transforming Your Excel-Based Process:From Fragile to Agile

9. Introducing multiple workbooks adds fuel to the fire Excel security is monolithic, auditing is weak Presentation = Storage • Slow recalculations, broken links, more errors • Locks apply to entire workbook, auditing is “bolted-on” • Format of workbooks is fixed; no analysis capability Shared in-memory business model Centrally-managed role-based security Presentation <> Storage • “Instant” propagation of changes to all users • Selective access at any level. Auditing is tamper-proof. • Pivot/slice/dice to serve all users needs, in Excel and/or Web Transforming Your Excel-Based ProcessFrom Fragile to Agile

10. WARNING! WHAT YOU ARE ABOUT TO SEE MAY SHOCK YOU. THE FOLLOWING SCREENSHOTS ARE FROM REAL APPLICATIONS BUILT ENTIRELY WITH COGNOS TM1 AND MICROSOFT EXCEL. NO DATA WAS HARMED IN THE MAKING OF THESE SLIDES.

11. Budgeting and Forecasting

12. Salary Planning

13. Integrated Workflow

14. Financial Reporting Aqua, Inc

15. Dashboards

16. Scorecards

17. Analytics

18. What’s the magic here? Cell H18 contains a “DBRW” formula, a TM1 function that retrieves or writes back a value to a TM1 cube on the server TM1 data values can be manipulated exactly like Excel values, enabling users to extend the application on their own with Excel functions and graphing features No data is stored in Excel. It is only used to present the TM1 data, meaning the same TM1 cube data can be presented in an entirely different way for other users or applications.

19. The Transformation is Complete! Cognos TM1 enables the transformation from Errors Untrustworthy data Unsecure Cumbersome Fragility Accuracy Trusted information Auditable Speed Agility

20. The Technical Transformation

21. Five Common Mistakes • Focusing on Point Solutions • Select a flexible, versatile product like Cognos TM1 that will grow with your needs • Paving The Cow Paths • Take the opportunity to challenge old practices and eliminate redundant/obsolete business processes • Relying Too Much On IT • Spreadsheets = User-driven & User-accountable • Replacing Ultimate Flexibility with Complete Rigidity • Purpose-built applications have a purpose, but… • Don’t Take Excel Away From the Users – Leverage It!

22. Proven Practice: Emulate existing Excel templates • Retain the look & feel of your existing Excel templates to • Lower training cost • Increase user acceptance • Reduce deployment risk • Deploy Excel applications with Cognos TM1 Web • “Tamper-proof” interface for casual users (e.g. annual budgeting cycle) • Simple, non-technical publishing process, owned and operated by business users not IT

23. Proven Practice: Centralized Management • Cognos TM1 centralizes and manages fact-based assumptions, drivers and business rules • Eliminates or greatly reduces “back of the envelope” calculations • Enables immediate distribution of updates (hierarchy changes, model drivers) • Enables management of parallel scenarios, each with its own set of assumptions • Cognos TM1 + Excel removes labor and latency from your business processes • Easier to deploy and manage for process owners • Easier to contribute and collaborate for end-users

24. Proven Practice: Provide users with multiple “lenses” • Personalized Dashboards for Performance Management • Unified analysis enables better insight

25. Summary • Cognos TM1 delivers the best of both worlds • A high-speed centrally-managed server for business data, hierarchies, calculations and workflow • A familiar and attractive Excel-based presentation layer, managed by end-users • Cognos TM1 + Excel: • enables enhanced insight and proactive management • removes artificial barriers to higher growth and business performance • provides a secure yet flexible Performance Management environment