By: Tom Edwards, DMS Sales Director
As a business manager, I am responsible for managing a number of activity and expense-related reports. Microsoft Excel has become my go-to application for this purpose. My software applications allow me to instantly download to Excel where I can really dig into the numbers. Our DMS solution, Dominion ACCESS®, allows users the same flexibility.
Recently, I was reviewing Dominion ACCESS reports with a dealer and shared a few tips with her that she claims have saved her an immense amount of time. These might also be helpful to you.
1. Reporting Ease: Subtotals
If you have a long spreadsheet, such as a salesperson’s commission report or a service advisor’s report, improve its functionality by using one of Excel’s hidden secrets: the Subtotal function.
Found near the far right of the DATA tab, the Subtotal function will filter the results and provide the subtotals you need. By clicking the Subtotals icon, Excel opens a dialog box where you apply what you want to see.
In the example below, I chose to view the total profit by sales person. Using the dialog box, I was able to select the column Salesperson 1 to identify which element to control. Then, moving down the dialog box, I selected the Sum function and the columns where I wanted to see the subtotaled results. Clicking OK lets me view it all in the same spreadsheet. Once I’ve completed that research, I can simply select the Subtotal function again and click on Remove All to return the entire spreadsheet to its previous state.
2. The Magic of Algebra: Using Negative Numbers
Remember sitting in class and wondering “When am I ever going to need to know how to do this?” Then you went to work for a car dealership where they have this unusual way of doing accounting using negative numbers. But sometimes, those negative numbers make reports confusing. For example, when your document says you have -$100,000 in the bank, it is difficult to know if that is a good thing or not.
To solve this dilemma, many dealerships create a Dealer’s Report where they aggregate data. Enter the Magic of Algebra. When importing data from a cell on another worksheet or report, you can add “*-1” to the cell formula so a positive result appears in that cell.
If =Sheet1!S18 results in a confusing negative number
Change it to =Sheet1!S18*-1 and it will give you the result you are looking for.
3. Looking for the Needle in the Haystack: Conditional Formatting
Wouldn’t it be great if outlier numbers in a report were easier to find?
One of the best ways to view a Trend Analysis or Deal Gross Report is in Excel but sometimes the sheer magnitude of the spreadsheet makes it difficult to find the incongruent numbers. That is where Excel’s Conditional Formatting will help.
Found in the Home ribbon near the middle, it will allow you to highlight numbers that are substantially above or below the norm.
In the example below, I am looking for numbers that are significantly different than the others. Select the column or section of a report that you want to review. Click the Conditional Formatting icon in the Home ribbon, select Color Scales. If you select Red, White, Green, your results will look like this.
These are just a few ways to utilize Excel to reduce your computational workload. Feel free to reach out to me at Thomas.Edwards@drivedominion.com with any questions. I’m happy to help you find better ways to use your software.
Interested in learning more about Dominion’s flexible DMS solution? Call 877.421.1040.