ID770 Financial Insights is a Power BI report built on your e-automate general ledger. It brings the Income Statement, Balance Sheet, budgets, cash flow, financial business ratios and key operational reports together in one place, with prior-year and budget comparisons on every page.
Jump to a specific section by clicking a link
Overview | Samples | Variables | Alert Functionality | Power BI Reporting | Related Alerts | Webinar
Click to Subscribe and Download Template
Overview
Overview
This is a Power BI Report showing financial & contract billing data, budget analysis, and KPIs. It includes Income Statement and Balance Sheet Analysis, as well as Financial Business Ratios, Cash Flow Analysis, Year-over-Year comparison, Budget to Actuals and Budget Analysis.
Month-end financial statements tell you where you landed, but they rarely tell you why. Financial Insights lets owners, controllers and department managers see every result next to the same period last year and next to budget, slice it by branch, department, customer type or your own account groups, and drill from any number straight down to the general ledger entries behind it. Liquidity and profitability ratios, inventory turns and SG&A efficiency are tracked month by month, and the operational pages surface money that is still in motion: unbilled contracts, open sales orders, and open A/R and A/P.
The report is delivered as a Power BI template that connects to your e-automate data. Once it is in place, you open the landing page and click into any of the 50+ report pages. Two optional GL account custom properties unlock the account group and inventory group views. See Alert Functionality for setup, and Power BI Reporting for a page-by-page guide.
Type of Output: On Demand Power BI Report
* * *
Sample
Sample
* * *
Variables
Variables
None
* * *
Alert Functionality
Alert Functionality
-See here for getting Power BI in place.
-Click here to download the file
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.
Optional setup: GL Account Groups (Account Group #1 and #2)
You can categorize your Income Statement and filter the reports for those categories by using the custom properties ZCJGLSubGroup01 and ZCJGLSubGroup02 on your GL accounts. Populate each custom property with the naming of your unique groups (e.g., "01 Equipment," "02 Service") on the appropriate GL accounts. Using two properties lets you separate groups into different layers if needed (for example, 01 Equipment in Group #1 and 01.1 Copiers / 01.3 Wide Format in Group #2). Any updates, including new groups or changes to existing ones, will appear in your drop-down filters the following day, after our background tables complete their nightly update.
Create these custom property attributes with a data type of User-Defined Lookup and add your groups to the Lookup List. Save the attributes and add them to your custom property configuration for GL Account Properties. For more details on creating and setting up custom properties, see our Custom Properties 101 article.
Once populated, your groups appear in the Account Group #1 and Account Group #2 filters on every page, and they drive the Sub Groups, Analysis and Budgeted Groups pages. Accounts without a value show as UnGrouped.
Optional setup: Inventory Groups (Inventory Turns)
For the Inventory Turns page, categorize the inventory-related GL accounts (Inventory on the Balance Sheet side and CoGS on the Income Statement side) using a custom property called ZCJGLInventoryGroup. Give the inventory account and its matching CoGS account the same group (e.g., Equipment, Parts, Supplies) so turns can be calculated per group. Any updates will appear in your drop-down filter the following day, after our background tables complete their nightly update.
Create this custom property attribute with a data type of User-Defined Lookup and add your groups to the Lookup List. Save the attribute and add it to your custom property configuration for GL Account Properties. For more details, see our Custom Properties 101 article.
* * *
Power BI Reporting
Power BI Reporting
Below is a guide to every page in the report. Click a page name to jump to it.
Report Pages
Income Statement Analysis: Income Statement *** Tabular Analysis | Income Statement Summary | Income Statement Analysis | Rolling Income Statement | Rolling Income Statement Analysis | Income Statement *** Per Branch | Income Statement *** Per Branch Transposed | Income Statement *** Sub Groups | Income Statement *** Sub Groups II | Income Statement *** Per Department | Income Statement *** Per Department Transposed | Income Statement *** Cumulative Rolling Periods | Customer Profit *** Customer Type First | Customer Profit *** PnL First | Income Statement *** eAuto | Income Statement *** eAuto - Rolling
Budget Analysis: Budget to Actuals | Budget to Actuals *** Per Branch | Budget to Actuals *** Per Department | Budget to Actuals *** Per GL Account | Budget Analysis | Income Statement *** Budgeted | Income Statement *** Budgeted Groups | Actuals vs Multiple Budgets | Just Budgets | Forecasts vs Budgets *** Graphical view | % to Revenue *** vs Budgets
Budget Allocations (ID40): Budget Allocations (ID40)
Balance Sheet Analysis: Balance Sheet *** Tabular Analysis | Balance Sheet Summary | Balance Sheet Analysis | Rolling Balance Sheet
Financial Business Ratios: Current Ratio | Quick Ratio | Debt to Equity | Return on Assets (ROA) | Return on Equity (ROE) | Sales to Assets | SG&A as a function of Gross Margin | Inventory Turns
Cockpits & Operational Reports: Financial Overview | General Ledger Details | Income Statement Details | Balance Sheet Details | Operational Overview | Unbilled Contracts Due | Open Sales Orders | Open Sales Orders Details | Aged A/R Invoices | Open A/R Notes & Activities | Aged Open A/P Invoices
Cash Flow Analysis: Cash Flow Statement | Rolling Cash Flow
Navigating the Report (landing page, filters, terms and colors)
Navigating the Report
Landing page. The report opens on the ID770 landing page. Report pages are grouped into Income Statement Analysis, Budget Analysis, Budget Allocations (ID40), Balance Sheet Analysis, Financial Business Ratios, Cockpits & Operational Reports, and Cash Flow Analysis. Click any link to open that page. The left panel shows the report revision, when the data was last refreshed, the signed-in user, a Documentation / Webinar link and the revision log (What is new?). The house icon in the top-left corner of every page brings you back to the landing page.
Filter bar. Most pages share the same filters across the top. Changing them updates every table, card and chart on the page.
- GL Branches / GL Departments: limit results to one or more branches or departments.
- Parent Customer Type: limit results to GL entries tied to customers of a given type.
- Account Group #1 / #2: filter by your ZCJGLSubGroup01 / 02 custom property groups (see setup).
- GL Account: filter to specific accounts.
- Report: the time window for amounts: Month to Date, Quarter to Date or Year to Date.
- Versus: what the period is compared against (e.g., Same Period Previous Year).
- Amounts in: the unit used to display amounts.
- Rolling Months: the slider sets the months shown in the trend charts. The right-hand value sets the reporting period (0 = current period, -1 = last month), and the left-hand value sets how far back the trend goes (e.g., -12). The Reporting and Against lines underneath confirm the exact dates and fiscal periods being compared.
- BS Groups (Balance Sheet pages) and Budget (Revision) (budget pages) appear where they apply.
Common terms.
- AC: cumulative actual amount for the selected reporting period.
- Prior: cumulative actual amount for the comparison period.
- Bdg: cumulative budgeted amount for the selected reporting period.
- Δ Prior / Δ Prior %: the difference between AC and Prior, in dollars and as a percentage.
- Δ Bdg: the difference between actuals and budget. Δ Bdg % shows actuals as a percentage of budget: 100% means on budget, below 100% means actuals are short of budget.
- PnL Entities: the Income Statement lines (Revenues, Cost of Goods Sold, Gross Margin, Operating Expenses, Operating Income, Non Operating Expenses, Tax Expenses, Net Income / (Loss)).
- UnGrouped: GL accounts that have no Account Group custom property value.
Colors and charts. Green means the change is favorable and red means it is unfavorable, so an increase in Cost of Goods Sold or Operating Expenses shows in red while an increase in Revenues shows in green. On the Δ prior & Δ Prior % charts, bars show the monthly difference in dollars and the black line shows the percentage difference. On the Actual & Prior charts, the darker line is the current period and the lighter line is the comparison period. Dotted lines on trend charts are trend lines. Because Year to Date amounts are cumulative, these charts reset to a low value at the start of each fiscal year.
Drill through. On pages that show the note Select a PnL Entity to enable Drill-Through, right-click any PnL Entity and choose Drill through > Drill - Income Statement to open the detail page for that line, down to individual GL entries. Use the back arrow at the top-left of the detail page to return. See the Income Statement *** Tabular Analysis page for a step-by-step example.
Hover, sort and export. Hover over any chart point for exact values, click a column header to sort a table, and use the ... menu on any visual to export its data.
Income Statement Analysis
Income Statement *** Tabular Analysis
Purpose: The core Income Statement for the selected period, compared line by line with the comparison period, with trend charts and drill-through to the GL entries behind every number.
Expected Outcome: You can see at a glance whether revenue, margin, expenses and income improved or declined versus last year, in which months the change happened, and which branches, departments or accounts drove it.
How to Read the Page:
- Income Statement table: each PnL Entity with Prior, AC, Δ Prior and Δ Prior %.
- % Contribution to Revenue: Gross Margin %, Operating Income % and Net Income / Loss % as a share of revenue, current versus prior. A margin that falls while revenue grows is an early warning sign.
- Revenues: Δ prior & Δ Prior %: the month-by-month gap between this year and last year.
- Revenues: Actual & Prior: cumulative actuals against the comparison period.
- Gross Margin % (vs Revenue) Trend: monthly gross margin percentage with a trend line.
Change the charts to another line. Click any PnL Entity in the table (for example, Gross Margin) and the two charts at the top right switch to that line. Click it again to go back to Revenues.
Drill into the details.
- Right-click the PnL Entity you want to investigate (for example, Revenues).
- Choose Drill through, then Drill - Income Statement.
- The Revenues Analysis (Multiple Groups) page opens, filtered to that line and keeping your filter bar selections.
- Use the back arrow at the top-left to return to the Income Statement.
The detail page shows summary cards (Prior, AC, Δ Prior, Δ Prior %), a contribution table, the same trend charts, and the General Ledger entries list with reference, transaction date and type, GL account, journal description, module, branch, department, account group and amount.
Use Slice Results By to break the contribution table down by Branch, Department, Account, Account Group #1 or Account Group #2.
Income Statement Summary
Purpose: An executive snapshot of the Income Statement using KPI cards, with an Analyze selector to chart any line.
Expected Outcome: A quick read on overall performance before digging into detail pages. It is useful for a leadership meeting or a first look after month-end close.
How to Read the Page:
- KPI cards: Revenues, CoGs, Gross Margin, Oper. Expenses, Oper. Income, Other Expenses, Tax Expenses and Net Income. Each card shows the current amount and the percent change versus the comparison period, with an arrow for direction and color for whether the change is favorable.
- Analyze: choose which PnL Entity the two charts on the right display (Cost of Goods Sold in the sample).
- % Contribution to Revenue and Gross Margin % (vs Revenue) Trend: margin percentages for the period and by month.
Income Statement Analysis
Purpose: A drillable Income Statement that breaks each Account Type down by Account Group #1, Account Group #2 and individual GL account.
Expected Outcome: You can pinpoint which product lines, sub-groups and specific accounts are behind a change, for example that Equipment revenue is up because Wide Format grew while Copiers declined slightly.
How to Read the Page:
- Click the + / − icons to expand or collapse each level. Bold rows are subtotals.
- Columns show Prior, AC, Δ Prior and Δ Prior % at every level.
- Accounts without an Account Group value appear under UnGrouped (see GL Account Groups setup).
- The KPI cards and revenue charts respond to the filter bar and give context for the table.
Rolling Income Statement
Purpose: The Income Statement laid out month by month across the Rolling Months window, with the comparison for each month.
Expected Outcome: You can see how each line developed through the year and spot the month where a trend started or reversed.
How to Read the Page:
- Each month column shows AC, Δ Prior and Δ Prior % for that month. Scroll right to see more months.
- Amounts follow the Report setting. With Year to Date, each column is the fiscal year-to-date total through that month.
- The % Contribution to Revenue (Rolling) table below shows Gross Margin %, Operating Income % and Net Income / Loss % for each month.
Rolling Income Statement Analysis
Purpose: The month-by-month rolling view combined with the Account Type / Group #1 / Group #2 / Account hierarchy.
Expected Outcome: You can track how each account group performed month by month, for example whether Service revenue is closing the gap to last year.
How to Read the Page:
- Rows start at Account Type (Revenues, Cost of Goods Sold, Operating Expenses, Other Expenses). Expand with + to see Account Group #1, then Group #2, then individual GL accounts.
- Each month column shows AC, Δ Prior and Δ Prior %. Scroll right for more months.
Income Statement *** Per Branch
Purpose: The Income Statement with a column group for each branch.
Expected Outcome: A side-by-side comparison of branch profitability and year-over-year change, showing which branch is carrying results and which needs attention.
How to Read the Page:
- Each branch has AC, Δ Prior and Δ Prior % columns. Scroll right to see all branches.
- The % Contribution to Revenue (per Branch) table compares margin percentages across branches.
- The charts on the right show the combined results for your filter selection. Filter to a single branch to chart it on its own.
Income Statement *** Per Branch Transposed
Purpose: The same branch comparison turned sideways: branches are rows and each PnL Entity is a column group.
Expected Outcome: An easy way to rank branches. For example, sort by Gross Margin Δ Prior to see which branch improved the most.
How to Read the Page:
- Each PnL Entity has AC, Δ Prior and Δ Prior % columns, with a Total row at the bottom.
- Click a column header to sort the branches by that measure.
- The % Contribution to Revenue (per Branch) table shows Gross Margin %, Operating Income % and Net Income / Loss % for each branch.
Income Statement *** Sub Groups
Purpose: The Income Statement with each PnL Entity expandable into your Account Group #1 categories.
Expected Outcome: You can see how Equipment, Service, Supplies and your other groups contribute to each line of the Income Statement, and how each group changed versus the comparison period.
How to Read the Page:
- Expand a PnL Entity with + to list its account groups, then expand again for the next level.
- Columns show Prior, AC, Δ Prior and Δ Prior %.
- Requires the ZCJGLSubGroup01 custom property. See GL Account Groups setup.
Income Statement *** Sub Groups II
Purpose: A mini Income Statement for each account group. The group comes first, and the full set of PnL Entities sits underneath it.
Expected Outcome: You can review each business line as its own P&L, for example Equipment revenue, cost, gross margin, expenses and net income, and compare those results year over year.
How to Read the Page:
- Use Group PnL by to choose which account group level to group by (Account Group #1 in the sample).
- Each group lists Revenues, Cost of Goods Sold, Gross Margin, Operating Expenses, Operating Income, Non Operating Expenses and Net Income / (Loss) with Prior, AC, Δ Prior and Δ Prior %.
- A group with little direct expense coded to it will show a high operating income. This reflects how expenses are assigned to groups in your GL.
Income Statement *** Per Department
Purpose: The Income Statement with a column group for each GL department.
Expected Outcome: You can compare department results and year-over-year change side by side.
How to Read the Page:
- Each department has AC, Δ Prior and Δ Prior % columns. Scroll right to see all departments.
- The % Contribution to Revenue (per Department) table compares margin percentages across departments.
- Very large percentages appear when a department had little or no activity in the comparison period.
Income Statement *** Per Department Transposed
Purpose: Departments as rows and PnL Entities as column groups, for ranking and sorting departments.
Expected Outcome: You can quickly find the departments with the largest revenue, margin or expense changes.
How to Read the Page:
- Each PnL Entity has AC, Δ Prior and Δ Prior % columns, with a Total row. Click a column header to sort.
- A row with no department name holds amounts posted without a department.
- The % Contribution to Revenue table shows margin percentages for each department.
Income Statement *** Cumulative Rolling Periods
Purpose: The Income Statement for a trailing period (for example, the last 12 months) compared with the same trailing period one year earlier, independent of the fiscal year.
Expected Outcome: A smoother, seasonality-free view of the business trend. The charts no longer reset at the start of the fiscal year, so it is easy to see whether the business is growing on a trailing 12-month basis.
How to Read the Page:
- Cumulative Rolling Period Comparison: the number of months in each trailing total (12 in the sample).
- The lines at the top right confirm the two windows being compared (for example, AC = 2021-02 through 2022-01 and Prior = 2020-02 through 2021-01).
- In the charts, each point is a trailing total ending in that month, so the Actual & Prior chart shows when trailing revenue overtook the prior-year trend.
Customer Profit *** Customer Type First
Purpose: Profitability by customer segment. Each Parent Customer Type is shown first, with its own Income Statement underneath.
Expected Outcome: You can see which customer segments are the most and least profitable, and how each segment changed versus last year.
How to Read the Page:
- Expand a customer type to see its Revenues, Cost of Goods Sold, Gross Margin, Operating Expenses and Net Income / (Loss).
- <No Assigned Customer Type> holds customer activity where the customer has no type in e-automate. <Not Associated to a customer> holds GL activity that is not tied to a customer, such as overhead expenses.
- The Revenues by Customer Type table on the right ranks segments by revenue.
- A large No Assigned Customer Type amount is a sign that customer types need to be maintained in e-automate.
Customer Profit *** PnL First
Purpose: The same customer-type profitability data organized by PnL Entity first, with customer types listed under each line.
Expected Outcome: You can see how each segment contributes to a single line, for example the share of revenue or cost that comes from Leasing Companies versus Government customers.
How to Read the Page:
- Expand a PnL Entity to list every Parent Customer Type with Prior, AC, Δ Prior and Δ Prior %.
- The Revenues by Customer Type table ranks segments by revenue; click a header to sort.
Income Statement *** eAuto
Purpose: An account-level Income Statement that lists every GL account under its PnL Entity, in the account-by-account format you are used to from e-automate.
Expected Outcome: You can tie the report back to your e-automate financial statements account by account, and spot individual accounts with unusual changes.
How to Read the Page:
- Expand or collapse each PnL Entity with + / − to show or hide its GL accounts.
- Each account shows Prior, AC, Δ Prior and Δ Prior %.
- Use the GL Account filter to focus on specific accounts.
Income Statement *** eAuto - Rolling
Purpose: The account-level e-automate style Income Statement with a column for each month in the Rolling Months window.
Expected Outcome: You can follow each GL account month by month and spot the month an account moved unexpectedly.
How to Read the Page:
- Rows are GL accounts grouped by PnL Entity; columns are months. Scroll right to see more months.
- Amounts follow the Report setting (for example, Year to Date through each month).
Budget Analysis
Budget pages compare actuals with the budgets stored in e-automate. Use the Budget (Revision) filter to choose which budget to compare against. Budgets can be loaded with the ID40 Budgets Import Utility.
Budget to Actuals
Purpose: A budget-versus-actual Income Statement for the selected fiscal period, showing month to date, year to date and full year in one table.
Expected Outcome: A month-end budget review in one view: how the month went, where you stand year to date, and how much of the full year budget has been achieved.
How to Read the Page:
- Period Slicer: choose the fiscal period to report on.
- Month to Date: MTD actuals, Bdg MTD, Δ Bdg MTD and Δ Bdg MTD %.
- Year to Date: YTD actuals, Bdg YTD, Δ Bdg YTD and Δ Bdg YTD %.
- Full Year: Bdg FY, Δ Bdg FY (remaining to reach the full year budget) and Δ Bdg FY % (year-to-date actuals as a percentage of the full year budget).
- Green / teal values are favorable and red values are unfavorable.
Budget to Actuals *** Per Branch
Purpose: Budget to Actuals with each PnL Entity broken down by branch.
Expected Outcome: You can see which branches are ahead of or behind budget for the month, year to date and full year.
How to Read the Page:
- Expand a PnL Entity to list each branch. The MTD, YTD and Full Year column blocks work as on the Budget to Actuals page.
- Budgets must be entered at the branch level for branch comparisons to be meaningful.
Budget to Actuals *** Per Department
Purpose: Budget to Actuals with each PnL Entity broken down by department.
Expected Outcome: You can hold each department accountable to its budget and find the departments with the largest variances.
How to Read the Page:
- Expand a PnL Entity to list each department with MTD, YTD and Full Year comparisons.
- Departments with actuals but no budget (or the reverse) show blank percentages.
Budget to Actuals *** Per GL Account
Purpose: Budget to Actuals at the individual GL account level.
Expected Outcome: You can find the specific accounts behind a budget variance and check whether the budget itself was realistic.
How to Read the Page:
- Expand a PnL Entity to list its GL accounts with MTD, YTD and Full Year comparisons.
- Use the GL Account or Account Group filters to focus on a single area.
Budget Analysis
Purpose: Actuals versus budget through the Account Type / Account Group #1 / Account Group #2 / Account hierarchy, with trend charts.
Expected Outcome: You can see which account groups are driving the gap to budget and how the gap developed month by month.
How to Read the Page:
- KPI cards: each Income Statement line with actuals as a percentage of budget (for example, Revenues 87.81% of budget).
- Table: AC, Bdg, Δ Bdg and Δ Bdg % at each level. Expand with + to go deeper.
- Revenues: Δ Budget & Δ Budget %: the monthly gap to budget in dollars and percent.
- Revenues: Actuals & Budget: actuals (solid line) against budget (dashed line) for the months that have a budget.
Income Statement *** Budgeted
Purpose: The Income Statement compared with both the prior period and the budget in one table.
Expected Outcome: A complete picture of each line: whether you beat last year, and whether you hit the plan.
How to Read the Page:
- Columns: Prior, AC, Δ Prior, Δ Prior %, then Bdg, Δ Bdg and Δ Bdg %.
- Charts show Revenues against budget, the monthly change versus prior, and Actual & Prior.
Income Statement *** Budgeted Groups
Purpose: The prior-and-budget Income Statement broken down by Account Group.
Expected Outcome: You can see which account groups beat last year and which hit budget, for example Service revenue slightly ahead of last year but well behind budget.
How to Read the Page:
- Each PnL Entity lists its account groups with AC, Δ Prior, Δ Prior %, Bdg, Δ Bdg and Δ Bdg %.
- KPI cards show each line's actuals as a percentage of budget.
Actuals vs Multiple Budgets
Purpose: Actuals compared against several budget revisions at once.
Expected Outcome: You can see which budget version is closest to reality, or compare an original budget with a re-forecast.
How to Read the Page:
- Select two or more revisions in Budget (Revision). The table shows a column group (AC, Bdg, Δ Bdg, Δ Bdg %) for each budget.
- The charts on the right show a small chart for each selected budget.
- If a budget has no amounts for the reporting period, its Bdg column is blank and Δ Bdg equals the actuals.
Just Budgets
Purpose: Budget revisions compared side by side, without actuals.
Expected Outcome: You can confirm what changed between budget versions and check that a budget was loaded correctly (for example, that it is spread across account groups rather than all UnGrouped).
How to Read the Page:
- Select the budget revisions to compare in Budget (Revision). Each revision is a column.
- Rows follow the Account Type / Account Group hierarchy.
- The Revenues Budgets charts show the monthly revenue budget for each revision.
Forecasts vs Budgets *** Graphical view
Purpose: A cumulative chart of actuals and forecast against budget for the fiscal year, for any Income Statement line.
Expected Outcome: An answer to "if we keep going at this pace, will we hit the budget?" by line, branch, department, account group or account.
How to Read the Page:
- Fiscal Year / BudgetName: choose the year and the budget to compare against.
- Report on: the PnL Entity to chart (Revenue, CoGs, Gross Profit, Operating Expenses, Operating Income, Other Expenses, Tax Expenses or Net Income / (Loss)).
- Visualize: turn the Actuals, Budget and Forecast lines on or off.
- Base period for forecasts: the last fiscal period whose actuals are used to build the forecast (Current or Previous Fiscal Period). Use Previous if the current month is not closed yet.
- Slice results by: split the chart by GL Branch, Department, Account Group #01 / #02 or GL Account. Results are ordered by actuals, largest first.
- Actuals / Forecasts labels: show no labels, amounts, the difference to budget, or the percentage of budget.
- The solid black line is actuals, the gray dashed line is forecast and the orange dashed line is budget. Percentage labels show actuals or forecast as a percentage of budget, green at 100% or more and red below.
Watch the video below for a 4-minute review.
% to Revenue *** vs Budgets
Purpose: Margin percentages (Gross Margin %, Operating Income % and Net Income %) compared against the margins in your budget.
Expected Outcome: You can see whether you are converting revenue into profit at the rate you planned, even when revenue itself is above or below budget.
How to Read the Page:
- Each KPI group shows the actual percentage, the budgeted percentage and Performance, the difference in percentage points (red when below budget).
- In each chart, the solid line is the actual percentage and the dashed line is the budgeted percentage. The markers show the monthly gap, green when ahead of budget and red when behind.
Budget Allocations (ID40)
Budget Allocations (ID40)
Purpose: A starting point for next year's budget: take a past fiscal year's actuals, adjust them by percentages, and export the result for the ID40 Budgets Import Utility.
Expected Outcome: A realistic, period-by-period budget file built from your actual history in minutes instead of building it from scratch.
How to use it (the steps are also shown on the page):
- Select a full fiscal year in the Fiscal Year filter.
- Filter any of the dimensions (Account Type, Account Group #1 / #2, GL Account, Branch, Department) to match the granularity of your budget.
- Set the Adjust Revenue by, Adjust CoGs by and Adjust All Expenses by percentages. Expense adjustments apply to Operating, Other and Tax expenses. To apply different factors, filter by Account Type and export separate files.
- Export the table (... menu > Export data) and paste it into the ID40 Budget Utility.
How to Read the Page:
- The main table lists each account, department and branch with the adjusted amount for each period (PA-01 through PA-13) and the adjusted total (PA-Total).
- The summary tables below compare the original total (P-Total) with the adjusted total (PA-Total) by Account Type, Account Group #1, Account Group #2, Branch and Department.
Balance Sheet Analysis
Balance Sheet pages show balances as of the end of the reporting period, compared with the same point in the comparison period. Liabilities and equity carry credit balances, so they appear as negative numbers. Use BS Groups to filter by balance sheet grouping.
Balance Sheet *** Tabular Analysis
Purpose: The Balance Sheet Statement as of the reporting period, compared with the comparison period.
Expected Outcome: You can see how the company's financial position changed: cash, receivables, inventory, fixed assets, payables and equity, and how the asset base trended through the year.
How to Read the Page:
- Assets: Current Assets (Cash, Accounts Receivable, Inventory, Other Current Assets) and Non Current Assets (Fixed Assets, Noncurrent Assets, Accumulated Depreciation).
- Liabilities & Equity: Liabilities (Accounts Payable, Current Liabilities, Noncurrent Liabilities) and Equity (Capital).
- Total Retained Earnings: Retained Earnings, Dividends and current Net Income.
- Columns: Prior, AC, Δ Prior and Δ Prior %.
- ASSETS: Δ Prior & Δ Prior % and ASSETS: Actual & Prior chart total assets month by month.
Balance Sheet Summary
Purpose: Balance Sheet KPI cards with a selector to analyze any balance sheet item.
Expected Outcome: A quick check of the headline balance sheet numbers, and a way to see which accounts are driving the change in a given item.
How to Read the Page:
- KPI cards: Current Assets, Non Current Assets, Liabilities, Retained Earnings, Total Equity, Total Assets and Total Liabilities, each with the percent change versus the comparison period.
- Analyse: choose the balance sheet item to study (Net Income in the sample).
- Slice By: choose how to break it down (for example, by Account).
- The table and the two charts update to the selected item.
Balance Sheet Analysis
Purpose: A drillable Balance Sheet from the major sections down to individual GL accounts.
Expected Outcome: You can find the specific accounts behind a balance change, for example which cash account increased or which inventory account grew.
How to Read the Page:
- Expand sections with + (Assets > Current Assets > Cash > GL accounts).
- Slice By (Balance Sheet): choose the level of detail for the rows (for example, Account).
- KPI cards and asset charts give context for the table.
Rolling Balance Sheet
Purpose: The Balance Sheet at each month-end in the Rolling Months window, with the change versus the same month last year.
Expected Outcome: You can see how balances moved through the year and spot build-ups in receivables, inventory or payables.
How to Read the Page:
- Each month column shows AC, Δ Prior and Δ Prior %. Scroll right to see more months.
- Rows follow the Balance Sheet Statement structure.
Financial Business Ratios
All ratio pages share the same layout:
- KPI cards: the prior ratio and the current ratio (with percent change), and the prior and current values of the two components used in the formula.
- Formula card: exactly how the ratio is calculated in the report.
- Six charts: the monthly change in the ratio and in each component (top row), and the ratio trend with a trend line plus Actual & Prior for each component (bottom row).
Ratios are displayed as percentages, so 100% equals a ratio of 1.0 (for example, a Current Ratio of 250% means $2.50 of current assets for every $1 of current liabilities). Because liabilities and equity carry credit balances, some formulas multiply by -1 to produce a positive ratio. Ratios are calculated as of the reporting period end, and the Rolling Months slider sets the trend window.
Current Ratio
Purpose: Measures liquidity: the ability to pay short-term obligations with current assets.
Expected Outcome: You can see whether your working capital position is strengthening or weakening, and whether the change came from assets or from liabilities.
Current Ratio is a popular financial ratio for measuring a firm's liquidity (also referred to as its working capital). It is calculated by dividing the firm's current assets by its current liabilities.
> Current Ratio = Current Assets / Current Liabilities > > In the report: Current Ratio = Current Assets / ( -1 * Current Liabilities )
- It is one of the most important liquidity measures because current liabilities are due within one year. The higher the ratio, the more current assets the company has compared with its liabilities.
- A ratio under 1 suggests the company might be unable to pay off its current liabilities if they all came due at the same time. This does not necessarily mean the company will go bankrupt, but it can indicate poor financial health. A ratio that is too high may mean current assets are not being used efficiently.
- The ratio can be misleading in both directions: a high ratio is not necessarily good and a low ratio is not necessarily bad. For example, when inventory makes up a large share of current assets the ratio can look healthy even though inventory can be difficult to liquidate.
- Analyze the ratio over time and against industry peers with a similar business model.
How to Read the Page:
- Compare the Prior and current Current Ratio cards, then check the Current Assets and Liabilities cards to see which side moved.
- Months where current liabilities are close to zero produce very large or swinging ratio values. Look at the component charts to understand these spikes.
Quick Ratio
Purpose: Measures short-term liquidity without relying on selling inventory.
Expected Outcome: A stricter liquidity check than the Current Ratio, showing whether you can cover obligations from cash and receivables alone.
Quick Ratio measures the short-term liquidity position of a company. It is calculated by dividing the firm's quick assets by its current liabilities. Quick assets are assets that can be converted into cash within about 90 days.
> Quick Assets = Current Assets - Inventory > > Quick Ratio = ( Current Assets - Inventory ) / Current Liabilities > > In the report: Quick Ratio = ( Current Assets - Inventory ) / ( -1 * Liabilities )
- The Quick Ratio is similar to the Current Ratio, but it excludes inventory, on the assumption that inventory may not be turned into cash within 90 days.
- A Quick Ratio of 1 or more is considered good: for every $1 of current liabilities, the company has at least $1 in quick assets to pay it off. A Quick Ratio of 2 means $2 of quick assets for every $1 owed.
- The Quick Ratio helps management decide the optimum level of current assets to maintain to meet short-term liquidity needs.
How to Read the Page:
- Compare the Prior and current Quick Ratio cards, then the Quick Assets and Liabilities cards.
- A widening gap between the Current Ratio and the Quick Ratio means more of your liquidity is tied up in inventory.
Debt to Equity
Purpose: Measures leverage: how much of the business is funded by creditors compared with owners.
Expected Outcome: You can see whether the company is taking on more debt relative to equity, or building equity faster than liabilities.
Debt to Equity Ratio compares the capital contributed by creditors with the capital contributed by shareholders. In other words, it captures how much of the company's assets have been funded by creditors versus shareholders. It is calculated by dividing the firm's total liabilities by its total equity.
> Debt to Equity = Total Liabilities / Total Equity
In the report: Debt to Equity Ratio = Liabilities / Total Equity (the page is labeled "Dept to Equity").
- Lenders and credit analysts use this ratio to assess how leveraged a company is. A relatively high ratio can mean the company is not generating enough cash to cover its debt obligations. A low ratio can mean the company is not taking advantage of financial leverage.
- Debt to equity ratios are naturally higher in capital-intensive businesses, which must invest regularly in equipment and facilities.
How to Read the Page:
- Compare the Prior and current ratio cards, then the Liabilities and Total Equity cards to see which side drove the change.
- Both liabilities and equity are credit balances (negative numbers), so the ratio itself displays as positive.
Return on Assets (ROA)
Purpose: Measures how efficiently the business turns its assets into profit.
Expected Outcome: You can see whether each dollar of assets is earning more or less than last year.
Return on Assets (ROA) shows how much money is earned for each dollar invested in assets. A higher ROA means the company is using its assets efficiently.
> Return on Assets (ROA) = Net Income / Average Total Assets
- Net income is the amount realized after deducting all business costs for the period, including operating and non-operating expenses, taxes and interest. It also includes income from sources outside the primary operations, such as proceeds from the sale of fixed assets.
- Average total assets is the average of total assets over the period, for example the beginning and ending balances divided by 2.
- Use ROA to compare the company's current performance with its own history, or with companies of similar size, scale and industry. Do not compare ROA across industries, because the assets needed to generate income vary widely.
- ROA also indicates whether a company is asset-intensive or asset-light.
How to Read the Page:
- Compare the Prior and current ROA cards, then the Net Income and Average Total Assets cards.
- A falling ROA with growing assets means new assets are not yet producing matching profit.
Return on Equity (ROE)
Purpose: Measures the return generated on the owners' investment in the business.
Expected Outcome: You can see whether the business is producing a stronger or weaker return on equity over time.
Return on Equity (ROE) measures the percentage return generated on shareholders' equity. Shareholders' equity is the company's assets less its liabilities.
> Shareholders' Equity = Total Assets - Total Liabilities > > Return on Equity = 12-Month Rolling Net Income / Total Equity
- ROE is expressed as a percentage. It cannot be meaningfully calculated when net income or equity is negative.
- ROE varies by sector, so it is most meaningful when comparing similar companies.
- A low ROE means the investment in the business is not being used efficiently. Investors often view an ROE below 13-14% as weak and 20% or above as strong.
- ROE is one of the most important measures of management effectiveness. Tracking it quarter to quarter or year to year shows how performance is changing.
How to Read the Page:
- Compare the Prior and current ROE cards, then the 12M Net Income and Total Equity cards.
- Because the numerator is a 12-month rolling net income, the trend is smoother than monthly net income.
Sales to Assets
Purpose: Measures how efficiently the business uses its assets to generate revenue.
Expected Outcome: You can see whether revenue is keeping pace with the asset base.
Sales to Assets Ratio (also known as asset turnover) is one of the primary ratios for measuring a company's efficiency. It shows how much revenue the company generates from the assets it manages. A higher ratio means more revenue from a smaller investment in assets.
> Sales to Assets = Revenues / Average Total Assets
- Assets come with costs such as maintenance, depreciation and financing. If the ratio is low and falling year after year, the company may need to restructure its operating assets, for example by selling unused assets, to reduce costs and improve margin.
- Analysts watch this ratio closely as a measure of competitiveness within an industry. A declining ratio can indicate falling business viability or less effective use of capital.
How to Read the Page:
- Compare the Prior and current ratio cards (the current value card is labeled "Return on Assets (RoA)" on this page), then the Revenues and Average Total Assets cards.
- With Year to Date revenue, the ratio builds through the year and resets at the start of each fiscal year.
SG&A as a function of Gross Margin
Purpose: Tracks how much of your gross profit is left as operating income after selling, general and administrative (SG&A) expenses. The KPI is % of Operating Income vs Gross Profit.
Expected Outcome: You can see whether overhead is growing faster than gross profit, overall and by branch or other dimension, and compare against industry benchmarks.
> % Operating Income to GP = Operating Income / Gross Margin
This page was created in conjunction with needs indicated by the CEO of a CEOJuice 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 as a % of Equipment GP, excluding all other revenue (aftermarket, solutions, rental, MIT, phones, etc.).
Selling expenses are typically:
- Sales Manager compensation
- Sales Rep compensation (salary, draws, commissions)
- Everything that goes with that payroll (computers, licenses, payroll taxes, benefits, travel, hotels, meals, etc.)
- Delivery driver expenses
- Customer meals & entertainment
Targets many 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, excluding phones, solutions, MIT, security cameras, etc. (target is around 16%)
G&A (General and Administrative expenses) is almost always tracked as a function of all revenue. The benchmark was about 15% before COVID and is now usually around 17% because of higher costs. Some dealers also track G&A as a % of Imaging Revenue and a % of MIT Revenue, which usually lands around 17-18%. G&A expenses usually include:
- 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 other expenses that are not directly equipment, aftermarket or MIT expenses
How to Read the Page:
- KPI cards: Revenues, CoGs, Gross Margin, Oper. Expenses, Oper. Income and % Operating Income to GP.
- Report: choose MTD, QTD or YTD.
- Slice Results By: break the table down by Branch or another dimension. The table shows Revenues, CoGs, Gross Margin, Operating Expenses, Operating Income and % Operating Income to GP for each row.
- % Operating Income to GP Trend: the KPI by month with a trend line.
- % Operating Income to GP Δ Trend: the monthly change versus the comparison period (green = improvement).
Inventory Turns
Purpose: Measures how many times inventory is sold and replaced over a rolling 12 months, and how many days of sales are sitting in inventory, by inventory group.
Expected Outcome: You can find slow-moving inventory categories and watch whether turns are improving, so you can free up cash tied up in stock.
This page requires the ZCJGLInventoryGroup custom property on your inventory and CoGS GL accounts. See Inventory Groups setup.
> Inventory Turns = 12M Rolling CoGs / Average Inventory > > Days Sales in Inventory = 365 / Inventory Turns
How to Read the Page:
- Calculate Turns as: choose how average inventory is calculated: (Inventory Start + Inventory End) / 2, or the average inventory over the last 12 periods.
- Inventory Group: filters the four charts. It does not affect the 12M Rolling Inventory Turns Summary table.
- Charts: 12-month rolling CoGs, average inventory, inventory turns and days sales in inventory by month.
- 12M Rolling Inventory Turns Summary: for each period, CoGs, Avg Inventory, Inv Turns and Days Sales in Inventory for each inventory group and in total.
- Higher turns and fewer days in inventory are better. A group with turns well below 1 (hundreds of days of inventory) is carrying stock that is not moving.
Cockpits & Operational Reports
Financial Overview
Purpose: A one-page financial cockpit with headline KPIs, trends and margin percentages by branch.
Expected Outcome: A fast answer to "how are we doing?" for owners and managers, without navigating the detailed statements.
How to Read the Page:
- KPI cards: Revenues, Gross Margin, Operating Income and Net Income with the change versus the comparison period.
- Trend charts: Revenues, Gross Margin, Operating Income and Net Income / (Loss) by month, each with a trend line.
- % Contribution to Revenue: Gross Margin %, Operating Income % and Net Income / Loss % in total and by branch.
- Gross Margin % (vs Revenue) Trend: monthly gross margin percentage.
General Ledger Details
Purpose: A searchable list of every general ledger entry, across both Income Statement and Balance Sheet accounts.
Expected Outcome: You can find and review specific transactions, such as the largest entries in a period, all activity on a reference, or entries posted to an unexpected account, and export them.
How to Read the Page:
- Filter by GL Branches, GL Departments, Reference, BS Groups, GL Account and Fiscal Year, Period.
- Columns include Period, Transaction Date, Reference, GL Account, Branch, Department, BS Group, Transaction Type, Journal and Detail descriptions, Debit Amount, Credit Amount and Amount.
- Total debits equal total credits, so the net Amount totals zero for a complete period.
- Click a column header to sort (for example, largest debits first).
Income Statement Details
Purpose: The general ledger entries behind the Income Statement, limited to income statement activity.
Expected Outcome: You can review exactly which transactions make up revenue, cost of goods sold or expenses for a period.
How to Read the Page:
- Filter by GL Branches, GL Departments, Reference, Account Group #1 / #2, Type IS, Account Type, GL Account and Fiscal Year, Period.
- Columns include Period, Transaction Date, Transaction Type, PnL Group, GL Account, Journal Desc, Module, Branch, Department and Amount.
- The Total at the bottom shows the net amount for your selection.
Balance Sheet Details
Purpose: The general ledger entries organized by Balance Sheet type and account type.
Expected Outcome: You can see the transactions that moved cash, receivables, payables, inventory or equity in a period.
How to Read the Page:
- Filter by GL Branches, GL Departments, Reference, BS Groups, TypeBS, AccountType, GL Account and Fiscal Year, Period.
- TypeBS shows where each entry lands on the Balance Sheet. Income statement accounts (revenue, cost, expense) roll into Retained Earnings.
- When all accounts are included, the Total is 0 because debits and credits offset.
Operational Overview
Purpose: A cockpit for money still in motion: unbilled contracts, open sales orders, open receivables and open payables.
Expected Outcome: At a glance, you can see revenue waiting to be billed or delivered, how old your receivables and payables are, and whether the subledgers agree with the general ledger.
How to Read the Page:
- Unbilled Contracts Due: contract billing that is due but not yet invoiced, by aging period and by Base, Lease, Misc-Other and Overages. The Overage Percentage Change slider adjusts the overage forecast (1.00 = 100% of the last 6 months' average billed), and Forecast using Overage Variance shows the result.
- Open Sales Orders: amount, cost, profit and margin, and a breakdown by order type.
- Aged Open A/R next to Balance Sheet A/R, and Aged Open A/P next to Balance Sheet A/P: a large difference between the aging total and the GL balance is worth investigating (for example, unapplied cash or entries posted directly to the control account).
- Total Due by Aged Open Invoices and Open A/P by Aging Period: balances by aging bucket.
Unbilled Contracts Due
Purpose: The detail behind contract billing that is due but not yet invoiced.
Expected Outcome: You can find past-due contract billing, see which customers and contracts it belongs to, and make sure it gets invoiced.
How to Read the Page:
- Unbilled Contracts Due and Forecast using Overage Variance cards, with the Overage Percentage Change slider (1.00 = overages forecast at 100% of the last 6 months' average billed).
- Unbilled Contracts Due by Aging Period: amounts by Base, Lease, Misc-Other and Overages.
- Contracts Due by Contract Type: Due After Today, Due Today and Past Due for each billing type.
- Contracts Due by Customer & Bill To Customer: amount, % to total and number of contracts per customer.
- Contracts Due by Contract Number: each contract with its type, row type, aging period and amount.
- Queue Issues / Queue Validated / Queue Ready to Invoice Amount: where the unbilled amount sits in the contract billing queue.
- Filter by GL Branches, GL Departments, Contract Type and Contract Number.
Open Sales Orders
Purpose: A summary of sales orders that are entered but not yet invoiced, with their expected cost and profit.
Expected Outcome: You can see the revenue and gross profit in the pipeline, which reps and managers own it, and which customers and order types it comes from.
How to Read the Page:
- KPI cards: Open Sales Orders Amount, Cost, Profits and Open Margins (profit as a percentage of sales).
- Open Sales Orders by Manager & Sales Rep: sales and % to total.
- Open Sales Orders by Order Type and by Customer: sales, costs, profits and margins.
- Filter by GL Branches, Order Type, Order Status, Manager and Sales Rep.
Open Sales Orders Details
Purpose: The line-level detail of every open sales order.
Expected Outcome: You can follow up on specific orders, and find old orders that should be delivered, invoiced or closed.
How to Read the Page:
- Columns: Date, Order #, Customer, Order Type, Sales Rep, Item, Open Qty, Sales Amt, Cost, Profit and Margin.
- In addition to the filters on the Open Sales Orders page, filter by Customers and Items.
- Sort by Date to find the oldest open orders. Orders that are years old are usually candidates for cleanup.
Aged A/R Invoices
Purpose: Open accounts receivable by aging period, sales rep and customer, with the note activity on each account.
Expected Outcome: You can prioritize collections: see how much is past due, who owns it, and whether it is being worked.
How to Read the Page:
- Total Due and Created this month cards.
- Total Due by Aging Period and Total Due by Date show how old the balance is.
- Total Due / Notes by Sales Rep & BillTo Customer: expand a sales rep to see their customers, with the number of Bill To notes and invoice notes on each.
- Open A/R Details: each open invoice with due date, transaction type, module, aging period and total due.
- Filter by GL Branches, Transaction Type, Module, Ageing Period, Bill To Customer and Year, Quarter, Month, Day.
Open A/R Notes & Activities
Purpose: Open invoices together with the notes and activities recorded on the customer and on each invoice.
Expected Outcome: Collectors can see the full history of an account before calling, and managers can confirm that past-due accounts are being followed up.
How to Read the Page:
- Invoice Details: each open invoice with Bill To, invoice #, due date, type, module, aging period, total due, and the number of invoice notes and Bill To notes.
- Click an invoice to show its Notes / Activities on BillTo Customer and Notes / Activities on Invoice (type, status, scheduled and completed dates, and the note text).
- Use the two Note / Activity Status filters to show, for example, only pending activities.
Aged Open A/P Invoices
Purpose: Open accounts payable by aging period, date and vendor.
Expected Outcome: You can plan payments, spot past-due vendor invoices and see which vendors you owe the most.
How to Read the Page:
- Aged Open A/P card and Open A/P Amount by Aging Period.
- Open A/P Amount by Date: the open balance over time.
- Open A/P Amount by Vendor and Open A/P Transactions: vendor invoice, transaction type, date, due date, aging, vendor and amount.
- Payables are credit balances, so amounts are shown as negative numbers.
Cash Flow Analysis
Cash Flow Statement
Purpose: A Cash Flow Statement for the selected periods, built from changes in your Balance Sheet (indirect method).
Expected Outcome: You can see where cash came from and where it went (operations, investing or financing), and why cash changed even when net income did not.
How to Read the Page:
- Cash Flows from operations: Net Income adjusted for changes in Accounts Payable, Accounts Receivable, Accumulated Depreciation, Current Assets, Current Liabilities and Inventory.
- Cash Flows from investing activities: changes in Fixed Assets and Noncurrent Assets.
- Cash Flows from financing activities: Capital, Dividends, Noncurrent Liabilities and Retained Earnings.
- Net Increase (decrease) in cash, plus cash at the beginning and end of the selected period. The ending cash ties to Cash on the Balance Sheet.
- Green bars add cash; red bars use cash. The Rolling Months slider sets the periods included.
Rolling Cash Flow
Purpose: The Cash Flow Statement period by period across the Rolling Months window.
Expected Outcome: You can see the months where cash was generated or consumed and which line caused it.
How to Read the Page:
- Each column is a fiscal period, and Cash Flow for period at the right is the total for the whole window.
- Rows follow the Cash Flow Statement lines, ending with cash at the beginning and end of each period.
- The small bars in each cell show the size of the value, green for positive and red for negative.
* * *
Related Alerts
Related Alerts
Related alerts that you may want to consider:
ID40 - Budget Import Utility. Load the budgets used on the Budget Analysis pages; the Budget Allocations page exports straight into it.
ID704 - The Financial "Model" Power BI
ID771 - Service MIF & Profitability Power BI
See full list of all Power BI reports that we currently have available
https://support.ceojuice.com/hc/en-us/sections/360011259132-Power-BI-Reports
* * *
Webinar
Webinar
3 minutes preview below, one hour below that.
One hour extended below with sneak peek at "The Model"
Original one hour extended webinar below (outdated now).
0 Comments