17 Nov Statements via Templates in Excel
Posted at 13:54h
in
Template Example Using Excel
If financial reports are only issued annually for the client, it is probably just as efficient to make the required modifications in Excel each time. For clients that will receive issued financial statements more frequently, it may be worth the time to set up an Excel spreadsheet for issuing the statements as detailed below.
To create an Excel template for issuing financial statements it is easiest to start with one file that contains the financial statements and trial balance. There are more efficient ways to do it in Excel but this way is easy to follow even with limited Excel experience.
- 1. Make the changes within QuickBooks to create financial statement reports that are as close as possible to what will be issued for the Balance Sheet, Profit and Loss and Statement of Cash Flows.
- 2. Send the Balance Sheet report to a new Excel spreadsheet.
- 3. Save the file in Excel and close the Excel program.
- 4. Return to QuickBooks and generate the Income Statement report then send the report to the existing Excel spreadsheet created above.
- 5. Save the Excel file and close the Excel program.
- 6. Return to QuickBooks and generate the Statement of Cash Flows report then send the report to the existing Excel spreadsheet created above.
- 7. Save the Excel file and close the Excel program.
- 8. Return to QuickBooks and generate the Trial Balance report then send the report to the existing Excel spreadsheet created above.
- 9. Save the Excel file.
- 10. To change the names of the sheets on the bottom, right click on the tab then rename the sheet. The name of the sheet will be highlighted so simply type the new name.
- 11. Now it is possible to make the additional changes to the financial statements as appropriate for each individual report.
- 12. Link the trial balance amounts for each account by using formulas.
- 13. Save the file.
- 14. Some CPAs prefer to include the Accountants’ Report and footnotes as additional pages in this file. Others prefer to handle those requirements through Word or some other word processing software.
- 15. When generating financial statements in the future, create a trial balance report in QuickBooks and save it to the same existing Excel file.
- 16. Confirm that the trial balance accounts are the same as in the previous version.
- 17. Copy the new trial balance into the sheet for the old trial balance. This will update the formulas with the new information.