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 • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel & Management Reporting