Mike M Eghtebas
Database Development, Web Programming
How automations during my employment at AIG reduced annual overtime costs in a major way: At this position, I was able to maintain a friendly and cooperative working relationship with the tax and IT team to create a win-win situation. For the most part, the credit goes to the very professional CPAs at AIG I was assigned to work with. They were very cooperative and made it easy for me to keep my concentration on the automation related challenges as I was discussing the automation methods with them.
My automation tasks during employment at AIG SunAmerica reduced annual overtime costs drastically. Every year, from December through April, it was crunch time at AIG SunAmerica Tax Department to prepare and file the required tax papers for 1,800 partnerships by April 15 deadline to avoid IRS hefty penalties.
During the interview in December 2006, I was told AIG SunAmerica is looking for someone who thinks out of the box and is able to create solutions to reduce and, or better yet, to eliminate overtime expenses of the CPAs during annual tax season.
The interview was handled by two vice presidents and one senior IT manager which lasted for three hours during which time I demoed some past sample projects. I agreed to start contract to hire bases. That evening before I arrived at my home in Orange County, I got a call from Lisa Fulton (of Pro Staff IT) that I was hired. Per discussion I have had earlier with Mrs. Fulton, I had agreed with $45 per hour starting pay rate , but a few days later during signing my contract at ProStaff IT in Culver City, CA office, I noticed the pay rate has been increased by $2.5 per hour to $47.50. This was a nice surprise to which I had no objections. The second surprise came about, when I received my first paycheck with yet another $2.5 per hour increase at $50.00 per hour. Now, including two weeks of vacation time ProStaff added to my benefits, my starting annual salary was about $110K.
The automations developed were either proprietary, for AIG SunAmerica use only, or generic automations solutions I had developed in the past and refined it over the years for use in a typical office utilizing Micosoft Office Suites.
As I began my work at AIG SunAmerica, with some minor adaptations, I made these generic automation tools available to the staff in the AIG SunAmerica Tax Department. Because these tools were very user friendly and easy to use, they became very popular as they were put in use. These tools reduced the number of clicks 20 to 100 folds for typical file operations, printing, etc. thus saving lots of time and effort.
Meanwhile, the management distributed a weekly progress reports recognizing the high achievers in order to promote some competitive spirit amongst the CPAs. These weekly reports also kept the urgency of tax documentations on tax team’s mind afresh. But, as the office productivity was gradually improving, because of the generic automations employed so far, the management as of March 2007 decided this weekly progress report is no longer necessary and its distribution was stopped since.The management in the appreciation of the team effort and improved productivity, gave half day off to the entire tax department (see documentation…).
Project: Excel Workbook Rollup Application, Access 2003
Project: Excel Mass Print, Visual Studio 2005
Project: Admin-worksheet, Excel 2003
SQL-Server Data Conversion, Access 2003
Three months (December through April) was crunch time for AIG Tax Department annually to prepare and file the required tax paper by April 15 deadline to avoid IRS hefty penalties. These tax papers were filed annually for about 1,800 partnerships between AIG (Min. 51%-share) and the Partners (Max 49%-share).
During the two phases (Populating Affordable Housing Database, phase one), and (Apportionment Data Processing, phase two), there were 4 weeks combined waiting periods in the middle of busy tax season where the CPAs had to wait for the IT Department to create the required workbooks for them to get started with. During the waiting weeks, the CPAs moved on with other low priority tasks instead of continuing with high priority tax documentation activities. After this combined 4-week delays for IT related work, the management had no other good options other than encouraging the CPAs to use as much overtime they need to complete the tax papers on time by April 15; therefore, some new automations, to reduce the waiting periods and possibly eliminating the extreme overtime expenses, had the most urgent priority. Prior to describing how the automation challenges were met, here is the description of how the previous process worked up to this point.Phase One: Populating Affordable Housing Database. On December 15 each year, after IRS released the latest requirements for the upcoming year, one of the senior CPAs created a template workbook (stamped as Model) by revising an existing workbook from the year before (in this example 2006) with a new name such as “Affordable Housing 2007_Model.xls.” This newly created template workbook to be later used, by IT Department, as master template for creation of 1,800 production workbooks. This new template usually differed in the following areas compared to its old version year before in order to stay compliant with the latest IRS requirements.
1. New worksheets were added,
2. Some worksheet layouts were modified,
3. Many external and internal links had changed,
4. Scores of the calculations had been modified, and
5. Number of VBA Macros and MS Queries had been revised.
Phase One waiting period: After the above template got ready, IT Department was tasked to create 1,800 Production Workbooks based on this template in design and configuration but with the financial data rolled over for each account from the prior year’s workbooks to maintain continuity. This set of processes took about two weeks to be completed by running a number of VBA macros executed manually one at a time versus via a single batch command to create the required workbooks. Item 5 changes (VBA Macros and MS Queries added to the template) however, were done using time consuming manual cut and paste method, as opposed to a VBA macro, and it had to be automated.
The final 1,800 production workbooks were created based on this template and adding account numbers to the file names (AffordableHousing2007_0001.xls, AffordableHousing2007_0002.xls, …, AffordableHousing2007_1800.xls, etc.
Early in February when all these workbooks got processed by CPAs and their data had been uploaded to a SQL Server database nightly, the CPAs were getting ready to start Apportionment phase (Phase two) but they had to again to stop the tax documentation for another two weeks until all of the Apportionment Production Workbooks made available by the IT Department.
During the busy tax season, there was a weekly progress report distributed by the management on the work progress, reporting and recognizing those who have contributed the most. This in a way kept the urgency of tax documentation activities on entire team’s mind afresh and created a competitive environment. This in itself was very effective but on the other hand the managements ordering the CPAs stop their tax work to and wait for the Apportionment from the IT department to get the production workbooks ready created a mixed message.
However, as I was implementing the automations, the overall productivity of the office was improving. Because of the jump in the office productivities brought about using the automations, the management as of March 2007 decided this weekly progress report is no longer necessary and its distribution was stopped since. The management in the appreciation of the team effort and improved productivity, gave half day off to the entire team (see the email announcing this occasion).New and improved Automations:
- Apportionment Automation:
Starting with Apportionment 2006 template, number of new VBA macros and new MS Queries were included to self-generate the entire template automatically eliminating 2-week waiting period for phase 2. At the end of phase one, a senior CPA in the tax team, when ready for the Apportionment Documentation, was able to just copy 2006 version in a new folder and rename the workbook for year 2007. This manual operation took less than 2 minutes but it eliminated 2-week waiting period.
At this point, the renamed template had 2007 signature in the file name but its data belonged to 2006. Upon opening this template for the first time after it had been renamed to 2007, a VBA macros recognized this mismatch thus proceeded to delete the old data replacing it with the correct data for 2007 using some parametric MS Query inherited from the original template.Tweeking footing 100% match Auto print Roll over, check a workbook is open or not, if open record it to handle it later on otherwise do it then. This eliminated the need for interacting with the CPAs and issuing stop/start commands. AIG Big Picture