|
Super Excel
for Dealership
Management
Including
Advanced Case Studies, Sample Spreadsheets, and an Excel 2007
Tutorial along with
Downloading
Instructions for Arkona, ACS, ADAM, DPC, ADP websuite 2007,
web1000,
ARG(EDS- DMS@net,) R+R ERA and Power (UCS,) Auto/Mate and
Dubuque
Jack
Ross
&
Sandi Jerome
Table of Contents
About the
Authors 2
About Super Excel for Dealerships 6
Formulas 9
Useful Excel Functions 10
Divide, Multiply, Add, and Subtract functions 10
Relative versus Absolute 11
=VLOOKUP 14
=OFFSET and =SUM(OFFSET) 15
Formulas to work with text 17
Superformulas 19
Keyboard Shortcuts 21
Case Study #1 - Finishing workbooks prepared from DMS data 23
A little about Excel macros… 24
Case Study #2 - Building flexible workbooks 36
Example 1: Gross_Book.xls 36
Step 1: Foundation 38
Step 2: Flexibility 44
Example 2: Commission.xls 50
Case Study #3 - Using Excel and Outlook 59
The Excel side 60
The Outlook side 71
Learning Excel 97-2003 and Converting to 2007 77
The Excel 2007 “Ribbon” Difference 77
Super - Tip – Quick Access Tool Bar 78
Super New AutoFilter Exercise 79
Super Tip – Getting Leading Zeros 80
Using the Text (Custom) Filter 82
The Over 90 Days in Stock List 83
Creating Subtotals 85
Summary Section of the Over 90 Days in Stock List 85
Top Ten Oldest Vehicles 87
Creating a Pivot Table – Model Color Report 88
Super Tip – Keyboard Shortcuts 88
Pivot table for 97-2003 Users 99
Extracting Data 102
DMS File Name Basics 102
The Extraction Process 103
PDF to Excel Conversion 103
Arkona 104
Arkona Print to File Option 104
Arkona Print to PDF Option 104
Arkona File Download 105
Scheduled Downloads with Arkona 107
Edit the Status of a Scheduled Job 123
ACS – Automotive Computer Services 127
Auto/Mate Systems 127
ADAM Systems 127
AutoSoft Extractions 128
ADP Alliance/web 1000 Report Writer 129
Downloading with ADP’s webSuite 2007 131
ADP’s Enhanced Report Generator 131
Downloading ADP’s Trial Balance 132
Dubuque Systems 132
Downloading the Dubuque GL to Excel 132
ARG – (former EDS) 132
To Create a New EA Report 132
Downloading the GL with EA 136
DPC Downloads 136
R+R ERALink 137
R+R FileNames 137
Downloading R+R System Reports – Trial Balance Example
138
Advanced Reporting with R+R 138
Power R+R (former UCS) 139
Enterprise Report Management and User Programming 141
Downloading GL with UCS POWER 146
Welcome to
Super Excel for Dealership Management. This is one of
those really uncanny coincidences the came out just right. I
had started a book on using Excel in the dealership, and found
out that Sandi was already in the process of writing one
herself. Sandi and I both have different strengths when it
comes to using Excel (case in point: pivot tables. I bow to
Sandi’s mastery of them, and when I grow up, I hope to be as
good as she is with them!) So we sought to combine our “super”
powers and create a “Super Excel” book that really would be a
comprehensive work on using Excel in the dealership environment.
Actually,
the working title for my book, before I learned about Sandi’s
book, was Excel in the Dealership. I was trying to be
clever and witty when coming up with the title, but it would
probably have confused some people into thinking we were going
to teach you how to sell more cars in the dealership, or make
more money in the dealership, or be more popular in the
dealership.
On the
other hand, those things may very well happen as a benefit of
learning how to better use Excel. If your people have more
informative and timelier reports, perhaps they will sell
more cars. If you become so proficient at Excel that you become
the go-to person for solutions, hopefully you will make
more money and become more popular! Hey, it could happen.
Okay, down
to brass tacks. This book is written to provide you, the user
of Excel, with practical help in using this application in your
company. While the principles that follow can apply to any
general-use purpose, the examples and case studies are geared
toward common dealership procedures.
I’ve found
that people who use Excel often fall into one of four broad
categories:
1) ![MCj04244720000[1]](super_excel_contents_files/image002.gif) The
uncomfortable user. This may or may not be a newbie in the
sense that they’re new to Excel, but they generally are
uncomfortable with using a spreadsheet, or they may be somewhat
comfortable but limit themselves to the most basic features and
tools available in Excel. As Scott Adams of Dilbert fame
once characterized a certain type of technophobe: “These are the
users who secretly add up their Excel lists with a calculator.”
This is not to belittle or make light of someone who is
unfamiliar or uncomfortable with using a spreadsheet; it’s
merely a humorous observation about the way some people approach
it. They open up a spreadsheet and see this vast screen of
little cells with rows and columns, menu options, toolbars,
status bars, cryptic icons and more, and it can all be
overwhelming. They may know to do what someone showed them how
to do (whether it’s right or wrong), or they may try to
accomplish something and quickly become frustrated. Then they
grab their hand calculators and add the numbers up themselves.
And why not? Without sufficient exposure and practice, it’s
easier to use a calculator. It’s familiar, it’s quick, and it’s
easy.
2)  The
comfortable user. This is a person who has either used a
spreadsheet for some time, or who may intuitively take to it
right away. This person generally knows his way around the
spreadsheet, knows what many or most of the menu items and
toolbars are for, and can move quickly and comfortably through
many spreadsheet tasks. They may not know what many of the
advanced tools built into Excel can do, and they may not be
familiar with VBA, the programming language of Excel.
Nevertheless, they can build and use basic spreadsheets that can
get the job done, efficiently or otherwise.
3) ![MCj04377910000[1]](super_excel_contents_files/image005.gif) The
power user. You may or may not have one of these in your
dealership, but if you do, they’re generally the person that
everybody comes to with questions about using Excel or building
solutions with spreadsheets. They are quite comfortable with
this application and can often build spreadsheets that are
designed for end-users. These may include features such as data
validation, conditional formatting, protected sheets or ranges,
charts, pivot tables, linking formulas, and so on. When it
comes to VBA, they may run the gamut from complete novice to
fairly sophisticated, but generally are somewhere in the
middle. Their spreadsheets therefore may often include some
programming that makes the spreadsheet easier to use or more
powerful.
4) ![MCj04244900000[1]](super_excel_contents_files/image007.gif) The
developer (sometimes known affectionately or otherwise as “the
geek”). It’s unlikely that you’ll have one of these in the
dealership, but it’s possible. These are the ones who know how
to wring much of the true power out of Excel. Their
spreadsheets and VBA programs are often quite professional, and
may involve a level of automation that can astound lesser
mortals. This type often works freelance or for a consulting or
programming company, and can create custom-made spreadsheets
based on a client’s needs.
So…which
one are you? People usually fall somewhere in the range of
these categories rather than squarely in the middle of one.
Perhaps you have used Excel for some time and know how to handle
basic tasks such as entering simple formulas, basic formatting
and so on, but would like to learn more without having to trudge
through one of those 600-page 10-pound books you see in the
bookstore. Perhaps you realize your job or someone else’s in
the dealership could be made more efficient or easier if only
there was a spreadsheet that could do…this. Or do…that.
Perhaps you would like to take your spreadsheets just a little
further, such as creating a set of spreadsheets that link to a
master workbook so that others could enter their information and
have it flow to you automatically. Perhaps a department manager
or the GM would like something with charts or more concise
summary information. This book is designed to help you get
started in that direction.
We’ll be
examining three case studies in this section of the book. The
first is the Car Inventory Summary. This workbook will draw
information from a downloaded report from the DMS, then
automatically format the report and add formulas for additional
information not provided in the DMS download.
The second
is the Gross Book Analysis. This workbook will use information
from a gross book report file and allow the end-user to view and
analyze the performance of the salespeople. We’ll also begin
building a salesperson commission workbook which will allow us
to enter our commission information in an efficient way by
building a workbook with a good foundation and flexibility.
The third
is the Excel and Outlook combination. This is a two-part
application, where Excel creates workbooks on its own based on a
master workbook. We’ll be continuing with our commission
workbook and adding some code to it. A simple Outlook macro
then takes those newly created spreadsheets and emails them to
the designated recipients.
Sandi’s
section will teach you Excel 2007 with a simple tutorial that
includes a Pivot Table and the AutoFilter. She has the
instructions for both 97-2003 and the new Excel 2007. This
enables those of you who are still using the older Excel to have
a tutorial, but those of you who are struggling with Excel 2007
– to have “quick start” method to convert. Since most of you
need to get information from your DMS system, she has a section
on Data Extraction with information on getting data out of ADP’s
new Web Suite 2007, websuite 1000 (Alliance,) ARG (former EDS,)
Arkona, ACS, Auto/Mate, DPC, Dubuque, R+R Eralink, and R+R Power
(UCS). Along the way,
we’ll be covering some topics that will hopefully enlighten you
as to some of the capabilities of Excel. We’ll touch on the
idea of superformulas, handy formulas such as OFFSET and
multiple-condition AND/OR statements, how to use arrays in
formulas, quick little tips on Excel shortcuts, and more.
Note that
while I am still in the Excel 2003 world, Sandi covers material
from both 2003 and the latest version, 2007. I think this is
good from the reader’s standpoint, because I’m sure that a lot
of dealerships are still using Excel 2003 or even Excel 2000,
but sooner or later we’re all going to have to make the leap to
Excel 2007 (or even later, if we wait too long), and Sandi’s
explanations of the benefits of 2007 will ease the fear and
hesitation of upgrading.
-----------------------------------------------------------------------------------------------------
Super
Excel Price (please check
one) ___ $295
(includes an e-book and spreadsheets on a CD mailed to you)
___ $395
Printed book with CD
Dealership Name: ___________________________________________
Address:___________________________________________________
City:_____________________ State:________ Zip____________
Phone:_______________________ Fax:______________
Email:______________________ Contact
Name:_______________________
Credit
Card#_____________________________________________
Exp. Date__/___
Signature___________________________________________
Credit
Card orders fax to 503-715-5600
Mail
checks to: Sandi Jerome Computer Consulting
1391 NW
St. Lucie West Blvd #362
Port St.
Lucie, FL 34986
Questions? Email
sandi@sandijerome.com |
