Want to slice and dice your most profitable customers, contracts, equipment? Want to know how your segment 3 Canon models compare to Konica? Not just compare costs and profit by model, but customer satisfaction levels also. See some tips here.
Jump to Overview | Samples | Variables | Alert Functionality | Power BI Reporting | KPIs Glossary | ConnectWise Feed | Related Alerts | Webinar
Overview
Overview
Power BI Financial Dashboard providing Service Contract MIF and Profitability. Use to analyze Top/Bottom performers at contract, customer or equipment model level. Includes KPI's to see how clicks are driving revenue and profit as well as which clicks are costing you the most.
Click here for getting Power BI in place.
Click here to download the Power BI template.
-For ECi Hosted clients: We are working with ECi to open up our access so we can set an ODBC connection for you. If you are interested, then please provide us the public static IP address for your company network so that ECi can whitelist it to allow us access.
Type of Output: On Demand Power BI Report
* * *
Sample
Sample
Landing Page:
Customer Profitability:
Year Over Year Revenue Comparison:
Biggest Toner Problems:
Worst Performing Contracts:
Click Data: by Make / Model / Drill down to machine
* * *
Variables
Variables
Variable1: Custom Property on Contracts or Customers indicating the date when an Action Plan will be up for review. This value should match the Custom Property used in our ID768 process.
Variable2: Note Type for notes attached to Contracts or Customers detailing the Action Plan to resolve profitability issues. This value should match the Note Type used in our ID768 process.
Variable3: If you have monthly expenses not easily recorded in e-automate but you want them considered for total Contract/Customer profitability, enter them into the Custom Property of this name on the Contract (you may have to ask your e-auto admin to create the Custom Property if not available).
Variable4: If you have initial expenses to acquire a Contract, and those costs are not easily recorded in e-automate but you want them considered for total Contract/Customer profitability, enter them into the Custom Property of this name on the Contract (you may have to ask your e-auto admin to create the Custom Property if not available).
* * *
Alert Functionality
Alert Functionality
Revenue and cost are from the financial period they are booked in, and not the document date or the coverage period. So, if something was voided and rebilled later and booked in a different financial month, it’s going to show in the new financial month.
1. See here for getting Power BI in place.
2. Click here to download the template.
3. Real time data pulled from eAuto transactions. We do not calculate any data, we are just pulling into report format what eAuto transactions provide.
4. Data includes non-metered contracts/ equipment
5. Max amount of data is 60 months look back (limited due to report/dataset size and Power BI requirements)
6. For ECi Hosted clients: We work with ECi to open up our access so we can set an ODBC connection for you. If you are interested, then please provide us the public static IP address for your company network so that ECi can whitelist it to allow us access.
7. Custom Properties listed in Variable3 and Variable explanation:
Variable3: If you have monthly expenses not easily recorded in e-automate but you want them considered for total Contract/Customer profitability, enter them into the Custom Property of this name on the Contract (you may have to ask your e-auto admin to create the Custom Property if not available).
Variable4: If you have initial expenses to acquire a Contract, and those costs are not easily recorded in e-automate but you want them considered for total Contract/Customer profitability, enter them into the Custom Property of this name on the Contract (you may have to ask your e-auto admin to create the Custom Property if not available).
These costs will then be accounted for you in your results from ID771 as Other Costs.
* * *
Power BI Reporting
Power BI Reporting
1. See this link on How To navigate through hierarchies : https://support.ceojuice.com/hc/en-us/articles/360053721991-ID771-How-to-navigate-through-hierarchies-with-the-power-bi-matrix-visual
2. Use filter option to refine your search / filters
Pay close attention to ALL the filter options including whether you want Contract Only related transactions considered or note:
3. Filters should be noted as these filters carry through each page you access in the report. Another words these filters stay in tact as you move throughout the report:
4. Please note the reporting period filter RM Section. -12 to 0 is the last 12 calendar months plus the days in this current month. Set to -12 to -1 to see last 12 completed months (excludes days in current month). Max allowed is 65-months (we are not pulling in data an older):
5. Be sure to check out Custom Reports to create exactly what you need:
Use this Custom Report to see break down of Clicks and Cost for BW and CLR Supplies, for example:
6. Equipment Analysis:
*Breaks down based on your Model Categories in eAuto. Model Class is set via CEO Juice Model Mapping app. See here on how to update your Model Categories in eAuto: https://support.ceojuice.com/hc/en-us/articles/211464986-How-To-update-your-model-categories-ID295-
Click on any line once and get to drill down options (highlighted below):
For example, KPI Trends:
7. Equipment Costs & Clicks (based on 5 years max of data)
This page is intended to give you an estimate of your click costs by model, device, etc.
Supply BW and Color CPC are easy to determine by using the Usage Limit Meter Type on your Item Records. However, splitting BW and Color CPC for Labor, Parts, and Other is not done in e-Auto. So we have come up with an algorithm to get you as close as we can.
For each equipment record, if there is both BW and/or Color Supply costs, then we use the % split between BW and Color costs to assign the same % split to all other costs (Labor, Parts, Other). We use the same formula for for splitting the CPC between BW and CPC Color as well.
If an equipment record has NO BW or Color Supply costs, then we use the % split between BW and Color clicks billed (based on Meter Type) and assign the same % split to all other costs (Labor, Parts, Other). We use the same formula for for splitting the CPC between BW and CPC Color as well.
**Please note, anything that is NOT given a BW or Color designation (i.e. scans) is not considered in % split costs at all. Because of this, the CPC (total) will not always equal the summary of CPC BW and CPC Color
8. Equipment MIF - comparing Model Profit
Go to Equipment Profitability page:
Be sure your Rolling Months (Date) filter is as you need, then right click anywhere on the left (rows) Model Class and select 'Collapse' -> All
Now that everything is collapsed you can move down levels in the hierarchy by selecting the double arrow 3 times until you get to the Models:
By selecting any model you enable all the drill through buttons to the detail pages:
*** All columns in the table are sortable
9. Please read below if you are comparing Power BI Results for Model Profitability to the eAuto Model Profitably report.
A few key differences between the e-Auto Model Profitability report and our Power BI and eViews:
- The e-Auto Model Profit Report uses the Invoice Date for transactions, whereas Power BI and eView rely on the Fiscal Period.
- The e-Auto Model Profit Report includes a filter that accounts for Billed Items by referencing the ICServiceLedger and checking if Revenue = 0 and Cost ≠ 0. Unfortunately, our current Power BI and eView setups don't support reporting on this specific filter, making it challenging to determine how you might want to include or exclude this information for Model Profit based on your e-Auto transactions.
We find that particular report filter problematic for a number of reasons, primarily because the results of applying the filter can be misleading depending upon the construction of the Contract. By including/excluding ONLY Items explicitly covered by Bill Codes you may not get a proper understanding of profitability. And the EA report itself does not provide sufficient context to inform the user on this point.
There are multiple ways to construct an EA Contract such that revenue from non-covered Items is integral to the overall picture. Add to this the fact that EA Contracts can be constructed so that certain Items are routinely covered DESPITE not flipping the Billed flag (not best practice but we see it often), and our conclusion is that the only valid review of profitability of a Contract is ALL Revenue compared to ALL Costs.
Properly supporting the filter (providing the necessary context to the user) would require multiple changes to our Power BI, including provide complete Item-level detail which would bloat the dataset to the point many clients would need an upgraded Power BI license to support the dataset. Even with that detail it's difficult to provide sufficient context on how each Contract is constructed AND ensure that information is fronted to the user such that it is not easily missed, even if rolled up to the Customer or down to the Equipment level.
* * *
Additional Reporting Options:
Click here to learn how to use ID771 to review profitability of in-state versus out-of-state equipment.
* * *
KPIs Glossary
KPIs Glossary
General Measures
Average Rate: Revenue / Total Clicks Billed (Clicks from Invoice Period Billed) *** this is Revenue Per Click
Machine Count (Machines In the Field): Number of machines in the field during reporting period.
NPS: Net Promoter Score
VR: Valid Responses for Net Promoter Score
Key Services
CSI (Cross Selling Index): Sum of distinct active accounts utilizing services within each Key Service category / Sum of distinct active accounts utilizing at least one Key Service
Clicks Distributed Across Contract Coverage Periods
Based on coverage period, not invoice date. For example, if contract coverage is from 15th - 15th each month, then 1/2 clicks in first month and other 1/2 clicks in second month.
Covered Clicks: Number of clicks included in copy allowance
Overage Clicks: Number of clicks exceeded from copy allowance
Underage Clicks: Number or clicks under copy allowance (did not meet minimum copy allowance)
Invoiced Clicks:
Clicks per the period they were invoiced, clicks are not allocated across coverage periods.
BW Base: Number of base clicks billed for BW meter groups
BW Overages: Number of overage clicks billed for BW meter groups
Color Base: Number of base clicks billed for Color meter groups
Color Overages: Number of overage clicks billed for Color meter groups
Actual Meter Color: Number of actual Color clicks run on machines
Total Clicks Billed: Sum of BW and Color Clicks billed on machines
Actual Clicks Run
Actual clicks produced (per your EA meter readings) for the period listed in your date filter
Actual BW Copies: Number of actual BW clicks ran on machines
Actual Color Copies: Number of actual Color clicks ran on machines
Actual Copies: Number of total actual clicks ran on machines
Cost per Copy
CPC: Total Cost / Actual Copies
CPC Labor: Labor Cost / Actual Copies
CPC Other: Other Cost / Actual Copies
CPC Parts: Parts Cost / Actual Copies
CPC Supplies: Supplies Cost / Actual Copies
**Please note Supplies BW + Supplies Color will NOT equal CPC Supplies.
Example a simplified machine which has (for period reported):
10,000 BW Clicks
15,000 Color Clicks
$250 BW Supplies
$350 Color Supplies
By extension, total clicks is 25,000 and total supply costs is $600
CPC Supplies BW: Supplies BW Cost / Actual BW Copies
CPC Supplies Color: Supplies Color Cost / Actual Color Copies
**We look at your eAuto coding to determine what you consider BLK and CLR.
For Supply and Parts, we use the Usage Limit Meter Type on the Item Record. If none there or not BLK or CLR, then we look at the Meter Clicks Meter Type Category for that period. If none there, then we look at Lifetime Meter Clicks Meter Type Category. If nothing designated as BLK or CLR, then we don't consider in CPC cost.
For Clicks, Labor, and Other, we use Meter Clicks Meter Type Category for that period. If none there, then we look at Lifetime Meter Clicks Meter Type Category. If nothing designated as BLK or CLR, then we don't consider in CPC cost.
Usage Limit Meter Type on Item Record:
Meter Type Category:
* * *
CW Feed
ConnectWise Feed
For clients using ConnectWise to run their IT business they may want to compare the profit numbers that CW shows versus eAutomate. For this to work you need to have data syncing from CW to eAuto, Agreements, tickets etc.
For this 1st version we query CW and get Expected Revenue, Cost, profit & Margin by agreement. Then we compare expected CW agreement profit with actual eAuto contract profit. Added user defined deviation thresholds and highlighted contracts that deviate N% from the expected profit ( positive or negative ).
We also added a report where we show Rolling Agreement Gross Profit. As of Jan 2024 we have only rolled this out to OnPrem CW users but ask us for Cloud.
Related Alerts
Related Alerts
ID281 - Customer Retention Rate Power BI
ID568 - eAuto Activity Trends Power BI
ID704 - The Financial "Model" Power BI
ID770 - Financial Dashboard Power BI
ID771 - Service MIF & Profitability Power BI
ID774 - Customer Business Review Power BI
* * *
Webinar
Webinar
What's New ID771 2024.06.21 Version
Overview What's New:
Key Services / CSR:
MIF:
Equipment Performance:
* * *
Webinar recorded October 5, 2022
0 Comments