Analytical Procedures Exercise

You are a staff auditor at Willis & 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 (e.g., $13,100,000, not $13,128,500). Using a formula for rounding would be smart.

2. Use Excel's Pivot Tables function to create three distinct pivot tables: (1) for the Corporate Location; (2) a combined table for the three store locations (i.e., Atlanta, Dallas, Oxford); (3) a consolidated table for all locations (Corporate, Atlanta, Dallas, and Oxford). You must use the Pivot Tables function, you cannot create the tables manually. You should only include balance sheet accounts. You should include Location as a Report Filter. The Pivot Tables should be classified using the FS Account column, and you should include 2014, 2013, 2012, and 2011 balances. You should finish with 3 separate pivot tables on 3 separate tabs in your Excel document. Name the three tabs: ConsolidatedPT, StoresPT, and CorporatePT.

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. Format the dollar difference cells using the accounting number format and no decimal places (i.e., $13,827,000). Format the percentage difference cells using one decimal place (i.e., 8.1%). Horizontal Analysis should be performed on each of your three pivot tables.

4. Based on the horizontal 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 Pivot Tables. 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 Pivot Tables. Willis & Adams' LP instructs their auditors to use a "scope" for this analysis of Planning Materiality and a ten percent change.

5. Based on the FS accounts that you identified in Step 4, above, list 2 (and only 2) assertions that you believe represent the greatest risk of material misstatement.

Upload your completed file to iCollege. Save your Excel document using a combination of your class section, team number, separated by periods.

Attachment:- Assignment Files.rar