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. Highlights suspect data also.
Jump to: Overview | Samples | Variables | Alert Functionality | OnDemand Report Filters | Best Practices & Tips | Related Alerts
Overview
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
Sample
Our email from ID768 will launch this page, click here if you get a security error. ECi Hosted clients will need to copy/paste URL into eAuto hosted site/domain.
The brown section below should match ID768.
Suspect Data
Data entry issues shown under Suspect Cost Transactions, click + to load.
* * *
Variables
Variables
This alert has no variables
* * *
Alert Functionality
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:
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. The revenue is revenue accrued thus far and not revenue billed if a period is billed in advance.
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:
Overview 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.)
- This is the Sales Rep assigned to this Customer Record.
- This is the number of surveys customers replied to in the reporting period and their NPS score for you.
- Total count of distinct active equipment for ALL active contracts.
- Finds the first contract start date for the customers entire history.
- 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.
- Supply Costs
- 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.
- 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.
- Cost Labor includes mileage/travel costs
- Total Clicks Billed / Overage / Underage are "distributed" values - based on coverage period from invoices, NOT invoice date
- 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.)
- Actual Copies / BW / Color are based on clicks billed per invoice date
Other Costs in Overview Section:
Other Costs in Overview Section:
These are any costs not considered labor, parts, or supplies. Typically these are costs incurred via AP transactions. Using eView CEOJuice Contract and Transactional Analysis you can find these transactions by searching for Cost Flag = Other.
For example, ID150 shows Cost Other totaling $8473.00:
Using eView, filter by Contract# and Cost Flag. Voucher numbers shown are AP Invoice numbers:
Customer & Parent Customer Margin
Customer & Parent Customer Margin
- This is the Contract Customer Margin for the selected period and for the contracts that meet the report criteria you selected. Please see THIS LINK for date points explained (i.e. RM01, RM12). **These numbers may not match the customer period totals on the report unless the ID150 report is including ALL contracts for the customer. The report filter does only include the top/bottom 10 contracts by default so the number will not match for customers with a large number of contracts.
- 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 screenshot is of Parent Customer Record showing assigned Locations:
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.
Details Section shows by quarters showing revenue and associated costs, clicks billed with drill down details at Model/Equipment Level:
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.
Data below is looking back 2 years / 24 months
**This section of the report is hardcoded to show the previous rolling year values, the filter for rolling months does not affect this data.
Suspect Cost Transaction(s) Section:
(Select + button to expand for details)
Report includes section to help identify potential service call related issues inflating your expense (i.e. incorrect travel miles or labor hours entered by tech) per ID219/Service Call Data Entry Issues variable settings. Not running ID219, click HERE to subscribe.
In example above, ID219 variables are set to notify if Travel Hours more than 2 and/or if Labor Hours more than 4. ID219 would have reported these at the time the service call was closed and again when invoiced (if not corrected by invoicing).
* * *
Best Practices & Tips
Best Practices & Tips
1. Getting Blank Results? Check your filter settings:
- Did you enter complete Contract# with the -01 or -02?
- Is the Customer Name spelled correctly?
- Has the Contract been active for X months listed in 'Include Only Contracts Active for XX Months"?
- The default setting on filter "Show Only if Customer Margin < X%" is best left at 999 when running report for Best/Worst Of.
2. Are there no Supply Costs showing?
Our 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:
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 Profitability Report will reflect your eAuto updates.
3. Are there no BW or CLR Costs showing, only Total?
Item Records are missing needed Usage Limit Meter Type. This field should be designated as either BLK or Color:
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 Profitability Report will reflect your eAuto updates.
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/hc/en-us/articles/205121080-How-to-Enter-Action-Plan-Snooze-Contract-ID768-ID773-ID560-ID150-and-ID758https://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
OnDemand Report Filters
**Please note, if you are running for specific contract, be sure to populate Contract Number field with entire contract#**
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. **RM00 is a partial month, by definition, so it's not good for comparison to a complete month - some contracts have yet to bill, you don't have a full month of associated costs either - and you can't assume it's clean, because recent problems and errors have not been cleaned up before EOM as they should. Therefore we'd recommend always starting from rolling month RM01.
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. This is the Parent Customer, so you can then select from drop down in Contract Number filter those contracts you wish to review.
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. This is intentionally limited to allow max of 15 to prevent too much heavy lifting required by your server.
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%
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):
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.
* * *
Related Alerts
Related Alerts
ID219 - Closed Call Data Entry Issues
ID560 - Low profit contract (Last 12 months) up for renewal/escalation
ID768 - Worst Performing Contracts
ID555 - Contract Analysis eView
ID174 - Contract Change Audit
2 Comments