Automating Accounting Process

With increasing need to expedite the delivery of the financial package to upper management accounting managers are becoming increasingly challenged. Delivering accurate accruals in a timely manner can be very challenging. From rebates and commissions to employee benefits and inter-company consolidations automation is the best alternative rather than manpower.

To automate a process look at the rules involved in determining the calculation. Then look at the sources of the data. The number of systems used to provide information. Then look at extracting the information into one data source. I still find that Excel is an excellent way to automate tasks. Too often managers look to have an expensive third party to provide information related to automating a process. Ask yourself what steps they might use to make a calculation. For instance populating daily foreign exchange rates into your system. Doesn't yahoo.com offer this information for free. Isn't using your IT department to setup a quick import from this data source an excellent solution. It's free and shouldn't take internal staff more than two hours.

Something I have automated and incorporated into close are commissions. The accounting department needed the commission calculation for the commission accrual and the sales managers needed the information to pay third party sales agents. In our company the commission calculation is a part of the accounting departments responsibilities (I know you probably thinking the sales department should have this responsibility I'll answer controls). Unfortunately our ERP system in not able to support our commissions process due to a complex system of rules. I incorporated a data download and a pivot table to feed the information automatically into a worksheet containing the rules. Sometimes from multiple pivot tables. The way this is done is buy using "getpivottable" formulas with "if(iserror" functions. The "if(ieserror" is important because if a company or product class did not receive sales then an error is returned by the formula, and I would prefer a zero. (Contact me for an example.) Well it is important to test the automation and adapt it in case information being imported is changed. To do this compare the totals provided by the pivot table and incorporate the report into the task which might be a conglomeration of used and unused information. I setup my cack report to provide a balanced number. Doing this will make it easy to pin point out of place, missing and conflicting information. Finally, I track information using a running table and another pivot table providing month over month and year over year information. This is useful when manaagers are reviewing the information and is really an added bonus. I do this by copying the formulas renaming a month range then copying and pasting the previous month information as values. This used to be a two to three day process and it took four days to automate. Now the download takes 1 minute, refreshing the pivot tables takes 30 seconds, reviewing the check sheets and PivotTables takes 2 minutes and ironing out the rare error takes 3 minutes. The commission process once took from 16 - 24 hours and was highly susceptible to errors and quarterly exceptions added more time every three months and took the manpower of three people. Now the quarterly exceptions are incorporated into the monthly reports, there are far less errors and the whole thing takes less than five minutes.

Another extremely cumbersome task was the inter-company eliminations. I developed a process that only excludes the inter-company profit elimination on inventory. Our company had multiple level of eliminations. The process to eliminate all accounts took three people three days to complete and incorporating new accounts and companies often took months to complete. The eliminations were not accurate and were the most time consuming part of close. Due to restrictions with the ERP sysetm and the fact we had eight reporting levels I looked to define accounts with tags and account numbers that could be picked up using internal reports that eventually automated entries. This project almost got me fired but I completed it just before a hard cut throat deadline. Once, the process was completed it took the inter-company profit from 144 man hours to five minutes. It literally takes ten minutes to complete and thirty minutes to check. The point is that I looked to the systems internal reports and methods by which I could distinguish the accounts from each other to define the accounts and direct the reports to the appropriate selections at the appropriate levels. All together the automation took me approximately 200 hours to complete plus 100 hours of overtime I didn't get compensated for but helped me keep my job.