1 / 27

Excel & Management Reporting

Excel & Management Reporting. Is Excel the right tool for management reporting? Softworld Accounting & Finance 2 March 2005. John Stokdyk, Editor, AccountingWEB & IT Zone. http://www.accountingweb.co.uk/itzone. Excel & Management Reporting. Agenda. Prevalence of Excel in accountancy

ryder-ward
Download Presentation

Excel & Management Reporting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Excel & Management Reporting Is Excel the right tool for management reporting? Softworld Accounting & Finance 2 March 2005 John Stokdyk, Editor, AccountingWEB & IT Zone http://www.accountingweb.co.uk/itzone

  2. Excel & Management Reporting Agenda • Prevalence of Excel in accountancy • Microsoft’s Excel strategy in finance • Pros & Cons: Flexibility v Control • GIGO: Excel horror stories • Handling data (imports & Pivot Tables) • The Third Way: Excel under control • Questions and answers http://www.accountingweb.co.uk/itzone

  3. Excel & Management Reporting AccountingWEB & Excel • IT Zone survey results 2001-05 • David Carter’s Pivot Table tutorials • Any Answers & Expert Guides • Two of our all-time Top 10 on Excel • ExcelZone – work in progress http://www.accountingweb.co.uk/itzone

  4. Excel & Management Reporting Use of Excel for Acccounts • Microsoft UK SME Manger: 40% use Excel • Sage sees Line 50 user base eroding: 9% use Excel (11% in US according to Yankee Group) • IT Zone survey: Steady increase to 9% in 2004 • When asked, 24% said Excel was secondary tool http://www.accountingweb.co.uk/itzone

  5. Excel & Management Reporting Primarily use Excel for Accounts 2002-04 • 2004: 24% said Excel was secondary tool http://www.accountingweb.co.uk/itzone

  6. Excel & Management Reporting Microsoft’s strategy for Excel • “We want Excel to become the interface… it will become the front-end for many financial components” - 2002 • “As Microsoft develops its new .NET technology architecture, Excel will carry many of the financial applications to business managers.” - 2003 • “Business Intelligence will be increasingly through tighter integration of Excel as the primary reporting client with SQL Reporting Services” – 2005 http://www.accountingweb.co.uk/itzone

  7. Excel & Management Reporting Use Excel for Budgeting/Forecasting • 2004: 65% of those who budgeted used Excel • 2005: 49% use Excel for management reporting http://www.accountingweb.co.uk/itzone

  8. Excel & Management Reporting What we like about Excel • Graphics & Charts • Flexibility • Platform for powerful tools (& Games) • Pivot Tables: “The most important development in IT since the original invention of the spreadsheet” – David Carter, 1999 http://www.accountingweb.co.uk/itzone

  9. Excel & Management Reporting Excel graphics/ingenuity – Cashflow Wizard http://www.accountingweb.co.uk/itzone

  10. Excel & Management Reporting Excel flexibility – Data entry & calculators http://www.accountingweb.co.uk/itzone

  11. Excel & Management Reporting Excel ingenuity – Footy Manager http://www.accountingweb.co.uk/itzone

  12. Excel & Management Reporting Excel ingenuity/flexibility – Pivot Table project planner http://www.accountingweb.co.uk/itzone

  13. Excel & Management Reporting David Carter: The Pivot Table King • 1999 Budgeting with Pivot Tables – more than 87,000 reads • Sales analysis, Consolidation, working with Sage Line 50 data etc • Project Planner “Reporting Challenge” • Vendors and consultants: “Commerical solutions are better” http://www.accountingweb.co.uk/itzone

  14. Excel & Management Reporting What we don’t like about Excel • Error rates = 90%+ • Lack of control • DIY time-wasting • IT Zone users admit: commercial tools are better • Inappropriate for storing data (use Access!) • “One of the biggest holes in compliance occurs when business-critical information is held on spreadsheets,” Keith Bishop, Qtier 2005 http://www.accountingweb.co.uk/itzone

  15. Excel & Management Reporting Excel error horror stories • University of Hawaii – on average 91% of business spreadsheets contain errors • Findings mirrored by KPMG and PwC • TransAlta took a C$24m charge in 2004 after a bidding mistake caused by an Excel cut/paste error • $700m AIB Forex Fraud in 2001 – committed by Rusnak rigging bank risk assessment spreadsheet • £30,000 hole in 2001 school budget =SUM flaw http://www.accountingweb.co.uk/itzone

  16. Excel & Management Reporting Excel error horror: the £30,000 budget hole http://www.accountingweb.co.uk/itzone

  17. Excel & Management Reporting Excel error horror: the £30,000 budget hole • The values in two cells were entered as right aligned text values rather than as numbers • The column to the right of the cells included a formula using the values - there was no indication of the problem. • The total of the column containing the cells used the @sum formula. Sadly it was this lower total which rippled through to the budget submission. http://www.accountingweb.co.uk/itzone

  18. Excel & Management Reporting Carter’s conundrum • Ledgers devised by programmers for their uses – not management reporting • Even with ODBC, identifying the right ledger tables is the biggest challenge • Time is wasted tidying up the data • Dates incorrect, need to use filtering, VLOOKUP, data check routines • Why not create analysis-ready file? http://www.accountingweb.co.uk/itzone

  19. Excel & Management Reporting Sage Line 50: analysis-ready file http://www.accountingweb.co.uk/itzone

  20. Excel & Management Reporting ExFiles DataLink – Import/Export tool http://www.accountingweb.co.uk/itzone

  21. Excel & Management Reporting Response from Accounting software vendors • Sage & TAS collaborating with Carter • Access Accounts – Analysis-ready file • Raise it on your wishlists if you want to improve DIY management reporting • Better mapping tools and Reporting Services will make it easier http://www.accountingweb.co.uk/itzone

  22. Excel & Management Reporting Budgeting, forecasting & reporting • Sage Financial Forecasting: 15% better user rating than DIY forecasting tools • Budgeting: “Controlling a multi-site process is next to impossible” – Tim Ward • Business plans: 20mins with Cashflow Wizard • Reporting: CPM/OLAP databases & Crystal are better – but cost £££, nor as flexible as Excel • The Third Way: Excel under control http://www.accountingweb.co.uk/itzone

  23. Excel & Management Reporting “Third Way” reporting tools at Softworld • Sage IntelligentApps for Line 200/500 • OpenAccounts Executive Desktop Reporting • SunSystems & Pegasus resellers: Vision/XRL • CODA Collaborative Close with .NET tools • Exchequer Enterprise – 2-way drill down • Access Dimensions/MS Office integration • Sage Line 50 Accounts Analyst • XL Cubed (OEM deal with Topaz Financials) • Newcomers Qtier, Dillon PROPHET – NB: • Don’t forget Microsoft Business Solutions… http://www.accountingweb.co.uk/itzone

  24. Excel & Management Reporting Microsoft Business Solutions strategy • Four ERP product families, plus Microsoft Business Solutions for Analytics – FRx • Integration with MS Outlook & Excel – they are the user interface • Business Intelligence a horizontal activity, driven by SQL Server Reporting Services • Big dent in the CPM business model: £50k tools are being supplied FREE! • Early days: Needs to be implemented by people who know what they’re doing http://www.accountingweb.co.uk/itzone

  25. Excel & Management Reporting Excel: They can’t take it away from you • Acceptance that Excel is here to stay • Users like its flexibility and limitless potential • Understand its limitations • Reporting processes are management, not technology issue: training, discipline, then tools • SOX &c rules mean spreadsheets without audit trails are not compliant • Watch for Microsoft Reporting Services tools • Reporting meltdown: Excel still rules! http://www.accountingweb.co.uk/itzone

  26. Excel & Management Reporting Excel: Management reporting DOs and DON’Ts • DON’T use Excel as a financial forecasting/budgeting or reporting database • DON’T use Excel for compliance reports • DO use Excel as a presentation tool • DO use trusted add-ins, eg Cashflow Wizard • DO use Excel audit and checking tools • DO document how reports/forecasts developed • DO use file names to track versions (if no other way) • DO keep an eye out for Microsoft Reporting Services http://www.accountingweb.co.uk/itzone

  27. Excel & Management Reporting Questions • What reporting tools are you looking for? • Do you have a reporting tool strategy? • Web deployability? http://www.accountingweb.co.uk/itzone

More Related