Analytical Procedures Exercise
You are a staff auditor at Wills and Adams, LP Audit Firm and you are working on the 2014 audit of Bengro, Inc., a manufacturer of outdoor patio furniture. You have been asked by the audit manager to perform a preliminary analytical procedure on Bengro's financial statement (FS) accounts as of year-end. Bengro has downloaded its unadjusted trial balance into excel for your convenience.
Requirements
1. Following Willis & Adam's LP Materiality Guidelines, determine Planning Materiality. A tab has been included in the excel workbook for your benefit. Round PM to the hundred thousand dollar mark (i.e., $13,100,000).
2. Use Excel's Pivot Tables function to create three financial statements: (1) for the Corporate Location; (2) a combined financial statement for the three store locations (i.e., Atlanta, Dallas, Oxford); (3) a consolidated financial statement for all locations (Corporate, Atlanta, Dallas, and Oxford). You must use the Pivot Tables function, you cannot create the financial statements manually. You should finish with 3 separate financial statements, on 3 separate tabs in your Excel document. Name the three tabs: ConsolidatedFS, StoresFS, and CorporateFS. The Pivot Tables should be classified using the FS Account column, and you should include 2014, 2013, 2012, and 2011 balances.
3. Perform a Horizontal Analysis. This involves calculating a dollar difference, and a percentage difference for 2014 vs. 2013 FS Account balances. These calculations must be done using a formula, not by hand, and the formulas should be adjacent to the pivot table. Only perform Horizontal Analysis for balance sheet accounts. Format the dollar cells using the accounting number format and no decimal places (i.e., $13,827,000). Format the percentage cells using one decimal place (i.e., 8.1%). Horizontal Analysis should be performed on each of your three financial statements.
4. Based on this analysis, identify at least four FS accounts that you believe are important for the auditor to further investigate. At least one FS account should be identified from each of your three financial statements. It is important that you use your accounting knowledge when considering year-to-year fluctuations. Just because a large change occurs, does not mean that it is unexpected, and it may not require any additional audit attention. The accounts you identify can come from any of the three financial statements. Willis & Adams' LP instructs their auditors to use a "scope" for this analysis of Planning Materiality and a ten percent change.
Attachment:- Analytical_Procedures_Excel_Project.xlsx