Author: Business Intel

Top 5 Accessories By Deal Count

This bar chart shows the top 5 selling accessories on deals by the number of posted and delivered deals.  Selecting the Others bar will display the next 5 best selling accessories.

Completed Deals By Month

This chart shows the number of Posted and Delivered deals grouped by the month of the deal contract date.

Completed Deal Count By Sales Rep

This bar chart shows the number of Posted and Delivered deals for each sales rep.

Average Vehicle Gross By Sales Manager

This bar chart shows the average vehicle gross profit from Posted and Delivered deals for each sales manager.

Average Back End Gross By Business Manager

This bar chart shows the average F & I gross profit from deals that are Posted or Delivered for each business manager (primary or secondary).

Average Back End Gross By Sales Manager

This bar chart shows the average F & I gross profit from deals that are Posted and Delivered for each sales manager(primary or secondary).

Average Accessory Gross By Sales Manager

This bar chart shows the average gross profit from accessories sold on Posted and Delivered deals for each sales manager (primary or secondary).

# of Deals By Sales Manager

This Bar Chart Shows the number of posted and delivered deals for each Sales Manager (primary or secondary).

# of Deals By Business Manager

This bar chart shows the number of Posted or Delivered Deals for each Business Manager (primary or secondary).

 

Service Modifiers

Modifiers are a way to have your measures only target specific values in your data set without having to add a selection filter.  For example, when you view all the values for the labour line price in the labour line table, this includes all repair orders, appointments and estimates; even the open or void work items.  To only include repair orders that are cashiered in our measure we could add a modifier to only include repair orders that are cashiered.  To add a modifier to your expression, add the specific table and field you are targeting, then type the values you want to target between curly brackets.  For our example of labour line prices that are only cashiered, we would use the data field called [Repair Order.Status], then type that we want only values that are equal to Cashiered, the result will look like this: [Repair Order.Status]={‘Cashiered’}.  Be sure to add quotation marks around text, and to separate each value with a comma.

To help users with entering some common modifiers to their expressions or variable functions, the Business Intel team has put together a list of variables that can act as a modifier in your expression or variable function.  In order to use one of the variable modifiers, you will need to wrap the name of the modifier in $().  For example to add the modifier for only cashiered repair orders you would type $(vROIsCashiered) into your expression editor.  Some of the variable modifiers allow you to add your own value to target.  One example of this is the variable modifier named vpExcludeLabourOpDesc.  To use this modifier and to add our own value for the labour line description we are trying to exclude, you will need to wrap the modifier in a $()$(vpExcludeLabourOpDesc), then to add the value we are targeting, add an open and close parentheses after the modifiers name.  Between the parentheses type in the value to target, $(vpExcludeLabourOpDesc(‘Lube, Oil & Filter’)).

Below is a list of the deal modifiers currently available and a brief description of what data the are targeting.

vpIncludeLabourPC – use this modifier to target a specific labour price code.  You can add a single price code in the modifier.

vpIncludeLabourOpDesc – use this modifier to target a specific description on the labour line of the repair order.  You can add a single value for the description you are targeting.

vROIsVoid – use this modifier to target repair orders that have a status of void.

vExcludeZeroLabourCost – use this modifier to target labour lines that do not have a cost of $0.00.  To remove zero labour repair orders, also include the modifier “vExcludeZeroLabourPrice” in your expression.

vExcludeZeroLabourPrice – use this modifier to target labour lines that do not have a price of $0.00.  To remove zero labour repair orders, also include the modifier “vExcludeZeroLabourCost” in your expression.

vpExcludeLabourOpDesc – use this modifier to exclude labour lines that have a description matching the value you specify.  You can add up to 1 description to exclude.

vExcludeVoidRO – use this modifier to remove void repair orders.

Service Functions

The variable functions are a group of  measures that the Business Intel team has put together to further assist users with creating their very own expressions.  These are different from the measures that currently exist in the master library because you will be able to customize the measure to target the specific data you are looking for.  The functions start off as a very basic measure, and from there the user can add their very own modifiers so that only the data they are looking for will be included.  Each function can have up to 9 different modifiers.

To use a variable function, wrap the functions name in $(), for example to use the variable function fnLabourGP, we would type $(fnLabourGP) into the expression editor.  To add your modifiers, add an open and close parentheses after the variable functions name, for example with our $(fnLabourGP), to add modifiers we would type in $(fnLabourGP(Modifier1, Modifier2, Modifier3)).  Separate each of the modifiers with a comma.

The following list shows the different variable functions that have been created in the Business Intelligence application and a brief description of what each function is measuring.

fnCountROCasheried – count of distinct repair order numbers that have a status of Cashiered.

fnLabourCost – the sum of the cost of labour from repair orders that have a status of Cashiered.

fnLabourGP – the sum of the labour line price less the labour line cost from repair orders that have a a status of Cashiered.

fnLabourGP% = the sum of the labour line price, less the labour line cost, divided by the sum of the labour line price from repair orders that have a status of Cashiered.

fnLabourSales – the sum of the labour line price from repair orders that have a status of Cashiered.

fnLabourSalesPerRO – the sum of the labour line price, divided by the count of distinct repair order number from repair orders that have a status of Cashiered.

fnLabourSalesPayType – based on the cashiering date of the pay type, the sum of the labour line price from repair orders that are not void and the request lines are either original or approved status.

fnPartsROSalesPayType – based on the cashiering date of the pay type, the sum of the extended parts price from repair orders that are not void and the request lines are either original or approved status.

fnPartsROCostPayType – based on the cashiering date of the pay type, the sum of the extended parts cost from repair orders that are not void and the request lines are either original or approved status.

fnROCashieredPayTypeCount – based on the cashiering date of the pay type, the count of distinct repair order numbers from repair orders that are not void and the request lines are either original or approved status.

fnLabourGrossPayType – based  on the cashiering date of the pay type, the sum of the labour line price, less the labour line cost from repair orders that are not void and have request lines in original or approved status.

fnLabourCostPayType – based on the cashiering date of the pay type, the sum of the labour line cost from repair orders that are not void and have request lines in original or approved status.

fnPartsROGrossPayType – based on the cashiering date of the pay type, the sum of the parts line extended price, less the parts line extended cost from repair orders that are not void and have request lines in original or approved status.

fnLabourSalesCPPayType – based on the cashiering date of the customer pay portion of the RO, the sum of the labour line price from repair orders that are not void and have the request line in original or approved status.

fnLabourSalesWPPayType – based on the cashiering date of the warranty pay portion of the RO, the sum of the labour line price from repair orders that are not void and have the request lines in original or approved status.

fnLabourSalesIPPayType – based on the cashiering date of the internal pay portion of the RO, the sum of the labour line price from repair orders that are not void and have the request lines in original or approved status.

fnLabourGrossCPPayType – based on the cashiering date of the customer pay portion of the RO, the sum of the labour line price, less the labour line cost from repair orders that are not void and have the request line in original or approved status.

fnLabourGrossWPPayType – based on the cashiering date of the warranty pay portion of the RO, the sum of the labour line price, less the labour line cost from repair orders that are not void and have the request lines in original or approved status.

fnLabourGrossIPPayType – based on the cashiering date of the internal pay portion of the RO, the sum of the labour line price, less the labour line cost from repair orders that are not void and have the request lines in original or approved status.

fnLabourCostCPPayType – based on the cashiering date of the customer pay portion of the RO, the sum of the labour line cost from repair orders that are not void and have the request line in original or approved status.

fnLabourCostWPPayType – based on the cashiering date of the warranty pay portion of the RO, the sum of the labour line cost from repair orders that are not void and have the request lines in original or approved status.

fnLabourCostIPPayType – based on the cashiering date of the internal pay portion of the RO, the sum of the labour line cost from repair orders that are not void and have the request lines in original or approved status.

fnPartsROGrossCPPayType – based on the cashiering date of the customer pay portion of the RO, the sum of the extended parts line price, less the extended cost from repair orders that are not void and have the request line in original or approved status.

fnPartsROGrossWPPayType – based on the cashiering date of the warranty pay portion of the RO, the sum of the extended parts line price, less the extended cost from repair orders that are not void and have the request line in original or approved status.

fnPartsROGrossIPPayType – based on the cashiering date of the internal pay portion of the RO, the sum of the extended parts line price, less the extended cost from repair orders that are not void and have the request line in original or approved status.

fnPartsROSalesCPPayType – based on the cashiering date of the customer pay portion of the RO, the sum of the extended parts line price from repair orders that are not void and have the request line in original or approved status.

fnPartsROSalesWPPayType – based on the cashiering date of the warranty pay portion of the RO, the sum of the extended parts line price from repair orders that are not void and have the request line in original or approved status.

fnPartsROSalesIPPayType – based on the cashiering date of the internal pay portion of the RO, the sum of the extended parts line price from repair orders that are not void and have the request line in original or approved status.

fnPartsROCostCPPayType – based on the cashiering date of the customer pay portion of the RO, the sum of the extended parts line cost from repair orders that are not void and have the request line in original or approved status.

fnPartsROCostWPPayType – based on the cashiering date of the warranty pay portion of the RO, the sum of the extended parts line cost from repair orders that are not void and have the request line in original or approved status.

fnPartsROCostIPPayType – based on the cashiering date of the internal pay portion of the RO, the sum of the extended parts line cost from repair orders that are not void and have the request line in original or approved status.

fnLabourSoldHoursPayType – based on the cashiering date of the pay type, the sum of the sold hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnLabourSoldHoursCPPayType – based on the cashiering date of the customer pay type, the sum of the sold hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnLabourSoldHoursWPPayType – based on the cashiering date of the warranty pay type, the sum of the sold hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnLabourSoldHoursIPPayType – based on the cashiering date of the internal pay type, the sum of the sold hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnLabourActualHoursPayType – based on the cashiering date of the  pay type, the sum of the actual hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnLabourActualHoursCPPayType – based on the cashiering date of the customer pay type, the sum of the actual hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnLabourActualHoursWPPayType – based on the cashiering date of the warranty pay type, the sum of the actual hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnLabourActualHoursIPPayType – based on the cashiering date of the internal pay type, the sum of the actual hours on the labour line from repair orders that are not void and have the request line in status original or approved.

fnROCashieredCPPayTypeCount – based on the cashiering date of the customer pay portion, the count of distinct repair order number from repair orders that are not void and have the request status as original or approved.

fnROCashieredWPPayTypeCount – based on the cashiering date of the warranty pay portion, the count of distinct repair order number from repair orders that are not void and have the request status as original or approved.

fnROCashieredIPPayTypeCount – based on the cashiering date of the internal pay portion, the count of distinct repair order number from repair orders that are not void and have the request status as original or approved.

fnSubletSalesPayType – based on the cashiering date of the pay type, the sum of the sublet line price from repair orders that are not void, and have the request line status of original or approved.

fnSubletSalesCPPayType – based on the cashiering date of the customer pay type, the sum of the sublet line price from repair orders that are not void, and have the request line status of original or approved.

fnSubletSalesWPPayType – based on the cashiering date of the warranty pay type, the sum of the sublet line price from repair orders that are not void, and have the request line status of original or approved.

fnSubletSalesIPPayType – based on the cashiering date of the internal pay type, the sum of the sublet line price from repair orders that are not void, and have the request line status of original or approved.

fnSubletCostPayType – based on the cashiering date of the pay type, the sum of the sublet line cost from repair orders that are not void and have the request line status of original or approved.

Deal Modifiers

Modifiers are a way to have your measures only target specific values in your data set without having to add a selection filter.  For example, when you view all the values for the vehicle gross in the scenarios table, this includes all deals even the pending or cancelled deals.  To only include deals that are completed in our measure we could add a modifier to only include deals that are posted or delivered.  To add a modifier to your expression, add the specific table and field you are targeting, then type the values you want to target between curly brackets.  For our example of deals that are only posted and delivered, we would use the data field called [Deals.DealStatus], then type that we want only values that are equal to Posted and Delivered, the result will look like this: [Deals.DealStatus]={‘Posted’,’Delivered’}.  Be sure to add quotation marks around text, and to separate each value with a comma.

To help users with entering some common modifiers to their expressions or variable functions, the Business Intel team has put together a list of variables that can act as a modifier in your expression or variable function.  In order to use one of the variable modifiers, you will need to wrap the name of the modifier in $().  For example to add the modifier for only completed deals you would type $(vDealStatusCompleted) into your expression editor.  Some of the variable modifiers allow you to add your own value to target.  One example of this is the variable modifier named vpDealProtectionsInclude.  To use this modifier and to add our own value for the protection we are trying to target, you will need to wrap the modifier in a $(), $(vpDealProtectionsInclude), then to add the value we are targeting, add an open and close parentheses after the modifiers name.  Between the parentheses type in the value to target, $(vpDealProtectionsInclude(‘GAP’)).

Below is a list of the deal modifiers currently available and a brief description of what data the are targeting.

vDealStatusPosted – only include deals that are in a posted status.

vDealStatusCompleted – only include deals that are in a posted or delivered status.

vDealStatusSold – only include deals that are in a sold status.

vDealStatusCancelled – only include deals that are in a Deleted, Cancelled or Inactive status.

vDealStatusPending – only include deals that do not have a status equal to Posted, Delivered, Sold, Inactive, Deleted, Cancelled or Lost.

vDealIsNew – only include deals that are sold as new.

vDealIsUsed – only include deals that are sold as used.

vDealIsQuote – only include deals that are set as a quote.

vDealIsFromLead – only include deals that are associated to a lead.

vDealIsNotFromLead – only include deals that are not associated to a lead.

vDealHasAccessories – only include deals that have at least 1 accessory with a price greater than $0.00.

vDealHasProtections – only include deals that have at least 1 protection with a price greater than $0.00.

vDealHasWarranty – only include deals  that have at least 1 warranty with a price greater than $0.00.

vDealHasReserve – only include deals that have a reserve gross profit greater than $0.00.

vDealHasInsurance – only include deals that have an insurance premium greater than $0.00.

vpDealProtectionsInclude – only include deals that have a protection with he  name listed.  You can only add 1 value to target.

vpDealProtectionsExclude – only include deals that do not have a protection with the name listed.  You can only add 1 value to target.

vpDealWarrantiesInclude – only include deals that have a warranty with he  name listed.  You can only add 1 value to target.

vpDealWarrantiesExclude – only include deals that do not have a warranty with the name listed.  You can only add 1 value to target.

vpDealAccessoriesInclude – only include deals that have an accessory with he  name listed.  You can only add 1 value to target.

vpDealAccessoriesExclude – only include deals that do not have an accessory with the name listed.  You can only add 1 value to target.

vpDealFeesInclude – only include deals that have a fee with he  name listed.  You can only add 1 value to target.

vpDealFeesExclude – only include deals that do not have a fees with the name listed.  You can only add 1 value to target.

vpDealFinanceRateBetween – only include deals that have a finance rate between the two values listed.  You will need to add a minimum value and a maximum value.

vpDealFinanceTermBetween – only include deals that have a finance term between the two values listed.  You will need to add a minimum value and a maximum value.

vpDealInsurancesInclude – only include deals that have the insurance type listed.  You can only add 1 value for the insurance type.

vpDealInsurancesExclude – Only include deals that do not have the insurance type listed.  You can only add 1 value for the insurance type.

vpDealProgramsInclude – only include deals that have the listed program.  You can only add 1 value for the program name.

vpDealProgramsExclude – only include deals that do not have the listed program.  You can only add 1 value for the program name.

vpDealLeaseRateBetween – only include deals that have a lease rate between the two values listed.  You will need to add a minimum value and a maximum value.

vpDealLeaseTermBetween – only include deals that have a lease term between the two values listed.  You will need to add a minimum value and a maximum value.

vpDealSaleTypesInclude – only include deals that have the sale type that is listed.  You can only add 1 value for the sale type.

vpDealSaleTypesExclude – only include deals that do not have the sale type that is listed.  You can only add 1 value for the sale type.

vDealSaleTypesIsRetail – only include deals that have a sale type with the words Retail or RETAIL in their name.

vDealSalesTypesIsNotRetail – only inlcude deals that do not have a sale type with the words Retail or RETAIL in their name.

vDealHasTrades – only include deals that have at least 1 VIN in the deal trades.

vDealHasNoTrades – only include deals that have no VINs in the deal trades.

vDealIsCertified – only include deals that are set as certified.

vDealIsNotCertified – only include deals that are not set as certified.

Deal Functions

The variable functions are a group of  measures that the Business Intel team has put together to further assist users with creating their very own expressions.  These are different from the measures that currently exist in the master library because you will be able to customize the measure to target the specific data you are looking for.  The functions start off as a very basic measure, and from there the user can add their very own modifiers so that only the data they are looking for will be included.  Each function can have up to 9 different modifiers.

To use a variable function, wrap the functions name in $(), for example to use the variable function fnDealCount, we would type $(fnDealCount) into the expression editor.  To add your modifiers, add an open and close parentheses after the variable functions name, for example with our $(fnDealCount), to add modifiers we would type in $(fnDealCount(Modifier1, Modifier2, Modifier3)).  Separate each of the modifiers with a comma.

The following list shows the different variable functions that have been created in the Business Intelligence application and a brief description of what each function is measuring.

fnDealPrice – the sum of the vehicle price based on the current selections.

fnDealCount – the count of all deal keys that are not set as a quote based on the current selections.

fnDealAccessoriesTotalSales – the sum of the accessories price from all deals that are not set as a quote based on the current selections.

fnDealAccessoriesTotalGross – the sum of the accessories price, less the accessories cost from all deals that are not quotes based on the current selections.

fnDealTotalGross – the sum of the total accessories gross profit, back end gross profit and vehicle gross profit from all deals that are not set as quotes based on the current selections.

fnDealVehicleGross – the sum of the vehicle gross from all deals that are not set as quotes based on the current selections.

fnDealBackEndGross – the sum of the F&I gross profit from all deals that are not set as quotes based on the current selections.

fnDealProtectionsGross – the sum of the deal protections sale price less the cost for all deals that are not set as quotes based on the current selections.

fnDealProtectionsSales – the sum of the deal protections sale price from all deals that are not set as quotes based on the current selections.

fnDealWarrantyGross – the sum of the deal warranty price, less the cost for all deals that are not set as quotes based on the current selections.

fnDealWarrantySales –  the sum of the deal warranty price from all deals that are not set as quotes based on the current selections.

fnDealReserveGross – the sum of the deal reserve gross profit from all deals that are not set as quotes based on the current selections.

fnDealInsuranceGross – the sum of the insurance price, less the insurance cost from all deals that are not set as quotes based on the current selections.

fnDealInsuranceSales – the sum of the insurance price from all deals that are not set as quotes based on the current selections.

fnDealQuotesCount – the count of all deal keys from deals that are set as quotes based on the current selections.

fnLeadCount – the count of all lead ids  based on the current selections.

fnDealFeeGross – the sum of the deal fees amount from all deals that are not set as quotes based on the current selections.

 

PBS Business Intel Workshop

The Business Intel team at PBS Systems would like to announce that we will be hosting a BI workshop on Wednesday September 20th 2017.  If you are already attending the PBS User Conference in Calgary, the BI workshop will be the same week and a great chance to learn more about the capabilities of PBS Business Intel.  For more information about the workshop and to register, please Click Here .

For any additional questions please contact the PBS Business Intel team at businessintel@pbssystems.com, or call us at 1 (800) 665-6304 ext 779.  Hope to see you there.

Historical Fixed Ops Invoice Measures

The following Measures are currently available in the Historical Fixed Ops Invoice application:

  • Actual Hours Booked – This calculation is the sum of the actual hours that have been added to the labour line of an open repair order.  Cashiered portions of the repair order are not included in the total.
  • Cost of Parts – This calculation is the total cost of parts on open parts invoices and repair orders.  Cashiered portions of the repair orders are not included in the total.
  • Labour Costs – This calculation is the total cost of labour on open repair orders.  Cashiered portions of the repair orders are not included in the total.
  • Sold Hours Booked – This calculation is the sum of the sold hours that have been added to labour lines on open repair orders.  Cashiered Portions of the repair order are not included in the total.
  • Sublet Costs – This calculation is the total cost of sublet items added onto open repair orders.  Cashiered portions of the repair order are not included in the total.

Deal Retention

This pivot table displays information from vehicles that have been sold at the dealership and displays calculations for the retention levels in service.  Columns include: Vehicle Model Year, Vehicle Make, Vehicle Model, Vehicle Trim, Stock Number, Repair Order Number, # of Deals Delivered, Number of Deals Returned for Service, % of Deals Returned for Service, Number of Deals Returned for Customer Pay Service, % of Deals Returned for Customer Pay Service, % of Deals Returned After Base Warranty.  Columns for the vehicle details can be drilled down from vehicle year to repair order number by selecting the circle with + in it beside the value.

Service Parts Sales Details

This table displays results from parts sold on repair orders.  As a pivot table the columns can be reordered to allow you to view the information in your own way.  Clicking on the +s will allow you to drill down into information.  Columns include Part Number, Date Cashiered, Repair Order Number, Parts Line Advisor, Sales, Gross Profit, Gross Profit Margin.

Parts Invoice Sales Details

This table displays parts sales and gross profit information from parts invoice sales.  As a pivot table the columns can be rearranged to display results in your own personally preferred way.  Clicking on the +s will expand the columns to drill down into the data.  Columns include Part Number, Date Cashiered, Invoice Number, Advisor, Sales, Gross Profit and Gross Profit Margin.

Aristo Time Clock Total Hours

This line chart shows total hours logged into aristo time clock by all selected employees over the available date range.  The date dimension has drill down ability to start showing the year and month totals, then drill down to individual days.

Aristo Time Clock User Totals

This table displays information from hours logged into aristo time clock.  Columns include employee’s name, department name, total hours logged, number of days worked and average hours logged per day.

Loading...
X