2014 SedonaOffice Users Conference Financial Reporting Using Excel
Marco Island, Florida Presented By: Jim Lee
Page 2 of 34
Table of Contents
Financial Reporting Overview ................................................................................................ 4
Reporting Periods.................................................................................................................................................................... 4
Microsoft Excel ......................................................................................................................................................................... 4
SedonaOffice General Ledger Structure ................................................................................. 5
Invoice Example ....................................................................................................................................................................... 5
General Ledger Account Code ............................................................................................................................................ 6
Branch Code ............................................................................................................................................................................... 6
Category Code ........................................................................................................................................................................... 7
Fiscal Year ................................................................................................................................................................................... 7
Monthly Period ......................................................................................................................................................................... 7
GL Mask ....................................................................................................................................................................................... 7
Extracting the GL Data from SedonaOffice to Excel ................................................................ 8
The GL Summary Table ......................................................................................................................................................... 8
GL Summary Update Wizard............................................................................................................................................... 8
Importing the SedonaOffice GL Data into Excel .................................................................... 11
Understanding the GL Data .................................................................................................. 15
GL Data Elements................................................................................................................................................................... 15
The Zero (0) Period – Balance Sheet Accounts Only ............................................................................................... 16
Retained Earnings Account ............................................................................................................................................... 16
GL Account, Branch and Category Codes .............................................................................. 17
Excel Commands ................................................................................................................. 18
Concatenate ............................................................................................................................................................................. 18
Sumif ........................................................................................................................................................................................... 18
Setting up your “Template” Excel Spreadsheet .................................................................... 19
The Variables Tab .................................................................................................................................................................. 19
Creating a Simple Income Statement ................................................................................... 20
Report Header ......................................................................................................................................................................... 20
Report Data .............................................................................................................................................................................. 20
GL Mask ........................................................................................................................................................................................... 20
Data Selection .............................................................................................................................................................................. 20
GL Data ........................................................................................................................................................................................... 21
Copy and Paste Additional Rows .................................................................................................................................... 21
Summing Data and Format ................................................................................................................................................ 21
Setting the Print Area ........................................................................................................................................................... 24
Adding a Year-to-Date column to the Income Statement ...................................................... 25
The SUMIF for YTD ............................................................................................................................................................... 25
Creating a Balance Sheet ..................................................................................................... 27
Retained Earnings Balance ................................................................................................................................................ 27
Net Income (Loss) for the Current Fiscal Year .......................................................................................................... 27