Home   Overview   Screenshots  How to Buy   Help Page   Testimonials   Videos


Custom Financial Reporter

Help Page

 

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

 

 

 

  (or quarter)  

  (or qtr)

 

 

 

 

 

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