Power BI Report showing financial & contract billing data
Jump to Overview | Samples | Variables | Alert Functionality | Power BI Reporting | | Best Practices & Tips | Related Alerts | Webinar
Overview
Overview
This is a Power BI Report showing financial & contract billing data, budget analysis, and KPI's. Includes Income Statement and Balance Sheet Analysis, as well as Financial Business Ratios, Cash Flow Analysis, Year-over-year comparison, Budget to Actuals and Analysis.
Subscribe to ID40 to get your Budget(s) in the report
Click here for getting Power BI in place.
Click here to download the file. Be sure when you do download, save as the exact file name (i.e. ID770.pbx) of the previous version; otherwise, you will end up with two different ones.
Type of Output: On Demand Power BI Report
* * *
Sample
Sample
Landing Page:
Income Statement:
Operational Summary:
Budget to Actuals:
* * *
Variables
Variables
This alert has no variables
* * *
Alert Functionality
Alert Functionality
-See here for getting Power BI in place.
-Click here to download the file
-Real time financial data pulled from eAuto transactions. We do not calculate any data, we are just pulling into report format what eAuto transactions provide.
-Note you must be signed up for ID770 for this to install and run as we push needed support tables.
-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.
* * *
Power BI Reporting
Power BI Reporting
1. Use filters at top to filter as needed, be sure to review Reporting Period to ensure you have needed Fiscal Period:
2. Details for transactions can be seen / drilled down to:
3. You can categorize on your Income Statement and filter the reports for those categories by using Custom Properties ZCJGLSubGroup01 AND ZCJSubGroup02 on your GL accounts. Populate each Custom Property with the naming of your groups (e.g., "01 Equipment," "02 Service") on appropriate GL accounts. This allows you to separate groups into different layers if needed. Any updates, including new creations or modifications to these categories, will appear in your drop-down filter the following day, after our background tables complete their nightly update.
For more details on creating and setting up custom properties, see our Custom Properties 101 article.
4. The Aged AR Invoices, will only show open invoices IF there is an amount due. If the total amount due is a negative balance, we will show no data on this page.
5. Forecast vs Budgets provides insight of your Actuals, Budget, and Forecasted Actuals. You can filter PnL entities, GL Branches, GL Departments, GL Accounts, and more! Watch the video link below for 4-minute review.
#6
6. SG&A as a function of Gross Margin (Selling G&A) - the KPI we are tracking here is % of Operating Income vs Gross Profit. This page was created in conjunction with needs indicated below (provided by CEO of CEO Juice client):
Most of the industry tracks Selling Expense as a % of Equipment Revenue, not as a % of GP. Best in class is to track both, Selling Expense as a % of Revenue and a % of Equipment GP. This would exclude all other revenue (aftermarket, solutions, rental, MIT, phones, etc.).
Selling expenses are typically:
- Sales Manager Comp
- Sales Rep Comp (salary, draws, commissions)
- All the stuff that goes with payroll above (computers, licenses, payroll, benefits, travel, hotels, meals, etc..)
- Delivery driver expenses
- Customer meals & entertainment
Some of the targets many of the dealers track:
- Selling Expense as a % of Equipment Revenue (target is around 30%)
- Selling Expense as a % of Equipment GP
- Selling Expense as a % of all Imaging Revenue (exclude phones, solutions, MIT, security cameras, etc.) (target is around 16%)
G&A is General and Administrative Expenses. I’ve never seen a dealer or report that doesn’t track this as a function of ALL Revenue. Benchmark used to be 15% pre-covid, now it’s usually 17% because of how high costs are nowadays. Todd Johnson is the only one I see that does G&A as a % of Imaging Revenue and a % of MIT Revenue. Granted, it’s about the exact same thing though around 17-18% for most dealers. G&A expenses are usually:
- Field Admin staff (billing, contracts, lease returns)
- Warehouse & logistics staff
- Accounting staff
- HR, Internal IT, executives, assistants
- Rent, utilities, Internet, phones, repairs
- Legal fees, accounting fees, insurance, etc.
- All the various other expenses that aren’t directly equipment, aftermarket, MIT expenses
* * *
Best Practices & Tips
Best Practices & Tips
None at this time
* * *
Related Alerts
Related Alerts
ID40 - Budget Import Utility
ID281 - Customer Retention Rate Power BI
ID568 - eAuto Activity Trends Power BI
ID704 - The Financial "Model" Power BI
ID771 - Service MIF & Profitability Power BI
ID774 - Customer Business Review Power BI
* * *
Webinar
Webinar
3 minutes preview below, one hour below that.
One hour extended below with sneak peak at "The Model"
Original one hour extended webinar below (outdated now).
0 Comments