please explain how you got the excel numbers in detail

Instructions: Complete yellow fields Planning Materiality Calculation Enter the balance of the Accounting Base you are selecting Choose a percentage Planning Materiality - Question: What Accounting Base did you choose and why? Account No. 100-100 100-200 100-300 101-000 101-100 101-200 101-300 102-000 103-000 110-000 120-100 120-200 120-300 125-100 125-200 125-300 130-000 140-000 140-100 140-200 140-300 150-000 160-000 165-000 165-100 165-200 165-300 170-000 200-000 210-000 210-100 210-200 210-300 211-000 212-000 212-100 212-200 212-300 213-000 Account Location Cash on hand Dallas Cash on hand Atlanta Cash on hand Oxford Cash - Operating Corporate Cash - Operating Dallas Cash - Operating Atlanta Cash - Operating Oxford Cash - Sweep Corporate Cash - Deposits Corporate Short term investment securities Corporate Accounts receivable Dallas Accounts receivable Atlanta Accounts receivable Oxford Allowance for doubtful accounts Dallas Allowance for doubtful accounts Atlanta Allowance for doubtful accounts Oxford Stock, Bonds, Mutual Funds Corporate Inventory Corporate Inventory Dallas Inventory Atlanta Inventory Oxford Notes receivable Corporate Prepaid insurance Corporate Prepaid other expenses Corporate Prepaid other expenses Dallas Prepaid other expenses Atlanta Prepaid other expenses Oxford Other current assets Corporate Long-term Investment securities Corporate Vehicles Corporate Vehicles Dallas Vehicles Atlanta Vehicles Oxford Accumulated depreciation - vehicles Corporate Furniture and fixtures Corporate Furniture and fixtures Dallas Furniture and fixtures Atlanta Furniture and fixtures Oxford Accumulated depreciation - furniture and fixturCorporate 214-000 214-100 214-200 214-300 215-000 216-000 216-100 216-200 216-300 217-000 218-000 220-000 225-000 226-000 300-000 300-100 300-200 300-300 310-000 320-000 320-100 320-200 320-300 330-000 330-100 330-200 330-300 340-000 350-000 360-000 370-000 375-000 380-000 380-100 380-200 385-000 385-100 385-200 390-000 395-000 Equipment Equipment Equipment Equipment Accumulated depreciation - equipment Buildings and Improvements Buildings and Improvements Buildings and Improvements Buildings and Improvements Accumulated depreciation - buildings Land Goodwill Other intangible assets Accumulated amortization - intangible assets Accounts payable Accounts payable Accounts payable Accounts payable Accrued interest expense Withholding taxes payable Withholding taxes payable Withholding taxes payable Withholding taxes payable Accrued wages Accrued wages Accrued wages Accrued wages Unearned revenue Accrued income taxes Deferred tax liability Short-term bank loan payable Mortagage payable Current Portion of Long-Term Debt Current Portion of Long-Term Debt Current Portion of Long-Term Debt Long-Term Debt Long-Term Debt Long-Term Debt Short-term notes payable Long-term notes payable Corporate Dallas Atlanta Oxford Corporate Corporate Dallas Atlanta Oxford Corporate Corporate Corporate Corporate Corporate Corporate Dallas Atlanta Oxford Corporate Corporate Dallas Atlanta Oxford Corporate Dallas Atlanta Oxford Corporate Corporate Corporate Corporate Corporate Corporate Dallas Atlanta Corporate Dallas Atlanta Corporate Corporate 400-000 410-000 450-000 470-000 500-000 500-100 500-200 500-300 505-000 505-100 505-200 505-300 600-000 600-100 600-200 600-300 605-000 610-00 700-000 710-000 720-000 730-000 735-000 740-000 745-000 750-000 760-000 770-000 775-000 780-000 785-000 790-000 795-000 800-000 810-000 820-000 900-000 910-000 920-000 930-000 Common stock Additional paid-in capital Retained earnings Treasury Stock Sales Sales Sales Sales Sales returns and allowances Sales returns and allowances Sales returns and allowances Sales returns and allowances Cost of Goods Sold Cost of Goods Sold Cost of Goods Sold Cost of Goods Sold Discount received Purchase returns and allowances Advertising Expense Bad debt expense Bank charges Commissions expense Contract labor Delivery expense Postage expense Dues and subscriptions Entertainment expense Insurance expense Rent expense Office expense Repairs expense Payroll Expenses Payroll taxes expense Depreciation Expense Amortization Expense Interest expense Investment income Rental income Interest received Gain (loss) on sale of assets Corporate Corporate Corporate Corporate Corporate Dallas Atlanta Oxford Corporate Dallas Atlanta Oxford Corporate Dallas Atlanta Oxford Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate Corporate 950-000 Income taxes expense Corporate FS Account FS SubGroup 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 100 Cash and Cash Equivalents Current Assets 120 Account Receivables, net Current Assets 120 Account Receivables, net Current Assets 120 Account Receivables, net Current Assets 120 Account Receivables, net Current Assets 120 Account Receivables, net Current Assets 120 Account Receivables, net Current Assets 130 Investments Current Assets 140 Inventory, net Current Assets 140 Inventory, net Current Assets 140 Inventory, net Current Assets 140 Inventory, net Current Assets 150 Notes Receivable Current Assets 160 Prepaid Expenses Current Assets 160 Prepaid Expenses Current Assets 160 Prepaid Expenses Current Assets 160 Prepaid Expenses Current Assets 160 Prepaid Expenses Current Assets 170 Other Current Assets Current Assets 200 Investments Non-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 220 Accumulated Depreciation Non-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 220 Accumulated Depreciation Non-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 220 Accumulated Depreciation Non-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 220 Accumulated Depreciation Non-Current Assets 210 Property, Plant, and EquipmentNon-Current Assets 230 Goodwill Non-Current Assets 240 Intangibles, net Non-Current Assets 240 Intangibles, net Non-Current Assets 300 Accounts Payable Current Liabilities 300 Accounts Payable Current Liabilities 300 Accounts Payable Current Liabilities 300 Accounts Payable Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 310 Accrued Expenses Current Liabilities 320 Deferred Tax Liability Current Liabilities 330 Loan Payable Current Liabilities 360 Long-Term Debt Non-Current Liabilities 340 Current Portion of Long-Term Current Liabilities 340 Current Portion of Long-Term Current Liabilities 340 Current Portion of Long-Term Current Liabilities 360 Long-Term Debt Non-Current Liabilities 360 Long-Term Debt Non-Current Liabilities 360 Long-Term Debt Non-Current Liabilities 350 Current Portion of Notes PayabCurrent Liabilities 370 Notes Payable Non-Current Liabilities 400 Common Stock Stockholders' Equity 410 Additional Paid-In Capital Stockholders' Equity 450 Retained Earnings Stockholders' Equity 470 Treasury Stock Stockholders' Equity 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 500 Net Sales 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 600 Cost of good sold 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 700 Selling, General, and Administr700 Selling, General, and Administr 800 Depreciation and Amortization800 Depreciation and Amortization 800 Depreciation and Amortization800 Depreciation and Amortization 810 Interest Expense 810 Interest Expense 900 Interest and Investment Income900 Interest and Investment Income 910 Other income 910 Other income 900 Interest and Investment Income900 Interest and Investment Income 910 Other income 910 Other income 950 Provision for Income Taxes 950 Provision for Income Taxes FS Group Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets 2016 18,000 16,000 5,000 30,215,000 116,000 234,000 84,000 3,827,000 2,100,000 18,337,000 36,278,000 3,881,000 (815,000) (1,807,000) (104,000) 46,587,000 29,284,000 2,837,000 6,718,000 871,000 6,281,000 862,000 1,612,000 152,000 203,000 18,000 13,347,000 38,850,000 8,371,000 186,000 346,000 62,000 (5,629,000) 14,928,000 641,000 1,038,000 328,000 (8,609,000) 2015 9,000 18,000 1,000 28,745,000 108,000 252,000 97,000 410,000 2,693,000 1,800,000 14,827,000 31,592,000 3,928,000 (593,000) (1,754,000) (98,000) 42,680,000 28,450,000 2,291,000 7,532,000 881,000 5,898,000 845,000 1,468,000 177,000 228,000 22,000 12,487,000 37,385,000 8,206,000 ### 302,000 ### (5,473,000) 15,026,000 621,000 867,000 320,000 (8,645,000) 2014 6,000 14,000 2,000 31,928,000 98,000 241,000 88,000 2,581,000 1,700,000 14,027,000 30,887,000 3,672,000 (522,000) (1,682,000) (87,000) 39,132,000 23,871,000 2,019,000 6,912,000 791,000 6,385,000 836,000 1,421,000 182,000 201,000 14,000 8,371,000 34,827,000 8,112,000 186,000 302,000 62,000 (5,308,000) 14,821,000 608,000 858,000 324,000 (8,572,000) 2013 10,000 15,000 1,000 23,783,000 85,000 202,000 103,000 2,617,000 5,600,000 12,636,000 33,928,000 3,498,000 (413,000) (1,288,000) (79,000) 43,675,000 18,817,000 1,871,000 6,512,000 843,000 5,497,000 804,000 1,381,000 143,000 195,000 10,000 7,812,000 32,918,000 7,918,000 152,000 302,000 62,000 (5,102,000) 14,863,000 614,000 843,000 308,000 (8,372,000) Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Assets Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity 100,100,000 89,132,000 78,216,000 76,919,000 152,000 144,000 138,000 131,000 198,000 206,000 196,000 174,000 57,000 53,000 54,000 52,000 (74,900,000) (67,845,000) (64,918,000) (62,118,000) 85,200,000 93,411,000 91,878,000 87,128,000 4,910,000 ### 4,910,000 4,910,000 5,300,000 ### 3,250,000 3,250,000 1,820,000 ### 1,820,000 1,820,000 (17,320,000) (15,980,000) (14,237,000) (12,928,000) 37,040,000 ### 37,040,000 37,040,000 12,391,000 11,382,000 10,879,000 7,181,000 7,019,000 (2,133,000) (1,687,000) (1,610,000) (1,543,000) 53,275,000 52,102,000 54,087,000 59,323,000 1,897,000 1,582,000 1,413,000 1,726,000 3,112,000 3,989,000 2,817,000 3,291,000 627,000 576,000 682,000 602,000 6,250,000 7,500,000 7,750,000 7,900,000 9,745,000 9,283,000 8,172,000 7,629,000 354,000 340,000 331,000 330,000 629,000 616,000 598,000 586,000 82,000 79,000 75,000 77,000 4,035,000 5,837,000 4,901,000 5,343,000 180,000 170,000 156,000 163,000 2,300,000 300,000 250,000 250,000 40,000 ### 40,000 40,000 4,392,000 4,837,000 4,019,000 5,217,000 3,876,000 3,428,000 3,019,000 2,736,000 14,463,000 16,789,000 23,928,000 29,928,000 4,000,000 4,000,000 4,400,000 4,800,000 2,800,000 1,800,000 1,500,000 1,200,000 500,000 ### 500,000 500,000 750,000 ### 750,000 750,000 16,000,000 18,400,000 15,000,000 18,000,000 2,500,000 3,000,000 3,500,000 4,000,000 4,250,000 5,000,000 5,750,000 6,500,000 5,000,000 ### 5,000,000 5,000,000 50,000,000 55,000,000 60,000,000 65,000,000 Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Liabilities and Stockholder's Equity Net Sales Net Sales Net Sales Net Sales Net Sales Net Sales Net Sales Net Sales Cost of Goods Sold Cost of Goods Sold Cost of Goods Sold Cost of Goods Sold Cost of Goods Sold Cost of Goods Sold Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Interest and Other (Income) Expens Interest and Other (Income) Expens Interest and Other (Income) Expens Interest and Other (Income) Expens Interest and Other (Income) Expens 5,000,000 65,000,000 153,309,000 (2,500,000) (77,058,000) (102,177,000) (283,918,000) (36,718,000) 82,000 118,000 327,000 73,000 52,489,000 69,360,000 193,394,000 25,011,000 (3,219,000) (861,000) 9,452,000 9,644,000 18,000 3,248,000 2,718,000 3,927,000 1,982,000 28,000 1,832,000 3,721,000 2,830,000 6,119,000 1,718,000 49,478,000 7,955,000 7,850,000 446,000 5,674,000 (3,847,000) (1,600,000) (1,080,000) 563,000 ### ### 122,416,000 ### (93,839,000) (98,271,000) (259,019,000) (30,918,000) 78,000 70,000 283,000 101,000 50,181,000 66,221,000 178,267,000 21,919,000 (2,856,000) (392,000) 13,475,000 9,328,000 22,000 3,520,000 1,914,000 4,209,000 1,627,000 26,000 2,012,000 3,517,000 2,254,000 5,981,000 2,090,000 46,286,000 6,492,000 7,485,000 ### 5,831,000 (2,718,000) ### (1,267,000) 450,000 5,000,000 65,000,000 86,688,000 (2,500,000) (86,172,000) (82,712,000) (232,019,000) (30,121,000) 74,000 84,000 255,000 68,000 46,133,000 59,811,000 160,182,000 20,723,000 (2,361,000) (429,000) 15,271,000 8,712,000 16,000 3,102,000 1,827,000 3,318,000 1,537,000 19,000 2,637,000 3,301,000 1,827,000 5,726,000 3,928,000 44,938,000 6,019,000 7,291,000 446,000 5,162,000 (2,192,000) (1,600,000) (918,000) (192,000) 5,000,000 65,000,000 61,027,000 (2,500,000) (84,637,000) (84,347,000) (208,172,000) (26,172,000) 68,000 102,000 199,000 58,000 45,737,000 60,241,000 141,634,000 19,647,000 (2,064,000) (275,000) 13,928,000 8,465,000 18,000 2,837,000 2,462,000 3,192,000 1,382,000 22,000 2,537,000 3,029,000 1,728,000 5,028,000 1,873,000 43,822,000 5,714,000 6,689,000 446,000 5,473,000 (4,918,000) (1,600,000) (1,129,000) 319,000 Provision for Income Taxes 19,528,000 21,067,000 10,648,000 8,745,000 Bonus: (2 points) Why would a $30,543,000 change in Retained Earnings not concern the auditor? Answer: Analytical Procedures Exercise - 1, 2, or 3 person teams You are a staff auditor at Willis & Adams, LP Audit Firm and you are working on the 2016 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. You must use a formula in cell C7 on the PlanningMateriality tab to calculate PM. Also, indicate your base in cell D7 (i.e., total asses, net income before taxes, current assets, total liabilities, etc.). Round PM to the hundred-thousand-dollar mark (e.g., $10,100,000, not $10,128,500). Be sure to answer the question on this tab. 2. Based on the Trial Balance, and using Excel's Pivot Tables function, 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 2016, 2015, 2014, and 2013 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 2016 vs. 2015 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 dollar difference cells using the accounting number format and no decimal places (i.e., $10,827,000). Format percentage difference cells using one decimal place (i.e., 9.1%). Horizontal Analysis should be performed on each of your pivot tables. 4. Based on the horizontal analyses, 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 on 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, last name, and first name, separated by periods. For example, if I were in the MW 12:00 class, then my file would be saved as: 003.Mullis.Curtis.xls