FedRptAvgBal
This is the Federal Report Average Balance function. It returns the daily average balance of a Federal Report line for a given month (or span of months).
The daily average balance is calculated by adding the balance at the end of each day in the time span and then dividing by the total number of days in that span. A daily average is calculated for each individual account. This final average is used to determine where an account's balance is reported for accounts whose balances are reported on different lines depending on whether the account has a positive or negative balance.
The Federal Reports are accessible from the GL's Main Menu via the button [Fed Rpt Lines].
WARNING: This function ignores the day parts of AvgBalDate and StartDate. The function always calculates daily averages beginning with the first day of the starting month and running through the last day of the ending month.
FedRptAvgBal(RptName, LineNum, AvgBalDate, Company, PosNeg, StartDate)
Example Usage
- =FedRptAvgBal("TFR", "SC11", "7/31/2009") Returns the daily average balance for TFR line SC11 (Total Cash, Deposits, and Investment Securities) during the month of July, 2009.
- =FedRptAvgBal("TFR", "SC26", "6/30/2009", , ,"1/1/2009") Returns the daily average balance for TFR line SC26 (Total Mortgage Loans) during the first two quarters of 2009.
- Note the two extra commas between the AvgBalDate and the StartDate. Those must be included because the Company and PosNeg parameters are not specified.
- =FedRptAvgBal("Call", "RI-4518", "3/17/2009", , "D", "10/22/2008") Returns 'Credit Balance' because the daily average balance for line RI-4518 of Federal Report "Call" for the last quarter of 2008 and first quarter of 2009 is a debit balance. The "D" in our formula told Excel to only display the balance if it was a debit. See below for more information about the PosNeg parameter.
Required Paramaters
RptName
The name of the federal report (not its description)
"Call" "TFR"
%fineprint{Used in: FedRptAvgBal, FedRptBalance, FedRptBudget, FedRptDescription}%
LineNum
The abbreviated federal report line number.
"SC11" "SO141" "RI-4435"
%fineprint{Used in: FedRptAvgBal, FedRptBalance, FedRptBudget, FedRptDescription}%
AvgBalDate
The date that represents the month and year for which to compute the daily average balance (when used without the optional StartDate parameter).
"3/31/2009" Returns the daily average balance for 3/1/2009 through 3/31/2009, inclusive "3/1/2009" Returns the daily average balance for 3/1/2009 through 3/31/2009, inclusive "3/17/2009" Returns the daily average balance for 3/1/2009 through 3/31/2009, inclusive
The date that represents the month and year through which to compute the daily average balance (when used with the optional StartDate parameter).
"3/31/2009", with StartDate of 1/1/2009 Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive "3/1/2009", with StartDate of 1/31/2009 Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive "3/17/2009", with StartDate of 1/12/2009 Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive
%fineprint{Used in: AvgBal, FedRptAvgBal, LineAverage, LineAverageRound}%
Optional Parameters
Company
The company suffix code used to restrict user report numbers to a single company. To include all companies, either 1) do not enter anything for this parameter or 2) use the abbreviation "Con" (for Consolidated). To include only the default Citizens company, use either 1) double quotes with nothing inside: "" or 2) the abbreviation "Cit" (for Citizens).
Consolidated "Con" Consolidated "" Citizens Savings Bank "Cit" Citizens Savings Bank "CSA" CSA Services, LLC "CSB" CSB Services, Inc.
%fineprint{Used in: FedRptAvgBal, FedRptBalance, FedRptBudget, LineAverage, LineBalance, LineBudget}%
PosNeg
Limit results to Positive, Negative, Debit, or Credit only. You may spell out or abbreviate Positive, Negative, Debit, and Credit if you would like to make your formula more readable; the function performs a match on the first character only.
When evaluating numeric formulas such as SUM, Excel treats text as 0. This makes it possible to use this PosNeg parameter to include results of GL formulas in your other Excel formulas only in specific circumstances.
"P" Show balance only if positive, otherwise display: Neg Balance "N" Show balance only if negative, otherwise display: Pos Balance "D" Show positive number if debit balance, otherwise display: Credit Balance "C" Show positive number if credit balance, otherwise display: Debit Balance
%fineprint{Used in: AvgBal, Balance, FedRptAvgBal, FedRptBalance, LineAverage, LineAverageRound, LineBalance, LineRound}%
StartDate
The date that represents the month and year from which to compute the daily average balance (used only with the required AvgBalDate parameter).
"1/1/2009", with AvgBalDate of 3/31/2009 Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive "1/31/2009", with AvgBalDate of 3/1/2009 Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive "1/17/2009", with AvgBalDate of 3/12/2009 Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive
%fineprint{Used in: AvgBal, FedRptAvgBal, LineAverageRound}%