DOCUMENTATION

PURPOSE

NEXUS brings together all the actuals data from the MASTER LEDGER and the forecast and/or budget data from HORIZON, displayed in various pivot tables for financial statements, invoice management, project management, and cash balance overview. NEXUS (for reporting) and HORIZON (for forecasting) are designed to only import lookup names and transactions and are therefore more suitable to share with stakeholders outside the organization. To help anonymize employee, vendor and customer names when sharing reports, the Export Lookup to Nexus tab in the MASTER LEDGER brings in IDs instead of Names to help keep Customer Name anonymous when adding to or filtering in Nexus reports.

Note: The Refresh time can vary greatly depending on how much data is imported and connected into the data model. For example, a report with 5000 rows of actuals and 5000 rows of forecast or budget can take between 5 to 10 minutes to refresh and recreate all the pivot tables.

 

Metadata Slicers

Metadata slicers reference the transaction (actual, forecast or budget) information imported from the MASTER LEDGER and HORIZON. If this information is required for analysis but missing (blank) in NEXUS, trace back to the source transaction and add in the needed metadata.

• • • • Date
      • Project Name
      • Product Name
      • Department Name
      • Customer Name
      • Tracking Reference 1
      • Tracking Reference 2
      • Invoice ID
      • Invoice Due Date
      • Source Journal
      • Account Category
      • Account ID Name

Connected Metadata Slicers 

Depending on the nature of the reporting table, some slicers are connected between tabs while others operate independently with their slicers. This facilitates analysis for reports that are often viewed together. The below grouping shows which tabs operate together (where a date timeline or metadata slicer change on one tab affects the selection on the other connected tabs) or independently (updating the slicer selections does not impact the view on other tabs). 

Connected Date Timeline	Connected Metadata Slicers	Independent Date Timeline	Independent Metadata Slicers
IS – Actuals	IS – Actuals	EA	GJ
IS – Forecast	IS – Forecast	GJ	GL
IS – Budget	IS – Budget	TB	Invoices Outstanding
IS – Act + Fcst	IS – Act + Fcst	GL	Cash Balance
IS – Act vs Bdgt	IS – Act vs Bdgt	Invoices Outstanding	Project Quick View
IS – Act vs Fcst	IS – Act vs Fcst	Cash Balance	Addendum – Account Totals
IS – Fcst vs Bdgt	IS – Fcst vs Bdgt	Project Quick View
IS – ABF View	IS – ABF View	Performance Dashboard
BS	EA	Addendum – Account Totals
SCF		Addendum – Profitability

 

 

Back to Top

 

1 – Import Lookup

 

Purpose

Sets up the company name used in report headers and brings in ID Name metadata from the MASTER LEDGER to provide context for the IDs in the imported actuals, forecast and budget data. To help anonymize employee, vendor and customer names when sharing reports, the Export Lookup to Nexus tab in the MASTER LEDGER brings in IDs instead of Names to help keep Customer Name anonymous when adding to or filtering in Nexus reports. 

 

Step 1: Input the company name for report headers in the gold cell.

Step 2: Paste contents of ‘Export Lookup to Nexus’ from the MASTER LEDGER.

Paste Values (Ctrl + Shift + V) the contents of Export Lookup to Nexus tab in the MASTER LEDGER to the Import Lookup Table, starting below the header row.

Step 3: Proceed to import actuals, forecast and budget, then Refresh All to update reports.

Proceed to 2 – Import Actuals, 3 – Import Forecast, and 4 – Import Budget. When ready to refresh the PivotTable reports, navigate to the Data tab on the top Excel ribbon, then under Queries & Connections group, select Refresh All.

 

 

2 – Import Actuals

 

Purpose

Brings in the extracted and transformed transaction data from the MASTER LEDGER to populate pivot tables. For reports combining actuals and forecast, the ‘Actuals Through’ date is determined by the last month with data from the actual transactions in Import Actuals. Forecast data begins the month after the final month with actuals.

 

Step 1: Paste contents of ‘Export Actuals’ from the MASTER LEDGER.

Paste Values (Ctrl + Shift + V) the transaction contents of the Export Actuals table in the MASTER LEDGER to the Import Actuals Table, starting below the header row.

Step 2: Finalize importing actuals, forecast and budget data, then Refresh All to update reports.

Proceed to 3 – Import Forecast and 4 – Import Budget. When ready to refresh the PivotTable reports, navigate to the Data tab on the top Excel ribbon, then under Queries & Connections group, select Refresh All.

 

 

3 – Import Forecast

 

Purpose

Brings in the extracted and transformed forecast data from HORIZON to populate pivot tables. For reports combining actuals and forecast, the ‘Actuals Through’ date is determined by the last month with data from the actual transactions in Import Actuals. Forecast data begins the month after the final month with actuals.

Budget versus Forecast: Forecast data and budget data have separate import tabs to differentiate their purpose. Forecast is usually updated more frequently whereas budget data is set ahead of time as a benchmark for performance over a period of time, usually an entire fiscal year. See Budgeting versus Forecasting for a quick overview of the differences between a budget and a forecast.

 

Step 1: Paste contents of 3 – Export Forecast from HORIZON.

Paste Values (Ctrl + Shift + V) the transaction contents of the 3 – Export Forecast table in HORIZON to the Import Forecast Table, starting below the header row.

Step 2: Finalize importing actuals, forecast and budget data, then Refresh All to update reports.

Proceed to 4 – Import Budget. When ready to refresh the PivotTable reports, navigate to the Data tab on the top Excel ribbon, then under Queries & Connections group, select Refresh All.

 

 

4 – Import Budget

 

Purpose

Brings in the extracted and transformed budget data from HORIZON to populate ‘budget’ in any pivot table views. The suggested use of ‘budget’ is for locking in shorter term (e.g. quarterly or annual) targets, even if forecast is updated on a more regular basis. Users may choose to keep only one budget by clearing previous period budgets, or append new budgets as time goes on to view historical budgets against historical actuals.

Budget versus Forecast: Forecast data and budget data have separate import tabs to differentiate their purpose. Forecast is usually updated more frequently whereas budget data is set ahead of time as a benchmark for performance over a period of time, usually an entire fiscal year. See Budgeting versus Forecasting for a quick overview of the differences between a budget and a forecast. 

 

Step 1: Paste contents of ‘Export Forecast from HORIZON.

Paste Values (Ctrl + Shift + V) the transaction contents of the 3 – Export Forecast table in HORIZON to the Import Budget Table, starting below the header row.

Step 2: Finalize importing actuals, forecast and budget data, then Refresh All to update reports.

Finalize adding transactions to 2 – Import Actuals, 3 – Import Forecast. When ready to refresh the PivotTable reports, navigate to the Data tab on the top Excel ribbon, then under Queries & Connections group, select Refresh All.

 

 

Back to Top

 

Purpose

The Income Statement, also called a profit-and-loss (P&L) statement, is a key financial statement that shows revenues, expenses and profitability over time. Various pivot views have been pre-built into Nexus to facilitate financial analysis between various combinations of actual transaction amounts, forecasted amounts and budgeted amounts if available. Use the date timelines and slicers (categorical filters) to dive deeper into the profit and loss data by project, department, and product. The date timelines and slicers of the Income Statement, Balance Sheet and Statement of Cash Flow tabs are connected, carrying the filters over to facilitate analyzing the core financial statements under the same selected filters.

Note: The Income Statement reports have a hidden filter of Statement = IS, which is located behind the date timeline. If you used a full ‘Clear’ of all filters, the Statement filter will bring back BS (balance sheet) accounts. To fix this, move the date timeline out of the way to see the Statement filter and set Statement = IS only to limit the report to showing only the income statement accounts.

To add or remove value fields (measures) which show up as columns, click anywhere in the pivot table to activate the PivotTable Analyze tab in Excel, then in the Show group, click on Field List. The active measures appear in the ∑ Values box. Remove or add measures in this area as needed from the ∑ Income Statement list. For example, to simplify the pivot table columns to show amounts only and not % of Rev, remove the % of Rev measure from the ∑ Values box.

The print area has been default set to 1 page auto-width and pre-adjusted to start from the left-most edge of the pivot table and extend horizontally up to the right-most edge of the date timeline slicer. To readjust the print area to fewer or more columns wide as needed, first select the columns to include in the print range, then go to the Page Layout tab in Excel, then to the Page Setup group, then click Print Area, and finally click Set Print Area. To recenter the header (company name and statement title), unmerge the header cells and remerge them to match the new print area.

 

 

IS – Actuals

 

Purpose

Shows imported actual transactions over time in a profit and loss format. Actual transactions are prepared in the MASTER LEDGER and imported into 2 – Import Actuals.

 

Default Power Pivot Fields (∑ Values measures)

 Actual $ – Sum of actual amounts by account

% of Rev – The actual amount by account as a percentage of actual revenue

 

 

IS – Forecast

 

Purpose

Shows imported forecast amounts over time in a profit and loss format. Forecast amounts are prepared in HORIZON and imported into 3 – Import Forecast.

 

Default Power Pivot Fields (∑ Values measures) 

Forecast $ – Sum of forecasted amounts by account

% of Rev – The forecasted amount by account as a percentage of the forecasted revenue

 

 

IS – Budget

 

Purpose

Shows imported budget amounts over time in a profit and loss format. Budget amounts are prepared in HORIZON and imported into 4 – Import Budget.

 

Default Power Pivot Fields (∑ Values measures) 

Budget $ – Sum of budgeted amounts by account

% of Rev – The budgeted amount by account as a percentage of the budgeted revenue

 

 

IS – Act + Fcst

 

Purpose

Shows a hybrid amount of actuals or forecast over time in a profit and loss format. All months with actual transactions will display actuals amounts only, while forecast amounts begin starting after the last month with actuals. This assumes actuals are up to date in Nexus. For the most accurate combination, review the report only after the full actuals have been added for a month else the month’s data will be incomplete.

 

Default Power Pivot Fields (∑ Values measures) 

Act + Fcst $ – Sum of actual or forecast amounts by account, with each month showing either actuals or forecast but not both

% of Rev – The amount by account as a percentage of the revenue

 

 

IS – Act vs Bdgt

 

Purpose

Shows imported actual amounts compared to budget amounts over time in a profit and loss format.

 

Default Power Pivot Fields (∑ Values measures) 

Actual $ – Sum of actual amounts by account

Budget $ – Sum of budgeted amounts by account

Variance $ – The difference between budgeted amount and actual amount by account shown in absolute value, with a “U” to designate the difference as unfavorable (e.g. revenue less than budget, expenses greater than budget) and an “F” to designate the difference as favorable (e.g. revenue greater than budget, expenses less than budget)

 

 

IS – Act vs Fcst

 

Purpose

Shows imported actual amounts compared to forecast amounts over time in a profit and loss format.

 

Default Power Pivot Fields (∑ Values measures)

Actual $ – Sum of actual amounts by account

Forecast $ – Sum of forecasted amounts by account

Variance $ – The difference between forecasted amount and actual amount by account shown in absolute value, with a “U” to designate the difference as unfavorable (revenue less than forecast, expenses greater than forecast) and an “F” to designate the difference as favorable (revenue greater than forecast, expenses less than forecast)

 

 

IS – Fcst vs Bdgt

 

Purpose

Shows imported forecast amounts compared to budget amounts over time in a profit and loss format.

 

Default Power Pivot Fields (∑ Values measures)

Forecast $ – Sum of forecasted amounts by account

Budget $ – Sum of budgeted amounts by account

Variance $ – The difference between budgeted amount and forecasted amount by account shown in absolute value, with a “U” to designate the difference as unfavorable (revenue less than budget, expenses greater than budget) and an “F” to designate the difference as favorable (revenue greater than budget, expenses less than budget)

 

 

IS – ABF View

 

Purpose

Shows imported actual, forecast, actual plus forecast, and budget, stacked vertically, in a profit and loss format over time. This pivot facilitates comparisons against all three parameters (actual, forecast, budget) and can be arranged in various Row field orders to compare by Class, Group or Account Name. To change the row field order, click anywhere in the pivot table to activate the PivotTable Analyze tab in Excel, then in the Show group, click on Field List. The active row fields appear in the Rows box. Reorder the row fields as desired to facilitate analysis.

 

Default Power Pivot Fields (∑ Values measures) Order

Actual $ – Sum of actual amounts by account

Forecast $ – Sum of forecasted amounts by account

Act + Fcst $ – Sum of actual or forecast amounts by account, with each month showing either actuals or forecast but not both

Budget $ – Sum of budgeted amounts by account

 

Default Power Pivot Row Field Order

Class – Refers to the large Income Statement categories of Revenues, Expenses, or Other Income (Expenses)

∑ Values – Separates the amounts by Actual $, Forecast $, Act + Fcst $, and Budget $

Group – Further breaks down Expenses into Direct Costs or Operating Expenses, and Other Income (Expenses) into Other Income or Other Expenses

Account Name – Name of the account under each Group

 

 

Back to Top

 

Purpose

While the data is formatted as an income statement report, the Expected Actuals (EA) pivot uniquely works within a single calendar year showing various combinations of actuals plus forecast with actuals through a particular selected month, showing the expected annual view. Only up to 12 months show at any time, and the monthly amounts show actuals through the latest selected month and year in the date timeline slicer. 

The most common use of this pivot view is to facilitate manual toggling of actuals plus forecast, including actuals that may end earlier than the current actuals period. For example, the business may have actuals through August of year 20XX, but be asked to provide a historical total of actuals plus forecast as of June 20XX for a particular analysis. To view actuals plus forecast with the latest actuals always included and spanning multiple years, use the IS – Act + Fcst pivot view instead. 

The date timeline is disconnected from the other income statement views due to unique single calendar year nature.

 

Default Power Pivot Fields (∑ Values measures)

Expected $ – Sum of actual or forecasted amounts by account based on the date selected

 

 

 

Back to Top

Purpose

The Balance Sheet (BS) is a key financial statement that shows a snapshot of a company’s financial position at a point in time, separated into the business’s assets, liabilities and equity. The sum of total assets should equal the sum of total liabilities plus equity, which is facilitated by the double entry accounting method in the MASTER LEDGER.

 As the Balance Sheet is often viewed within the same time parameters as the Income Statement and Statement of Cash Flows, the date timeline is connected.

 

 

Back to Top

Purpose

The Statement of Cash Flows (SCF) is a key financial statement that reports the cash generated (positive amount) and spent (negative amount) during a specific period of time, acting as a bridge between the Income Statement and Balance Sheet by showing how cash moved in and out of the business. As the Statement of Cash Flows is often viewed within the same time parameters as the Income Statement and Balance Sheet, the date timeline is connected.

 

 

Back to Top

 

Purpose

The General Journal (GJ) shows all imported transaction actuals as journal entries in chronological date order, documenting every transaction’s balance between debits and credits.

For convenience in searching or reconciling transactions in any date range and filter condition, the General Journal tab’s metadata slicers and timeline are independent of other tabs.

 

Independent Metadata Slicers 

• • • Date
    • Source Journal
    • Account ID Name
    • Invoice ID
    • Project Name
    • Product Name
    • Department Name
    • Customer Name
    • Tracking Reference 1
    • Tracking Reference 2 

Source Journal – The Source Journal code identifies the accounting model from which a transaction is posted from. In the MASTER LEDGER and NEXUS, reversal or correction entries are denoted with ‘ -R ’.

 

Accounting Module	Source Journal Code	Description
Bank	CD	Cash disbursements (outflow) entries
Bank	CR	Cash receipts (inflow) entries
Sales	SJ	Sales and revenue entries
Indirect Revenue	SJI	Indirect cost recovery revenue entries
Expenses	AP	Payable expense entries
Payroll & Wages	PR	Payroll check entries
Inventory & Assets	FA	Fixed asset entries
Inventory & Assets	PO	Inventory purchase order entries
Loans	GJL	Loan entries
Equity (Stock)	GJS	Stock-related equity entries
Equity (Non-Stock)	GJE	Non-stock equity entries

 

 

Back to Top

Purpose

The Trial Balance (TB) is a financial report that lists the closing balances of all ledger accounts at a specific point in time, used to help confirm that total debits equal total credits. If the total debits do not equal credits, it means there was an error made in the recording of the transactions and requires investigation to pinpoint and correct the source transaction. The MASTER LEDGER facilitates the minimization of errors balancing debits and credits of a transaction with a combination of the Column Requirements columns and the error checks in each accounting module.

 

 

Back to Top

Purpose

The General Ledger organizes all business transactions by ledger accounts to summarize overall activity and see the financial picture of your business over time. While the information portrayed has many similarities to the GJ (General Journal), the General Ledger is used to help turn financial data into the financial statements (Income Statement, Balance Sheet, Statement of Cash Flows) because it also portrays the opening and final running balance (as a Dr for Debit, or Cr for Credit) of each ledger account as of a point in time.

Note: The General Ledger requires more computing power to run, and works best when viewing short timeframes (such as one or two months at a time) or limited account names (e.g. just Cash). Trying to unfilter for all dates and all accounts may cause Nexus to spin excessively long compared to filter changes on other reports.

 

Independent Metadata Slicers 

• • • Date
    • Account Category
    • Account ID Name

 

Back to Top

 

Purpose

The Invoices Outstanding report facilitates tracking of invoices, both receivables and payables, that have not been cleared with a zero outstanding balance. The imported actuals have been pre-cleansed to remove any cleared (0 balance) invoices leaving only invoices with outstanding balances, or transactions on receivable accounts and payable accounts that are not tied to an Invoice ID. Also, any receivable or payable accounts that are cleared (0 balance) also are removed from the report, in case a single clearing transaction is used to handle multiple invoices rather than clearing invoices individually. If an invoice appears on the report that should already have been cleared in the Bank accounting module of the MASTER LEDGER, return to the source transactions and fill in the missing Invoice ID metadata.

Tip: For Receivables, keep an eye out for concentration risk in which a large proportion of receivables outstanding may come from one client or one client has overdue invoices. A problem with those clients defaulting or paying late can lead to cash flow problems for your business.

 

Independent Metadata Slicers 

• • • Invoice Due Date
    • Invoice Category
    • Customer Name
    • Project Name

Default Power Pivot Row Field Order 

• • • Category – Divided into receivables outstanding and payables outstanding
    • Account Name – Name of the receivable or payable account
    • Invoice ID – Used to track the amounts due and amounts cleared under the same invoice ID
    • Customer Name – ID of the customer or vendor
    • Invoice Due Date – Due date based on the original invoice date plus the payment terms for the customer or vendor
    • Ref – Transaction reference to help trace additional details using the General Ledger

Back to Top

 

Purpose

The Cash Balance report provides a quick view of the company’s cash balance over time, divided into different banks or accounts or other optional custom Tracking Reference metadata that may have been applied to the source transactions in the MASTER LEDGER wherever Cash was involved in the double entry accounting.

Cash is involved in any accounting and banking module that had a column for Cash, such as Cash Paid or Cash Received (Paid). In those transactions, a user can use the optional Tracking Reference 1 or Tracking Reference 2 fields to provide metadata for the cash movement.  For example, a business may use Tracking Reference 1 to differentiate cash balances by bank first, then use Tracking Reference 2 to differentiate bank balances by bank account name.

If the optional tracking reference metadata was left blank or was used for other purposes other than identifying bank and bank account information, then users may find it helpful to remove the Tracking Reference 1 and Tracking Reference 2 fields from the PivotTable Rows entirely or filter out the unrelated metadata inputs using the slicers. The default PivotTable Row order is Tracking Reference 1, then Tracking Reference 2. 

 

Independent Metadata Slicers 

• • • Date
    • Tracking Reference 1
    • Tracking Reference 2 

Back to Top

 

Purpose

The Project Quick View report tables are specific to projects and provide a quick view of the financial status of all projects. The suggested default quick view is to filter for Revenue in the Account Category slicer. 

 

Two separate tables have been provided as follows: 

Project Variance Table – Shows actual, forecast or budget amounts for the selected Account Category (e.g. Revenue) based on the selected month, year or date range in the Variance Date timeline. The Variance Date timeline does not impact the Project Lifetime Table. Default sorted by Act vs Fcst Var $.

 Project Lifetime Table – Shows actuals to date and forecast or budget amounts in a lifetime view. The Project Lifetime Table is not impacted by the Variance Date timeline filter. Default sorted by Lifetime Var $.

Default Power Pivot Fields (∑ Values measures)

 

Actual $ to Date – Sum of all actual amounts by project and filtered account category from inception of imported actual data to the latest imported actual data

Remaining Fcst $ – Sum of forecasted amounts by project and filtered account category for all remaining months forecasted following the latest month with actuals

Lifetime Act + Fcst $ – Combined sum of actual amounts to date and remaining forecast amounts by project and filtered account category

Lifetime Budget $ – Sum of budgeted amounts by project and filtered account category for the entire lifetime of a project, usually as the contractual budget

Lifetime Var $ – Difference between the lifetime actuals plus forecast and the lifetime budget by amount, often used to monitor whether a project is running under or over contractual budget

Lifetime Var % – Difference between the lifetime actuals plus forecast and the lifetime budget as a percentage

Independent Metadata Slicers 

Variance Date (affects only Project Variance Table)

Account Category

Project Name 

 

 

Back to Top

 

Purpose

The Performance Dashboard tab shows several key financial metrics and charts that most businesses should monitor. The metrics and charts are calculated using actuals plus forecast amounts (all actuals plus forecast after). Actuals cover data from inception through the ‘Actuals Through’ date. Users can reference the Performance Dashboard to help answer the following types of questions:

• • • Is my revenue growing over time?
    • Which products or projects contribute the most revenue?
    • Which products or projects are the most profitable (by gross profit margin)?
    • Which customers contribute the most revenue?
    • Which customers are the most profitable (by gross profit margin)?
    • Are operating expenses growing faster or slower than revenue?
    • Which are my highest operating expenses?
    • Which departments require the most operating expenses to run?

 Tip: For businesses that engage in projects instead of selling products or services, Product Name can be substituted with Project Name in the PivotTable Fields Axis (Categories) for any chart, graph or table in the Performance Dashboard.

 

Key Performance Indicators

Shows some of the most common financial health metrics monitored by small businesses for financial health. Along with tracking cash flow (ensuring a recurring positive operating cash flow via Cash Generated From Operating Activities) and cash balance to ensure a long enough runway (how long a company can operate before running out of cash) to sustain upcoming operating activities, these financial metrics offer at-a-glance performance health.

Power Pivot Fields (∑ Values measures) 

Gross Profit $ – Revenue less Direct Costs

Gross Profit Margin % – Measures how efficiently you produce goods and services and indicates what percentage of sales revenue remains to pay for operating and overhead costs; a decreasing Gross Profit Margin % suggests that production costs are increasing faster than the selling price

Net Profit $ – Revenue less All Expenses

Net Profit Margin % – Measures overall profitability after all expenses are deducted from revenue

OPEX % of Revenue – Measures the proportion of Revenue spent on running the business; an increasing % indicates that operating expense growth is exceeding revenue growth and cutting into Net Profit Margin

Current Ratio – Also known as Working Capital Ratio and calculated by dividing current assets by current liabilities, measures the ability to cover short-term liabilities with short-term assets; helps indicate liquidity and short-term financial stability with the goal to always maintain a ratio > 1, with a ratio of 2 or higher considered a healthy buffer for short-term payment risks

Totals by Date (Charts)

Shows four stacked bar charts with actual plus forecast amounts over time (default in years). The stacked bars visually show the totals by month or year and growth trends over time, with totals broken down by a subset category.

Revenue by Product – Shows revenue over time and the proportion of revenue contributed by Product Name. Product Name can be substituted with Project Name in the PivotTable Fields Axis (Categories).

Revenue by Customer – Shows revenue over time and the proportion of revenue contributed by certain customers.

OPEX by Account – Shows operating expenses over time and the proportion of OPEX contributed by each expense account.

OPEX by Department – Shows operating expenses over time and the proportion of OPEX spent by each department.

Profitability by Date (Graphs)

Shows two line graphs with gross profit margins (profitability) calculated using actual plus forecast amounts over time (default in years). The lines visually show the profitability trends over time, with higher gross profit margin lines at the top, down to the lowest and lease profitable lines at the bottom. Within a particular line, an uptrend shows improving profitability over time while downtrends show decreasing profitability. Knowing which products and services are more or less profitable helps businesses find opportunities for growth, reduce losses and optimize costs.

Gross Profit Margin by Product – Shows profitability over time by Product Name. Product Name can be substituted with Project Name in the PivotTable Fields Axis (Categories).

Gross Profit Margin by Customer – Shows profitability over time by certain customers.

 

 

Top 10 by Amount (Charts)

Shows a series of four stacked bar charts with categories visually ranked by actual plus forecast amounts for the selected time period. Use the date timeline to select the date or range of dates for comparison. The timeline is connected to the four charts in this section only and does not influence the rest of the Performance Dashboard.

Revenue by Product – Shows the total revenue earned by the top 10 products or services for the period selected. Product Name can be substituted with Project Name in the PivotTable Fields Axis (Categories).

Revenue by Customer – Shows the total revenue earned by the top 10 customers for the period selected.

OPEX by Account – Shows the total revenue earned by the top 10 products or services for the period selected.

OPEX by Department – Shows the total revenue earned by the top 10 products or services for the period selected.

 

Back to Top

 

Purpose

Shows actual plus forecast total amounts by account, grouped by account category, over time and grand totaled. The amounts shown are calculated using actuals plus forecast amounts (all actuals plus forecast after). Actuals cover data from inception through the ‘Actuals Through’ date.

 

Users can use this table to help ask or answer questions including but not limited to the following: 

• • • Which are my highest expenses?
    • How frequently do certain expenses occur?
    • Are there opportunities to cut costs?
    • Which expense accounts are growing the fastest?
    • Are expenses growing faster than revenues?
    • Do forecasted amounts suddenly increase or decrease sharply compared to actuals?
    • Are certain revenue or expense accounts missing forecast when there are historical actuals? 

 

Back to Top

Purpose

Shows the total revenue earned by different products, projects or customers for the period selected, as well as their contribution to Gross Profit and their overall profitability by %. Users can use this table to monitor their top revenue or profit generators as well as their lowest. The least profitable revenue generators may require attention or a shift of effort to more profitable business lines.

 

Users can use this table to help ask or answer questions including but not limited to the following: 

• • • Which streams are the highest revenue earners? The lowest?
    • Which revenue streams contribute the most gross profit?
    • Which revenue streams are the most profitable by gross profit margin?
    • Are there opportunities to increase sales for the revenue streams with the highest profit margins?
    • Do any revenue streams need to be reduced or removed to improve profitability?
    • Can direct costs be optimized for any revenue streams to improve profitability?
    • Which are my top customers or clients? Am I prioritizing these top clients?
    • How do I find more customers or clients like my top favorites?
    • Which are my least favorite customers or clients? Are these also small customers or high paying customers?
    • Are there any patterns between the most profitable and lease profitable clients, products or projects?
    • Are any of my smallest or least favorite customers or clients using up more than their fair share of effort to maintain doing business?
    • Are there any clients that I should avoid repeating doing business with going forward? 

 

Product Name, Project Name and Customer Name can be reordered or included/excluded from the PivotTable Rows, depending on the most helpful way of viewing your company’s revenue streams.

 

Default Pivot Row Field Order 

Project Name – Name of the project, if applicable

Product Name – Name of the product or service provided, if applicable

Customer Name – ID of the customer or client, if applicable

 

 

Back to Top