FAR675 - Deferred Revenue Analysis

Overview

Personify allows an organization to defer revenue for product purchases and then to recognize this revenue over time. The purpose of the FAR675 report is to provide an analysis of how much of each selected order line’s revenue is deferred and how much is recognized and then to summarize that information. This report is intended to show that deferred revenue balances are recorded correctly, or in audit terms “fairly stated”.

This report can also be used to project remaining revenue for the year, and it can be used to verify data conversions.

The FAR675 report is organized by product, and then for each product, includes all selected order lines for that product. Only invoiced order lines are included in the analysis and report. For each order line, the report includes the sales amount, total amount deferred, and total amount recognized. Additionally, the report provides a breakdown of how much is remaining in the deferred accounts for the current fiscal year, along with how much is deferred for future years.

For each revenue and deferred revenue account linked to the order line, the report calculates what the balance should be and also lists the actual balance. If the calculated balance is different than the actual balance, the difference is displayed. Order lines where the calculated deferred balance is different than the actual deferred balance as of the specified cut-off date are identified as exceptions on the report.

Exceptions are not necessarily indicative of a revenue recognition processing error. Variances may be caused by manual adjustments made prior to running of the Revenue Recognition (FAR670) process. For example, if a Cutoff Date is 8/31/2012 and an adjustment is made on 9/15/12, this will be considered an exception until the end of September 2012, when the Revenue Recognition (FAR670) process is run. This is an exception because the projected numbers will include the adjustment, but Far_Txn and Far_Txn_Detail will not as of 8/31/2012. The Revenue Recognition (FAR670) process will balance the recognition, but not until the next time the process is run. Any time that the projections do not support the actual numbers, as of the Cutoff Date specified, the order is considered an exception, even if it is only a timing difference.

Background

A given product in Personify can have many revenue distribution account pairs which link revenue accounts with specific deferred revenue accounts. Revenue and deferred revenue accounts can be used on many products.

As part of each product definition, the revenue recognition method is defined, which identifies how revenue will be recognized. The following is a list of product types with their revenue recognition method:

Subscriptions – Revenue recognition is typically related to the publication (or fulfillment) of an issue. Only if the issue is fulfilled for an active order does the application recognize revenue. Revenue is not recognized on grace issues, replacement issues, or issues that were not mailed because of bad addresses. Revenue is recognized on back issues, which were not grace issues. Therefore, revenue recognition is calculated based on records in the issues served table (Sub_Issue_Fulfillment) and not just based on date ranges.
Memberships – Revenue is recognized based on a period of time, typically monthly. Revenue is not recognized for proforma orders, but back revenue is recognized for orders made active after the membership begin date.
Meetings and Exhibitions – Revenue is recognized once the event has occurred. Revenue is recognized on or after a specific date, rather than a date range.
Facility Reservations – Revenue is recognized once the facility reservation date has occurred. Revenue is recognized on or after a specific date, rather than a date range.
Advertising Orders – Revenue is typically recognized on invoice, because advertising insertion orders are typically not invoiced until after the ad has appeared.
All other orders – Revenue is typically recognized on invoice.

When Should FAR675 Be Run

FAR675 should be run after the Revenue Recognition (FAR670) process is run for the month to recognize revenue. If FAR675 is run prior to recognizing revenue for the specified cutoff date, many orders will show up as errors since the amount that should be recognized has not been recognized.

FAR675 Exceptions

The situations that will cause an order line to be listed as an exception on the FAR675 report are:

The calculated deferred revenue balance does not equal the actual deferred revenue balance.
An expired membership order does not have a deferred revenue balance of zero.
An expired subscription order does not have a deferred revenue balance of zero. This means that back-issues have not been fulfilled.
A cancelled order has a deferred revenue balance.

If an order line cancellation date falls after the FAR675 Cutoff Date, the order line will appear on the FAR675 report as an exception if it has a balance in deferred revenue as of the cutoff date.

The sum of sales and adjustment transaction amounts do not equal the order line amount.

Summary of Processing Logic

FAR675 analyzes each order line separately.
This is necessary because changes at the order-line level in terms of price or length of a membership or subscription may change the value of an issue or a month of service from what would be expected if the original rate was used for the calculation.
FAR675 will select Order_Detail records of invoiced order lines along with posted sales, adjustment, and revenue recognition transactions (transaction type 4, 6 and 7, respectively).
Amounts are summed by product and then by GL account and transaction function code. Only order lines with an invoice date greater than or equal to the “Earliest Order Date” parameter will be selected, and only transactions with a transaction date less than or equal to the cutoff date will be selected.
The order line sales amount is calculated by summing the selected sales and adjustment transactions.
This is done rather than by selecting Order_Detail.BASE_TOTAL_AMOUNT, because Order_Detail.BASE_TOTAL_AMOUNT might include an adjustment made after the cut-off date specified for the FAR675 report.
FAR675 looks at the revenue recognition method code defined for the product on the order line.
If the revenue recognition method code defined for the product on the order line is monthly or by issue, the process will calculate the number of periods between the start date and end date, divide the sales amount by the number of periods and then calculate whether the correct amount of revenue has been recognized through the cut-off date.
The report will show projected numbers for both deferred revenue and actual revenue.
However, exception comparisons will be based on deferred revenue balances. The implication is that if deferred revenue (and the rest of the balance sheet) is correct, revenue will be correct.
FAR675 calculates the projected amount of deferred revenue based on the sales transactions.
Also FAR675 compares this amount to totals in the order to verify that the sales and adjustment transactions match what is in the order. If not, this will also generate an exception.

Income Statements are cleared at the beginning of each fiscal year, while balance sheet accounts are not. Revenue and expense accounts are found on the Income Statement and deferred revenue accounts are found on the Balance Sheet. Therefore, there is a difference in the amount recorded against a revenue account and the total that is displayed on the Income Statement if the order has revenue recognition that crosses fiscal year boundaries.

The detail section of the report displays the total revenue amount recorded against all accounts.
This is regardless of fiscal year and is intended to make clear what has been recognized and what has not been recognized.

Calculating Number of Recognition Periods for an Order Line

The number of recognition periods for an order line depends on the revenue recognition method defined for the product. The sales amount of the order line (i.e., the sum of Far_Txn.BASE_AMOUNT for type 4 and 6 transactions) is divided by the number of recognition periods for the order line to identify the revenue amount that should be recognized each period.

The chart below shows the logic that is used by the FAR675 process in calculating the number of revenue recognition periods based on revenue recognition method:

Recognition Method	Cycle Begin Date	Cycle End Date	Total Periods	Recognized Periods
IMMEDIATE	Null	Order_Detail. INVOICE_DATE	1	1
MONTHLY	Order_Detail. CYCLE_BEGIN_DATE	Order_Detail. CYCLE_END_DATE	Periods Between begin and end date	Periods between begin and cutoff date
ISSUE	Order_Detail. CYCLE_BEGIN_DATE	Order_Detail. CYCLE_END_DATE	Periods Between begin and end issue	Total fulfilled issues by cutoff date
INVOICE	Null	Order_Detail. INVOICE_DATE	1	1
SPECDATE	Null	Product_Account. REVENUE_RECOG_DATE	1	1
BEGIN	Order_Detail. CYCLE_BEGIN_DATE	Null	1	1
END	Null	Cycle_end_date	1	1
YEAREND	Null	Fiscal Year End Date	The number of year-ends crossed.	The number of year-ends between start and cutoff date.

Short-Month Processing

Periodic revenue recognition includes an additional calculation for short-month processing for memberships and subscriptions defined to recognize revenue on a MONTHLY basis. For memberships and subscriptions that start and end mid-month, a parameter is provided so that organizations can decide whether the recognition has a short period at the beginning or at the end of the recognition. Others want it to be a full month at the beginning and nothing at the end. Note that this may result in a one-year membership having 13 periods if there is a short month in the beginning but the user has forced recognition to the end of the final month, i.e. 1/15/2012-1/31/2013.

Required Setup

The last month of the organization’s fiscal year must be defined.

Detailed Processing Logic

Parameters

Parameter	Description
Subtitle	Optional parameter that allows the user to specify a report subtitle that will print as part of the report header.
Run Mode	DETAIL – Shows projected distribution for all orders within the selection criteria (exception orders are clearly marked). EXCEPTION – shows detail only for orders where the amount recognized doesn't match the amount that should be recognized. Also provides summary by product and across all products selected. SUMMARY – provides summary information by product and summary information across products. This parameter defaults to EXCEPTION.
Earliest Order Date	FAR675 will select orders with an order date greater than or equal to the first of the month of the date selected here to eliminate unnecessary processing time for very old orders. This defaults to 1/1/1960. Note that any date entered here is treated as a full month.
Revenue Recognition Month (Cut-Off Date)	FAR675 will select transactions with a transaction date on or prior to the last day of the month of the date selected here. Before running FAR675 for this month, it is important to have already run revenue recognition for the selected month. Note that any date entered here is treated as a full month. This is also called the “cut-off date”.
Subsystem	Enter the Subsystem for which to run the deferred revenue analysis. Valid values are INV, SUB, MBR, MTG, MISC, XBT, TRN, CRT, FAC, ADV or ALL. ALL will generate revenue recognition analysis for all the subsystems.
Organization	Defaults to the ORG_ID of the logged in user and cannot be changed.
Organization Unit	Defaults to the ORG_UNIT_ID of the logged in user and cannot be changed.
Parent Product	Select a parent product code to limit analysis to a specific parent product code.
Product Code	Select a product code to limit the analysis for a specific product.
Short Month Processing	This parameter only applies to MONTHLY revenue recognition method code. MONTHLY revenue recognition is applicable for MBR or SUB Subsystems. The valid values are "BEGIN" or "END". Enter BEGIN to recognize the short month at the beginning of the Membership/Subscription term. Enter END to recognize the short month at the end of the Membership/Subscription term.
Select Criteria	Enter a SQL statement to be included in the record selection as an additional clause in the report query. The criteria can be based on far675_vw view. For example : FAR675_VW.PRODUCT_CODE = 'NEWBOOK'

Additional Selection Criteria

Order records are first selected from Far675_VW where ORG_ID and ORG_UNIT_ID = ORG_ID, ORG_UNIT_ID values in FAR675 parameters and INVOICE_DATE >= Earliest Order Date parameter and products meet the FAR675 parameter values for Subsystem, Parent Product and Product Code.

The process selects posted sales (TXN_TYPE_CODE = 4) and adjustment (TXN_TYPE_CODE = 6) records from Far_Txn and Far_Txn_Detail for selected order lines where Far_Txn.TXN_DATE <= specified Cut-Off Date and Far_Txn_Detail.TXN_FUNCTION_CODE equals Far675_VW.REAL_TXN_FUNCTION_CODE or Far675_VW. DEF_TXN_FUNCTION_CODE.

Last, the process selects records from Far_Txn_Detail for type 7 revenue/deferred revenue transactions linked to the selected order lines.

Sample Report

Report Fields

Caption	Field	Description
Product	Far675_Order_Detail. PRODUCT_ID, Product.PARENT_PRODUCT, Product.PRODUCT_CODE, Product.SHORT_NAME	The FAR675 report is organized first by product. The report does a page break on each change of product.
Order Number Line	Far675_Order_Detail. ORDER_NO - ORDER_LINE_NO	The order number and line number being analyzed. Format: [order number]-[line number]
Rev Recog Begin Date	Far675_Order_Detail. CYCLE_BEGIN_DATE	The value in this field depends on the revenue recognition method and the order line subsystem. For memberships and subscriptions, this is the cycle begin date of the membership or subscription. For meetings, this is the meeting start date. For exhibitions, this is the show begin date. For facility products, this is the room reservation start date. For revenue recognition of IMMEDIATE, INVOICE, SPECDATE and YEAREND, this is left null.
Rev Recog End Date	Far675_Order_Detail. CYCLE_END_DATE	The value in this field depends on the revenue recognition method and the order line subsystem. For memberships and subscriptions, this is the cycle end date of the membership or subscription. For meetings, this is the meeting end date. For exhibitions, this is the show end date. For facility products, this is the room reservation end date. For a recognition method of SPECDATE, this is the Product_Account. REVENUE_RECOGNITION_DATE. For recognition method of IMMEDIATE or INVOICE, this is the invoice date. For a recognition method of YEAREND, this is the last day of the fiscal year.
Tot Per	Far675_Order_Detail. TOTAL_PERIODS	Identifies the total revenue recognition periods for the order line. This value is dependent on revenue recognition method and the subsystem of the order line. For membership order lines where the recognition method is MONTHLY, the calculation considers the DEFAULT_END_MASK . If the default end mask is 1YRPLUS, if the membership starts on the first day of the month, total periods is set to the number of months between the cycle begin date and cycle end date + 1; if the membership start date is > 1, total periods is set to the number of months between the cycle begin date and the cycle end date. If the default end mask is 1YRMINUS, total periods is set to the number of months between the cycle begin date and cycle end date + 1 unless the day of the cycle begin date is > the day of the cycle end date, in which case total periods is set to the number of months between the cycle begin date and the cycle end date. For subscriptions where revenue is being recognized by ISSUE, total periods is set to the difference between the start issue and the end issue on the order line +1. For other types of revenue recognition methods besides MONTHLY and ISSUE, see the chart in the “Calculating Number of Recognition Periods for an Order Line” above in the section entitled “Summary of Processing Logic” in the Overview.
Per Recog	Far675_Order_Detail. RECOGNIZED_PERIODS	Number of periods that should be recognized based on the recognition start date and the cut-off date. This value is dependent on revenue recognition method and the subsystem of the order line. For membership order lines where the recognition method is MONTHLY, the calculation considers the DEFAULT_END_MASK . If the default end mask is 1YRPLUS, if the membership starts on the first day of the month or if the Short Month Processing parameter has been set to BEGIN, recognized periods is set to the number of months between the cycle begin date and cut-off date + 1; if the membership start date is > 1, recognized periods is set to the number of months between the cycle begin date and the cut-off date. If the default end mask is 1YRMINUS, recognized periods is set to the number of months between the cycle begin date and cut=off date + 1. For subscriptions where revenue is being recognized by ISSUE, recognized periods is set to the number of issues that have been fulfilled (excluding supplements, grace and replacement issues) in Sub_Issue_Fulfillment where the ISSUE_DATE falls between the CYCLE_BEGIN_DATE and CYCLE_END_DATE of the subscription order. For other types of revenue recognition methods besides MONTHLY and ISSUE, see the chart in the “Calculating Number of Recognition Periods for an Order Line” above in the section entitled “Summary of Processing Logic” in the Overview.
Sales Amount	Far675_Order_Detail. SALES_AMOUNT	The sales amount that will be used to calculate expected revenue recognition. This amount will be divided by the number of recognition periods for the order line times the number of expected recognized periods. This is the sum of BASE_AMOUNT * -1 from Far_Txn_Detail where TXN_FUNCTION_CODE in 'REVENUE', 'DEFSHIP', 'SHIP', 'DISC', 'DEFDISC', 'DEFAGDIS', 'AGDISC', 'DEFREV' and Far_Txn.TXN_TYPE_CODE in 4,6 and Far_Txn.TXN_DATE <= CutoffDate
Tot Deferred	Far675_Order_Detail. TOTAL_DEFERRED_AMOUNT	The amount of revenue for the order line that has been deferred. This is the sum of BASE_AMOUNT * -1 from Far_Txn_Detail where TXN_FUNCTION_CODE in 'DEFSHIP', 'DEFDISC', 'DEFAGDIS', 'DEFREV' and Far_Txn.TXN_TYPE_CODE in 4,6,7 and Far_Txn.TXN_DATE <= CutoffDate for type 4,6 transactions. + Sum of FAR675 calculated TOTAL_AMOUNT where txn_function_code IN 'DEFREV', 'DEFSHIP', 'DEFDISC', 'DEFAGDIS' Calculation of FAR675 calculated TOTAL_AMOUNT 1) Get BASE_AMOUNT from Far_Txn_Detail table where Far_Txn.TXN_TYPE_CODE In 4, 6 and Far_Txn.POSTED_FLAG= 'Y' and Fr_Txn.TXN_DATE < cut- off date parameter and Far_Txn_Detail.TXN_FUNCTION_CODE is equal to REAL_TXN_FUNCTION_CODE or DEF_TXN_FUNCTION_CODE 2) Multiply with Distribution percentage for REVENUE records only. This is based on the revenue distribution percentage defined in product setup. 3) Multiply with the FACTOR received from FAR675_get_period_duration function. Factor is calculated as follows: For REVENUE_RECOG_METHOD_CODE 'MONTHLY' >> @Period/@Duration 'BEGIN' >> Begin Date <= CutOff Date then 1 else 0 'END' >> End Date <= CutOff Date then 1 else 0 'SPECDATE' >>Begin Date<=CutOff Date then 1 else 0 'YEAREND' >> For 'MBR','SUB' > @Period/@Duration Others > Begin Date<=YearEnd Date then 1 else 0 (YearEnd Date = Last Fiscal end date) 'ISSUE' >> Issues_Fulfilled / Total Issues 4) Get the records where recognition_status_code <> 'C' and REVENUE_RECOG_METHOD_CODE <> 'INVOICE' 5) Add Base_Amount from FAR_TXN_DETAIL where TXN_Type = 7
Tot Recognized	Far675_Order_Detail. TOTAL_RECOGNIZED_AMOUNT	Far675_Order_Detail. SALES_AMOUNT - Far675_Order_Detail. TOTAL_DEFERRED_AMOUNT
Cur Yr Recog	Far675_Order_Detail. CURRENT_YEAR_RECOGNIZED_ AMOUNT	Calculates the amount of revenue recognized in the current year through the cut-off date. Number of periods to be recognized through cut-off date in the current year / total_periods * (Sum of Base_Amount-1 from FAR_TXN_DETAIL where txn_function_code in 'DEFSHIP', 'DEFDISC', 'DEFAGDIS', 'DEFREV' and TXN_TYPE_CODE in 4,6,7 and txn_date <= CutoffDate for type 4,6 transactions and line_status_code = 'C' for Type 7 transactions. + Sum of Base_Amount-1 from FAR_TXN_DETAIL where and txn_function_code IN 'REVENUE', 'SHIP', 'DISC', 'AGDISC' and TXN_TYPE_CODE in 4,6
Cur Yr Def	Far675_Order_Detail. CURRENT_YEAR_DEFERRED_ AMOUNT	The amount of revenue for the current fiscal year that has been deferred. This is calculated by taking total_deferred_amount * (current_year_periods – current year periods through cut-off date ) / ( total_periods - recognized_periods )
Future Yr Def	Far675_Order_Detail. FUTURE_YEARS_DEFERRED_ AMOUNT	The amount of revenue for future fiscal years that has been deferred. total_deferred_amount - current_year_deferred_amount
(Exception Flag)	Far675_Order_Detail. EXCEPTION_FLAG	Not visible on the screen. Set to Y if an exception condition is identified in the order line.
(Exception Description)	Far675_Order_Detail. EXCEPTION_DESCR	Message that identifies the exception condition, which includes: Actual recognized does not match expected.' where FAR675_FAR_TXN_DETAIL.ERROR_DIFFERENCE<>0 'Expired MBR order with deferred revenue.' Where fulfill_status_code = E and 'MBR' 'Expired SUB order with deferred revenue.' Where fulfill_status_code = E and 'SUB'
Account Balances
GL Account	Far675_Far_Txn_Detail. ACCOUNT	Far_Txn_Detail.ACCOUNT
Account Function	Far675_Far_Txn_Detail. TXN_FUNCTION_CODE	Far_Txn_Detail.TXN_FUNCTION_CODE
Calculated Balance	Far675_Far_Txn_Detail. ACCOUNT_BALANCE	Sum of far675 Calculated1 total_amount*-1 where txn_function_code IN 'DEFREV', 'DEFSHIP', 'DEFDISC', 'DEFAGDIS' + Sum of far675 Calculated2 total_amount where txn_function_code IN 'DEFREV', 'DEFSHIP', 'DEFDISC', 'DEFAGDIS' + Sum of Base_Amount from FAR_TXN_DETAIL where txn_function_code in 'DEFSHIP', 'DEFDISC', 'DEFAGDIS', 'DEFREV' and TXN_TYPE_CODE in 4,6,7 and txn_date <= CutoffDate for type 4,6 transactions.
Actual Balance	Far675_Far_Txn_Detail. BASE_AMOUNT	Sum of Base_Amount from FAR_TXN_DETAIL where txn_function_code in 'DEFSHIP', 'DEFDISC', 'DEFAGDIS', 'DEFREV' and TXN_TYPE_CODE in 4,6,7 and txn_date <= CutoffDate for type 4,6 transactions.
Error Difference	Far675_Far_Txn_Detail. ERROR_DIFFERENCE	account_balance - base_amount
Product Summary Totals
Product Summary Totals	Subtotal of all amounts listed in the detail report for each product.
Orders Processed	Distinct count of order number, order line number
Total Sales Amount	Sum of Far675_Order_Detail. SALES_AMOUNT
Tot Deferred	Sum of Far675_Order_Detail. TOTAL_DEFERRED_AMOUNT
Tot Recognized	Sum of Far675_Order_Detail. TOTAL_RECOGNIZED_AMOUNT
Cur Yr Recognized	Sum of Far675_Order_Detail. CURRENT_YEAR_RECOGNIZED_ AMOUNT
Cur Yr Deferred	Sum of Far675_Order_Detail. CURRENT_YEAR_DEFERRED_ AMOUNT
Future Yr Deferred	Sum of Far675_Order_Detail. FUTURE_YEARS_DEFERRED_ AMOUNT
Summary GL Account Totals for Product:	Subtotal of GL Account amounts for each product.
Account Balances
GL Account	Far675_Far_Txn_Detail. ACCOUNT
Account Function	Far675_Far_Txn_Detail. TXN_FUNCTION_CODE
Calculated Balance	Sum of Far675_Far_Txn_Detail. ACCOUNT_BALANCE
Actual Balance	Sum of Far675_Far_Txn_Detail. BASE_AMOUNT
Error Difference	Sum of Far675_Far_Txn_Detail. ERROR_DIFFERENCE
Report Summary Totals
Report Summary Totals:	Grand total of amounts listed in the detail report for all product order lines.
Orders Processed	Distinct count of order number, order line number
Total Sales Amount	Sum of Far675_Order_Detail. SALES_AMOUNT
Tot Deferred	Sum of Far675_Order_Detail. TOTAL_DEFERRED_AMOUNT
Tot Recognized	Sum of Far675_Order_Detail. TOTAL_RECOGNIZED_AMOUNT
Cur Yr Recognized	Sum of Far675_Order_Detail. CURRENT_YEAR_RECOGNIZED_ AMOUNT
Cur Yr Deferred	Sum of Far675_Order_Detail. CURRENT_YEAR_DEFERRED_ AMOUNT
Future Yr Deferred	Sum of Far675_Order_Detail. FUTURE_YEARS_DEFERRED_ AMOUNT
Summary GL Account Totals for All Product:	Grand total of GL Account amounts for all products.
Account Balances
GL Account	Far675_Far_Txn_Detail. ACCOUNT
Account Function	Far675_Far_Txn_Detail. TXN_FUNCTION_CODE
Calculated Balance	Sum of Far675_Far_Txn_Detail. ACCOUNT_BALANCE
Actual Balance	Sum of Far675_Far_Txn_Detail. BASE_AMOUNT
Error Difference	Sum of Far675_Far_Txn_Detail. ERROR_DIFFERENCE

Technical Implementation

FAR675_SP stored procedure sets variables and creates temporary tables
FAR675_Get_Data_SP stored procedure selects records from FAR675_VW view and inserts into temporary tables
FAR675_Set_Data_SP stored procedure updates data in temporary tables
FAR675_Get_Period_Duration function identifies number of recognition periods for each order line and updates data in temporary tables
FAR675_get_current_year_period function identifies the number of recognition periods in the current fiscal year for each order line
FAR675_Create_Data_SP stored procedure inserts data from temporary tables into reporting tables: FAR675_Order_Detail and FAR675_Far_Txn_Detail