LA - User Financial Reports

User Financial Reports provides the ability to implement Excel formulas, using an Info field in Row items to identify data to be used in these formulas. The Column Format screen allows defining an Info column in which the Info “codes” are placed for use by the formulas.

The report used for this example was created to report fund balances, as noted in Comments in the Report Format section below. Explanations of the Row

 

REPORT FORMAT

Name: Fund Report

Type: I&E Report

Comments: This report shows fund balances, with a beginning balance, income, expense and ending balance, also to report the current period activity. When running this report you ask only for the period you want to be displayed in the current period columns.

 

ROWS

  1. TEXT: "Sample Church" Font=Arial Size=10 Justify=Center Bold=Yes Italic=No
  2. TEXT: "Fund Activity Report" Font=Arial Size=10 Justify=Center Bold=Yes Italic=No
  3. PERIOD: Font=Arial Size=10 Justify=Center Bold=Yes Italic=No
  4. LF: Line Feeds=1
  5. CH Font=Arial Size=10 Justify=Left Bold=Yes Italic=No Print Column Header=Yes Include Organization Name=No City=No ID=No Display Period Closed=No
  6. ACCOUNT: Acct.=2610-000-01 Type=Total Print=No Bold=No Italic=No TitleAcct=No Info=B
  7. RANGE: Acct. From=4???-???-01 To=4???-???-01 Print=No TitleAcct=No Info=I
  8. RANGE: Acct. From=5???-???-01 To=9???-???-01 Print=No TitleAcct=No Info=E
  9. SUBT: Subtotal=1 "Building Fund" Type=Net Print=Yes Font=Arial Size=10 Justify=Left Bold=No Italic=No
  10. LF: Line Feeds=1
  11. ACCOUNT: Acct.=2620-000-02 Type=Total Print=No Bold=No Italic=No TitleAcct=No Info=B
  12. RANGE: Acct. From=4???-???-02 To=4???-???-02 Print=No TitleAcct=No Info=I
  13. RANGE: Acct. From=5???-???-02 To=9???-???-02 Print=No TitleAcct=No Info=E
  14. SUBT: Subtotal=1 "Memorial Fund" Type=Net Print=Yes Font=Arial Size=10 Justify=Left Bold=No Italic=No
  15. LF: Line Feeds=1
  16. ACCOUNT: Acct.=2630-300-03 Type=Total Print=No Bold=No Italic=No TitleAcct=No Info=B
  17. RANGE: Acct. From=4???-???-03 To=4???-???-03 Print=No TitleAcct=No Info=I
  18. RANGE: Acct. From=5???-???-03 To=9???-???-03 Print=No TitleAcct=No Info=E

  19. SUBT: Subtotal=1 "Missions Fund" Type=Net Print=Yes Font=Arial Size=10 Justify=Left Bold=No Italic=No
  20. LF: Line Feeds=1
  21. SUBT: Subtotal=2 "Total Restricted Funds" Type=Net Print=Yes Font=Arial Size=10
    Justify=Left Bold=Yes Italic=No

 

COLUMNS

  1. GL ACCT DESCRIPTION "Description" Print=Yes
  2. INFO: Print=No
  3. BEGINNING BALANCE FOR PERIOD “Bgn Balance” Period = 1[ ] Print=No
  4. YTD "Ytd" DataSet= Print=No
  5. EXCEL (Beginning Balances) if(c2="b",c3,0)
  6. EXCEL (Income) if(c2="i",c4,0)
  7. EXCEL (Expenses) if(c2="e",c4,0)
  8. EXCEL c5+c6+c7
  9. PERIOD "Period" Period=Current DataSet= Print=No
  10. EXCEL (Period Income) if(c2="i",c9,0)
  11. EXCEL (Period Expense) if(c2="e",c9,0)

 

Explanations

ROWS

The first 5 rows are standard for most reports: the name of the organization, the type of report, the period, a line feed to separate those and then the column headers.

Row 6 pulls the LT account for the first fund. (This is a Balance Sheet account and will act differently on the report than Income and Expense accounts.) The print is set to No and Info contains “B” (since it’s a Balance Sheet account) so that this account will not print, but can be used in calculations.

Row 7 pulls the Income accounts for that same fund. In this chart of accounts, all Income accounts start with a 4 and the first fund being scanned is Fund 01, so the Range is 4???-???-01 to 4???-???-01. The print is set to No and Info contains “I” (since these are Income accounts) so that this range will not print, but can be used in calculations.

Row 8 pulls the Expense accounts for that same fund. In this chart of accounts, all Expense accounts start with a 5 or greater and the first fund being scanned is Fund 01, so the Range is 5???-???-01 to 9???-???-01. The print is set to No and Info contains “E” (since these are Expense accounts) so that this range will not print, but can be used in calculations.

Row 9 is a Subtotal #1 of the above lines. Text = Building Fund and Print is set to Yes.

Row 10 is a Line Feed to separate the funds.

Rows 11 through 20 are a repeat of rows 6 through 10 using different fund numbers and names.

Row 21 is a Subtotal #2 of all of the above rows.

 

COLUMNS

 Column 1 (A) is the GL Account Description. It sometimes is helpful to use both the GL Account and the Description and choose to not print the GL Account. This can aid in doing research if the report has problems.

Column 2 (B) is Info – Print is set to No; it is used for calculation purposes only, using the Row data with the entries (B, I, E) that were made in the Info fields.

Column 3 (C) is the Beginning Balance for Period 1 – Print is set to No; it is used for calculation purposes only.

Column 4 (D) is YTD – Print is set to No; it is used for calculation purposes only.

Column 5 (E) is a formula to allow the Beginning balance to print in the first column (after GL Account Description) that is displayed on the report. The formula is if(c2=”b”,c3,0), which means if Column 2 (the information from Info) equals B, then print the data in Column 3 (the Beginning Balance); otherwise, print 0 (zero). Another way to think of this formula is that for any item where it finds “B” in Column 2 (Info), it will print the data in Column 3. For all items where Column 2 does not have “B”, it returns a zero. (Text for Column Header = Beginning Balance)

Column 6 (F) is a formula to allow the YTD Income to print in this column on the report. The formula is if(c2=”i”,c4,0), which means if Column 2 (Info) equals I, then print the data in Column 4 (which is the YTD total); otherwise print 0. (Text = Income)

Column 7 (G) is a formula to allow the YTD Expense to print in this column on the report. The formula is if(c2=”e”,c4,0), which means if Column 2 (Info) equals E, then print the data in Column 4 (which is the YTD total); otherwise print 0. (Text = Expense)

Column 8 (H) is a formula that calculates the Ending Balance of the fund. The formula is c5+c6+c7, which just adds column 5, 6 and 7. (Text = Ending Balance)

Column 9 (I) is the Period Total for the Current Period – Print is set to No; it is used for calculation
purposes only.

Column 10 (J) is a formula to allow the Current Period Income to print in this column on the report. The formula is if(c2=”i”,c9,0), which means if Column 2 (Info) equals I, then print the data in Column 9
(the Period Total); otherwise, print 0. (Text = Period Income)

Column 11 (K) is a formula to allow the Current Period Expense to print in this column on the report.
The formula is if(c2=”e”,c9,0), which means if Column 2 (Info) equals E, then print the data in Column 9 (which is the Period Total); otherwise print 0. (Text = Period Expense)

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.