Follow

ID150 Contract Profitability SSRS Report Overview & Sample

Contract / Customer Profitability Report is an On Demand SSRS Report providing profitability details by contract. Can also use to show best and worst performing contracts based on gross margin. 

Overview | Samples | Variables |  Alert Functionality | OnDemand Report FiltersBest Practices & Tips |  Related Alerts

Click to Subscribe 

Overview:

It is a customer profitability report not just contract profit.  The report sums up all of a customers contracts to give you a more complete service picture. We found that a straight up contract profitability report could lead to some inaccurate conclusions when you factor in renewals, terminations, multiple contracts for different services across one account..etc. 

It’s really designed to be a ‘Contract Customer’ profitability so that you're always evaluating the customers overall performance even if you filter down to only selected contract types etc.  So the ‘Period’ numbers are always the ‘filtered contracts’…but the Customer Lifetime is the total of ALL contracts billed thru eAutomate regardless of any ‘ContractTypes’ you filter the report on.  Reasoning is if we showed only Totals for the contracts selected..then client is not evaluating the complete picture and will not have enough information to make an informed decision.

Type of Output: On Demand SSRS Report

*  *  *

Sample

1.PNG

2.PNG

3.PNG

*  *  *

Variables

This alert has no variables

 

 *  *  *

Alert Functionality

1. Please note SSRS Report does not pick up info from already terminated contracts. It is only able to pull profitability info on active contracts.

2. Running for specific contract? Always be sure to list the complete active Contract Number (with -01 or -02, for example) in filter; otherwise report will return blank results:

ID150_Contract.PNG

3. All Revenue & Clicks are distributed into the coverage period on the contract invoices. This report will include data only as far back as the history you have billed contracts for in eAutomate and does not incorporate conversion data.

Period Totals Reflect previous Rolling Months per your selections. This does not include any revenue/cost for the Current Month. However the "lifetime" totals do include the current month.

ID150_Verbiage.PNG

4. Detailed Explanation of Results

Top section of report shows your summarized selected filter options as well as special notes that greatly impact the data in the report results:

This section provides Revenue/Costs/Click Details at both Customer and Contract Level:

The contract major level of the report represents the totals (for the selected period and the lifetime) for all contracts that have the same contract number (prefix to the revision #) and we show the count of Active contract revisions for this Contract Major# and the Contract Major period are the total for all revisions and the selected reporting period...and the Lifetime are the total for all the revisions for all revenue/cost billed in eAutomate. (Please see THIS LINK for more details on Lifetime margins.)

ID150Detail_1.PNG

  1. This is the Sales Rep assigned to this Customer.
  2. This is the number of surveys customers replied to in the reporting period and their NPS score for you.
  3. Total count of distinct active equipment for ALL active contracts.
  4. Finds the first contract start date for the customers entire history.
  5. The Lifetime numbers here in the Customer Level represent ALL contract related revenue/costs on ALL contracts for the Customer, not just the filtered contracts that appear on this report.
  6. Supply Costs
    1. Cost Supplies = anything cost for item associated to contract that is coded as S-Supply on Item Record. If the Total Cost does NOT equal the Color and BW Supply Costs, then there is a Supply Item Record missing the Meter Usage Type Field. Use our ID555 eView to see the details.
    2. Color and BW Supply Costs are connected via the Meter Usage Type listed in Toner Item Record. If one of these costs is missing, then first check your Meter Usage fields. Use our ID555 eView Contract Analytics to find Item Records missing Meter Usage setting. 
    3. Cost Labor includes mileage/travel costs
  7. Underage is clicks paid for, but not used (i.e. 10,000 clicks but you only recorded 6,000 for the period. You paid for 4,000 clicks you did not use.)

Customer & Parent Customer Margin

1.png

  1. This is the Contract Customer Margin for the selected period and for the contracts that meet the report criteria you selected.
  2. This is the Parent Customer's Lifetime margin for ALL contracts billed in eAutomate. Therefore you must set up customers in the parent/child relationships for the Lifetime totals to work. The "Parent" company is put on the contract as the customer - not the ‘bill to’, but the actual contract customer. This screen shot is of Parent Customer Record showing assigned Locations:ParentID150.PNG

The "Lifetime" totals on the report include all the history for that Customer and Contract that they have in eAuto thru the day the report is run. However the RQ totals on the contract level of the reports are as of the last "full" month. So they will find a discrepancy between "Lifetime" numbers and the contract totals (assuming the contract is no more that two years old) due to any billing/expenses incurred in the current month. 

 

 

This section shows by quarters showing revenue and associated costs, clicks billed with drill down details at Model/Equipment Level:

ID150Detail_2.PNG

Drill down details show at ID# level Revenue/Cost/Clicks Billed and will include lease details if linked to contract:

Use our ID555 eView CEOJuice Contract Analytics to see details at transaction level.

1__2_.PNG

  1. Details for RY01 (Rolling Year 01 = last completed 12 months)
  2. Details for RY02 (Rolling Year 02 = last completed 24 months)

Suspect Cost Transaction(s):

ID219Section.PNG

If you're subscribed to alert ID219/Closed Call Data Entry Issues, then this section will show Service Invoices that are suspect and could be effecting your costs. 

*  *  *

Best Practices & Tips

1. Getting Blank Results? Check your filter settings:

  1. Did you enter complete Contract# with the -01 or -02?
  2. Is the Customer Name spelled correctly?
  3. Has the Contract been active for X months listed in 'Include Only Contracts Active for XX Months"?
  4. The default setting on filter "Show Only if Customer Margin < X%" is best left at 999 when running report for Best/Worst Of.

Blankd.PNG

2. Are there no Supply Costs showing?

ID150_Blank_Supply.PNGOur report only qualifies Item Records set as Supply Items for the Cost Supplies column. You must have Service Code Category on Item Record Reflect S-Supply:ID150_Supply_Service_Code.PNG

Use CEO Juice eView CEOJuice Contract and Transactional Analysis eView to find which Item Records not coded correctly. Sort by Contract Number and RevCostType contains Sales. This will show you Item Records and their Service Code so you can identify which ones to correct. Our nightly background tables will need to run after you update Item Records before ID150 Contract Profitabilty Report will reflect your eAuto updates.

ID150_eView_Service_Code.PNG

3.  Are there no BW or CLR Costs showing, only Total? 

ID150_0_cost_supply.PNG

Item Records are missing needed Usage Limit Meter Type. This field should be designated as either BLK or Color:

ID150_ULMT.PNG

Use CEO Juice eView CEOJuice Contract and Transactional Analysis eView to find which Item Records missing. Sort by Contract Number and RevCostType contains Sales. This will show you Item Records with no ItemMeterTypeCategory that need updating. Our nightly background tables will need to run after you update Item Records before ID150 Contract Profitabilty Report will reflect your eAuto updates.

ID150_eView_Item_Meter_Type.PNG

4. We have built the report to accommodate a process whereby your service managers could look at a small/manageable slice of their worst performing contract customers, sit with sales and decide what, if any, actions need to be taken to bring the account into the black, and create an action plan note on the contract and set a ‘next review’ snooze date (See https://support.ceojuice.com/entries/23501038-ID150-Contract-Profit-Snoozing-Contracts) so that the contracts will not show up on the worst performing again until after that ‘snooze’ date, and then only if it’s still in an unprofitable state.

*  *  *

OnDemand Report Filters

 **Please note, if you are running for specific contract, be sure to populate Contract Number field with entire contract#**

ID150_Filters.PNG

From Rolling Month AND Back to Previous Rolling Month - RM01 is the last full month that has past (so if it is September 2016 now, then RM01 is August 2016.) and RM12 goes back to 12 months. 

Customer Search (contains) - this search allows you to pull up Customer Name(s) contain whatever text you enter in this field. If you would like exact customer, then you will need to put exact customer name as it is in eAuto.

Contract Number - this requires the EXACT Contract Number in eAuto, with revision number included (i.e. -01 extension)

**The following filters should be used when running report to find grouping of contracts or group of best or worst. If you're using this report to run on one contract, use defaults on filter.

Include Only Contracts Where Avg Monthly Rev > - indicate greater than revenue amount as perhaps you don't want to consider super low revenue contracts. To run wide open, please leave at default of -999.

Show Only If Customer Margin < X% - indicate less than margin % as perhaps you want to filter down to specific margin level customers/contracts. To run wide open, please leave at default of 999.

Show Only (X) Best/Worst Contracts - maximize the amount of customers/contracts included in this report so you keep to a manageable size to effectively work on. 

Include Only Customers With Current Active Contract - Yes/No, you may not want, for example, ABC Realty location #1 info if there's no active contract on it anymore, but do want to see their location #2 info.

Include Snooze Contract Dates Thru - can filter out by date any contracts "snoozed" for review, see THIS LINK on "snoozing" contracts.

Show Equipment Detail - by selecting YES, the report will automatically provided equipment location, contract dates, etc. on report. You can also use drill down in report if you select NO.

Min # Of Active Machines On Any Contract - this allows you to limit contracts shown to minimum number of active machines currently under the contract

Branch - you can use this filter to run for specified branch

Show Notes - report can show you notes recorded on Contract Record

Sort Results By - filter by Worst GM Dollars, Worst GM%, Best GM Dollars, Best GM%

ID150_BestWorst.PNG

This is based on Contract Major section (not Customer Period Totals) Margin $ or Contract Major Period % AND not based on lifetime but based on period filters (i.e. RM01 - RM12): 

ID150_BestWorst_Marg.PNG

 

Exporting Report: Save as icon - you can export the report to PDF, Excel or Word. We always recommend excel as that provides best option for formatting, printer settings, editing, etc.

f.PNG

 

*  *  *

Related Alerts

ID219 - Closed Call Data Entry Issues

ID560 - Low profit contract (Last 12 months) up for renewal/escalation

ID768 - Worst Performing Contracts

ID773 - Worst Profitable Customers

ID555 - Contract Analysis eView

ID174 - Contract Change Audit

 

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

2 Comments

  • 0
    Avatar
    Michael Hippler

    #4 If the old contract was terminated and they have a new contract it pulls information from the old contract also. Creates a large negative in my case.

  • 0
    Avatar
    Juice Support

    Michael,

    It's meant to be more of a customer profitability report rather than a contract profitability report. See this post. https://support.ceojuice.com/entries/23478611-ID150-Output-explained

Please sign in to leave a comment.
Powered by Zendesk