Messier 10e_cha_Mini_case_Analytics (1) - Excel X ? - Hector Bonilla General Percent 2 Autosum A7 Bad Normal X 5 HILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Xit Anal Narrow 10 - Wrap Text Paste BIU. "A Format Painter -A=== EE Merge & Center- clipboard Font 2 Alignment H13 X Da Copy 487 Neutral Conditional Format Good Formatting Table Calculation Insert Delete Format Fill- Clear Sort & Find & Filter - Select Luiting Numbe Slyes D H 1 J 5 1. Establish Threshold for Unexpected Fluctuations To begin identfying accounts with unexpected fluctuations auditors must establish a threshold or account difference. All accounts whose actual 2018 unaudited account balance difers from the expected ba anca by a value greater than the threshold established will be shown in the charts balon. As a general rule the threshold should not exceed materiality. For the purposes of this exercise we sume planning materialty 1953 1 milion Enter this value in the teld bekw as 3100 6 7 8 9 10 A. Set threshold for account difference in thousands (0.9., 3100 $3,100 2. Evaluate Unexpected Fluctuations of Baanca Shee, and income Statement accums have been generalad 10 desed on your testic accoudience. In the Evaluaton couTN DIESE Identy Z or more balance sheet and 2 or more income statement accounts where you believe the difference presente increased risk of material misstatement that may require a change in the nature, timing cr extent of planned and procedures Please indicate possible reasons for the difference. potential risks and suggested audi plan revisions 13 11 A. Balance Sheet Accounts Account 15 16 17 18 19 21 Cash and cash equivalents Receivables, not Inventory Other prepaid expenses Computer hardware and watere Linus of credi Accounts payable Accrued liabile Income mes payable Defurred income taxes Additional paid-in capital Accumulated other comprehensive income Treasury slock 6,854.7.114 and 8,546 shares al cost respectively Difference from Evaluation Expectations $31071 Indicate poes ble reasons for difference potential risks and suggested audit plan revisione (55,568) Indicate possble rousons for doronce, polonial risks, and suggested audit plan revisions $8.444 Indicate possble reasons for difference, potential risks, and suggested audit plan revisions ($3,414) Indicate poes ble reasons for difference, potental risks, and suggested audit plan revisions (S7 107) Indicate cable reasons for difference potential risks and suggested audit plan revisione ($3,062) Iridicate pessble reasons for diference, polenlial risks, and suggested audil plan revisions ($22.401 Indicate posable reasons for diference, potential risks, and suggested audit plan revisions $5,456 Indicate peable reasons for difference, potential risks and suggested audit plan revisione $5.711 Indicate possble reasons for difference, potential risks and suggested audit plan revisione (84.868) Indicate possble reasons for difference, potential risks, and suggested audil plan revisions $3,550 Indicate pouble reasons for difference, potential risks, and suggested audit plan revisions (83 Indicate peesble reasons for difference potential risks and suggested audit plan revisione 523.928 Indicato possbic reasons for diference, potential risks, and suggested audil plan revisions 23 24 25 28 30 31 Instructions Work Paper 5-1 Work Paper 5-2 Work Paper 5-3 Corririon Size Balance Sheet Common Size Income Statement READY 5 - 100 x] 3:23 PM 2/25/2021 Messier 10e_cha_Mini_case_Analytics (1) - Excel X ? - Hector Bonilla FORMULAS DATA REVIEW VIEW X 5 HILE HOME INSERT PAGE LAYOUT Xit Anal Narrow -110 10 Copy Paste Format Painter clipboard Autosum A7 Bad Normal AA Wrap Text AMerge Center General Percent 2 $ -% ; * Conditional Format as Good Formatting" Table Number Neutral Calculation Insert Delete Format Fill- Clear Sort & Find & Filter - Select Luiting Alignment Slyes H13 D . G 11 1 J 31 32 33 B. Income Statement Accounts Account 34 Net Sales Difference from Evaluation Expectations ($23,193) indicate peesble reasons for difference, potential risks and suggested audit plan revisione (547,895) Indicate ces ble reasons for difference, potentialrieks, and suggested audit plan revisione ($18,851) Indicate possble reasons for difference, potential risks, and suggested audil plan revisions $10.864 Indicate possible reasons for difference, potential risks, and suggested audit plan revisions Seling general and administrative expenses Income tax provision 37 38 39 40 41 12 43 44 45 46 47 48 49 51 52 53 54 55 58 Oradation 2018 59 80 61 Instructions Work Paper 5-1 Work Paper 5-2 Work Paper 5-3 Corrimon Size Balance Sheet Common Size Income Statement READY 5 -+ 100 O 3:27 PM 2/25/2021 Messier 10e_cha_Mini_case_Analytics (1) - Excel X ? - Hector Bonilla FORMULAS DATA REVIEW VIEW X 5 HILE HOME INSERT PAGE LAYOUT Xit Anal Narrow - 10 Fa Copy Paste Format Painter BIU- clipboard Font AA Wrap lext Normal Bad General Percent 2 $ - % * Conditional Format as Good Formatting Table Number FEX Insert Delete Format Fill- Clear AEEEEE Merge & Center Neutral Calculation Sort & Find & Filter - Select Slyes D32 B C 11 1 J K L N 0 P 55 D C EARTHWEAR CLOTHIERS Common-size Statements of Operations (in thousands, except er snare dere! M SAU: Steve Nop Senior Statt SKA 1/3/2017 1 2 3 4 5 6 7 8 For the perioder Den 2015 Dolar Vale % of Sales 950484 100.00% 546 393 57.49% 404.051 42 51% 384.012 38.30% 0.00% 40.729 4.29% 2014 DollarValue of Sales 857,885 100.00% 4/2/30 55.11% 305 146 44 19% 334994 39.05% (1.153) -0.13% 51,305 5.98% 0.00% (1229) -0 14% 573 0.07% 0.COM (1.091) -0.13% - 20% 49,559 5.78% 18 337 2.14% 31.222 3.64% 1.60 158 19,555 20.055 9 Net Sales 10 Cost of sales 11 Gross Pro 12 Seling general and administrative expenses 13 Non-recurring charge (credit 14 Income from operations 15 Other income (expenses 16 Interest expense 17 Trilares, income 18 Gain on sale of subsidiary 19 Other 20 Total other income (expense), nel 21 Income before income taxes 22 Income tax provision 23 Net income 24 Basic earnings per share 25 Diluted earnings per share 28 Dasic weighted average shares outstanding 27 Diluted weighted average shares outstanding (983) 1.450 - 10% 0.15% 0.00% Expected Lolar Value % af Sales 1,013,083 100.00% 620 045 59 44% 423,037 40.56% 393,031 37.88% 0.00% 40,050 0.00% -0 17% 1,017 0.10% 0.00% (2.947) -0.289 (3.037) 0 299 42,689 4.09% 15,794 1.51% 28,894 1.30 135 19.556 19.920 2018 Actual Dolar VALA of Saias 1,019,890 100.00% 572,153 56.10% 447,737 43.90% 374,180 36.69% 0.00% 73,567 7.21% 0.00% (878) -0.09% 999 0.10% 0.00% 13,514) -0.34% 13,4031 -0.33% 70,154 6.88% 26,658 2.61% 43,496 4.26% 1.48 1.45 19,159 19.485 Difference Dalar va Xar Sie (23,193) 0.00% (47,893) -334% 24.700 (18,851) 0 0.00% 27,506 2.80% 0 0.00% (140) -0.02% (28) 0.00% 0 0.00% (567) -0.05% (366) 27.466 2.79% 10.864 1.10% 16.602 1.69% 0.10 0.10 (398) (445) 0 45% 3.78% 1395 (4.798) 4.322) 35.757 13.230 22.527 1.15 1 14 19,531 19,774 2.37% 2.58% 29 Expected values are obtained by using the forecast function in Excel (using the row of data from 2014 and 2015 to obtain the expected value for 2016) 30 31 32 EM -14 Instructions Work Paper 5-1 Work Paper 5-2 Work Paper 5-3 Common Size Balance Sheel Common Size Income Statement READY O x] 5 - + 100% 3:32 PM 2/25/2021 Messier 10e_cha_Mini_case_Analytics (1) - Excel X ? - Hector Bonilla General Percent 2 Autosum A7 Bad Normal X 5 HILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Xit Anal Narrow 10 - Wrap Text Paste BIU. "A Format Painter -A=== EE Merge & Center- clipboard Font 2 Alignment H13 X Da Copy 487 Neutral Conditional Format Good Formatting Table Calculation Insert Delete Format Fill- Clear Sort & Find & Filter - Select Luiting Numbe Slyes D H 1 J 5 1. Establish Threshold for Unexpected Fluctuations To begin identfying accounts with unexpected fluctuations auditors must establish a threshold or account difference. All accounts whose actual 2018 unaudited account balance difers from the expected ba anca by a value greater than the threshold established will be shown in the charts balon. As a general rule the threshold should not exceed materiality. For the purposes of this exercise we sume planning materialty 1953 1 milion Enter this value in the teld bekw as 3100 6 7 8 9 10 A. Set threshold for account difference in thousands (0.9., 3100 $3,100 2. Evaluate Unexpected Fluctuations of Baanca Shee, and income Statement accums have been generalad 10 desed on your testic accoudience. In the Evaluaton couTN DIESE Identy Z or more balance sheet and 2 or more income statement accounts where you believe the difference presente increased risk of material misstatement that may require a change in the nature, timing cr extent of planned and procedures Please indicate possible reasons for the difference. potential risks and suggested audi plan revisions 13 11 A. Balance Sheet Accounts Account 15 16 17 18 19 21 Cash and cash equivalents Receivables, not Inventory Other prepaid expenses Computer hardware and watere Linus of credi Accounts payable Accrued liabile Income mes payable Defurred income taxes Additional paid-in capital Accumulated other comprehensive income Treasury slock 6,854.7.114 and 8,546 shares al cost respectively Difference from Evaluation Expectations $31071 Indicate poes ble reasons for difference potential risks and suggested audit plan revisione (55,568) Indicate possble rousons for doronce, polonial risks, and suggested audit plan revisions $8.444 Indicate possble reasons for difference, potential risks, and suggested audit plan revisions ($3,414) Indicate poes ble reasons for difference, potental risks, and suggested audit plan revisions (S7 107) Indicate cable reasons for difference potential risks and suggested audit plan revisione ($3,062) Iridicate pessble reasons for diference, polenlial risks, and suggested audil plan revisions ($22.401 Indicate posable reasons for diference, potential risks, and suggested audit plan revisions $5,456 Indicate peable reasons for difference, potential risks and suggested audit plan revisione $5.711 Indicate possble reasons for difference, potential risks and suggested audit plan revisione (84.868) Indicate possble reasons for difference, potential risks, and suggested audil plan revisions $3,550 Indicate pouble reasons for difference, potential risks, and suggested audit plan revisions (83 Indicate peesble reasons for difference potential risks and suggested audit plan revisione 523.928 Indicato possbic reasons for diference, potential risks, and suggested audil plan revisions 23 24 25 28 30 31 Instructions Work Paper 5-1 Work Paper 5-2 Work Paper 5-3 Corririon Size Balance Sheet Common Size Income Statement READY 5 - 100 x] 3:23 PM 2/25/2021 Messier 10e_cha_Mini_case_Analytics (1) - Excel X ? - Hector Bonilla FORMULAS DATA REVIEW VIEW X 5 HILE HOME INSERT PAGE LAYOUT Xit Anal Narrow -110 10 Copy Paste Format Painter clipboard Autosum A7 Bad Normal AA Wrap Text AMerge Center General Percent 2 $ -% ; * Conditional Format as Good Formatting" Table Number Neutral Calculation Insert Delete Format Fill- Clear Sort & Find & Filter - Select Luiting Alignment Slyes H13 D . G 11 1 J 31 32 33 B. Income Statement Accounts Account 34 Net Sales Difference from Evaluation Expectations ($23,193) indicate peesble reasons for difference, potential risks and suggested audit plan revisione (547,895) Indicate ces ble reasons for difference, potentialrieks, and suggested audit plan revisione ($18,851) Indicate possble reasons for difference, potential risks, and suggested audil plan revisions $10.864 Indicate possible reasons for difference, potential risks, and suggested audit plan revisions Seling general and administrative expenses Income tax provision 37 38 39 40 41 12 43 44 45 46 47 48 49 51 52 53 54 55 58 Oradation 2018 59 80 61 Instructions Work Paper 5-1 Work Paper 5-2 Work Paper 5-3 Corrimon Size Balance Sheet Common Size Income Statement READY 5 -+ 100 O 3:27 PM 2/25/2021 Messier 10e_cha_Mini_case_Analytics (1) - Excel X ? - Hector Bonilla FORMULAS DATA REVIEW VIEW X 5 HILE HOME INSERT PAGE LAYOUT Xit Anal Narrow - 10 Fa Copy Paste Format Painter BIU- clipboard Font AA Wrap lext Normal Bad General Percent 2 $ - % * Conditional Format as Good Formatting Table Number FEX Insert Delete Format Fill- Clear AEEEEE Merge & Center Neutral Calculation Sort & Find & Filter - Select Slyes D32 B C 11 1 J K L N 0 P 55 D C EARTHWEAR CLOTHIERS Common-size Statements of Operations (in thousands, except er snare dere! M SAU: Steve Nop Senior Statt SKA 1/3/2017 1 2 3 4 5 6 7 8 For the perioder Den 2015 Dolar Vale % of Sales 950484 100.00% 546 393 57.49% 404.051 42 51% 384.012 38.30% 0.00% 40.729 4.29% 2014 DollarValue of Sales 857,885 100.00% 4/2/30 55.11% 305 146 44 19% 334994 39.05% (1.153) -0.13% 51,305 5.98% 0.00% (1229) -0 14% 573 0.07% 0.COM (1.091) -0.13% - 20% 49,559 5.78% 18 337 2.14% 31.222 3.64% 1.60 158 19,555 20.055 9 Net Sales 10 Cost of sales 11 Gross Pro 12 Seling general and administrative expenses 13 Non-recurring charge (credit 14 Income from operations 15 Other income (expenses 16 Interest expense 17 Trilares, income 18 Gain on sale of subsidiary 19 Other 20 Total other income (expense), nel 21 Income before income taxes 22 Income tax provision 23 Net income 24 Basic earnings per share 25 Diluted earnings per share 28 Dasic weighted average shares outstanding 27 Diluted weighted average shares outstanding (983) 1.450 - 10% 0.15% 0.00% Expected Lolar Value % af Sales 1,013,083 100.00% 620 045 59 44% 423,037 40.56% 393,031 37.88% 0.00% 40,050 0.00% -0 17% 1,017 0.10% 0.00% (2.947) -0.289 (3.037) 0 299 42,689 4.09% 15,794 1.51% 28,894 1.30 135 19.556 19.920 2018 Actual Dolar VALA of Saias 1,019,890 100.00% 572,153 56.10% 447,737 43.90% 374,180 36.69% 0.00% 73,567 7.21% 0.00% (878) -0.09% 999 0.10% 0.00% 13,514) -0.34% 13,4031 -0.33% 70,154 6.88% 26,658 2.61% 43,496 4.26% 1.48 1.45 19,159 19.485 Difference Dalar va Xar Sie (23,193) 0.00% (47,893) -334% 24.700 (18,851) 0 0.00% 27,506 2.80% 0 0.00% (140) -0.02% (28) 0.00% 0 0.00% (567) -0.05% (366) 27.466 2.79% 10.864 1.10% 16.602 1.69% 0.10 0.10 (398) (445) 0 45% 3.78% 1395 (4.798) 4.322) 35.757 13.230 22.527 1.15 1 14 19,531 19,774 2.37% 2.58% 29 Expected values are obtained by using the forecast function in Excel (using the row of data from 2014 and 2015 to obtain the expected value for 2016) 30 31 32 EM -14 Instructions Work Paper 5-1 Work Paper 5-2 Work Paper 5-3 Common Size Balance Sheel Common Size Income Statement READY O x] 5 - + 100% 3:32 PM 2/25/2021