Translating QuickBooks Source Data To Custom Formatted Repor ✓ Solved

Translating Quickbooks Source Data To Custom Formatted Reports In Exce

Translating QuickBooks Source Data to Custom Formatted Reports in Excel

Good, strategically useful financial reports include more information than QuickBooks (QB) can put in one report. Translating data from QB reports into comprehensive, customized Excel reports, while preserving data integrity, can be accomplished by memorizing a report in QB to serve as the source data, sending it to Excel, and pasting the contents into a source data tab in a workbook with other customized report tabs that have some of their formulas linked to the source data tab. Below are the steps, presuming that the organization minimally uses classes or “other income” type accounts for restricted transactions to distinguish them from unrestricted.

SOA = Statement of Activities aka P&L, Income Statement

SOP = Statement of Financial Position, aka Balance Sheet

TR = Temporarily Restricted

PR = Permanently Restricted

TO PREPARE DATA FOR THE REPORTS – IN QUICKBOOKS

  1. Create a memorized report (in QB) labeled “SOA Source” that is a P&L By Class report for the entire fiscal year, with the advanced customization of “display – all rows” and “display – all classes”.
  2. Create a memorized report (in QB) labeled “SOP Source” that is a Balance Sheet report for the entire fiscal year, with the advanced customization of “display – all rows” and “display – all classes”.
  3. Make all of the regular end-of-month balance sheet reconciliations and journal entries, including all of the appropriate TR releases to date, for the latest completed month, plus:
    • Check the SOA Source report to ensure that there are no “unclassified” transactions, and that all transaction activity and account assignments are accurate.

TO SET UP THE FORMATTED REPORTS – IN EXCEL

  1. Create an Excel file (workbook) named “ORG Financial Statements [year.mo.day]” – e.g., “ORG FS 2007.10.31” for October 2007 statements.
  2. Include enough worksheets to label tabs for SOP Source Data, SOP, SOA Source Data, SOA Detail, and SOA Summary. (Insert, copy, move and/or label/rename worksheets as needed.) Other custom reports and graph tabs can be added and linked to the source data as well, but these are the basic reports.
  3. Format the SOP Summary, SOA Summary, and SOA by Activity as desired. The SOP Report should include a column for prior year actual, and be disaggregated to show restricted and board designated columns separate from unrestricted, ideally fitting on one page. Create “cross check to zero” formulas outside the print area to compare bottoms and subtotals between source data and report to ensure data accuracy. Also, create cross checks to verify balances in the SOP columns such as Total Assets minus Total Liabilities minus Total Net Assets equaling zero.
  4. The SOA Summary report should include columns for prior year actual, current year-to-date, current year budget, year-end projection, projection vs. budget variance, and notes explaining variances. Prior year and current budget columns should remain stable throughout the fiscal year; formulas for variance should not change once set. It should fit on one page, followed by narrative notes.

TO PRODUCE THE REPORTS FOR THE SOP

  1. Open the Excel file “ORG Financial Statements [year.mo.day]”.
  2. Open the memorized FY0x SOP Source report in QB.
  3. Change the date to the desired month end and refresh if needed.
  4. Send the report to a new Excel worksheet.
  5. Copy all source data from the new worksheet and paste into the SOP Source tab; this becomes your source data linked to the formatted report.
  6. Go to the SOP Summary tab and input formulas in each cell in the year-to-date column linking to the source data cells.
  7. If updating monthly, save the previous file with a new date in the filename and overwrite source data with new month’s QB data to automatically update the report. Adjust balances for unrestricted, designated, and restricted columns as needed and update notes about unusual balances.
  8. Ensure all cross check cells equal zero to confirm data accuracy.

TO PRODUCE THE SOA

  1. Open the Excel file “ORG Financial Statements [year.mo.day]”.
  2. Open the memorized SOA Source report in QB.
  3. Change the end date to the desired month end and refresh if needed.
  4. Ensure that net income in the SOA Source matches the net income in the SOP Source.
  5. Send the report to a new Excel worksheet.
  6. Copy the entire new worksheet contents into the SOA Source Data tab.
  7. Link the SOA report tab formulas to the SOA Source Data tab cells.
  8. If updating monthly, save the file with a new date, replace prior month’s source data with new QB data, and the totals will update automatically. Confirm all cross checks are zero. Update year-end projections and notes for significant variances.

CAVEATS AND OPTIONS

Ensure SOP and SOA reports are for the same date with matching net income totals. When updating source data, confirm that the last data cell matches previous data to avoid link issues. For inactive classes/accounts, move them to the bottom of the list in QB. After changes to the Chart of Accounts or Class list, adjust the source data tab to include new rows/columns and update linked formulas accordingly.

To share reports with the board, copy the SOP and SOA Summary tabs into a new workbook, remove source data tabs, and send as PDF to avoid formula overwriting. When recipients open the file, they can choose to update links; converting to PDF prevents this and preserves static data.

Important Considerations

QuickBooks' default settings do not show zero-balance rows or class columns, which may impact linked reports especially as the year progresses. Display all rows and classes by adjusting settings in QB. Active classes should be moved to the bottom to prevent misalignment after data updates. Regularly verify links by comparing totals and subtotals between source data and formatted reports to maintain accuracy.

References

  • Finkler, S. A., Purtell, R. M., Calabrese, T. D., & Smith, D. L. (2013). Financial Management for Public, Health and Not-for-Profit Organizations. Pearson.
  • Marsh, T., & Fischer, M. (2011). FASB/GASB Recognition and Reporting Differences. Journal of Accounting and Finance, 21-29.
  • Williams, J. R. (1996). Miller GAAP Guide. Harcourt Brace Publishing.
  • Cornia, G. C., Nelson, R. D., & Wilko, A. (2004). Fiscal Planning, Budgeting, and Rebudgeting Using Revenue Semaphores. Public Administration Review, 64(2), 164-177.
  • Finkler, S. A., Purtell, R. M., Calabrese, T. D., & Smith, D. L. (2013). Financial Management for Public, Health and Not-for-Profit Organizations. Pearson.
  • Marsh, T., & Fischer, M. (2011). Recogniton and reporting differences in GAAP and GASB. Journal of Accounting and Finance, 21–29.
  • Williams, J. R. (1996). Miller GAAP Guide. Harcourt Brace College Publishers.
  • Finkler, S. A., Purtell, R. M., Calabrese, T. D., & Smith, D. L. (2013). Financial Management for Public, Health and Not-for-Profit Organizations. Pearson.
  • GASB. (2010). Statement No. 34, Basic Financial Statements—and Management’s Discussion and Analysis—for State and Local Governments. GASB.
  • FASB. (1993). Statement of Financial Accounting Standards No. 117 (FAS-117): Financial Statements of Not-for-Profit Organizations.

Related Assignments