Improve your reporting skills with self-teach tutorials.

A good way to improve your skills with a package is by working your way through a practical example on the keyboard. This collection of AccountingWEB self-teach tutorials will help you develop your skills with Excel pivot tables, Microsoft Access, Sage and QuickBooks

• Excel Pivot Tables
• Sales Reporting (Northwind)
• Reporting against Budget
• Microsoft Access
• Sage Line 50 & MMS
• QuickBooks
• Import External Data to other accounting programs

  Excel Pivot Tables
  For anyone whose job is to analyse data, pivot table skills are essential. Whether you are concerned with sales and marketing or with accounting, we have tutorials in areas that are relevant to you.

  If you are pushed for time, take a quick dip into the Five Minute pivot table tips series. This contains 28 tips which show you how to analyse sales and margins, each one taking 5-10 minutes to work through. Together they add up to a complete guide on how to analyse your sales with pivot tables.

  If you have an hour or so to spare, try working through one of David Carter’s full tutorials. Go to Interested in Pivot Tables? Start here. This contains a series of tutorials for both accountants and non-accountants.

  Once you’ve mastered the basics, develop your skills further with Interested in Pivot Tables? Part 2. This contains tutorials on how to use the all-important Vlookup function, how to consolidate multiple reports in a pivot table, and how to use pivot tables for forecasting. There are also some real-life cases submitted by AccountingWEB readers, plus a series on how to produce KPI reports on sales and margins.

  Northwind Traders - building a sales reporting system
  If you are interested in analysing customer data rather than in accounting, try these tutorials. The Northwind Traders demo database is supplied by Microsoft as part of MS Access. David Carter shows you how to live link Excel to the Northwind database and generate your own sales reports with pivot tables.

  In the other tutorials you are analysing data that has been "massaged" for analysis in pivot tables. But in this series you have to get raw data out of a real transaction processing system. You map the various data tables and make joins to combine them into a simple data warehouse. A copy of the Northwind database is supplied with the tutorial.

  Tutorial #1: Improving sales reports at Northwind Traders
  Tutorial #2: Creating the daybook
  Tutorial #3: Create sales reports with pivot tables
  Tutorial #4 : Add cost prices and margins

  Reporting against Budget
  One of the most difficult areas of reporting is how to show actuals versus budget. Actuals usually come in the form of multiple transactions, whereas budgets are single monthly balances. Combining these two different types of data into one report is the problem.

  Start with Reporting against Budget with pivot tables - tutorials by David Carter

  Microsoft Access
  Simon Hurst is our specialist on Microsoft Access. Start with his Pain-free introduction to Microsoft Access

  Then move on to Build a Trial Balance in Access

  Once you’ve got the basics, Simon then provides a four-part series developing what you’ve done so far:
  Create your own extended TB application - Part 1
  Create your own extended TB application – Part 2
  Create your own extended TB application – Part 3
  Create your own extended TB application – Part 4

  And if you want to tie your Access application into Excel, try Excel clinic: Link drop-down menus to MS Access

  Sage Line 50
  There's various ways of getting data out of Sage and into Excel. It's probably best to live link your Sage data to Excel via ODBC and use the Import External Data command. David Carter shows how.

  MSQuery and Sage Tutorial #1: How to Set up Import External Data
  MSQuery and Sage Tutorial #2: Import Balances into Excel
  MSQuery and Sage Tutorial #3: Set up your analysis-ready file
  MSQuery and Sage Tutorial #4: Add the missing pieces of data
  MSQuery and Sage Tutorial #5: Add nominal categories
  MSQuery and Sage Tutorial #6: Add customer and supplier names
  MSQuery and Sage Tutorial #7: Create an analysis-ready sales file in Sage

  Alternatively, you can export data out of Sage and into Excel via the File–Send to Excel command. But there are still some danger points you need to be aware of. Find out some of them in this three-part series:
  Tips on Exporting from Sage Line 50 into Excel
  Tips on Exporting from Sage into Excel – Part 2
  Exporting Sage data Part 3 – Building a P&L in Excel

  Sage Report Designer (Line 50 and MMS)
  Exporting into Excel is fine if you want to write a once-off report. But if you want to create a report that is going to be used regularly, you should either live link via ODBC (see above) or use the Sage Report Designer.

  The Report Designer is available in both Line 50 and Sage MMS, but has a steep learning curve. In his first tutorial Getting to Grips with the Sage Report Designer - 1 David Carter shows you how to modify the standard Purchase Daybook report in Sage and add essential fields such as the supplier name.

  Sage has added a Wizard to make the Report Designer easier to use. The second tutorial Getting to Grips with the Sage Report Designer - 2 shows you how to use the Wizard to build the Purchase Daybook report from scratch. But it turns out a lot more difficult than it should be.

  It's not exactly a tutorial, but you can find some useful Any Answers tips on report writing in Sage in the Sage Tips and Queries collection.

  QuickBooks
  QuickBooks has a very good on-screen report writer with the potential to produce excellent Costing and Daybook reports. However, it has one or two design peculiarities which can confuse new users.

  To understand the Daybook problem, try Can you get a good daybook out of QuickBooks?.

  And on Job Costing, see Tips on QuickBooks and Job Costing – Part 1.

  Once again, there are some useful Any Answers tips on report writing in QuickBooks in Management Reporting in QuickBooks and the Tips and Queries collection.

  Import External Data to other accounting applications
  The Import External Data technique used in the Sage Line 50 tutorial series will also work with any other ODBC-compliant accounting application. Individual tutorials are available for the following systems:
  Import data from Navision into Excel
  Import data from Pegasus Opera into Excel
  Import data from TAS Books into Excel
  Import data from Iris Exchequer into Excel. Part 1
  Import data from Iris Exchequer into Excel. Part 2
  Import data from MYOB into Excel. Part 1
  Import data from MYOB into Excel. Part 2
  Import data intoExcel from Access Dimensions. Part 1
  Access Dimensions and Excel, part 2 - Access Analytics
  Import data from Sage Line 100 into Excel
  Import data from Sage Line 200 into Excel

  Related material
  David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
  Reporting tutorials index: Use MS Query to extract and analyse accounts data
  Reporting Tools #4: Introduction to summary reporting
  Towards an analysis-ready file for reporting #1
  Towards an analysis-ready file for reporting #2
  Management Reporting in Sage Line 50 and Instant
  Want to learn more about pivot tables? Start here
  Improve your reporting skills with self-teach tutorials
  An introduction to Excel-driven reporting tools

  Subscribe to the ExcelZone newswire
  To ensure you don't miss any of AccountingWEB's management reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.