Used Vehicle Sales Log & Analysis Report

Used Vehicle Sales Log & Analysis Report
The Excel® spreadsheets within this workbook are designed to assist both the General and Used Vehicle Sales Manager monitor
Used Vehicle Sales within their dealership. They were originally created to replace a manual Used Vehicle Sales Log and later
expanded to included a self-calculating, in-depth Used Vehicle Sales Analysis Report.
The Used Vehicle Sales Analysis Report will assist in Micro-Managing your vehicle sales and trends, in turn simplifying the
process of managing and Optimizing a Used Vehicle Inventory.
It's use of statistical data & formulas will help;
Identify which Vehicle Makes and Models are generating profits or losses
Determine which Used Vehicle Acquisition Sources are the most or least profitable
Calculate your real Return-on-Assets (Vehicle Inventory) purchased and sold
Track inventory Aging Factors and determine its effect on profitability
You will also notice the Sales Logs do allow you to not only monitor Gross Profit on unit sales, they also assist in monitoring
Net Profit as well. This is done by utilizing the input cells for floorplan interest expenses and miscellaneous vehicle expenses.
A more detailed explanation on how to monitor these expenses are listed below.
General Spreadsheet Usage Instructions
Color Coding is utilized to simply usage of these spreadsheets. The only fields-cells that a user may input data are YELLOW
in color. All remaining cells are self-calculating. Drop-Down boxes are made available on each of the 2 Used Vehicle Sales Logs
that will track up to 40 unit sales each, for a total of 80 monthly unit sales. If your monthly sales volume exceeds 80 unit sales
feel free to contact IntactAuto support for assistances.
To Begin using this Spreadsheet is simple;
1). Enter the Month-End date that you'll be tracking below.
Sales Report Date
05/31/09
format (mm/dd/yyyy)
2). Enter the Used Vehicle Make Codes & Descriptions below. Default Values have been entered, simply type over them.
Make Codes: Limit Code to a maximum length of 2 characters.
Required for proper Drop-Down displaying. DO NOT enter duplicate Make Codes.
Make Description: Limit Description to a maximum length of 20 characters.
Required for labeling values on the Used Vehicle Analysis Report. DO NOT enter duplicate descriptions.
Make
Make
Codes
Description
Required Default
OT
Other
Used Vehicle - Type 1
TO
Toyota
Used Vehicle - Type 2
FO
Ford
Used Vehicle - Type 3
FT
Ford Truck
Used Vehicle - Type 4
GC
GMC
Used Vehicle - Type 5
CV
Chevrolet
Used Vehicle - Type 6
CT
Chevrolet Truck
Used Vehicle - Type 7
HO
Honda
Used Vehicle - Type 8
MB
Mercedes
Used Vehicle - Type 9
CY
Chrysler
Used Vehicle - Type 10
BU
Buick
Used Vehicle - Type 11
CD
Cadillac
Used Vehicle - Type 12
PN
Pontiac
Used Vehicle - Type 13
DO
Dodge
Used Vehicle - Type 14
BM
BMW
Special Notes; It is suggested that prior to entering Make Codes & Descriptions you give it some thought.
If you're going to utilize the Use Vehicle Sales Analysis Reports to create a Year-to-Date Analysis Summary
report you don't really monthly analysis reports to vary much.
Also, never change these codes or descriptions mid-month. Since this would effect all the vehicle sales
already logged within that month.
You now have a functional workbook ……………
©2009 IntactAuto Holdings, LLC. All rights reserved, Spreadsheet Designs by IntactAuto™

---

Used Vehicle Sales Log Worksheet Heading Descriptions & Reporting Options
1). Sold Unit Stock #
Your in-house vehicle identification number
2). Vehicle Source
Vehicle Sourcing is used to monitor the Profit or Losses generated on Used Unit Sales by originating source.
Utilize when reviewing unit sales profitability on the Unit Sales Summary report. Selection required for Analysis Report.
5 Chooses Available :
New Trade - Incoming Trade-In on a New Unit Sold if you have a New Vehicle Franchise
New Cycle - Trade-In on Used Unit originating from a Trade on New Unit Sales
Purchase - Units purchased at either an Auctions or Outside Source
Purch Cycle - Trade on unit originating from a Purchased unit
Other - Miscellaneous Unit Source if other than the above 4 chooses
3). Model Year
Used Unit Model Year sold which is segregated on the Unit Sales Summary Report Selection required for Analysis Report.
4). Make Code
A two digit code identifying the manufacturer brand established on the Maintenance worksheet, e.g. CV for Chevrolet
Make Codes are segregated on the Unit Sales Summary Report. Selection required for Analysis Report.
5). Vehicle Descriptions
An additional reference cell for you to utilize when reviewing unit Sales. Can be anything from Model Description to Color.
6). Acquisition Date
Date that your dealership acquired the Used Unit. Entry format; mm/dd/yyyy - Required entry for aging analysis
7). Unit Sale Date
Date that your dealership Sold the Used Unit. Entry format; mm/dd/yyyy - Required entry for aging analysis
8). Age in Days
Worksheet calculation of sold units age in days based on the above 2 entries. Verify accuracy of date entries
Segregated and Analyzed on the Unit Sales Summary Report.
9). Inventory Asset Cost
Actual investment plus adjustments made to vehicle asset cost/value, purchase price or ACV. Include reconditioning costs.
This value is usually your accounting total value, which includes Unit Cost Adjustments. See explanation below.
Segregated and Analyzed on the Unit Sales Summary Report. Required entry for analysis report.
10). Unit Cost Adjustments
Accounting entries made to either increase or decrease unit cost. Vehicle packs, Cost Averaging, Write-down's or Accrual
Adjustments. Remember to include any miscellaneous cost adjustments effecting the calculation of true gross profit.
Write-down entries are entered as a negative amount. In turn reducing your TRUE Vehicle Gross Profit.
Segregated and Analyzed on the Unit Sales Summary Report. Required entry if any for analysis report.
11). Actual Unit Cost
Worksheet calculation of sold units actual monetary cost after inventory cost adjustments.
Actual Unit Cost value is used for calculating actual Vehicle Sales Gross Profit. The Vehicle Gross Profit may differ
from your accounting entry since all miscellaneous, adjusting entries are removed for the purpose of calculating a
TRUE Return-on-Asset. Review accuracy of Cost & Adjustment entries
12). Unit Sale Gross Profit
Accounting entry made to your financial statement. Doesn't include commissioning packs applied to unit sale and
does reflect all Unit Cost Adjustments accounted for in description number 10 - Unit Cost Adjustments.
Segregated and Analyzed on the Unit Sales Summary Report. Required entry if any for analysis report.
©2009 IntactAuto Holdings, LLC. All rights reserved, Spreadsheet Designs by IntactAuto™

---

13). Adjusted Gross Profit
Calculated Actual Used Vehicle Gross Profit generated after all miscellaneous adjusting entries.
14). Interest Expense
Wholesale Floorplan Interest Expenses have a tendency to be forgotten when calculating an aging vehicles Gross Profit,
but should always be a consideration when determining your True Return-on-Asset value. This Cells is made available
for your convenience, yet is not a mandatory entry, you determine how accurate of an Analysis Report you would like.
15). Unique Expenses
The Unique Expense column again is not a mandatory entry field, but many times an specific unit may incur unusual
expenses you may want to consider, such as Sales Commission Spiffs on Aged Units or the added expense of a special
Advertising Campaign. You decide how you would like to utilize these cells. Just remember consistency is important
when completing an analysis of your Used Vehicle Sales.
16). Vehicle Net Profit
Calculated Used Vehicle Net Profit generated by the sale of this unit. Remember additional expenses may occur, thus
reducing this Profit value further depending on how you utilize the Unique Expense cell.
17). Return On Asset
Your dealerships calculated Return-on-Asset value generated by the sale of this used unit.
Return-on-Asset; The Ratio(%) of return you generate in Pretax Profit for every dollar you invested in an Asset/Vehicle.
Workbook Notations
Worksheets are protected to prevent formula corruption and contain No Active Macros
Workbook Created:
March 1, 2005
Workbook Last Updated:
May 20, 2009
Workbook Current size:
910 KB
Last and most importantly, if you have any questions concerning these spreadsheet or how to use them;
E-mail or Call, IntactAuto Support: ExcelHelp@IntactAuto.com or visit: www.IntactAuto.com for contact information.
Excel® Workbooks designed specifically for the Retail Automotive Dealership & Automobile Industry
IntactAuto Holdings, LLC is an independent service provider and is not affiliated with, nor has it been authorized,
Sponsored, or otherwise approved by Microsoft Corporation.
©2009 IntactAuto Holdings, LLC. All rights reserved, Spreadsheet Designs by IntactAuto™

---

Used Vehicle Sales & Analysis Report (1)
IntactAuto.com
Created 05/10/09, ©All Rights Reserved IntactAuto Holdings, LLC
Sold Used Vehicle Information
Unit Age Calculator
Inventory Acquisition Cost
Vehicle Gross Profit & Miscellaneous Expenses
Sold Unit
Used Vehicle
Model
Make
Generalized Vehicle
Acquisition
Unit Sale
Age in
Inventory Asset
Unit Cost
Actual Unit
Unit Sale
Adjusted
Interest
Unique
Vehicle Net
Return On
Stock #
Source
Year
Code
Description
Date
Date
Days
Cost
Adjustments
Cost
Gross Profit
Gross Profit
Expense
Expenses
Profit
Asset
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%
0
$
- $
- $
-
$
- $
- $
- $
- $
-
0.0%

---

September 30, 2009
Used Vehicle Sales & Analysis Report
IntactAuto.com
Vehicle Age in Days Analysis
Age of Units Sold
Total Adjusted
Average Adjusted
Total Used Vehicle
Average Vehicle Net
# of Units Sold
Gross Profit
Gross Profit
Net Profit
Profit
1 - 30 Days
0
$
-
$
-
$
-
$
-
31 - 45 Days
0
$
-
$
-
$
-
$
-
46 - 60 Days
0
$
-
$
-
$
-
$
-
61 - 90 Days
0
$
-
$
-
$
-
$
-
91 - 120 Days
0
$
-
$
-
$
-
$
-
121 + Days
0
$
-
$
-
$
-
$
-
Acquisition Source Analysis
Department Sale
Purchased Units
New Vehicle Trade-In's
Other Sources
Totals
Purchase
Cycle
New
Cycle
Other
# of Units Sold
0
0
0
0
0
0
Average Age of Sold Unit
0
0
0
0
0
0
Total Dollar Values
Asset Value - Cost
$
-
$
-
$
-
$
-
$
-
$
-
Adjusted Gross Profit
$
-
$
-
$
-
$
-
$
-
$
-
Vehicle Net Profit
$
-
$
-
$
-
$
-
$
-
$
-
Averages Per Unit Sold
Asset Value - Cost
$ -
$ -
$ -
$ -
$ -
$
-
Adjusted Gross Profit
$ -
$ -
$ -
$ -
$ -
$
-
Vehicle Net Profit
$ -
$ -
$ -
$ -
$ -
$
-
ROA - Return On Asset
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Model Year Sales Analysis - Average per Year
Model Years Sold
Average Adjusted
Average Vehicle
% of Total Sales Net
# of Units Sold
Age In Days
Gross Profit
Net Profit
Profit
2010
0
0
$
-
$
-
0.0%
2009
0
0
$
-
$
-
0.0%
2008
0
0
$
-
$
-
0.0%
2007
0
0
$
-
$
-
0.0%
2006
0
0
$
-
$
-
0.0%
2005
0
0
$
-
$
-
0.0%
2004
0
0
$
-
$
-
0.0%
< =2003
0
0
$
-
$
-
0.0%
Vehicle Sales Make Analysis - Average per Make
Make Type Sold
Average Adjusted
Average Vehicle
% of Total Sales Net
# of Units Sold
Age In Days
Gross Profit
Net Profit
Profit
Other
0
0
$
-
$
-
0.0%
Pontiac
0
0
$
-
$
-
0.0%
Ford
0
0
$
-
$
-
0.0%
Ford Truck
0
0
$
-
$
-
0.0%
GMC
0
0
$
-
$
-
0.0%
Chevrolet
0
0
$
-
$
-
0.0%
Chevrolet Truck
0
0
$
-
$
-
0.0%
Honda
0
0
$
-
$
-
0.0%
Mercedes
0
0
$
-
$
-
0.0%
Oldsmobile
0
0
$
-
$
-
0.0%
Buick
0
0
$
-
$
-
0.0%
Cadillac
0
0
$
-
$
-
0.0%
Toyota
0
0
$
-
$
-
0.0%
Dodge
0
0
$
-
$
-
0.0%
Audi
0
0
$
-
$
-
0.0%
Created 05/10/09, © All Rights Reserved IntactAuto Holdings, LLC

---