financial statements done right, in less time
DESCRIPTION
TRANSCRIPT
Financial Statements Done Right, in Less Time
Copyright
Copyright 2009 Intuit, Inc. Intuit, Inc.
All rights reserved. 5601 Headquarters Drive
Plano, TX 75024
Trademarks
©2009 Intuit Inc. All rights reserved. Intuit, the Intuit logo, Intuit ProLine, Lacerte, ProAdvisor,
ProSeries and QuickBooks, among others, are trademarks or registered trademarks of Intuit Inc.
in the United States and other countries. Other parties’ marks are the property of their respective
owners.
Notice To Readers
The publications distributed by Intuit, Inc. are intended to assist accounting professionals in their
practices by providing current and accurate information. However, no assurance is given that the
information is comprehensive in its coverage or that it is suitable in dealing with a client’s
particular situation. Accordingly, the information provided should not be relied upon as a
substitute for independent research. Intuit, Inc. does not render any accounting, legal, or other
professional advice nor does it have any responsibility for updating or revising any information
presented herein. Intuit, Inc. cannot warrant that the material contained herein will continue to be
accurate nor that it is completely free of errors when published. Readers should verify statements
before relying on them.
Financial Statements Done Right, in Less Time
1
TABLE OF CONTENTS
About the Author ......................................................................................................................................... 3
How to Use this Guide ................................................................................................................................ 4
Introduction ................................................................................................................................................. 5
Learning Objectives .................................................................................................................................. 5
About this Course ..................................................................................................................................... 5
New Features for 2010 ................................................................................................................................ 7
The Basics of Using the Intuit Statement Writer.................................................................................... 17
Accessing Intuit Statement Writer for the First Time .............................................................................. 17
Creating a Financial Statement .............................................................................................................. 20
Overview of the Document Actions Panel .............................................................................................. 23
Report Properties ............................................................................................................................... 23
Statement Properties .......................................................................................................................... 24
Row Properties ................................................................................................................................... 25
Column Properties .............................................................................................................................. 26
Cell Properties .................................................................................................................................... 27
Editing the Appearance of Financial Statements ................................................................................... 27
Setting Report Preferences .................................................................................................................... 29
Using Intuit Statement Writer Help ......................................................................................................... 34
Saving Customized Intuit Statement Writer Templates .......................................................................... 34
Designing and Customizing Financial Statements ............................................................................... 35
Managing Accounts on Financial Reports .............................................................................................. 35
QuickZoom from Intuit Statement Writer to the QuickBooks General Ledger ....................................... 39
Adding Columns to Financial Statements............................................................................................... 40
Adding Calculating Columns to Financial Statements ............................................................................ 41
Adding a % of the Whole Column to a Financial Statement .............................................................. 41
Adding a Variance Column to a Financial Statement ......................................................................... 43
Adding Additional Columns with Summary Total and Grand Total Columns
to a Financial Statement .................................................................................................................... 44
Filtering for Class or Customer:Job Columns on the Income Statement ........................................... 48
Attaching Microsoft Word Documents to a Reporting Package in Intuit Statement Writer 2010 ... 51
Financial Statements Done Right, in Less Time
2
Appendix – Advanced Reports and Graphs Created in Intuit Statement Writer 2010 ....................... 59
Ratio Analysis ......................................................................................................................................... 59
Charts and Graphs ................................................................................................................................. 60
Cash Flow Statement by Month ............................................................................................................. 61
Balance Sheet For Multiple Years .......................................................................................................... 62
Retained Earnings Statement ................................................................................................................. 63
Budget Versus Actual Report ................................................................................................................. 64
Operating Expenses by Month ............................................................................................................... 65
Financial Statements Done Right, in Less Time
3
ABOUT THE AUTHOR
Amy Vetter, CPA.CITP, MBA leads the Florida office for Technology In A Box as Executive Director and is
President of AV Accounting Services, Inc. Amy specializes in operational accounting and finance support,
including QuickBooks and third party application set-up, implementation, training and trouble-shooting, as
well as fractional controllership and bookkeeping services.
Amy's public accounting experience includes tenure with KPMG, LLP, as well as, Fortune 500
corporations. Amy is an accomplished author of QuickBooks reference material and education provider,
having served as an Adjunct Professor and Guest Speaker for many universities and organizations. Amy
is a national and local speaker on QuickBooks training topics for consultants and software application
users, in addition to being a contributor and speaker for Intuit’s courses for Certification and Training of
QuickBooks ProAdvisors.
Amy earned a Masters of Business Administration degree from Nova Southeastern University and a
Bachelor of Business Administration degree in accounting from the University of Cincinnati. She is a
licensed Certified Public Accountant in the State of Florida, Certified Information Technology Professional
(CITP) awarded by the AICPA, Advanced Certified QuickBooks® ProAdvisor, Certified QuickBooks
®
Enterprise and POS ProAdvisor, and a Sleeter Group Certified Consultant. Amy is a member of the Intuit
Trainer/Writer Network where she is one of the few trainers from around the country to achieve Intuit's
"Tier 3" instructor status and present for Intuit at various accounting technology and user conferences
throughout the United States.
Amy has been a participant in the Accountant and Advisor Customer Council for Intuit, Inc. during 2006-
2008 and recognized by The CPA Technology Advisor as an outstanding 40 under 40 technology-based
accountant in 2006 and 2009. In 2007, she won the South Florida Business Journal’s Up & Comer award
for her expertise in accounting and dedication to the community. Amy is the Expert Contributor on All
Things QuickBooks for The Progressive Accountant, a premier digital resource for tax and accounting
professionals, serves on the AICPA’s Journal of Accountancy Technology Advisory Panel and Intuit,
Inc.’s Solution Provider Advisor Council for 2009-2011. Amy is a member of the Florida Institute of
Certified Public Accountants, the American Women’s Society of CPA’s and the American Institute of
Certified Public Accountants.
* Regulated by the State of Florida
Amy E Vetter, CPA*.CITP
Executive Director, Technology In A Box
President. AV Accounting Services, Inc.
Ft. Lauderdale, FL
www.technologyinabox.net
Financial Statements Done Right, in Less Time
4
HOW TO USE THIS GUIDE
These reference materials are designed to complement the information presented by the instructor and to
act as a stand-alone training guide.
Financial Statements Done Right, in Less Time
5
INTRODUCTION
LEARNING OBJECTIVES
In this course you will learn how to:
• Combine, re-order, and batch-print multiple financial statements in one workbook
• Create new financial reports added in 2010 such as:
o The Cash Flow Statement
o Retained Earnings
o Expanded Date Capability on Financial Reports – cross fiscal years, 13-week quarters, and
4-week months available!
o Filter reporting by Class and Customer:Jobs
• Generate Word document templates and attach documents to a financial reporting package
• Easily insert rows, columns, subtotals, and grand totals in Microsoft Excel templates created in
Intuit Statement Writer
• Consolidate financial reporting and create as a PDF document package for your client
• Analyze financial reporting created in the Intuit Statement Writer by creating charts, graphs, and
ratio analyses that refresh at a click of a button!
ABOUT THIS COURSE
This course will cover the core functionality of Intuit Statement Writer and the new and improved features
in 2010 that allow you to present financial statements in compliance with GAAP. By using the Intuit
Statement Writer you will also be able to create financial and management reports that are not possible
within the QuickBooks Financial Software. Impress your clients with charts, graphs, and ratio analyses
that feed directly from Intuit Statement Writer so you can create them once and simply refresh the data
each reporting period.
Intuit Statement Writer is included in all editions of QuickBooks Enterprise Solutions and available for an
additional fee in QuickBooks Premier: Accountant Edition.
Financial Statements Done Right, in Less Time
6
Financial Statements Done Right, in Less Time
7
NEW FEATURES FOR 2010
Intuit Statement Writer (ISW) uses Microsoft Excel 20031 and 2007 as its platform to generate
professional and customized financial statements for you and your clients. In 2010, the following features
were improved or added:
� New financial statement templates were added such as the Cash Flow and Retained Earnings
statements
� You now have the ability to work with up to 16 statements in one Microsoft Excel Workbook
� Documents such as cover letters and compilation reports created in Intuit Statement Writer using
Microsoft Word2can be attached to a Microsoft Excel Workbook
� Prepare financial statements for any arbitrary date range such as 4-week months, 13-week
quarters, or crossing fiscal periods
� Entire financial statements can be filtered by Class and/or Customer:Job
� Combine more than one class in one column
� Combine QuickBooks accounts by Account Name, Account Type, or Account Range
� Refresh one statement or the whole Microsoft Excel Workbook
� Microsoft Excel worksheet rows are unlocked to allow for insertion of rows
� Create Summary Total and Grand Total columns
� Customize Headers and/or Footers to meet firm or client-specific needs
� Change a variety of preferences including:
o Show/hide inactive accounts
o Show/hide decimals
o Divide balances by 1,000, 100, or 10
o Show/hide and format zero balances
o Save print order
o Change document order
� Run ISW Diagnostics to identify any potential issues and resolutions
� Create charts and graphs within the Intuit Statement Writer template and refresh data as needed
� Easily generate PDF3 reports containing your Microsoft Excel financial statements and Microsoft
Word documents
1 Does not work with Microsoft Office Student and Standard Editions.
2 Requires Microsoft Office 2003 or greater. Does not work with Microsoft Office Student or Standard Edition.
3 With Windows XP or Vista, Adobe Acrobat 5 or greater required; with Windows 7, Adobe Acrobat 9 required.
Financial Statements Done Right, in Less Time
8
ISW allows you to create a new report from a list of templates or open an existing report template you
created previously to use again. To choose a report from a list of templates available in ISW, choose
Create New Report.
Financial Statements Done Right, in Less Time
9
The list of ISW templates includes the new reporting available in 2010 and also allows you to select
multiple templates to include in the Microsoft Excel Workbook. Additionally, Word templates can be
attached to a workbook for letters such as cover letters, compilations and reviews.
Financial Statements Done Right, in Less Time
10
ISW reports are not limited to the fiscal period in the QuickBooks file. You are able to filter the reporting
by any time period including 4-week months, 13-week quarters, and crossing fiscal periods.
Financial Statements Done Right, in Less Time
11
You are able to filter all of the reports by Class or Customer:Job. Additionally, you can filter one report at
a time and combine more than one Class or Customer:Job in one column.
Accounts from the Chart of Accounts in the associated QuickBooks file can be combined on any row of a
financial statement by Account Name, Account Type, or Account Number in ISW.
Financial Statements Done Right, in Less Time
12
Each time a template is changed with any formatting or amounts are changed in QuickBooks, the
financial statements can be refreshed from ISW. Now in 2010, with the new feature to export multiple
templates to one Microsoft Excel Workbook, you have the option to refresh the data for the whole
workbook, or just one report at a time.
In 2010, the ISW templates are no longer protected. Therefore, rows and columns can be inserted into
the Microsoft Excel spreadsheet and formulas can be manually entered.
Financial Statements Done Right, in Less Time
13
Summary Total and Grand Total columns can be inserted into an ISW template using the ISW Sidebar.
This allows you to provide a total for a group of columns with the Grand Total or insert a Summary Total
column to create a 2-column statement.
Financial Statements Done Right, in Less Time
14
Customize the Headers and Footers in the ISW reporting by selecting the Statement Writer menu and
choose Preferences…
ISW reporting can easily be formatted in the Intuit Statement Writer Preferences as well. The reports can
be filtered to show or not show inactive accounts, decimals, whether or not to show zero balances, how to
present negative values on reports, and whether to divide the data by 10, 100, or 1,000.
Financial Statements Done Right, in Less Time
15
If you are having any trouble opening ISW or any of the reporting, ISW Diagnostics can be run to identify
any components that may be missing on a particular computer system.
Report packages can be created as a PDF by selecting the Statement Writer menu and choosing Save
as PDF. Reports can be selected from the workbook to issue. The order of the reports can be changed
based on how the reporting should be copied or printed.
Financial Statements Done Right, in Less Time
16
Financial Statements Done Right, in Less Time
17
THE BASICS OF USING THE INTUIT STATEMENT WRITER
Intuit Statement Writer is included in all editions of QuickBooks Enterprise Solutions and available for an
additional fee in QuickBooks Premier: Accountant Edition. Intuit designed ISW for three primary
purposes:
• To empower accounting professionals to present financial statements to their clients that comply
with Generally Accepted Accounting Principles (GAAP)
• To empower QuickBooks users with additional reporting options when preparing account-based
reports (i.e., Balance Sheet, Profit & Loss, Cash Flow Statement, and budget reports)
• To reduce data entry transposition errors and/or version control issues with reporting. In the past,
many financial reports were exported from QuickBooks to Microsoft Excel to create financial
statement reporting packages. Rather than having to export data and manually update figures,
ISW will allow you to refresh data directly from QuickBooks when you are using ISW in Microsoft
Excel.
ACCESSING INTUIT STATEMENT WRITER FOR THE FIRST TIME
Perform the following steps to open Intuit Statement Writer:
1. Select Intuit Statement Writer from the Reports or Accountant drop-down menu in QuickBooks.
NOTE: A 30-Day Free Trial of ISW is included in QuickBooks Premier Accountant Edition 2010. After the 30-
Day Free Trial is over, ISW can be purchased for an additional fee to continue to utilize the feature.
QuickBooks Enterprise includes ISW at no additional cost.
Financial Statements Done Right, in Less Time
18
2. When you open ISW the first time, QuickBooks will display the window shown below. Select Yes,
whenever this QuickBooks company file is open then click Continue...
3. Click Yes to allow ISW to have access to the client’s QuickBooks file.
Financial Statements Done Right, in Less Time
19
4. Click Done to continue to Intuit Statement Writer.
Financial Statements Done Right, in Less Time
20
CREATING A FINANCIAL STATEMENT
ISW displays the window shown below prompting you to create a new statement from one of the number
of available templates, or to open an existing statement that you previously created and saved.
1. Click Create New Report to choose from a pre-populated list of financial report templates that
can be used to create a customized financial statement from your QuickBooks data.
Financial Statements Done Right, in Less Time
21
2. In the New Financial Report window shown on the following screen shot you will select the
Report Date you want to use to generate financial statements.
3. Next, you will select the reports from the list of report templates in the Statement and Document
Templates section of the window. You have the option to pick one statement only or select
multiple statements and create a workbook.
4. After you select the statement(s) or document(s) you want to create, select the arrow key to copy
it over to the Financial Report Contents.
5. Enter the name of the financial report package in the Report Name field. When finished,
select OK.
Financial Statements Done Right, in Less Time
22
Intuit Statement Writer will open the Microsoft Excel Workbook and import the appropriate data from your
QuickBooks file as shown in the screen shot below.
The default templates are created to be in accordance with GAAP rather than the typical formatting used
for QuickBooks reports. For example, there is a single Current Assets section that includes all of the
current asset account types that QuickBooks uses: Bank, A/R, and Other Current Asset. Once the
templates are opened, you will be able to edit the design of the default template to meet your firm’s
specific reporting preferences and/or bring the statement format into tighter compliance with GAAP.
Financial Statements Done Right, in Less Time
23
OVERVIEW OF THE DOCUMENT ACTIONS PANEL
Use the Document Actions panel on the right side of the spreadsheet to edit Report, Statement, Row,
Column, and Cell Properties as described in this section.
Report Properties
To change properties for the entire workbook, select the Report Properties tab. This area allows you to
change the date of all the reports in the workbook, to edit the overall statement appearance/layout, to
change the basis of the statement between the accrual and cash basis, refresh the QuickBooks data for
the entire workbook and to attach additional Microsoft Word documents to the workbook. If you change
data in the QuickBooks data file, the changes will not automatically update Intuit Statement Writer. Click
the Refresh from QuickBooks button to update the workbook with any changes you made to
QuickBooks since opening ISW.
Financial Statements Done Right, in Less Time
24
Statement Properties
To change properties for just one statement, rather than the whole workbook, select the Statement
Properties tab while in the report you want to update. This area allows you to change the date of the one
report that is selected, change the title of the report, and filter the report for Class and/or Customer:Job.
Additionally, you are able to refresh the QuickBooks data for just one statement at a time or delete the
statement from an entire workbook.
Financial Statements Done Right, in Less Time
25
Row Properties
To change the properties for any given row or rows, select the row(s) and then click the Row Properties
tab on the Document Actions panel. If you select a single row as shown on the following screen shot for
the Money Market account, the Row Properties tab allows you to change the label (name) of the
row/account, to delete the selected row, or to add a new row above the selected row. When inserting a
row, you have the option to insert a blank row, accounts from QuickBooks, totals, or column headers. You
are also able to combine accounts in a row or separate accounts out, format zero balances, and reverse
a positive balance to a negative balance, or vice versa, using Manage Accounts.
Financial Statements Done Right, in Less Time
26
Column Properties
When you select a column and click the Column Properties tab, Intuit Statement Writer allows you to
change the type of data contained in the column or insert a new column with the option to insert a blank
column, QuickBooks Data, Variance, % of the Whole, a Summary Total, or Grand Total. You can also use
the Column Properties tab to change the date of the QuickBooks data for the selected column only (not
the entire statement) or to filter the QuickBooks data in the selected column by Class or Customer:Job.
You can also delete a column from the statement as well.
NOTE: You can add columns for each class on a Balance Sheet template. However, doing so will display
limited information for each class. For example, if you write a check in QuickBooks, the credit (reduction) in
cash is not assigned to a class. If you debit or credit a bank account on a journal entry and assign a class to the
bank account line, that information is assigned to a class. Using the class columns in ISW does not provide
you with any additional class tracking benefit as compared to internal QuickBooks reports, so the
disbursement to cash in the example above would not affect the column that is filtered by class and the journal
entry would affect the column.
Financial Statements Done Right, in Less Time
27
Cell Properties
ISW allows you to dive even deeper to control the data on a report by giving you access to change just
one cell’s data. When you select a cell and click the Cell Properties tab, ISW allows you to change the
date of the data in the cell or reset it back to the original date. You can also use the Cell Properties tab to
insert a new field into the spreadsheet, such as client or accountant information, change the basis of
accounting information in a cell, and format the statement date.
EDITING THE APPEARANCE OF FINANCIAL STATEMENTS
ISW allows you to format the report templates within the financial reporting package so that the same
report appearances can be applied to any report you create in ISW.
1. From the Report Properties tab, choose Edit Appearance.
Financial Statements Done Right, in Less Time
28
2. Select the Current Appearance to select one of the saved overall appearances created
previously for the report (e.g., fonts, indentions, header and footer placement, etc.) or create a
new appearance to save it for a future report.
3. To edit individual components of an appearance template, select the cell and change the font, the
font size, and/or the indention.
Financial Statements Done Right, in Less Time
29
SETTING REPORT PREFERENCES
1. To set preferences for the overall financial statements, choose the Intuit Statement Writer menu
at the top of the Document Actions panel, then select Preferences…
Financial Statements Done Right, in Less Time
30
2. Select General Preferences in the Intuit Statement Writer Preferences window to change where
an ISW report template is saved on your computer or on a server. Additionally, to find out more
information on how to use ISW in a server environment, select the link in this window and run
ISW Diagnostics to identify any errors that may be occurring when trying to use ISW.
Financial Statements Done Right, in Less Time
31
3. Select Formatting in the Intuit Statement Writer Preferences window to create overall style
changes to the entire financial statement workbook. Options that can be chosen are items such
as deciding on whether or not to show decimals or inactive accounts on a financial statement,
how to present zero balances and negative numbers, and whether to show amounts in full or
divided by a multiple of 10, 100, or 1,000 on your financial statements.
Financial Statements Done Right, in Less Time
32
4. Select the Accountant Information option in the Intuit Statement Writer Preferences window to
enter information about your accounting firm. ISW will store this information for use on cover
sheets, letters and/or reports for your clients as part of a financial statement package.
5. Select the Header/Footer option in the Intuit Statement Writer Preferences window to modify,
change, add, or remove what will appear on reporting for Title, Headers, and Footers.
Financial Statements Done Right, in Less Time
33
6. Place your cursor in the cell in the report you want the Accountant Information entered into.
Select Cell Properties, then Insert Fields to insert four types of information into the financial
statement report template: 1) Accountant Information, 2) Client Information, 3) the report Basis
(accrual or cash), 4) Statement Date Information.
Financial Statements Done Right, in Less Time
34
USING INTUIT STATEMENT WRITER HELP
There are resources available to you 24 hours a day to help you work with ISW. Choose Resources from
the Intuit Statement Writer Preferences to find links for asking questions and getting answers, finding the
ISW Support Center, downloading 30+ templates that have already been created for your use to import
into your ISW, and more.
NOTE: For issues such as using ISW when your My Documents has been redirected to a server, use the
Resources center to search that topic and related topics to resolve those issues.
SAVING CUSTOMIZED INTUIT STATEMENT WRITER TEMPLATES
When you finish all edits to a financial statement template, click the Save Report button from the
Statement Writer drop-down menu on the Document Actions panel. You can save templates in any folder
on your computer or network – allowing other users in your company to easily access the template. You
can also email and upload templates to Intuit if you would like to share your format with the larger
QuickBooks user and accountant communities.
NOTE: Saving and opening templates is as easy as saving and opening Excel worksheets so this guide does
not provide any step-by-step processes.
Financial Statements Done Right, in Less Time
35
DESIGNING AND CUSTOMIZING FINANCIAL STATEMENTS
MANAGING ACCOUNTS ON FINANCIAL REPORTS
Intuit Statement Writer allows you to combine multiple General Ledger account balances as a single row
on a financial report. When you first open a default statement template, all sub-accounts are automatically
combined into their parent accounts. Each account group that is combined will have a red notation mark
in the cell. Hover your mouse over the notation to see which accounts are combined.
To combine accounts in a row, follow the steps below:
1. Select each row you want to combine as one row.
Financial Statements Done Right, in Less Time
36
2. Select the Manage Accounts button in the Row Properties section of the Document Actions
panel, then select Combine accounts from selected rows.
Financial Statements Done Right, in Less Time
37
3. ISW will consolidate all of the accounts in one line. You also have the option to rename the row
as well. In the example below, the Money Market account was renamed to Cash & Cash
Equivalents once all the accounts were combined.
4. Undeposited funds as of the statement date also represents cash on hand. When combining cash
accounts on the Balance Sheet you should also include the Undeposited Funds account. Select
Manage Accounts, then Add accounts, then By name.
Financial Statements Done Right, in Less Time
38
5. Select Undeposited Funds from the Add Accounts by Name window, then Add.
NOTE: You can also add accounts to a row by Account Type or Account Number. This allows you to combine
accounts by selecting a range of account numbers or an entire account type grouping.
6. If you want to separate the accounts back out into separate lines or remove a single account from
the combined row, select the consolidated row (e.g., Cash & Cash Equivalents) and select the
specific account you want to break out. Select Manage Accounts, then Separate accounts.
Financial Statements Done Right, in Less Time
39
QUICKZOOM FROM INTUIT STATEMENT WRITER
TO THE QUICKBOOKS GENERAL LEDGER
Intuit Statement Writer allows you to QuickZoom from the balance on the report template to the
QuickBooks General Ledger detail. If you drill down on a consolidated balance like the one shown below,
QuickBooks displays General Ledger detail for each of the accounts included in the combined row.
Perform the following steps to QuickZoom from Intuit Statement Writer into the QuickBooks General
Ledger.
1. Click the account balance on the ISW report template to obtain the QuickBooks detail, as shown
below.
2. Select the Statement Writer menu, then Zoom into QuickBooks in the Document Actions
panel.
NOTE: QuickBooks shows the General Ledger for the Checking, Savings, Money Market, Cash Expenditures,
and Undeposited Funds accounts – all accounts are included into the combined row.
Financial Statements Done Right, in Less Time
40
ADDING COLUMNS TO FINANCIAL STATEMENTS
Intuit Statement Writer allows you to add as many columns as you want to your financial statements. By
using ISW, you are not limited to the comparison and variance columns in QuickBooks reports. Examples
of column information you can add to the financial statements include:
• Accounts from the QuickBooks Chart of Accounts
• Blank columns
• Variances expressed as percentages or dollars
• % of the Whole
• QuickBooks Data (account balances)
• Summary Total – summarize a group of columns
• Grand Total – summarizes the Summary Total columns
Financial Statements Done Right, in Less Time
41
ADDING CALCULATING COLUMNS TO FINANCIAL STATEMENTS
In Intuit Statement Writer there are 4 types of calculating columns you can add to a financial statement:
• % of the Whole (i.e. % of Column)
• Variances expressed as either a percentage or amount
• Summary Total
• Grand Total
Adding a % of the Whole Column to a Financial Statement
To add a % of the Whole column to a financial statement, perform the following:
1. Select a cell in a column on an ISW template that you want to place a % of the Whole column in.
% of the Whole columns are used to calculate an account balance as a percent of a total value
such as Total Assets or Total Revenue.
2. Choose Insert Column(s) from the Column Properties tab on the Document Actions panel, then
choose % of the Whole.
Financial Statements Done Right, in Less Time
42
3. The financial statement will now have a % of the Whole column inserted in the spreadsheet.
Select column B from the QuickBooks Data: field to choose which column to calculate the
percentage from.
4. The financial report template will now have a % of the Whole column inserted for the Balance
Sheet as seen on the screen shot below. The spreadsheet is unprotected in ISW, which allows
you to change individual cells or change the header title for a column as well if you do not like the
default.
Financial Statements Done Right, in Less Time
43
Adding a Variance Column to a Financial Statement
To add a Variance column to a financial statement, perform the following:
1. Select a cell in a column on an ISW template that you want to place a Variance column in.
Variance columns can be expressed as a percent change or dollar change between 2 columns.
2. Choose Insert Column(s) from the Column Properties tab on the Document Actions panel, then
choose Variance.
3. The financial statement will now have a Variance column inserted in the spreadsheet. You can
select whether you want the Variance column to be a Percent (%) or Amount. For this example,
select Percent (%). It is also important to select which columns you want to calculate the variance
for. In the example below, column C will be subtracted from column B.
Financial Statements Done Right, in Less Time
44
4. The financial report template will now have a Variance column as a percent difference between
2007 and 2006 seen on the screen shot below. The spreadsheet is unprotected in ISW, which
allows you to change individual cells or change the header title for a column as well if you do not
like the default title.
Adding Additional Columns with Summary Total and Grand Total Columns
to a Financial Statement
One of the advantages of using ISW is that it has more reporting flexibility than using QuickBooks reports.
You have the ability to add additional columns to a financial statement and add subtotals as necessary. In
the following example, a Cash Flow Statement was selected from the ISW template list.
1. In the Column Properties tab on the Document Actions panel, choose the time period of the data
in the Period field as Specific Month, then select January from the Show data from: field.
Financial Statements Done Right, in Less Time
45
2. Repeat these steps in each column for the entire year to create a cash flow statement by month.
See the screen shot below.
NOTE: The date ranges are flexible when choosing a time period for any report, column, or cell in a financial
statement report. You can choose 4-week months, 13-week quarters, and cross fiscal periods. The following
options are available as seen in the following screen shot:
Financial Statements Done Right, in Less Time
46
3. If you are interested in subtotaling a group of columns, you will insert a Grand Total column. To
create a Grand Total for the first quarter, select Insert Column(s) from the Column Properties tab
on the Document Actions panel, choose Grand Total.
4. This will create a column adding up the columns preceding the Grand Total for January,
February, and March.
Financial Statements Done Right, in Less Time
47
5. To create a 2-column statement, a Summary Total can be used. For a Balance Sheet report,
place your cursor in the column after the year or time period, then choose Insert Column(s) from
the Column Properties tab on the Document Actions panel, then choose Summary Total.
6. The financial statement will now have a Summary Total column inserted in the spreadsheet.
Financial Statements Done Right, in Less Time
48
Filtering for Class or Customer:Job Columns on the Income Statement
Intuit Statement Writer allows you to filter a report by Class or Customer:Job. You are also able to filter a
column by Class or Customer:Job or combine multiple classes or customers in one column. In the
following example, an Income Statement was selected from the ISW template list.
1. In the Column Properties tab on the Document Actions panel, choose the Class field and select
as many classes as you want to filter the column for; or choose the Job field and select as many
customers as you want to filter the column for.
Financial Statements Done Right, in Less Time
49
2. The financial statements will now have a Class column filtered for New Construction and
Remodel inserted in the spreadsheet and a Job column filtered for the Abercrombie and Baker
jobs.
NOTE: The spreadsheet is unprotected in ISW, which allows you to change individual cells or change the
header title for a column as well if you do not like the default title used in QuickBooks. You have the flexibility
in ISW to call any cell or column by the title that is most appropriate.
3. In order to refresh the data that is shown on the screen shot, you have the option to refresh the
data for the whole workbook or just the report you are on. To refresh the data for the entire
workbook, select the Report Properties tab from the Document Actions panel, then Refresh
from QuickBooks to update the data with balances from QuickBooks.
Financial Statements Done Right, in Less Time
50
4. To refresh the data for just the statement you are on and not the whole workbook, select the
Statement Properties tab from the Document Actions panel, then Refresh from QuickBooks to
update the data in the current statement with balances from QuickBooks.
NOTE: The Statement Properties tab enables you to change the Title of this statement as well.
Financial Statements Done Right, in Less Time
51
ATTACHING MICROSOFT WORD DOCUMENTS TO A REPORTING PACKAGE IN INTUIT STATEMENT WRITER 2010
Intuit Statement Writer allows you to create and modify supporting documents (e.g., title page, cover
letter, and compilation report). The application includes numerous templates you can use without any
modification or that you can use as a starting point to create a list of customized document templates for
your firm.
These templates are formatted for Microsoft Word 2003 and 2007. You will use Microsoft Word to create
and/or modify supporting documents.
When you customize the templates you can insert two different types of data into the documents:
• Hard text: This is text that you type into the document using Word. This text will not change as
you create supporting schedules for different clients and different financial periods. The text
below is an example of “hard text” you might use in your document.
“We conducted our audit in accordance with auditing standards generally
accepted in the United States of America. Those standards require that we plan
and perform the audit to obtain reasonable assurance about whether the financial
statements are free of material misstatement. An audit includes examining, on a
test basis, evidence supporting the amounts and disclosures in the financial
statements.”
• Dynamic/Merged Text: This information in the document changes. When you open the
supporting document, it includes the current ISW firm information, client contact information,
reporting period, etc. When you are creating or editing the supporting document templates, this
data will show in double carets (i.e., «Accountant_Firm_Name»). If you edit the name of your firm
in ISW (e.g., due to a merger with another firm), all supporting documents will automatically
reflect the update when you print financial statements for your clients. The fields that are available
as dynamic fields in Intuit Statement Writer are the following:
Client’s Company Name Client’s ZIP Client’s Legal State
Client’s Address1 Client’s Legal Company Name Client’s Legal ZIP
Client’s Address2 Client’s Legal Address1 Client’s Phone
Client’s City Client’s Legal Address2 Client’s Fax
Client’s State Client’s Legal City Client’s Email
Client’s Website Client’s Federal EIN Client’s SSN
Client’s Company Type Accountant’s Firm Name Accountant’s Address1
Accountant’s Address2 Accountant’s City Accountant’s State
Accountant’s ZIP Accountant’s Phone Accountant’s Fax
Accountant’s Email Accountant Website Accountant’s Federal EIN
Accountant’s Name Accountant’s Initials Report Date
Months in Period Current Date
Financial Statements Done Right, in Less Time
52
Perform the following steps to use an existing document template in Intuit Statement Writer 2010:
1. Select a Microsoft Word template from the New Financial Report window when opening ISW
from QuickBooks, or select a Microsoft Word template from within ISW by choosing the
Statement Writer menu from the Document Actions panel, then Insert Documents, From
Templates...
2. Select a statement such as the Accrual Basis Compilation Report with Disclosures, then
, and click OK.
Financial Statements Done Right, in Less Time
53
3. After you have selected all the Word documents you want to include in your workbook, select
Documents… from the Report Properties section of the Document Actions panel to open up the
template. Select the document and then choose Open. ISW opens Microsoft Word and displays
the document(s) you selected.
4. The template will include hard text and dynamic fields from the QuickBooks file and Accountant
Information you entered in Intuit Statement Writer.
Financial Statements Done Right, in Less Time
54
5. Edit and/or add all non-merge code data (i.e., hard text) as necessary. You can edit this hard text
without limitation. Do not edit any merge fields during this step.
6. Add and/or remove merge field data as necessary. When working with existing merge fields you
can remove the entire field from the document or you can insert new fields into the document.
NOTE: You cannot edit the merge field data by typing the change as you can with hard text – for example to
change the case, font, etc.
7. To insert new merge fields into the document (and to select the case, font and other formats for
the merged data) place your cursor where you want the field to appear, click the Add-Ins tab,
select the field you want to insert from the drop-down menu, then choose Insert Field.
Financial Statements Done Right, in Less Time
55
8. To save the changed template, select the Add-Ins tab, then the Save As icon. Save the
document as a Word template as shown below, then select OK. Word requires you to save the
template as a unique file name – which is the preference so you can preserve the original formats
that came with ISW. It is best to save the template in a folder to which all professionals in your
practice have access (e.g., an administrative folder on your file server).
9. The dynamic fields in the Microsoft Word document do not update until you are ready to print or
create the report as a PDF document. To create as a PDF, go back to your Document Actions
panel in Microsoft Excel, select the Statement Writer menu, Save As, then PDF.
Financial Statements Done Right, in Less Time
56
10. Select the Accrual Basis Compilation Report with Disclosures from the Create PDF window,
highlight the report in the PDF Contents section and then choose Create.
Save the report in the file folder of your choosing, name the file and select Save as shown below.
Financial Statements Done Right, in Less Time
57
11. The PDF document will now update the dynamic fields that are inserted into the Microsoft Word
template as shown on the screen shot below.
NOTE: At the time this book was being written, you were not able to view the values for these dynamic fields
until a Microsoft Word document had been printed or saved as a PDF.
Financial Statements Done Right, in Less Time
58
Financial Statements Done Right, in Less Time
59
APPENDIX – ADVANCED REPORTS AND GRAPHS CREATED IN INTUIT STATEMENT WRITER 2010
The Intuit Statement Writer 2010 will allow you to create reporting that you are unable to create in
QuickBooks Financial Software. Included in this section are some examples of reports you can create in
Intuit Statement Writer 2010.
RATIO ANALYSIS
All in one report you can combine Balance Sheet and Income Statement accounts. You can calculate
variances between quarters and years as well as ratios. See the screen shot below.
CHARTS AND GRAPHS
Charts and graphs can be created directly from Intuit Statement Writ
the data from QuickBooks on any report, the charts and graphs that are linked to them update as well.
Below are some examples of charts that can be created using Microsoft Excel linking to Intuit Statement
Writer data.
0.00
100,000.00
200,000.00
300,000.00
12
Do
lla
rs
Current Assets v Current Liabilities
0
5
10
15
20
25
Current Ratio By Quarter
Financial Statements Done Right, in Less Time
60
Charts and graphs can be created directly from Intuit Statement Writer 2010 templates. When you refresh
the data from QuickBooks on any report, the charts and graphs that are linked to them update as well.
Below are some examples of charts that can be created using Microsoft Excel linking to Intuit Statement
34
Quarter
Current Assets v Current Liabilities
Current
Assets
Current
Liabilities
Current Ratio By Quarter
Qtr 1
Qtr 2
Qtr 3
Qtr 4
er 2010 templates. When you refresh
the data from QuickBooks on any report, the charts and graphs that are linked to them update as well.
Below are some examples of charts that can be created using Microsoft Excel linking to Intuit Statement
Financial Statements Done Right, in Less Time
61
CASH FLOW STATEMENT BY MONTH
In QuickBooks Financial Software, you are unable to create a Cash Flow Statement side by side for a
month or quarter or a comparison of years. Using the Intuit Statement Writer 2010, you are now able to
query the date range. In the next example, a Cash Flow Statement was created by month.
Financial Statements Done Right, in Less Time
62
BALANCE SHEET FOR MULTIPLE YEARS
Using the Intuit Statement Writer you are able to create a Balance Sheet that compares more than 2
years, which is the limitation in the QuickBooks Financial Software. In the screen shot below, a 3-year
Balance Sheet was created using the Intuit Statement Writer 2010
Financial Statements Done Right, in Less Time
63
RETAINED EARNINGS STATEMENT
New in Intuit Statement Writer 2010, a Retained Earnings Statement is now included. This report cannot
be generated in the QuickBooks Financial Software. See screen shot below.
Financial Statements Done Right, in Less Time
64
BUDGET VERSUS ACTUAL REPORT
Budget reports in Intuit Statement Writer pull information from the QuickBooks Financial Software
database. You can modify the template to filter information by Class or Customer:Job, or change the date
ranges so you can view it by month, quarter, or any fiscal period that your client uses. Below is an
example of a budget vs. actual report by quarter.
Financial Statements Done Right, in Less Time
65
OPERATING EXPENSES BY MONTH
You have the flexibility to remove any information you want from a report in ISW so you can generate the
analysis you need. Below is an example of a comparison of operating expenses only on a monthly basis.