Home Overview Screenshots How to Buy Help Page Testimonials Videos
Note: When using this program, temporarily turn off the "Use account numbers" preference in QuickBooks or assign Account numbers to all accounts. If you don't the statements may be out of balance.
1. Creating Financial Statement Format Workbooks. The
Reporter comes with sample financial statement format workbooks (Excel files)
for the US and Canadian QuickBooks sample companies. These Excel files will be
in the folder where the program is installed. To
copy this workbook, open it in Excel and save it under a different name. Then
you can export date to this new workbook and modify the financial statement
format sheets to meet your reporting requirements.
2. Rounding. The sample workbook included with the Reporter, rounds amounts to whole dollars. This may create $1 or $2 rounding differences. Any larger difference usually indicates that there are accounts with balances which have not been assigned row names. We recommend the following sequence of procedures to account for rounding differences.
a. Compare total assets and total liabilities. If different, edit the existing formula for the first current liability account in the balance sheet (usually accounts payable) and append +1 or - 1 to the existing formula. Don't erase the original formula.
b. Compare net income on the income statement to the net income line the balance sheet. If different, edit the formula for the sales account and append +1 or - 1 to the formula. Don't erase the original formula.
c. If presenting a cash flow statement, compare net income in the cash flow state to net income in the income statement. If different, add or subtract a dollar in the reclassification column on the left side of the page. Then compare the ending cash balance to the balance sheet. If different, go to the reclassification column and add or subtract a dollar from any row other than the net income row.
If you make rounding changes in same statement rows each time, it is easier to find and remove the rounding adjustments if they are not needed.
You can also change the formulas and cell formats to show dollars and cents instead of just whole dollars.
3. Excel functions. The Reporter uses the SUMIF function to calculate amounts in the financial statements and uses the VLOOKUP function to place Company names and dates at the top of the financial statements. Both of these functions rely on Range Names. For more information on these topics, see Excel help.
4. Trial Balance Column Names. The columns in the Trial Balance sheet have trial balance data from different time periods in them so that reports for various time periods can be prepared. These columns have the following column headings which are also the Range Name for the entire column. The columns' range name is used in the SUMIF formula to map data for a specific time period to a particular financial statement column.
The following assumes you have exported data from QuickBooks using a date of 12/31/03 and the company reports on the calendar year basis.
a. CYCMYTD. Current year, current month, year to date. Would contain the balance sheet data as of 12/31/03 and profit and loss data for the 12 months then ended.
b. CYPMYTD. Current year, prior month, year to data. Would contain the balance sheet data as of 11/30/03 and profit and loss data for the 11 months then ended. The differences between a. and b. is used to compute the column labeled CYCM discussed in e. below. The cash accounts in this column are used to calculate the beginning cash balance for the December cash flow statement.
c. LYE. Last year end. Would contain the balance sheet data as of 12/31/02 and profit and loss data for the last full year (02). The cash accounts in this column are used to calculate the beginning cash balance for the year to date cash flow statement.
d. PYCMYTD. Prior year, current month, year to date. Would contain the balance sheet data as of 12/31/02 and profit and loss data for the 12 months then ended.
e. CYCM. Current year, current month . Would contain the net change in the balance sheet accounts in December 03 and the profit and loss for December 03. Effective with version 4.09 (04/29/05) this column can include a quarters worth of data instead of a month. So it could include profit and loss for quarter ending December 03 if the quarterly option is chosen on the data export.
f. PYCM. Prior year, current month. Would contain the net change in the balance sheet accounts in December 02 and the profit and loss for December 02. Effective with version 4.09 (04/29/05) this column can include a quarters worth of data instead of a month. So it could include profit and loss for quarter ending December 03 if the quarterly option is chosen on the data export.
g. CFCYYTD. Cash flow, current year, year to date. Would contain the net changes in the balance sheet accounts during 03.
g. CFPYYTD. Cash flow, prior year, year to date. Would contain the net changes in the balance sheet accounts during 02.
h. LYE2. The year end previous to the last year end. Would contain the balance sheet data as of 12/31/01 and profit and loss data for (01). The cash accounts in this column are used to calculate the beginning cash balance for the prior year, year to date cash flow statement.
i. BudgetCurMo. Current month budget data from QuickBooks.
j. BudgetYTD. Year-to-date budget data from QuickBooks.
k. AnnualBudget. Annual budget amount from QuickBooks.
5. Column Mapping. The Reporter uses the Excel SUMIF function to map or link data the Trial Balance sheet to the financial format sheets. The SUMIF function, sums amounts in the Trial Balance sheet where the “row name” in the financial format sheet matches with the assigned row name in Trial Balance sheet. The SUMIF function only sums amounts in the specific column you designate using the column names notes above. Following is a mapping of row names to various statement formats.
Annual
Balance Sheets: |
|
|
|
|
Description |
This
Year |
Last
Year |
|
|
Dates |
12/31/2003 |
12/31/2002 |
|
|
Use
Trial Balance column |
CYCMYTD |
PYCMYTD |
|
|
|
|
|
|
|
|
|
|
|
|
Interim
Balance Sheets: |
|
|
|
|
Description |
This
Date |
Last
Year |
Last
Year End |
Last
Month |
Dates |
6/30/2003 |
6/30/2002 |
12/31/2002 |
5/31/2003 |
Use
Trial Balance column |
CYCMYTD |
PYCMYTD |
LYE |
CYPMYTD |
|
|
|
|
|
|
|
|
|
|
Annual
Income Statements: |
|
|
|
|
Description |
This
Year |
Last
Year |
|
|
From |
1/1/2003 |
1/1/2002 |
|
|
To |
12/31/2003 |
12/31/2002 |
|
|
Use
Trial Balance column |
CYCMYTD |
PYCMYTD |
|
|
|
|
|
|
|
|
|
|
|
|
Interim
Income Statements: |
|
|
|
|
Description |
|
|
|
Same
Month |
Description |
This
YTD |
Prior
YTD |
Current
Month |
Last
Year |
From |
1/1/2003 |
1/1/2002 |
6/1/2003 |
6/1/2002 |
To |
6/30/2003 |
6/30/2002 |
6/30/2003 |
6/30/2002 |
Use
Trial Balance column |
CYCMYTD |
PYCMYTD |
CYCM |
PYCM |
|
|
|
|
|
|
|
|
|
|
Annual
Cash Flow: |
|
|
|
|
Description |
This
Year |
Last
Year |
|
|
From |
1/1/2003 |
1/1/2002 |
|
|
To |
12/31/2003 |
12/31/2002 |
|
|
Use
Trial Balance column |
CFCYYTD |
CFPYYTD |
|
|
Column
for beginning cash |
LYE |
LYE2 |
|
|
|
|
|
|
|
|
|
|
|
|
Interim
Cash Flow: |
|
|
|
|
Description |
|
|
|
|
Description |
This
YTD |
Prior
YTD |
Current
Month |
|
From |
1/1/2003 |
1/1/2002 |
6/1/2003 |
|
To |
6/30/2003 |
6/30/2002 |
6/30/2003 |
|
Use
Trial Balance column |
CFCYYTD |
CFPYYTD |
CYCM |
|
Columns
for beginning cash |
LYE |
LYE2 |
CYPMYTD |
|
|
|
|
|
|