| About the Author |
2 |
| Certificate of Completion |
2 |
| About Power ERALink |
7 |
| SJCC’s Top ERALink Tips |
7 |
| ERALink Advantages |
7 |
| ERALink is More than Terminal Software |
8 |
| Getting Started |
8 |
| Loading the Power ERALink Queries |
9 |
| Creating your DATA folder |
9 |
| Running your First Power Query |
10 |
| Opening the PWNew.csv File |
14 |
| Using the AutoFilter |
14 |
| The Model and Color Report |
14 |
| Using the Custom AutoFilter |
15 |
| The Over 90 Days in Stock List |
15 |
| Creating Subtotals |
16 |
| Summary Section of the Over 90 Days in Stock List |
16 |
| Top Ten Oldest Vehicles |
17 |
| Creating a Pivot Table |
19 |
| The Model-Color Report |
19 |
| Selecting Models and Adding Days in Stock to the Model Color Report |
26 |
| Creating Ten Sold Vehicle Reports from One Pull |
28 |
| Running the PWfi Query |
28 |
| Opening the PWfi.csv File |
28 |
| Deals Not Posted or Pending |
29 |
| Gross profit average for New Trade-ins vs. Purchased Vehicles |
30 |
| Deals with a Gross Profit Less Than XX |
31 |
| Salesperson Gross Profit Analysis |
31 |
| Sales Manager Comparison |
34 |
| Bank Gross Profit |
35 |
| F&I Manager Analysis |
36 |
| Bank Reserve Analysis |
38 |
| Best Sales Gross by Model Using the Page |
39 |
| Sales with No Extended Warranty Sold (with a Mail Merge to Word) |
41 |
| Automated Bank Reconciliation |
45 |
| Using Vlookup |
45 |
| Bank Reconciliation – Quick Start |
46 |
| Opening your Cash in Bank Report in Excel |
46 |
| Bringing in your Prior Month’s Detail |
47 |
| Bringing in the FromBank1104 Worksheet |
48 |
| Naming a Range for the Bank Detail |
48 |
| Removing Duplicate Reference Numbers |
49 |
| Entering the Formulas |
50 |
| Putting it All Together – Summary Bank Reconciliation |
51 |
| Creating your Balance Forward for Next Month |
52 |
| Downloading The R+R Trial Balance 0370 with ERALink |
53 |
| How to Download Schedules from R+R – Floor Plan Example |
55 |
| The ERA System - System Basics |
58 |
| Interfacing the Reynolds + Reynolds System to a PC |
59 |
| Using ERALink |
59 |
| Setting up ERALink |
59 |
| Setting up How You Will Connect |
60 |
| Entering the Phone Number to Dial (for Remote Access) |
60 |
| Logging On to the System |
61 |
| Other Setups for EraLink |
61 |
| Getting What you Want from ERALink |
63 |
| Report Generator – Run Query |
63 |
| Finding the File Name You Need |
65 |
| Entering ITEMS – Selection Criteria |
65 |
| Adding a User Prompt |
66 |
| Entering the Sort Order |
67 |
| Selecting your Output Field Names |
67 |
| Headers and Footers |
67 |
| Downloading Your Query to Excel |
67 |
| Using I-Descriptors to Avoid Wrapping |
68 |
| Easy Introduction to Report Basics |
70 |
| Report Generator Reports |
72 |
| System Reports – Client Version of ERALink |
73 |
| How to Get a List of All Field Names |
73 |
| Troubleshooting Running Queries |
73 |
| Reports you can Create from the Power Queries |
75 |
| Accounts Receivable |
75 |
| Using a Pivot Table to Create an AR Schedule |
75 |
| Accounts Receivable over 60, 90, 120 Days old - Over 120 |
77 |
| AR Credit Balances |
77 |
| AR with no names – NAME (Blanks) |
77 |
| AR with credits from Journal Vouchers |
77 |
| AR debits that came checks or Journal Vouchers |
77 |
| Cash Receipts for 8300 Review |
77 |
| Customer File |
78 |
| Customers with a Blank Address (Blanks) |
78 |
| Last Service Date is Blank (RO DATE) |
78 |
| Customers who Purchase Elsewhere – STOCK# is Blank |
78 |
| Customers by Zip Code - Sort |
78 |
| Customers who have Deflected (Last Service Date < Last Sold Date)
|
78 |
| Car Deals |
78 |
| Deals Not Posted or Pending |
78 |
| Gross Profit Average for New Trade-Ins vs Purchased Vehicles |
78 |
| Deals with a gross profit less than XX |
78 |
| Salesperson Gross Profit Analysis |
78 |
| Sales Manager Comparison |
78 |
| Bank Gross Profit Analysis |
78 |
| F&I Manager Analysis |
78 |
| Bank Reserve Analysis |
78 |
| Best Sales Gross by Model |
78 |
| Sales with No Extended Warranty |
78 |
| Using Vlookup to Compare Sales Gross with Accounting Gross |
79 |
| Conditional Formatting |
80 |
| GL - Trended |
83 |
| Average of Prior Months |
83 |
| Asset Accounts with Credit Balances |
83 |
| Liability Accounts with Debit Balances |
83 |
| Current Month Compared to Prior Month’s Average |
83 |
| Expense Accounts with Credit Balances |
83 |
| Balances Less than $1.00 |
83 |
| Current Month Compared to Prior Month’s Average with Conditional
Formatting |
83 |
| Journal Vouchers – Checks |
83 |
| How to Fix the Missing Reference Number and Dates |
83 |
| Journal Vouchers to Cost of Sale/Sale Accounts – ACCT |
84 |
| Journal Vouchers to Parts Inventory - ACCT |
84 |
| Journal Vouchers with Amount Greater than $5,000 – Amount |
84 |
| Journal Vouchers to Employees – Search for Names |
84 |
| Journal Vouchers to Other Deductions/Income - ACCT |
84 |
| Journal Vouchers with No Description – Description is Blank |
84 |
| New Vehicles |
84 |
| New Inventory by Model and Color |
84 |
| New Vehicles Over 90 Days Old |
84 |
| Top 10 Oldest New Vehicles |
84 |
| Model, Color and Days in Stock |
84 |
| Cost greater than $25,000 and Days in Stock over 90 |
84 |
| Parts Adjustments |
84 |
| Adjustments with a EXT-Cost Greater than $100 |
84 |
| Adjustments with a Qty Greater than -1 |
84 |
| Adjustments with an QOH Greater than 10 |
84 |
| Adjustments to NPN (Search in PART#) |
84 |
| Adjustments for a Particular Counterman |
84 |
| Parts with a High Value |
84 |
| Parts with a High Value and QOH less than 2 |
85 |
| Parts with a High Value and QOH greater than 100 |
85 |
| Top 10 Highest Value Parts |
85 |
| Parts with a High Value and No Sales in 12 months |
85 |
| Parts with No Sales |
85 |
| Parts with No Sales and No BIN Location (BIN1 – Blank) |
85 |
| Parts with No Sales and a status of AP (Phase-in) |
85 |
| Parts with No Sales and a status of NS (Non-Stock) |
85 |
| Parts with No Sales and a EXT-VAL Greater than $500 |
85 |
| Parts with No Sales and a QOH Greater than 1 |
85 |
| Repair Order Analysis |
85 |
| One Line Repair Orders |
85 |
| Labor Sales and Average by Advisor (Pivot Table) |
85 |
| Labor Sales and Average by Tech (Pivot Table) |
85 |
| Repair orders open more than 2 Days |
85 |
| Repair orders with no Parts - PARTSS = 0 |
85 |
| Schedule Detail – Using a Wildcard |
86 |
| Contracts in Transit |
86 |
| Vehicle Receivables |
86 |
| New Cars, Used Cars, Floor Plan |
86 |
| PDI, Warranty Claims |
86 |
| Using a Pivot Table to Create a Schedule – Floor Plan Reconciliation |
86 |
| Used Vehicles |
87 |
| Used Inventory by Model and Color |
87 |
| Used Vehicles over 90 Days Old |
87 |
| Top 10 Oldest New Vehicles |
87 |
| Model, Color and Days in Stock |
87 |
| Cost greater than $25,000 and Days in Stock over 90 |
87 |
| Open Repair Orders |
87 |
| Repair Orders with an Open Date Greater than XX |
87 |
| Top 10 Repair Orders by Dollar Amount |
87 |
| Open Repair Orders for Employees |
87 |
| Downloading your DOC |
87 |
