S19 3010 AdvReportingTechniques HOT 812763
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Presented by: Diane C. O. Gilson President, Info Plus Accounting, Inc. Certified Advanced QuickBooks® Pro Advisor e-mail: [email protected] © Info Plus Accounting, Inc. Ann Arbor, MI 48104 web: www.BuildYourNumbers.com + www.InfoPlusAccounting.com
This session : Way beyond the basics… Extracting the numbers that ‘count’: Company snapshots Excel exports & applications Custom reports ‘Detective’ reports Memorized reports Specialty reports Enterprise extras
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Company Snapshot
Management‐oriented “top‐down” view of various results o Numerous graphs and windows to important info o Mouse over to see actual $ amounts o Visual, colorful o Personalized to what you want to see (i.e., customized by individual users – no one else sees what you see) o Restricted to what you have been authorized to see 3 Tabs o Company o Payments o Customers Customizing o Add content option Displays previews at top Show preview Add if desired “Done” hides the options o Move modules around o Delete content modules o Various other selections within modules Printing o Whole tab (top right) o Individual modules: Print/Print Preview/Save as Image (.jpg)
Exporting reports to Excel
From top of report, click on Export o Basic & Advanced settings are maintained until you change them Basic tab o CSV (comma separated values) file o New workbook o Existing workbook New sheet? Existing sheet? (Browse to locate) New data will overwrite existing sheet – totally. I.e., any “side” formulas will be replaced by blank cells. Tip: If you want to re‐compute certain data on a regular basis – for new or revised information, create a secondary worksheet with formulas that “look at” the info coming into the updated “existing” sheet. o Include a worksheet that explains… you’ll probably need this only once or twice, then unclick.
© Info Plus Accounting (may not be used or re-printed without permission)
Page 1
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Advanced tab o Here’s the way I typically set my Advanced Settings – but you may want to experiment:
Notes: I like to delete the “Space between columns” because I may want to copy and paste formulas. It’s easier if there are no blank columns to contend with. If the AutoFit is clicked, your columns will all be wide enough to include the entire column name. If “off”, the columns will all be the same size, but you’ll probably want to turn on the “wrap text” in Excel for column titles that overlap. The Auto Outline feature works on many of the reports and allows you to have total control over the level of summarization that you wish to see. For example: You may wish to summarize at a lower sub‐level than QuickBooks allows OR You may wish to see detail for certain sections of a report, but not for others. What you get o Headers and footers o Column titles o Right‐justified columns o Formulas !!!!
© Info Plus Accounting (may not be used or re-printed without permission)
Page 2
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Applications – some ideas: o Analysis: Use your own add‐in formulas… Tip: Use “Conditional formatting” in Excel to highlight exceptions Sort data Run unique sub‐totals Create pivot tables Add graphs on the same page o Projections ‐ utilize built in formulas and change underlying data to see results for: Budgeting (covered in the Budgeting workshop) Other forecasts, or “what if” scenarios. o Format reports Collapsing/expanding rows and/or columns in certain reports (if auto‐outline was on) Hide columns (sometimes QuickBooks delivers “excess” columns) Change titles (e.g., Cost of Goods Sold title can’t be changed in QB, but you could change it to something like “Production Costs” in Excel). Add colors Change fonts o Make notes and document research or observations when reporting (e.g., budget vs. actual variances) o Send info to others & ask for feedback (recipients can add notes) o Export reports and data to accountants who don’t have your version of QuickBooks Tip: You can export a series of reports to a single spreadsheet. Just save and close the spreadsheet in between exports.
Custom Summary & Detail reports
Custom Summary Reports o Additional choices on the Display tab:
o o o o o
Choices for both columns and rows Columns: Amount & Quantity or Both Limitations – you’ll only be able to get what QB data tables allow. You’ll need to experiment if you’re looking to do something specific. Creativity is helpful – and data structure is important. Another reason to “begin with the end in mind…”
© Info Plus Accounting (may not be used or re-printed without permission)
Page 3
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Custom Transaction Detail Report o Options are nearly identical to those in the Modify Report screen of other transaction detail reports.
Accountant & taxes (detective) reports – my favorites:
Trial balance o Listing of all account balances at any given time. It will need to balance. o Good to keep after each month‐end closing o Your accountant will likely want to see this at year‐end. Journal o Every current transaction shown in original order of entry (i.e., by QuickBooks‐assigned transaction number) showing debits and credits. To show all line‐item detail, you’ll need to click on the Expand button at the top of the report. Audit Trail o Every transactions and every change that was made to it – exactly when and who made the change. Closing Date Exception Report o What got changed after the closing date was set? Voided/Deleted Transactions Summary o Lists all transactions voided or deleted within a specific date range Voided/Deleted Transactions Detail o Same as above but shows all line Item detail. From the “Banking” group (more detailed info in the “Reconciliations” workshop): o Reconciliation Discrepancy report When your beginning balance is no longer the same as when you last reconciled the bank account… o Missing Checks report Just what the name says… Tip: You can modify the account and transaction type filters to look for other types of missing transaction numbers (e.g., choose the Accounts Receivable account and transaction type “Invoices” to locate missing Invoice numbers).
© Info Plus Accounting (may not be used or re-printed without permission)
Page 4
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Memorized reports
Creating memorized reports – why? o Retain specific settings for future use: For example: Only certain accounts, only certain jobs, complex settings and filters, specific date ranges, transactions to be corrected, etc. o Saves time! o Infrequent use? Don’t struggle with how to re‐create the report… o Frequent use? At your (or someone else’s fingertips…) Remember the icon! Reports can be placed in an individual user’s icon bar. Makes it easy for you to create reports for people who want info from QuickBooks but aren’t familiar enough with it to properly construct their reports. Life becomes easier for you and for them… o A head start: Is a memorized report “almost” what you want? Then start there, memorize under another name, and modify it some more! o Copy reports between companies (assuming underlying elements are the same…) From source company: From Memorized Report list, highlight a report, then right‐ click and select Export Template. From target company: From Memorized Report list, right‐click and select Import Template. Special settings o Titles – specific vs. generic: Go to report Header and change title from something like “Missing Checks Report” to “Missing Checks – KeyBank Checking” o Include instructions, notes, or disclaimers in header (or footer). E.g.: Title: Detailed Costs by Job Stage for (Filter for Job) Footer: Job Started January 2012 – Internal Use Only Draft – CONFIDENTIAL o Date ranges If you want the report to always be only one specific date range (e.g., 1/1/13 – 3/31/13), use Custom Dates. If you want the report to typically reflect a “relational” date range (e.g., the prior year, or prior quarter, or current month) use the drop down menu dates to indicate your choice. That way it should always default to that range. In either case, the dates can be changed when you get ready to create the report again.
© Info Plus Accounting (may not be used or re-printed without permission)
Page 5
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Memorizing printing choices: If you make your printer choices PRIOR to memorizing (e.g., landscape – 3 pages wide), QuickBooks will remember it when you open it the next time. o Column widths: Column width settings WILL NOT hold – so if you care what your printed reports look like, you’ll want to display (and fix) reports before printing! (preview, preview, preview!) Naming memorized reports o When you choose to memorize a report for the first time by clicking on the Memorize button, the name that will automatically pop into the window will be the name that shows on the report. This is the name that the memorized report will carry in the Memorized Report List. o Example o Change the name of the report when memorizing? You can change the name of the report as it will display in the Memorized Report List when you memorize it for the first time – BUT – it won’t change the name of the report itself. Tip: Save time and be consistent: Change the name of the report in the Modify Report window BEFORE clicking on Memorize Report. o Editing after‐the‐fact If you want to change the name of the memorized report as it displays in the Memorized Report List, from the Memorized Report List, right click, choose Edit, and change the name. o
Creating memorized report groups
o o
o o o
How to create a Group How to add memorized reports to Groups When created After the fact Example: Template Company Numbering report names within Groups Numbering report names for users
© Info Plus Accounting (may not be used or re-printed without permission)
Page 6
Advanced reporting techniques: When management wants "More" or "Different" or "Better..." Processing multiple reports: How to From Memorized Report window From Reports menu o Display vs. Print Remember the issue with column widths – be sure to display and fix before printing. Variation on Group: o From the Report Center Mark a report as a Favorite Reports marked as Favorites go into the “Favorites” tab and show in the main menu Reports drop down menu Watch out for: o Filters (especially additions and/or inactivated elements) – remember there is no “exclusion” feature in filters. Put something in the report name to remind you to check, fix, and re‐memorize the report before you print it. o Corrupted memorized reports – this does happen! If you find this, DON’T DELETE the corrupted report. Create a new group called BAD REPORTS Move the bad memorized report into that Group and re‐name it. If you delete the corrupted report – you create a “bad memorized report home” that will be filled by the next memorized report that you create. Aaaargh! If you have experienced this in the past: Just create 8 or 10 “throwaway” memorized reports and throw them into the BAD REPORTS group. You may be able to sponge up all of the previously created “bad memorized report homes”. (This approach has worked for me before – and the suggestion was straight from Intuit tech support.) o
Specialty reports e.g.,
Worker's Comp o See example in Template company o Underlying Service Items, Payroll Items, time entry and payroll handled correctly. o Workers’ Comp auditors love these + fast & easy for you! o Good data and reporting can, in many cases, save significantly on Workers’ Comp premiums.
© Info Plus Accounting (may not be used or re-printed without permission)
Page 7
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Certified Payroll and other Sunburst Software options, e.g.: o A resource to investigate… The following information is quoted from http://www.sunburstsoftwaresolutions.com/ o Certified Payroll: Generates Federal and/or State specific Prevailing Wage Payroll Reports, Statements of Compliance, "No Work Performed" payrolls, 25 Federal, State, and Local EEOC/Workforce Utilization Reports ‐ PLUS ARRA reports, and generic or custom‐designed Union/bona‐fide plan Fringe Benefit Reports. o AIA Billing (Construction Application for Payment Solution): Generates percentage of completion contract billings using original AIA G‐702/703 and G‐702/703 CMa forms that you purchase (or similar plain paper versions), 30+ standard Contract Documents with the ability to create custom documents, 7 other plain paper AIA type formats, and the HUD 51000 series. o Wage Manager Solution: Allows you to quickly and easily modify, add, or remove multiple payroll wage items with rates of pay for multiple employees, all at the same time. o Crew/Overtime Entry Solution: Teach the program the Overtime laws that your state requires you to follow, create crews, assign employees to crews, enter times cards for your crews by job and day. You can enter time for hundreds of employees with a few mouse clicks and post them to your QuickBooks Weekly Timesheet. At the end of the week, retrieve all of your timesheets and the program will calculate and distribute overtime and/or weighted‐average overtime based on your state laws and/or union regulations; leaving you with the task of then processing payroll. Union reports o Need to determine exactly what is required o Underlying payroll system needs to be set up properly Looking for more extensive reporting options? o Contact us for the latest options including: Xpanded reports (newly developed package) – available through the Intuit App Center (newest version is cloud‐based). 30‐day free trial. For more info, see: http://marketplace.intuit.com/AppID‐3383‐Overview.aspx o Other new options that combine with Crystal Reports o Custom report prep o Move up to Enterprise as follows…
© Info Plus Accounting (may not be used or re-printed without permission)
Page 8
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Enterprise – additional reporting features:
Combine reports from several companies
Combines in Excel Underlying Chart of Accounts structure needs to be coordinated Limit on the number of companies that can be combined. (My understanding is that) other options (e.g., Xpanded Reports) aren’t as limited on the number of files that can be combined. Custom Reporting – ODBC (Open Database Connectivity) o Feeds: Excel Crystal Reports o On its own: Pretty complex & “geeky” (extensive database tables, links, etc.) If you’d like to poke around and look at raw data table schemas, go to: http://www.qodbc.com/schema.htm o Easier options available at reasonable prices – contact us for the latest insights. o o o
© Info Plus Accounting (may not be used or re-printed without permission)
Page 9
Presented by: Diane C. O. Gilson President, Info Plus Accounting, Inc. Certified Advanced QuickBooks® Pro Advisor e-mail: [email protected] © Info Plus Accounting, Inc. Ann Arbor, MI 48104 web: www.BuildYourNumbers.com + www.InfoPlusAccounting.com
This session : Way beyond the basics… Extracting the numbers that ‘count’: Company snapshots Excel exports & applications Custom reports ‘Detective’ reports Memorized reports Specialty reports Enterprise extras
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Company Snapshot
Management‐oriented “top‐down” view of various results o Numerous graphs and windows to important info o Mouse over to see actual $ amounts o Visual, colorful o Personalized to what you want to see (i.e., customized by individual users – no one else sees what you see) o Restricted to what you have been authorized to see 3 Tabs o Company o Payments o Customers Customizing o Add content option Displays previews at top Show preview Add if desired “Done” hides the options o Move modules around o Delete content modules o Various other selections within modules Printing o Whole tab (top right) o Individual modules: Print/Print Preview/Save as Image (.jpg)
Exporting reports to Excel
From top of report, click on Export o Basic & Advanced settings are maintained until you change them Basic tab o CSV (comma separated values) file o New workbook o Existing workbook New sheet? Existing sheet? (Browse to locate) New data will overwrite existing sheet – totally. I.e., any “side” formulas will be replaced by blank cells. Tip: If you want to re‐compute certain data on a regular basis – for new or revised information, create a secondary worksheet with formulas that “look at” the info coming into the updated “existing” sheet. o Include a worksheet that explains… you’ll probably need this only once or twice, then unclick.
© Info Plus Accounting (may not be used or re-printed without permission)
Page 1
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Advanced tab o Here’s the way I typically set my Advanced Settings – but you may want to experiment:
Notes: I like to delete the “Space between columns” because I may want to copy and paste formulas. It’s easier if there are no blank columns to contend with. If the AutoFit is clicked, your columns will all be wide enough to include the entire column name. If “off”, the columns will all be the same size, but you’ll probably want to turn on the “wrap text” in Excel for column titles that overlap. The Auto Outline feature works on many of the reports and allows you to have total control over the level of summarization that you wish to see. For example: You may wish to summarize at a lower sub‐level than QuickBooks allows OR You may wish to see detail for certain sections of a report, but not for others. What you get o Headers and footers o Column titles o Right‐justified columns o Formulas !!!!
© Info Plus Accounting (may not be used or re-printed without permission)
Page 2
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Applications – some ideas: o Analysis: Use your own add‐in formulas… Tip: Use “Conditional formatting” in Excel to highlight exceptions Sort data Run unique sub‐totals Create pivot tables Add graphs on the same page o Projections ‐ utilize built in formulas and change underlying data to see results for: Budgeting (covered in the Budgeting workshop) Other forecasts, or “what if” scenarios. o Format reports Collapsing/expanding rows and/or columns in certain reports (if auto‐outline was on) Hide columns (sometimes QuickBooks delivers “excess” columns) Change titles (e.g., Cost of Goods Sold title can’t be changed in QB, but you could change it to something like “Production Costs” in Excel). Add colors Change fonts o Make notes and document research or observations when reporting (e.g., budget vs. actual variances) o Send info to others & ask for feedback (recipients can add notes) o Export reports and data to accountants who don’t have your version of QuickBooks Tip: You can export a series of reports to a single spreadsheet. Just save and close the spreadsheet in between exports.
Custom Summary & Detail reports
Custom Summary Reports o Additional choices on the Display tab:
o o o o o
Choices for both columns and rows Columns: Amount & Quantity or Both Limitations – you’ll only be able to get what QB data tables allow. You’ll need to experiment if you’re looking to do something specific. Creativity is helpful – and data structure is important. Another reason to “begin with the end in mind…”
© Info Plus Accounting (may not be used or re-printed without permission)
Page 3
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Custom Transaction Detail Report o Options are nearly identical to those in the Modify Report screen of other transaction detail reports.
Accountant & taxes (detective) reports – my favorites:
Trial balance o Listing of all account balances at any given time. It will need to balance. o Good to keep after each month‐end closing o Your accountant will likely want to see this at year‐end. Journal o Every current transaction shown in original order of entry (i.e., by QuickBooks‐assigned transaction number) showing debits and credits. To show all line‐item detail, you’ll need to click on the Expand button at the top of the report. Audit Trail o Every transactions and every change that was made to it – exactly when and who made the change. Closing Date Exception Report o What got changed after the closing date was set? Voided/Deleted Transactions Summary o Lists all transactions voided or deleted within a specific date range Voided/Deleted Transactions Detail o Same as above but shows all line Item detail. From the “Banking” group (more detailed info in the “Reconciliations” workshop): o Reconciliation Discrepancy report When your beginning balance is no longer the same as when you last reconciled the bank account… o Missing Checks report Just what the name says… Tip: You can modify the account and transaction type filters to look for other types of missing transaction numbers (e.g., choose the Accounts Receivable account and transaction type “Invoices” to locate missing Invoice numbers).
© Info Plus Accounting (may not be used or re-printed without permission)
Page 4
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Memorized reports
Creating memorized reports – why? o Retain specific settings for future use: For example: Only certain accounts, only certain jobs, complex settings and filters, specific date ranges, transactions to be corrected, etc. o Saves time! o Infrequent use? Don’t struggle with how to re‐create the report… o Frequent use? At your (or someone else’s fingertips…) Remember the icon! Reports can be placed in an individual user’s icon bar. Makes it easy for you to create reports for people who want info from QuickBooks but aren’t familiar enough with it to properly construct their reports. Life becomes easier for you and for them… o A head start: Is a memorized report “almost” what you want? Then start there, memorize under another name, and modify it some more! o Copy reports between companies (assuming underlying elements are the same…) From source company: From Memorized Report list, highlight a report, then right‐ click and select Export Template. From target company: From Memorized Report list, right‐click and select Import Template. Special settings o Titles – specific vs. generic: Go to report Header and change title from something like “Missing Checks Report” to “Missing Checks – KeyBank Checking” o Include instructions, notes, or disclaimers in header (or footer). E.g.: Title: Detailed Costs by Job Stage for (Filter for Job) Footer: Job Started January 2012 – Internal Use Only Draft – CONFIDENTIAL o Date ranges If you want the report to always be only one specific date range (e.g., 1/1/13 – 3/31/13), use Custom Dates. If you want the report to typically reflect a “relational” date range (e.g., the prior year, or prior quarter, or current month) use the drop down menu dates to indicate your choice. That way it should always default to that range. In either case, the dates can be changed when you get ready to create the report again.
© Info Plus Accounting (may not be used or re-printed without permission)
Page 5
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Memorizing printing choices: If you make your printer choices PRIOR to memorizing (e.g., landscape – 3 pages wide), QuickBooks will remember it when you open it the next time. o Column widths: Column width settings WILL NOT hold – so if you care what your printed reports look like, you’ll want to display (and fix) reports before printing! (preview, preview, preview!) Naming memorized reports o When you choose to memorize a report for the first time by clicking on the Memorize button, the name that will automatically pop into the window will be the name that shows on the report. This is the name that the memorized report will carry in the Memorized Report List. o Example o Change the name of the report when memorizing? You can change the name of the report as it will display in the Memorized Report List when you memorize it for the first time – BUT – it won’t change the name of the report itself. Tip: Save time and be consistent: Change the name of the report in the Modify Report window BEFORE clicking on Memorize Report. o Editing after‐the‐fact If you want to change the name of the memorized report as it displays in the Memorized Report List, from the Memorized Report List, right click, choose Edit, and change the name. o
Creating memorized report groups
o o
o o o
How to create a Group How to add memorized reports to Groups When created After the fact Example: Template Company Numbering report names within Groups Numbering report names for users
© Info Plus Accounting (may not be used or re-printed without permission)
Page 6
Advanced reporting techniques: When management wants "More" or "Different" or "Better..." Processing multiple reports: How to From Memorized Report window From Reports menu o Display vs. Print Remember the issue with column widths – be sure to display and fix before printing. Variation on Group: o From the Report Center Mark a report as a Favorite Reports marked as Favorites go into the “Favorites” tab and show in the main menu Reports drop down menu Watch out for: o Filters (especially additions and/or inactivated elements) – remember there is no “exclusion” feature in filters. Put something in the report name to remind you to check, fix, and re‐memorize the report before you print it. o Corrupted memorized reports – this does happen! If you find this, DON’T DELETE the corrupted report. Create a new group called BAD REPORTS Move the bad memorized report into that Group and re‐name it. If you delete the corrupted report – you create a “bad memorized report home” that will be filled by the next memorized report that you create. Aaaargh! If you have experienced this in the past: Just create 8 or 10 “throwaway” memorized reports and throw them into the BAD REPORTS group. You may be able to sponge up all of the previously created “bad memorized report homes”. (This approach has worked for me before – and the suggestion was straight from Intuit tech support.) o
Specialty reports e.g.,
Worker's Comp o See example in Template company o Underlying Service Items, Payroll Items, time entry and payroll handled correctly. o Workers’ Comp auditors love these + fast & easy for you! o Good data and reporting can, in many cases, save significantly on Workers’ Comp premiums.
© Info Plus Accounting (may not be used or re-printed without permission)
Page 7
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Certified Payroll and other Sunburst Software options, e.g.: o A resource to investigate… The following information is quoted from http://www.sunburstsoftwaresolutions.com/ o Certified Payroll: Generates Federal and/or State specific Prevailing Wage Payroll Reports, Statements of Compliance, "No Work Performed" payrolls, 25 Federal, State, and Local EEOC/Workforce Utilization Reports ‐ PLUS ARRA reports, and generic or custom‐designed Union/bona‐fide plan Fringe Benefit Reports. o AIA Billing (Construction Application for Payment Solution): Generates percentage of completion contract billings using original AIA G‐702/703 and G‐702/703 CMa forms that you purchase (or similar plain paper versions), 30+ standard Contract Documents with the ability to create custom documents, 7 other plain paper AIA type formats, and the HUD 51000 series. o Wage Manager Solution: Allows you to quickly and easily modify, add, or remove multiple payroll wage items with rates of pay for multiple employees, all at the same time. o Crew/Overtime Entry Solution: Teach the program the Overtime laws that your state requires you to follow, create crews, assign employees to crews, enter times cards for your crews by job and day. You can enter time for hundreds of employees with a few mouse clicks and post them to your QuickBooks Weekly Timesheet. At the end of the week, retrieve all of your timesheets and the program will calculate and distribute overtime and/or weighted‐average overtime based on your state laws and/or union regulations; leaving you with the task of then processing payroll. Union reports o Need to determine exactly what is required o Underlying payroll system needs to be set up properly Looking for more extensive reporting options? o Contact us for the latest options including: Xpanded reports (newly developed package) – available through the Intuit App Center (newest version is cloud‐based). 30‐day free trial. For more info, see: http://marketplace.intuit.com/AppID‐3383‐Overview.aspx o Other new options that combine with Crystal Reports o Custom report prep o Move up to Enterprise as follows…
© Info Plus Accounting (may not be used or re-printed without permission)
Page 8
Advanced reporting techniques: When management wants "More" or "Different" or "Better..."
Enterprise – additional reporting features:
Combine reports from several companies
Combines in Excel Underlying Chart of Accounts structure needs to be coordinated Limit on the number of companies that can be combined. (My understanding is that) other options (e.g., Xpanded Reports) aren’t as limited on the number of files that can be combined. Custom Reporting – ODBC (Open Database Connectivity) o Feeds: Excel Crystal Reports o On its own: Pretty complex & “geeky” (extensive database tables, links, etc.) If you’d like to poke around and look at raw data table schemas, go to: http://www.qodbc.com/schema.htm o Easier options available at reasonable prices – contact us for the latest insights. o o o
© Info Plus Accounting (may not be used or re-printed without permission)
Page 9
