accounting-Autoliv Inc.: Using Lean Practices to Improve the AP Reconciliation Process

QUESTION

ISSN 1940-204XAutoliv Inc.: Using Lean Practices to Improvethe AP Reconciliation ProcessRosemary FullertonUtah State UniversityStaci F. GunnellThermoFisher ScientificINTRODUCTION TO AUTOLIV ANDTHE LEAN CULTUREencompassed all areas of the business. Support personnelwere trained in lean concepts and began to use them toimprove their processes as well. The facility-wide successesachieved from adopting a lean culture throughout Autoliv ledto multiple Shingo Prizes—internationally recognized awardsfor outstanding achievement in operational excellence.This case study focuses on applying lean concepts in theaccounting area. It represents an example of how to applyAPS lean tools to improve the accounts payable (AP) processin the finance department of a North American Autolivdivision. For more information on the company, visit itswebsite at www.autoliv.com.Autoliv Inc. was created in 1997 when Europe’s leadingautomotive safety company, Autoliv AB of Sweden,merged with North America and Asia’s leading airbagmanufacturer, Morton Automotive Safety Products. AutolivAB pioneered seatbelt technology in 1956, while MortonASP was a leader in airbag development, launching thefirst commercially successful airbag system in 1980. Today,Autoliv is a worldwide leader in technology and automotivesafety, having the widest product offerings for automotivesafety. Autoliv supplies all major and most other vehiclemanufacturers in the world. The company is often apartner with car manufacturers in the development of newvehicle models, a process that can take several years. Basedin Sweden and headquartered in Delaware, Autoliv iscomposed of 80 facilities in 29 countries, 21 crash test tracksin 11 countries, and has more than 50,000 employees.Autoliv realized in the late 1980s that the fundamentalgoal of any business is to produce the highest value productsand services with the least amount of time, effort, andcost. This led to its interest in the lean philosophy. Whilementoring Toyota, Autoliv began implementing leanpractices in the production arena as early as 1990. TheAutoliv Production System (APS) patterned after the ToyotaProduction System escalated into a system-wide culture thatIM A ED U C ATIO NA L C A S E JOURNALR. Chance MurrayUtah State UniversityINTRODUCTION TO ACCOUNTS PAYABLE(AP) RECONCILIATION PROCESS AT AUTOLIVThe AP group for Autoliv is a centralized function for NorthAmerica. The group consists of one supervisor and fouraccounting clerks. At the end of each month, Emily, anAP clerk, prepares the AP balance sheet reconciliation for30 facilities within North America. Two accounts for eachfacility are reconciled: the AP Open account and the MaterialClearing (MC) account. Emily follows the process that waspreviously developed and performed by Anne, an individualwho is no longer employed at Autoliv.1VOL. 6, N O. 3, ART. 1, SEPTEMBER 2013After Anne left Autoliv, several problems with the currentAP reconciliation process were exposed:department to make certain that the monthly informationhas been posted. Then she runs the two reports in Autoliv’ssystem software, Crystal: one report is for MC, whichrepresents those items that have been received but not paidfor; and one is for AP Open items, which are items that havebeen processed for payment. These two reports contain datafor all 30 Autoliv facilities. This process takes approximately10 minutes of Amanda’s time in preparation and 360 minutesof computer time. Amanda is able to do the work during thecomputer processing time. On Monday morning, the firstday of close, Emily opens the two Crystal reports Amandaprepared on Saturday and exports the reports to two excelfiles: MC_AL and AP OPEN_AL. This takes about 10minutes of Emily’s time and populates the two files withdata for all 30 facilities.2 Emily is now ready to work onreports that facilitate the AP reconciliation process for eachof the Autoliv facilities. The tasks and time commitment forpreparing the AP reconciling numbers for a single facility aredescribed below.1. here weren’t any documented standard work instructions,Tand no other employees were cross-trained to performthis process.2. Several intercompany transactions had to be handmanipulated because of the way the reports were createdand the legal taxing issues for operations in Mexico andCanada.3. Some unnecessary data continued to be added to thereport primarily because the previous employee hadincluded it.4. ue to the size of Autoliv operations and the transactionsDin the reports, the downloaded subsidiary report for theAP detail account was over 200,000 lines in Excel, whichpopulated several different Excel worksheets. In addition,the downloaded subsidiary report for the MC account wasapproximately 28,000 lines in Excel.5. ue to Emily’s lack of proficiency in Excel and theDmanual interaction required in reviewing 228,000individual lines of data, the total process for all facilitiestook in excess of two days to complete.Current process for AP reconciliation for Open items:E mily opens the Excel AP_MC Template_AL file thathas two worksheets prepared: One is a template forthe accounts payable MC by supplier data file, and theother is a template for the AP Open items data file.3The template file contains the correct “if” statementsfor establishing the AP aging. Emily first opens the APTemplate worksheet for the AP Open items from theAP_MC Template_AL file and copies the first two rows.These rows are then pasted over the original two rowsof the AP Open_AL data file.4 The purpose of row 2 isto facilitate copying the “if” statements on the report.Emily highlights row 2 to remind her that this is notactual data. This procedure takes Emily approximatelytwo minutes for each facility’s report.• mily then puts the last day of the month (12/31/2011) inEcell S1 and the first day of the following month (01/01/2012)in cell T1. In order to establish the aging schedule, Emilyremoves the highlights from cells O2, P2, Q2, and R2 andcopies the formulas in these four cells through to the bottomof the file. For Autoliv, this usually represents upwards of200,000 data items and takes Emily about three minutes tocomplete this stage of the process.5 Emily then deletes thehighlighted row 2 since this data is not real data.• he next step in the process identifies the internal andTexternal vendor amounts. If the mailing name in columnE contains “Autoliv,” then “INT” (for internal vendor)should be put in column B (User Code). If there is noreference to Autoliv in the vendor name, then “EXT” (for• Upon review of the situation, it was evident that therewas much room for process improvement.Autoliv applies the SMART objective to its improvementinitiatives, which stands for Specific, Measurable, Ambitious,Relevant, and Time-based goals: Specific—goals are clearenough to know exactly what needs to be done to achievethe objective; Measureable—the successful completionof a goal should have tangible evidence of completion;Ambitious—goals should be attainable through stretchingcapabilities; Relevant—goals should be aligned with highercorporate strategies; and Time-based—goals should have aspecified completion date. Emily’s supervisor has asked herto improve the reconciliation process. Her SMART objectiveis to decrease the average process time in AP reconciliationpreparation by 60%, from an average of 35 minutes perfacility to 14 minutes for Account 710 (A710) by the endof April. Emily has asked for your help in achieving thisSMART objective. She will walk you through the currentstate of the reconciliation process in detail.1CURRENT STATE OF THEAP RECONCILIATION PROCESSThe Saturday before the monthly close, staff accountantAmanda is responsible for preparing two AP reports foreach of the Autoliv facilities. First she checks with the ITIM A ED U C ATIO NA L C A S E JOURNAL2VOL. 6, N O. 3, ART. 1, SEPTEMBER 2013external vendor) should be input into column B. Thesetasks take Emily about four minutes for each division.• he next step in the process establishes the remittanceTmethod. Automatic clearing house payments (ACH)should be put into all the blank cells in column C.Looking for the blank cells is a tedious process that takesEmily about six minutes to complete.• mily then creates a pivot table in the AP Open_AL ExcelEdata file in which she is working.6 She uses all of the datain columns A through T and defines the aging schedule(Sum of 0-45, Sum of 46-90, Sum of 90+, and Sum ofOverdue) by “EXT” or “INT.” Creation of the pivot tabletakes Emily approximately two minutes. This completesEmily’s process for developing the AP reconciliationinformation for the Open items by company.Vendors with numbers from 20 to 100 represent internal,intercompany vendors. These must be tracked separatelybecause there is a central treasury, and no actual cashtransactions occur between legal taxing entity facilities. Forvendors in this range, Emily puts “INT” in column B. Forall other vendors (those with numbers greater than 100),Emily puts “EXT” in column B. This is a simple processthat only takes about one minute.• mily is then ready to create the AP MC aging pivotEtable. The pivot table for the MC_AL file is the sameformat as for the AP Open_AL file, as described above.The data in columns A through T is used, and the agingschedule is defined by “EXT” or “INT.” Emily spendsapproximately two minutes creating this pivot table.Current process for AP reconciliation by supplier:• AP aging reconciliation reports:E mily is now ready to complete the AP agingreconciliation reports. She opens the AP Recon_ALExcel file.9 There are three tabs in this file: SubsidiaryInfo, GL (General Ledger), and REC (Reconciliation).She first goes to the Subsidiary Info tab and fills in theaging amounts determined from her two pivot tables. Shecopies the appropriate “EXT” or “INT” aging amountsfrom the AP Open_AL file into cells (E5: E12). Shecopies the appropriate “EXT” or ”INT” aging amountsfrom the MC_AL file into cells (E22: E29). This takesapproximately five minutes.• mily then copies the general ledger balances fromEher trial balance onto the GL tab. This task requiresapproximately three minutes.10• he only account that Autoliv requires Emily to reconcileTis A710 which is the control account for all AP on thebalance sheet. Within A710, there are only two accountsthat have subsidiary accounts and require reconciliation.These are the MC account and the AP Open account.Emily is now ready to review the results and determineif reconciliation for AP is necessary. She examines theREC worksheet in the AP Recon_AL file. This worksheetis automatically updated from the information in theSubsidiary and GL worksheets. Cells (H7: K7) arepopulated for the AP Open subsidiary account and cells(H9: K9) are populated for the MC subsidiary account.Column B amounts are populated automatically from theGL information. Formulas are built into the reconciliationreport to determine variances between the general ledgerbalances and the supporting aging schedules. If the endingmonth-end variance (highlighted in yellow) is greater than$200,000, reconciliation must be performed.11 If there is aquarter-end variance that is material, Emily will proceedT hen Emily does approximately the same routine withthe AP MC by Supplier Excel data file.7 She opens thisfile and inserts a column before column B to initially prepit for the reconciliation schedule. Then she returns to theAP_MC Template_AL file and opens the MC Templateworksheet. She copies the first two rows of this file totransfer the correct “if” statements to the supplier datafile for establishing the AP aging schedule. The copiedrows from the template are pasted over the first two rowsin the MC_AL file. Emily again highlights row 2 of theedited file to remind her that this row will be deleted assoon as the aging formulas are copied to the data fields.This takes Emily about one minute to complete.• mily then puts the last day of the month (12/31/2011)Einto cell U1 and the first day of the following month(01/01/2012) into cell V1. After removing the highlightsfrom cells Q2, R2, S2, and T2, the formulas in these fourcells are copied down the columns to the end of the data.8Emily deletes row 2 to eliminate the false data from thetemplate. It takes Emily approximately two minutes toset up this aging schedule.• mily then sorts the data by vendor number (column C)Efrom low to high. All intercompany vendor entries shouldbe eliminated. Intercompany vendors are those thathave a vendor number less than 20. Identification andelimination of these entries take Emily approximatelytwo minutes.• olumn B that was inserted when this data file was firstCopened needs to differentiate between internal and externalsuppliers. The internal vendors that were just deleted withnumbers less than 20 were “legal entity intercompanyvendors,” where actual cash transactions are not exchanged.• IM A ED U C ATIO NA L C A S E JOURNAL3VOL. 6, N O. 3, ART. 1, SEPTEMBER 2013with the reconciliation. If it is considered immaterial,the difference is booked.12 The review of the variancesand determination of whether reconciliation needs to beperformed takes Emily approximately two minutes.Step 3: From the PivotTable Field List on the right, drag“User Code” (“IC” for MC) and “Co” (“Company” for MC)down to the box labeled Row Labels. Drag “0-45,” “46-90,”“90+,” and “overdue” down to the box labeled Values.Step 4: A drop-down menu titled ∑ Values should appearIMPROVEMENT OBJECTIVEin the box labeled Column Labels. Drag this into the RowLabels box.Emily recognizes that there is a lot of waste built into the APreconciliation process and is looking for ways to eliminatesome of her time in preparing the AP reconciliation reports.Currently, she is not concerned about ways to change the 20minutes of setup and preparation time that she and Amandaincur while transferring information from Crystal files toExcel files. That will be a future Kaizen opportunity. Emily’smost immediate objective is to reduce some of the non-valueadded activities that occur after the transfer of data to theExcel files. She is asking for your “fresh set of eyes” to helpher improvement efforts in meeting her SMART objective.Step 5: Ensure that values are presented as SUM (notCOUNT). To change this, click on each value individually(i.e., 0-45, 46-90, 90+, or overdue). In the drop-down menu,select Value Field Settings, then change COUNT to SUM,then click OK. Repeat this step for each value displayed.The information now displayed in the worksheet isformatted according to the needs of this case study.ENDNOTESSample data files (Excel program needed) for one majorAutoliv facility that will be used for this case study arelocated at the following URLs: http://huntsman.usu.edu/files/uploads/Fullerton/AP_MC Template_AL.xlsx, http://huntsman.usu.edu/files/uploads/Fullerton/AP Open_AL.xlsx, http://huntsman.usu.edu/files/uploads/Fullerton/MC_AL.xlsx, and http://huntsman.usu.edu/files/uploads/Fullerton/AP Recon_AL.xlsx.1ASSIGNMENT 1. repare a 5-10 minute oral presentation demonstratingPyour process improvements for the Autoliv APreconciliation process.2. repare and submit the Excel files that Emily can use toPachieve her SMART objective.3. urn in a written report that:Ta. xplains how your new process satisfies Autoliv’sESMART objectives.b. dentifies and explains which of the seven wastes areIfound in this current process.c. xplains the “lean lessons” learned from this case study.EThese data files for one facility are already prepared for you.Refer to first endnote for URLs.2http://huntsman.usu.edu/files/uploads/Fullerton/AP_MCTemplate_AL.xlsx3http://huntsman.usu.edu/files/uploads/Fullerton/AP Open_AL.xlsx44. Write the standard work instructions for the current andNote that the sample data for this case study is about onefourth of the actual Autoliv data file.5new reconciliation processes.5. repare a process map for the current and future state.PIdentify any Kaizen bursts for a future ideal state.If you are not familiar with creating pivot tables in Excel,simple instructions are provided in Appendix A.6APPENDIX Ahttp://huntsman.usu.edu/files/uploads/Fullerton/MC_AL.xlsx7Instructions for Building a Pivot TableYou will be copying over unnecessary data in columns Q, R,and S. Autoliv generally has in excess of 28,000 line itemsin this file, but your sample file is about one fourth of theactual Autoliv file.8Step 1: Click Insert then PivotTable. A dialog box will popup where you can select which data you want to be included.Step 2: Select the data range you would like to include in thepivot table. For this case, it should be the whole worksheet.(This should be done for you automatically.) Make sure NewWorksheet is selected in the bottom half of the box under“PivotTable report to be placed.” Click OK.IM A ED U C ATIO NA L C A S E JOURNALhttp://huntsman.usu.edu/files/uploads/Fullerton/AP Recon_AL.xlsx9The general ledger numbers have been copied for you andare in the AP Recon_AL file on the GL worksheet.104VOL. 6, N O. 3, ART. 1, SEPTEMBER 2013The reconciliation schedule indicates that the variance“must be zero.” This is the visual cue that Autoliv providedto indicate that a reconciliation entry needed to be made ifthere is a variance. However, this is only for year-end, notmonthly reconciliation, where in this case the reconciliationis made only if the variance is over $200,000.11The “overdue” amounts are included in the 0-46, 46-90,and 90+ days outstanding figures.12ABOUT IMA®With a worldwide network of more than 65,000 professionals,IMA (Institute of Management Accountants) is the world’sleading organization dedicated to empowering accountingand finance professionals to drive business performance.IMA provides a dynamic forum for professionals to advancetheir careers through CMA® (Certified ManagementAccountant) certification, research, professional education,networking, and advocacy of the highest ethical andprofessional standards. For more information about IMA,please visit www.imanet.org.IM A ED U C ATIO NA L C A S E JOURNAL5VOL. 6, N O. 3, ART. 1, SEPTEMBER 2013

ANSWER:

REQUEST HELP FROM A TUTOR