Skip to main content
Mitratech Success Center

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.



 

Budget vs. Spend YoY for Vendor displays the year over year spend vs. budget for the selected vendor.

clipboard_e256fb38425bf74df38cf2b6862403c15.png

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.

clipboard_e0341718cdbf7cb77db6def7d0b8c549b.png

Invoice Timeliness calculates the average number of days this vendor takes to submit an invoice considering billing date.

clipboard_ebc187bcae76feb761c05d8fbb0fa818a.png

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.

General Counsel_Budget_vs_Spend.png

Spend by TimeKeeper Class displays the Spend percentage for each timekeeper class calculated with invoice net total.

clipboard_effa3b5f19f44d7c486408f917a638c8f.png

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.

clipboard_e9b6883f665209440aeabcb1316001edf.png

Total Adjustments

clipboard_ec54fdc9b413562c62fe43bd58ea76374.png

Matter Count displays the year on year matter count for that vendor.

clipboard_e3fa61b89fa28bada2dfe1d65fb95c9a6.png

Invoice Line Item Warnings & Adjustments provides the summary view of all invoice line items warning and adjustments.

clipboard_ea91e269d2f72b38f5d4d68d71cc477bc.png

Detailed Specifications

Widget Name Description Type Parameters Obeys dashboard filters Design
Essentials
(EaB)
Enterprise (Legal/FM)
Budget vs. Spend YoY for Vendor


Average increase (positive value) or decrease (negative value) in spend calculated for last 5 years; timeframe based on Account period start on field.

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


Year over year spend vs. budget for the selected 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)


Yes (invoice vendor contact primary key, years in invoice line item service date)

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?
  • Was this article helpful?