Axiom Updates

Latest Industry News

That’s How We’ve Always Done It – Embracing Microsoft Excel to Promote Accounting Department Efficiency and Productivity

  • Articles
  • Comments Off on That’s How We’ve Always Done It – Embracing Microsoft Excel to Promote Accounting Department Efficiency and Productivity

By Lucian Bacallao, CPA

An easy way to identify when a change may be needed is to ask why things are done a certain way. If the response given is, “that’s how we’ve always done it,” then there may be a need for change. Throughout my time working with auto retailers, I have noticed one of the biggest opportunities for improvement in business offices is addressing the underutilization of Microsoft Excel (“Excel”) for accounting transactional postings, and operational and financial analyses. Excel is the predominant spreadsheet and financial analysis tool used in almost every accounting office, but it’s also the most underutilized accounting tool available to dealership employees.

Excel is often used as a sales delivery log, a parking spot for the accounting department’s summary schedules, a place to paste static expense analyses, but it’s seldom used for its unbounded analytical capabilities. Because of a general unfamiliarity with the program, most users are intimidated and hesitant to learn more about Excel, and use the program to its full potential. Whether it’s the summarization of vendor data, charting or graphing sales and gross profit data, or utilizing advanced functions, the fact remains that many dealerships are missing out on an opportunity to increase efficiency and boost productivity.

It appears there is greater comfort and openness by younger generations to adopt Excel than amongst the more seasoned employees who comprise many accounting offices today. However, a CFO or Controller’s inexperience with Excel should not hinder a business office’s ability to streamline processes and generate efficiencies. A potential solution to this issue may be to seek out one or two “Excel Champions” or “Power Users.” Someone who is either already proficient in the program or willing to learn the essential skills can become an in-house designer of complex Excel templates that simplify cumbersome or repetitive tasks. This person could be a billing clerk who has already gained a certain level of comfort with the program, or a parts stockperson who is going to school part-time and has considered a long-term career in dealership accounting. With proper training and the ability to apply what they’ve learned, these users could prove to be beneficial in helping the business office streamline several time consuming processes.

I have listed below some examples of potential efficiencies that may be realized by maximizing Excel utilization, but the list is not inclusive of all the opportunities that may exist at a particular store.

Beginning to Intermediate Excel Users

Ratios, Percentages, Commissions

A simple application that is often underutilized is Excel’s ability to automatically summarize and calculate information based on data that is keyed into a worksheet (often referred to as a “tab”). A beginning to intermediate user could provide a Dealer, GM, or CFO relevant trends and ratios on a monthly basis with minimal effort via Excel-generated reports. For example, by exporting the general ledger month-end balances to Excel, or entering relevant financial statement data or delivery log data, pertinent ratios and trends can be calculated and summarized on one to two pages. Whether that includes the new unit to used unit sales ratio, average days supply of inventory, fixed operations absorption, compensation per employee, or advertising per vehicle, almost any useful ratio can be automatically updated monthly and compared to any period for which data has been gathered.

Another use of Excel that is easy to implement is the creation of commission or bonus templates for employees. Because employee pay plans can become complex calculations when incorporating CSI scores, volume bonuses, and minimum gross profit thresholds, the use of an automated Excel template can help increase accuracy and decrease the amount of time spent by a payroll associate in calculating these monthly figures. For example, instead of having to repeatedly calculate percentages of gross or sales budget achievement levels, certain key numbers can be entered into one “helper worksheet” which is linked to formulas on multiple worksheets that automatically calculate salesperson, parts countermen, service advisor, or manager bonuses.

Intermediate to Advanced Excel Users

Reconciliations

Certain reconciliations performed on a monthly basis can take several hours to complete depending on the size of a store. This time may be extended longer for dealership groups with a centralized accounting office and multiple franchises. Inventory reconciliations, floor plan reconciliations, parts statement reconciliations, credit card reconciliations, and others could all be streamlined with intermediate Excel implementations.

An example may be an employee spending excessive time reconciling the vehicle inventory through a manual process of lining up two pages side-by-side, one with the physical count and one with the inventory schedule, and highlighting stock numbers from each list as they are identified. Instead, these vehicle stock numbers, which may be several hundred at some stores, could each be populated into their own column in Excel and a beginner or intermediate user could use the “Sort” function on both lists. The lists would automatically be organized in either alphabetical or numerical order for ease of comparison.

An intermediate to advanced user could identify all matching vehicles and exceptions in less than one minute by using a “Vlookup” formula. This function essentially does the hard work and will identify any stock number in the “vehicle count” column that matches a stock number in the “accounting inventory” column, and vice versa. The Vlookup function searches for information in one table (i.e. the vehicle count list) that matches a certain criteria (i.e. a stock number) in another table (i.e. accounting inventory) to indicate if there is a match.

See what a sample inventory reconciliation may look like in Figures 1 – 4 below. Columns C and F show stock numbers that match in the opposing lists, and show “#N/A” for stock numbers that do not have a match in the opposing list. This process can be streamlined even further by combining the Vlookup with other advanced functions.

If scan guns are used, and they don’t provide an automatic reconciliation feature, the inputting of each column can occur in just a few minutes by uploading the scan results and downloading the accounting schedule into Excel.

The same principals can be applied to the floor plan reconciliation when the statement can be obtained in Excel from your lender (this is often a feature on many lender websites). The exceptions can be organized in Excel and the research of each item can be done and documented on the same worksheet. The time savings in the reconciliation process could be dramatic at a medium to large store or auto group with large vehicle inventories.

Embracing Excel and all its capabilities is easier now than ever before, with many online Excel courses, books, forums, and even the built in “Help” function readily available to users. The examples listed above are just the tip of the iceberg on what could amount to hours of time savings every week. Although these suggested solutions are easier said than done, the investment upfront – to get employees sufficiently trained and allowing them to develop templates to streamline and automate repetitive tasks – will more than pay for itself with an increase in productivity and a reduction in manual errors. With proper training, the next time you ask someone in accounting “Why is it done that way?” the answer may come as a pleasant surprise.

Lucian Bacallao, CPA is a Dealership Consulting Manager at Axiom Advisors, a boutique automotive dealership consulting firm specializing in Mergers & Acquisitions, Enterprise Management and Litigation Support. He can be reached at lb@axiom-auto.com or 786-472-2801.

Back to top