Daily Financial Report Template
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
- annual report template
- annual report format navajo nation
- fy 2020 2021 network adequacy report colorado
- simple annual report template word
- ms word document template unfccc
- annual research progress report and individual development
- fire extinguisher monthly inspection form
- p hot o e l l i e van hout t e us a i d b ure a u f o r
- annual progress report sample
- mact nsps technical resources and permit templates
Related searches
- monthly financial report template free
- monthly financial report template word
- financial report template samples
- financial report template word
- financial report template word document
- financial report template pdf
- financial report template download
- simple financial report template free
- annual financial report template free
- financial report template free
- quarterly financial report template excel
- annual financial report template word