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 ? 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 ? 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
- simple project evaluation spreadsheet model world bank
- advanced modelling in finance using excel and vba free
- financial analyst cv template
- financial analysis cs sample reports
- 3 financial analysis and appraisal of projects
- dupont analysis template excel
- financial reporting using microsoft excel sedonaoffice
- financial needs analysis v2 04
- 7 financial analysis and interpretation
- manual process capability analysis excel template
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