| About the Author |
2 |
| Certificate of Completion |
2 |
| Getting Started |
7 |
| Loading the Settings File |
7 |
| Creating your DATA folder |
8 |
| Running your First Power Macro |
8 |
| Opening the PWNew.csv File |
10 |
| Using the AutoFilter |
11 |
| The Model and Color Report |
11 |
| Using the Custom AutoFilter |
12 |
| The Over 90 Days in Stock List |
12 |
| Creating Subtotals |
13 |
| Summary Section of the Over 90 Days in Stock List |
13 |
| Top Ten Oldest Vehicles |
14 |
| Creating a Pivot Table |
15 |
| The Model-Color Report |
15 |
| Selecting Models and Adding Days in Stock to the Model Color Report |
21 |
| Creating Ten Sold Vehicle Reports from one Pull |
23 |
| Running the PWfi Macro |
23 |
| Opening the PWfi.csv File |
25 |
| Deals not posted or pending |
26 |
| Gross profit average for New Trade-ins vs. Purchased Vehicles |
27 |
| Deals with a gross profit less than XX |
27 |
| Salesperson Gross Profit Analysis |
28 |
| Sales manager comparison |
29 |
| Bank gross profit |
30 |
| F&I manager analysis |
31 |
| Bank Reserve Analysis |
32 |
| Best Sales Gross by Model using the Page |
33 |
| Sales with No Extended Warranty Sold (with a Mail Merge to Word) |
34 |
| Automated Bank Reconciliation |
37 |
| Using Vlookup |
37 |
| Step-by-Step Bank Reconciliation |
38 |
| How to Download your Cash in Bank Report from ADP |
38 |
| Printing Reports from the HoldQ |
39 |
| Opening your Cash in Bank Report in Excel |
42 |
| Sorting the Cash in Bank by Source |
45 |
| Balancing your Detail to the Summary Amount |
46 |
| Bringing in your Prior Month’s Detail |
47 |
| Bringing in the FromBank1104 Worksheet |
48 |
| Naming a Range for the Bank Detail |
49 |
| Removing Duplicate Reference Numbers |
51 |
| Entering the formulas |
52 |
| Putting it all together – Summary Bank Reconciliation |
53 |
| Creating your Balance Forward for Next Month |
54 |
| How to Download Schedules from ADP – Floorplan Example |
55 |
| Printing Reports from the HoldQ |
55 |
| Setting up your Unattended ADP pulls using Reflection 10.0 |
58 |
| Downloading your MIS Report |
60 |
| Instructions for Downloading the AC Balance Sheet and Income Statement-(Trial
Balance) |
62 |
| Quick Steps |
62 |
| Detail steps |
62 |
| Using Reflection for ADP – A Reference Guide |
66 |
| Starting ADP Reflection |
66 |
| Copy, save, and print display memory |
67 |
| Downloading Basics from Reflections to Excel |
67 |
| Option 1 – How to Copy to Excel |
68 |
| Option 2 – Logging On – Downloading Reports |
69 |
| How to Get a Wider Report with ADP |
71 |
| Printing to a Slave Printer – Printing with AC900 |
71 |
| How to Use the HOLDQ for Printing |
72 |
| How to Fix a Negative Sign on the Wrong Side |
73 |
| Advanced Reflection Features – Function RPX Data Transfer |
73 |
| Advanced Reflection Features – Function RXR Data Transfer |
73 |
| Advanced Features – Building Macros |
74 |
| Other Fun Things to Do with Reflection and ADP |
74 |
| How to Find the Fieldnames – ADP |
74 |
| Reports you can Create from the Power Macros |
75 |
| Accounts Receivable |
75 |
| Accounts Receivable over 60, 90, 120 Days old - OVER-120 (Non-Blanks) |
75 |
| AR Credit Balances - TOTAL-BAL - Custom |
75 |
| AR with no Credit Limit – CREDIT, NONE |
75 |
| AR over Credit Limit |
75 |
| AR with no payments made – PAYMENT (Blanks) |
75 |
| AR with no names – NAME (Blanks) |
75 |
| Cash Receipts for 8300 Review |
75 |
| Customer File |
76 |
| Customers with a Blank Address (Blanks) |
76 |
| Payoff Date is less than 2 Years Ago- Custom |
76 |
| Customers who Purchase Elsewhere – STOCK-NO is Blank |
76 |
| Customers by Zip Code - Sort |
76 |
| Customers who have Deflected (Last Service Date is not greater than
last sold date) |
76 |
| Used Customers with Same Make – STOCK TYPE = Used |
76 |
| Car Deals |
76 |
| Deals not posted or pending |
76 |
| Gross Profit Average for New Trade-ins vs Purchased Vehicles |
76 |
| Deals with a gross profit less than XX |
76 |
| Salesperson Gross Profit Analysis |
76 |
| Sales Manager Comparison |
76 |
| Bank Gross Profit Analysis |
76 |
| F&I Manager Analysis |
76 |
| Bank Reserve Analysis |
76 |
| Best Sales Gross by Model |
76 |
| Sales with No Extended Warranty |
76 |
| GL - Trended |
76 |
| Average of Prior Months |
77 |
| Asset Accounts with Credit Balances |
77 |
| Liability Accounts with Debit Balances |
77 |
| Current Month Compared to Prior Month’s Average |
77 |
| Expense accounts with Credit Balances |
77 |
| Balances less than $1.00 |
77 |
| Current Month Compared to Prior Month’s Average with Conditional
Formatting |
77 |
| Journal Vouchers – Checks |
77 |
| Journal Vouchers to Cost of Sale/Sale Accounts - ACCT |
77 |
| Journal Vouchers to Parts Inventory - ACCT |
77 |
| Journal Vouchers with Amount Greater than $5,000 – POST.AMT.EXT |
77 |
| Journal Vouchers to Employees – NAME |
77 |
| Journal Vouchers to Other Deductions/Income - ACCT |
77 |
| Journal Vouchers with No Description - POST.DESC.LEN25 |
77 |
| New Vehicles |
77 |
| New Inventory by Model and Color |
78 |
| New Vehicles over 90 Days Old |
78 |
| Top 10 Oldest New Vehicles |
78 |
| Model, Color and Days in Stock |
78 |
| Cost greater than $25,000 and Days in Stock over 90 |
78 |
| Parts Adjustments |
78 |
| Adjustments with a value greater than $100 |
78 |
| Adjustments with a MINUS more than 1 |
78 |
| Adjustments with an OH greater than 10 |
78 |
| Adjustments to NPN (Search) |
78 |
| Adjustments for a particular SO – Source |
78 |
| Parts with a High Value |
78 |
| Parts with a High Value and QTY less than 2 |
78 |
| Parts with a High Value and QTY greater than 100 |
78 |
| Top 10 Highest Value Parts |
78 |
| Parts with a High Value and No Sales in 6 months |
78 |
| Parts with No Sales |
78 |
| Parts with No Sales and No BIN Location (BIN – Blank) |
79 |
| Parts with No Sales and a status of AP (Phase-in) |
79 |
| Parts with No Sales and a status of NS (Non-Stock) |
79 |
| Parts with No Sales and a Value greater than $500 |
79 |
| Parts with No Sales and a Quantity (O.H.) greater than 1 |
79 |
| Repair Order Analysis |
79 |
| One Line Repair Orders |
79 |
| Labor Sales and Average by Advisor (Pivot Table) |
79 |
| Labor Sales and Average by Model (Pivot Table) |
79 |
| Labor Sales and Average by Tech (Pivot Table) |
79 |
| Repair Orders Open More than 2 Days |
79 |
| Repair Orders with No Parts - PARTS$ = 0 |
79 |
| Parsing Multi-Value Fields |
79 |
| Schedule Detail |
80 |
| Contracts in Transit |
80 |
| Vehicle Receivables |
80 |
| New Cars, Used Cars, Floor Plan |
80 |
| PDI, Warranty Claims |
80 |
| Using a Pivot Table to Create a Schedule – Floor plan Reconciliation |
80 |
| Used Vehicles |
81 |
| Used Inventory by Model and Color |
81 |
| Used Vehicles over 90 Days Old |
81 |
| Top 10 Oldest New Vehicles |
81 |
| Model, Color and Days in Stock |
81 |
| Cost greater than $25,000 and Days in Stock over 90 |
81 |
| Open Repair Orders |
81 |
| Repair Orders with an Open Date Greater than XX |
81 |
| Top 10 Repair Orders by Dollar Amount |
81 |
| Open Repair Orders with More than 3 Parts |
81 |
| Open Repair Orders with No Parts |
81 |
| Open Repair Orders for Employees |
81 |
