Vendor Relationship Dashboard
TeamConnect Business Intelligence provides a number of starter dashboards. These dashboards can be configured to meet your specific needs, as they may not work with your system without customization and you may need to remap certain fields depending on your data model.
The minimum version that supports this vendor relationship dashboard is TCBI 6.3 and is available for both TeamConnect Essentials and TeamConnect Enterprise versions.
Introduction
The vendor relationship dashboard provides an overview of a vendor’s performance and relationship with the client across matters they have been assigned. These are designed to be embedded within an individual vendor contact record which will need to be done when the dashboard is imported.
Note: For more information on how to embed dashboard/widget at record level, refer to the link.
Budget vs. Spend YoY for Vendor displays the year over year spend vs. budget for the selected vendor.
Days Since Last Billing Activity calculates number of days elapsed since last invoice from this vendor was received.
Average Days Last Billing Activity calculates average number of days elapsed since last invoice was received considering all vendors in the system.
Invoice Timeliness calculates the average number of days this vendor takes to submit an invoice considering billing date.
Budget vs. Spend by Matter Type displays the sum of all budgets broken down by matter type, as compared to spend. This report only includes data for matters that have matter-specific budgets, and includes data for all fiscal years as selected in the Filters section.
Spend by TimeKeeper Class displays the Spend percentage for each timekeeper class calculated with invoice net total.
Average TimeKeeper Rate by Class for USD Rates Only displays the Year over year timekeeper rate for each timekeeper classification. This is by default filtered only to include USD rates.
Total Adjustments
Matter Count displays the year on year matter count for that vendor.
Invoice Line Item Warnings & Adjustments provides the summary view of all invoice line items warning and adjustments.
Detailed Specifications
| Widget Name | Description | Type | Parameters | Obeys dashboard filters | Design | |
|---|---|---|---|---|---|---|
| Essentials (EaB) |
Enterprise (Legal/FM) | |||||
| Budget vs. Spend YoY for Vendor |
Current year's predicted spend based on current year's allocated budget and average increase factor.
|
Indicator |
Values: formula to calculate average increase; AVG([Years in Account Period Start On], (sum([Account Total Spend])-sum([Account Total Budget Allocated]))/sum([Account Total Budget Allocated])); conditional formatting to display less than or equal to 0 value in Green and greater than 0 value in Red Secondary: formula to calculate current year’s predicted spend; ([Total Account Total Budget Allocated], [Years in Account Period Start On]) + (AVG([Years in Account Period Start On1], ((sum([Account Total Spend])-sum([Account Total Budget Allocated]))/sum([Account Total Budget Allocated]))) * ([Total Account Total Budget Allocated], [Years in Account Period Start On])) Widget Filters: Years in account period start on (with custom timeframe filter for last 5 years) |
Values: formula to calculate average increase; AVG([Years in Account Period Start On], (sum([Account Total Spend])-sum([Account Total Budget Allocated]))/sum([Account Total Budget Allocated])); conditional formatting to display less than or equal to 0 value in Green and greater than 0 value in Red Secondary: formula to calculate current year’s predicted spend; ([Total Account Total Budget Allocated], [Years in Account Period Start On]) + (AVG([Years in Account Period Start On1], ((sum([Account Total Spend])-sum([Account Total Budget Allocated]))/sum([Account Total Budget Allocated]))) * ([Total Account Total Budget Allocated], [Years in Account Period Start On])) Widget Filters: Years in account period start on (with custom timeframe filter for last 5 years) |
Yes (except: year in line item service date, Invoice vendor contact primary key) |
Indicator type: numeric Title: yes |
| Budget vs. Spend YoY for Vendor |
|
Line |
X-axis: account budget and spend / account fiscal year (filtered to ignore N/A) Values: SUM(account budget and spend / account total spend); SUM(account budget and spend / account total budget allocated) |
X-axis: account budget and spend / account fiscal year (filtered to ignore N/A) Values: SUM(account budget and spend / account total spend); SUM(account budget and spend / account total budget allocated) |
|
Line: smooth Line width: bold Legend: bottom X-axis & Y-axis: grid lines, labels |
| Days Since Last Billing Activity | Calculates number of days elapsed since last invoice from this vendor was received. | Indicator | Values: formula; MIN(DDiff(now([Days in Invoice Date]), [Days in Invoice Received Date])) | Values: formula; MIN(DDiff(now([Days in Invoice Date]), [Days in Invoice Received Date])) | Yes (except Account vendor contact primary key, years in invoice line item service date) |
Indicator type: numeric Title: yes |
| Benchmark: Days Since Last Billing Activity |
Calculates average number of days elapsed since last invoice was received considering all vendors in the system. |
Indicator |
Values: MIN(DDiff(now([Days in Invoice Date]), [Days in Invoice Received Date])) Widget filters: (Days in) invoice received date (with custom filter for last 365 days) |
Values: MIN(DDiff(now([Days in Invoice Date]), [Days in Invoice Received Date])) Widget filters: (Days in) invoice received date (with custom filter for last 365 days) |
No |
Indicator type: numeric Title: yes |
| Invoice Timeliness | Calculates the average number of days this vendor takes to submit an invoice considering billing date. | Indicator | Values: formula; AVG(DDIFF([Days in Invoice Received Date],[Days in Invoice Billing End Date])) | Values: formula; AVG(DDIFF([Days in Invoice Received Date],[Days in Invoice Billing End Date])) | Yes (except Account vendor contact primary key, years in invoice line item service date) |
Indicator type: numeric Title: yes |
| Benchmark: Invoice Timeliness | Calculates the average number of days it takes to submit an invoice considering billing date for all vendors in the system. | Indicator |
Values: AVG(DDIFF([Days in Invoice Received Date],[Days in Invoice Billing End Date])) Widget filters: (Days in) invoice received date (with custom filter for last 365 days); (Days in) invoice billing end date (with custom filter for last 365 days) |
Values: AVG(DDIFF([Days in Invoice Received Date],[Days in Invoice Billing End Date])) Widget filters: (Days in) invoice received date (with custom filter for last 365 days); (Days in) invoice billing end date (with custom filter for last 365 days) |
No |
Indicator type: numeric Title: yes |
| Diversity | Diversity information for the vendor. | Table | Columns: invoice office contact law firm details / minority owned firm (filtered to ignore N/A); invoice office contact law firm details / woman owned firm (filtered to ignore N/A) | Columns: invoice office contact law firm details / minority owned firm (filtered to ignore N/A); invoice office contact law firm details / woman owned firm (filtered to ignore N/A) | Yes | default |
| Budget vs. Spend by Matter Type | Spend information for the vendor across different matter types. | Bar |
Categories: matter details / matter type (filtered to ignore N/A) Values: Values: SUM(account budget and spend / account total spend); SUM(account budget and spend / account total budget allocated) Widget filters: (Days in) account period end on (with custom filter for last 365 days) |
Categories: dispute categories / dispute category full (filtered to ignore N/A) Values: Values: SUM(account budget and spend / account total spend); SUM(account budget and spend / account total budget allocated) Widget filters: (Days in) account period end on (with custom filter for last 365 days) |
Yes (except Invoice vendor contact primary key, days in invoice line item service date) |
Bar type: classic Legend: bottom X-axis & Y-axis: labels |
| Invoice With/Without | Widget to create toggle behavior for Adjustments and Warnings pie chart widgets. | Tabber | Modify script through ‘Edit Script’ option as provided here |
Modify script through ‘Edit Script’ option as provided here |
Yes | Default |
| Invoice With/Without Adjustments | Percentage of invoices received that included adjustments (manual or through a rule) | Pie |
Categories: invoice line item / invoice line item has adjustments (filtered to ignore N/A) Values: invoice / count(invoice number) |
Categories: invoice line item / invoice line item has adjustments (filtered to ignore N/A) Values: invoice / count(invoice number) |
Yes (except Account vendor contact primary key) |
Type: classic Legend: right Labels: categories, values, percent |
| Invoice With/Without Warnings | Percentage of invoices received that included warnings (manual or through a rule) | Pie |
Categories: invoice line item / invoice line item has warnings (filtered to ignore N/A) Values: invoice / count(invoice number) |
Categories: invoice line item / invoice line item has warnings (filtered to ignore N/A) Values: invoice / count(invoice number) |
Yes (except Account vendor contact primary key) |
Type: classic Legend: right Labels: categories, values, percent |
| Total Adjustments YoY for Vendor | Year over year trend of total adjustments made to invoices from the selected vendor. | Line |
X-axis: invoice / years in invoice date (filtered to ignore N/A) Y-axis: invoice line item adjustments / invoice line item adjustment calculated total |
X-axis: invoice / years in invoice date (filtered to ignore N/A) Y-axis: invoice line item adjustments / invoice line item adjustment calculated total |
Yes (except Account vendor contact primary key, years in invoice line item service date) |
Line: straight Line width: bold Legend: bottom X-axis & Y-axis: grid lines, labels |
| Spend by Timekeeper Class | Spend percentage for each timekeeper class calculated with invoice net total. | Pie |
Categories: invoice line item timekeeper contact / invoice line item timekeeper external category (filtered to ignore N/A) Values: SUM([Invoice Net Total]*[Invoice Exchange Rate]) |
Categories: invoice line item timekeeper contact / invoice line item timekeeper default category full (filtered to ignore N/A) Values: SUM([Invoice Net Total]*[Invoice Exchange Rate]) |
Yes (except Account vendor contact primary key) |
Type: classic Legend: bottom Labels: percent |
| Average Timekeeper Rate by Class for USD Rates Only | Year over year timekeeper rate for each timekeeper classification. This is by default filtered only to include USD rates | Column |
Categories: invoice line item timekeeper contact / invoice line item timekeeper external category (filtered to ignore N/A) Values: AVG([Timekeeper Current Rate]) Break by: invoice line item / years in invoice line item service date (filtered to ignore N/A) Widget filter: contact / contact currency code (filtered to USD) |
Categories: invoice line item timekeeper contact / invoice line item timekeeper default category full (filtered to ignore N/A) Values: AVG([Timekeeper Current Rate]) Break by: invoice line item / years in invoice line item service date (filtered to ignore N/A) Widget filters: contact / contact currency code (filtered to USD) |
Yes (except Account vendor contact primary key, years in invoice line item service date) |
Column type: classic Legend: bottom X-axis & Y-axis: labels |
| Matter Count | Year on year matter count for that vendor against the benchmark for all vendors. | Bar |
Categories: matter / years in matter opened on Values: matter / count(matter number) |
Categories: dispute / (years in) dispute opened on Values: dispute / count(dispute number) |
Yes (except Account vendor contact primary key, years in invoice line item service date) |
Bar type: classic Legend: bottom X-axis & Y-axis: labels |
| Invoice Line Item Warnings & Adjustments | Summary view of all invoice line items warning and adjustments. | Pivot | Rows: invoice (invoice vendor, invoice number, invoice date, invoice currency symbol, invoice net total), invoice line item (invoice line item has warnings, invoice line item warnings, invoice line item has adjustments, invoice line item adjustment source) | Rows: invoice (invoice vendor, invoice number, invoice date, invoice currency symbol, invoice net total), invoice line item (invoice line item has warnings, invoice line item warnings, invoice line item has adjustments, invoice line item adjustment source) | Yes (except Account vendor contact primary key) | Defaul |
Formulae Details
| Widget Name | Formula Title | Formula | Description |
|---|---|---|---|
| Budget vs. Spend YoY for Vendor | Average increase | AVG([Years in Account Period Start On], (sum([Account Total Spend])-sum([Account Total Budget Allocated]))/sum([Account Total Budget Allocated])) |
Calculates average increase / decrease in spend as compared to budget allocated. The widget includes a filter to calculate only for the last 5 years (not including the current year). |
| Budget vs. Spend YoY for Vendor | Current year's predicted spend | ([Total Account Total Budget Allocated], [Years in Account Period Start On]) + (AVG([Years in Account Period Start On1], ((sum([Account Total Spend])-sum([Account Total Budget Allocated]))/sum([Account Total Budget Allocated]))) * ([Total Account Total Budget Allocated], [Years in Account Period Start On])) |
Adds the adjusted budget allocated (adjusted based on the average increase calculated) and then adds that value to budget allocated to calculate the predicted spend for current year. The highlighted parts in the formula include filter to select ‘This Year’ to filter budget allocated for only the current year. |
| Days Since Last Billing Activity | Days since last invoice | MIN(DDiff(now([Days in Invoice Date]), [Days in Invoice Received Date])) | Calculates minimum number of days passed since the last invoice was received from the vendor. |
| Benchmark: Days Since Last Billing Activity | Benchmark: Days since last invoice | MIN(DDiff(now([Days in Invoice Date]), [Days in Invoice Received Date])) | Calculates minimum number of days passed since the last invoice was received from all the vendors in the system during the last 365 days. The formula is the same but dashboard filters are ignored in this widget. |
| Invoice Timeliness | Avg days to submit invoice | AVG(DDIFF([Days in Invoice Received Date],[Days in Invoice Billing End Date])) | Calculates average number of days between the billing end date and received date across all invoices from the vendor. |
| Benchmark: Invoice Timeliness | Benchmark: Avg days to submit invoice | AVG(DDIFF([Days in Invoice Received Date],[Days in Invoice Billing End Date])) | Calculates average number of days between the billing end date and received date across all invoices from all the vendors in the system during the last 365 days. The formula is the same but all dashboard filters are ignored in this widget. |
Implementation Notes
Tabber Widget plugin should be added to the Plugins folder for the widget to work. If you don’t want to add the tabs, you can remove the widget from the dashboard.
Summary
As a quick summary, this dashboard addresses some of the following activities:
- What is our overall spend? When, where, with whom?
- How are we tracking spend vs. budget?
- Are we engaging the right firms for the right things? Am I getting the value I needed?
- How well is this vendor performing?
- Am I using my resources optimally?
- Are my vendors submitting accurate budgets?