Agreement Item Revenue draft


				

Contents

Understanding the Agreement Item Revenue Report

The Agreement Item Revenue module reports agreement revenue over a user defined period of time. Only agreement revenue is recognized in this module, for PIF, Installments and Perpetual Billing.

This report combines the standard agreement sales and sales revenue report to give the user unprecedented insight into the revenue generated on agreements throughout the membership lifecycle. It allows the user to view agreements sold within a specific period and how these agreements generate revenue over a given period of time. This gives the user the ability to gauge the effectiveness of a membership promotion over the length of a membership, understand the trend of when agreement revenue generates from a specific month or see when sales will start to decline.

Uses

This module is designed to track agreement revenue and units in specific ways:

  • Select only agreements sold in a specific date range, and view how those agreements generated revenue of the course of time. For example, the user can view how agreements sold in January 2014 generated revenue throughout the 2014 year.
  • View all agreement sales in a date range frame to analyze trends.
  • View agreement revenue connected to a specific organization
  • View agreement revenue associated with a specific agreement, agreement group or promotion.

Accounting Method

This report only uses the Accrual Basis accounting method, which accounts for when invoices are created, not when payment is collected.

Location

Data Analytics > Warehouse > Financials > Agreement Item Revenue

Audience

Sales Director, Accounting Department, Operations Director

Filters

  • Calendar: The Warehouse offers three types of calendars: Gregorian, Fiscal, and Retail. Selections here will determine what days of revenue are gathered for the months in the date range. (i.e. if the Fiscal Year begins Jun 15, the value calculated for ‘June’ would be a calculation of revenue between June 15 – July 14)
  • Calendar Filter
  • Agreement Revenue in Months: The dates entered into this filter will be used to determine which months to display in the module. It does not look at the specific dates that are entered, but rather the months that include the dates that are entered. (i.e. if the user enters 3/5/14, the report will display all of March revenue)
  • Agreement Revenue in Months Filter
  • Display Revenue by Agreements initiated: There is an additional date option in this module that allows the user to filter the results to show only agreements that were initiated during a specified date range. If the user wanted to enable this option, select ‘Date Range’. Otherwise, select ‘All’ and all agreements that generate revenue (regardless of initiation date) will be displayed.
  • Display Revenue by Agreements initiated Filter
  • Date Range Enter the date range of the agreement start dates needed in the report.
  • Division: If multiple divisions are configured MoSo, the user can filter out agreements based on the revenue division and business unit. Run this report for a specific division, multiple divisions, or for all divisions at once. This list may be pre-selected based on user permissions and drives the Business Units available for selection in the Business Unit filter.
  • Division Filter
  • Business Unit: Run this report for a specific division, multiple divisions, or for all divisions at once. This list may be pre-selected based on user permissions. Only business units in the selected Divisions filter will appear in this drop down list.
  • New/Rewrite Agreement: Filters the results on revenue generated from a new/original agreement or from an agreement that was a rewrite of an original.
  • Revenue From: Agreement revenue is classified in the warehouse as Down Payment or Non-Down Payment revenue. This allows the user to look at revenue that was acquired during the agreement initiation process vs acquired during the latter course of the agreement.
  • Contract Role: Filters the report to show revenue from agreements that are either the Primary agreement or an Add-on agreement, or all.
  • Qualifies as Member: Filters the report to show revenue from agreements that are either qualify the owner as a member, do not qualify the owner as a member, or all.
  • Classification: Filters the report to show revenue from agreements that fall under the Agreement Classification selection(s).
  • Group Type: This selection does not filter the report, but it filters the select list available in the Group selection.
  • Group Type Filter
  • Agreement Group: Filters the report to show revenue from agreements that fall under the Agreement Group selection(s).
  • Agreement: Filters the report to show revenue from agreements that fall under the Agreement selection(s).
  • Organization: Filters the report to show specific organizations.
  • Show Drill Down By: The data is grouped by Revenue Category. Users may choose what other subject to drill down when a Category is expanded. Options are Item, Agreement, and Sales Person.

Export Options

The report download options available for this report are:

  • Export to Excel – This will generate a spreadsheet contain the report’s data in Microsoft Excel.
  • Export to PDF – This will format the report’s results in to a PDF file that can be saved to the users computer. Printing directly from PDF may solve formatting issues that arise when trying to print reports from an internet browser.

Result Views

Once the user has selected the filters and generated the report, the screen will display in a Table View. The Table View will display the revenue categories on the left and month for each selected in the date range. Data columns displayed on the table view include:

  • Revenue Category – The category each item is attached to from the MOSO item setup screen.
  • Under each month the following data columns display:
    • Amount – Net amount sold for the month
    • Units – Net number of items sold for the month
    • Unit Avg – The average price per unit sold (Amount/Units = Unit Avg)
  • At the end of the report the Total Columns display:
    • Total Revenue – The total amount for the time period displayed
    • Total Units – The total units for the time period displayed
    • Unit Avg - The average price per unit sold

(Total Revenue/ Total Units = Unit Avg)

Drill Downs, Sorting & Drill-Thru

Drill Downs

A drill down can be found under the column “Revenue Category” on the table view. Clicking on the Revenue Category link will activate a sub-report that displays an Agreement Revenue Analysis grouped by whatever selection the user made for “Show Drill Down By” in the Options.

  • Item: Items sold in the agreement
  • Sales Person: Sales person on record for the agreement
  • Agreement: The name of the agreement

Sorting

There are two columns that can be sorted within this module:

  • Revenue Category: Clicking on the revenue category option the column will sort in alpha order
  • Amount Sorting: Clicking on any of the Amount sub column headers will sort each month (from highest to lowest or lowest to highest based on the results of that month.

Drill Thru

This module has no Drill Thru

FAQ

Report Use Cases

This module is designed to track agreement revenue and units sold a defined period of time, in an effort to see trends and make budgeting decisions.

Case 1: Enterprise View of all agreement revenue created over a 12mth period:

This is the basic use case for this module. In this Use Case the user will view at the Enterprise level, all agreement revenue options, for the past 12 months.

Filters Used:

Agreement Revenue in Months Between: 12 month range
Display Revenue for Agreements Initiated: All
Division: All Classification: All
Show drill down detail by: Item Business Units: All
Group Type: All New/Rewrite Agreement: All
Agreement Group: All Revenue from: All
Agreement: All Contract Role: All
Qualifies as Member: All

Information Learned:

Viewing the report in this manner the user will see all agreement revenue and units generated for a full year, broken down by revenue category.

Case 2: Enterprise View of all agreement revenue from agreements initiated in Jan over the year:

In this use case the user can see how agreements sold in a specific month has continued to generate revenue over the year.

Filters Used:

Agreement Revenue in Months Between: 2/1/2014 – 12/31/2014
Display Revenue for Agreements Initiated: 1/1/2014 – 1/31/2014
Division: All Classification: All
Show drill down detail by: Item Business Units: All
Group Type: All New/Rewrite Agreement: All
Agreement Group: All Revenue from: All
Agreement: All Contract Role: All
Qualifies as Member: All

Information Learned:

Viewing the report in this manner the user will see how agreements sold in January 2014 continued to generate revenue throughout the year.

Report Comparison

Standard Reports

The Agreement Item Revenue is a combination of the agreement reports and the sales revenue report and cannot be balanced to any standard report. It is import to remember the following about this module.

  • Installment revenue is recorded only on the initial sale (down payment)

Warehouse modules

Technical Details

Module Files:

  1.  
  2. WARE_FIN0002_AgreementItemRevenue.lgx
  3. WARE_FIN0002_AgreementItemRevenue_SubReport.lgx
  4.  

Datamart Tables:

  1.  
  2. FROM datamart.RevenueDetails RD
  3. JOIN datamart.Contract C on C.ContractId = RD.ContractId
  4. JOIN datamart.CalendarDay CD on CD.CalendarDayId = RD.InvoiceDateId
  5. JOIN datamart.CalendarMonth M on M.CalendarMonthId = CD.CalendarMonthId
  6. JOIN datamart.BusinessUnit BU on BU.BusinessUnitId = RD.IssuingBusinessUnitId
  7. JOIN datamart.Division D on D.DivisionId = BU.DivisionId
  8. LEFT JOIN datamart.RevenueSku RS on RS.RevenueSkuId = RD.RevenueSkuId
  9.  

Sub-Report:

  1.  
  2. LEFT JOIN datamart.EmployeeWorkRole S on S.EmployeeWorkRoleId = C.SalesPersonId
  3. JOIN datamart.RevenueSku RS on RS.RevenueSkuId = RD.RevenueSkuId
  4. JOIN datamart.Agreement A on A.AgreementId = C.AgreementId
  5.  

Data QA Directive: Compare one month of Revenue with the following query, replacing the dates to match report selections.

  1.  
  2. SELECT sum(Amount) FROM DM.DM_Financial WHERE MemberAgreementID IS NOT NULL
  3. and TransactionDateTime >= '2/1/2014' and TransactionDateTime < '3/1/2014'
  4.  

Compare one month of Units with the following query, replacing the dates to match report selections.

  1.  
  2. SELECT sum(Quantity) FROM DM.DM_Financial WHERE MemberAgreementID IS NOT NULL
  3. and TransactionDateTime >= '2/1/2014' and TransactionDateTime < '3/1/2014'
  4.  

Compare a month of Revenue for Member Agreements that started within a selected period with the following query, replacing the dates to match report selections.

  1.  
  2. SELECT sum(Amount) FROM DM.DM_Financial F
  3. JOIN rpt_MemberAgreementHeader A on A.MemberAgreementId = F.MemberAgreementId
  4. WHERE TransactionDateTime >= '2/1/2014' and TransactionDateTime < '3/1/2014'
  5. AND A.StartDate >= '1/1/2014' and A.StartDate <= '2/1/2014'
  6.