Financial Reporting Using Microsoft Excel - SedonaOffice
Financial Reporting
Using Microsoft Excel
Presented By:
Jim Lee
2014 SedonaOffice Users Conference
Marco Island, Florida
Financial Reporting Using Excel
Presented By: Jim Lee
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 ¨C 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
Page 2 of 34
2014 SedonaOffice Users Conference
Marco Island, Florida
Financial Reporting Using Excel
Presented By: Jim Lee
Creating a Budget ................................................................................................................ 30
Actual to Budget ¨C Income Statement ........................................................................................................................... 31
Month to Date Budget .............................................................................................................................................................. 31
Yearly Budget ............................................................................................................................................................................... 31
Branch Level Income Statement........................................................................................... 33
Category Level Income Statement........................................................................................ 34
Page 3 of 34
2014 SedonaOffice Users Conference
Marco Island, Florida
Financial Reporting Using Excel
Presented By: Jim Lee
Financial Reporting Overview
Balance sheets, income statements and other financial statements are essential for
understanding a company¡¯s financial status and performance. This guide illustrates the
steps for creating different types of financial statements using the general ledger data from
SedonaOffice.
Reporting Periods
The reports created using the tools described herein are period end reports. Reports can be
run for a complete year, year-to-date, quarterly or monthly periods. They are not meant to
be used for mid-month, weekly or daily financial reports.
Microsoft Excel
All the reports as reviewed within this guide are created using Microsoft Excel. For
purposes of this guide Microsoft Excel 2007 has been used. Excel 2007 or higher is highly
recommended as it has expanded capabilities to handle over one million rows of data.
Page 4 of 34
2014 SedonaOffice Users Conference
Marco Island, Florida
Financial Reporting Using Excel
Presented By: Jim Lee
SedonaOffice General Ledger Structure
Before we begin, let¡¯s review the SedonaOffice General Ledger Structure. Understanding of
this and how transactions are created in SedonaOffice is essential to create useful financial
reports.
Let¡¯s start off by looking at the creation of a general ledger transaction. Remember there
are many different ways a general ledger transaction is created in SedonaOffice, but each
has the same characteristics upon completion.
Invoice Example
This is a simple service invoice.
Below is the journal information for the invoice created.
Page 5 of 34
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- business plan
- startup costs worksheet
- business plan template complete fill in the blanks sample
- simple project evaluation spreadsheet model world bank
- business planning and financial
- growthink s business plan guide farmingdale state college
- financial statement samples and templates
- financial reporting using microsoft excel sedonaoffice
- how to prepare a financial forecast jumpstart inc
Related searches
- financial reporting best practices
- advanced financial reporting acca
- financial reporting in healthcare finance
- examples of financial reporting practices
- financial reporting system examples
- types of financial reporting systems
- financial reporting systems in healthcare
- financial reporting package
- acca financial reporting questions pdf
- advanced financial reporting notes pdf
- advanced financial reporting pdf
- financial reporting book pdf