Analytical procedures are a process consisting of four phases: expectation formation, identification, investigation, and evaluation. The most important phase is the first - expectation formation - where the auditor develops expectations about an account balance or a plausible financial relationship. Auditors realize that numerical information of this nature is of little benefit without comparisons to benchmark expectations. Appropriate expectation methods range from simplistic to complex based on auditor judgment that considers the desired level of assurance for the procedure. Analytical procedures illustrated here include trend analysis, ratio analysis, reasonableness testing and regression analysis. This case demonstrates the use of analytical procedures in planning and substantive testing for a fictional chain of convenience stores named On the Go Stores. On the Go Stores has 23 convenience stores including five new stores (Stores 1, 4, 10, 13, and 22) that opened during the year. Operations vary by geographic location and the mix of products sold. The geographical placement of stores depends on several factors, such as competition and the economic environment of the location. Stores 2, 4, 6, 8, 9, 11, 13, 15, 17, 18, 20, 21, and 23 are considered to be in favorable locations. The mix of products and services at each store can vary depending on location and the most important factor is whether the store sells gasoline. Stores 5, 6, 7, 8, 14, 15, 16, 17, 18, 19, 20 and 21 sell gasoline. The mix of products offered for sale affects the volume of customers as well as the number of full-time employees. Trend Analysis Trend analysis considers changes in account balances over time and is most appropriate when the relationships are predictable. Therefore, the auditor must consider volatility of the business environment and corresponding effects on trends. Trend analysis might apply to both audit planning and for evidence gathering; therefore, high disaggregation of data is more likely in the substantive testing phase because of more precise expectation formation. On the Go Stores provides information shown in Exhibit A-1. Exhibit A-1 Relevant Information for On the Go Stores Current- Year Current- Year Prior-Year Dollar Sales Change Current Percent Change Inventory Sales Audited Store Average Number Employees Square Feet HA | 1 781,793 781,793 N/A 48,725 2,500 11.00 2 1,165, 221 1,146,438 (18,783) -1.61% 44,171 2,500 11.31 3 1,147,430 1,195,004 47,574 4.1596 45,714 2,500 12.46 4 951,784 951,784 N/A 37,218 4,000 11.86 5 2,037,463 1,981,409 (56,054) -2.75% 45,826 4,000 10.06 6 2,257,920 2,300,671 42,751 1.8996 53,862 4,000 11.10 7 1,850,354 1,956,481 106,127 5.7496 49,883 4,000 10.71 8 1,916,884 1,799,713 (117,171) -6.11% 47,016 4,000 7.50 9 1,833,209 1,820,641 (12,568) -0.69% 59,726 4,000 14.00 10* 774,954 774,954 N/A 35,882 2,500 11.20 11 980,484 1,159,004 178,520 18.21% 37,664 2,500 11.60 12 1,069,652 1,139,475 69,823 6.5396 34,662 2,500 12.70 13 948,522 948,522 N/A 44,782 4,000 11.86 14 1,795,123 1,984,777 189,654 10.56% 38,774 4,000 12.20 15 2,119,015 2,293,847 174,832 8.2596 55,423 4,000 11.10 16 1,947,303 1,984,722 37,419 1.9296 52,884 4,000 10.40 17 1,705,789 1,798,336 92,547 5.4396 46,834 4,000 8.84 18 2,396,971 2,484,503 87,532 3.6596 53,772 4,000 12.10 19 1,901,631 1,837,400 (54,231) -3.38% 43,982 4,000 9.70 20 1,514,798 1,609,385 94,587 6.2496 44,893 4,000 7.20 21 1,886,587 1,874,229 (12,358) -0.66% 37,665 4,000 10.50 22" 698,333 698,333 N/A 33,826 2,500 10.50 23 1,092,908 1,198,229 105,321 9.6496 44,857 2,500 10.90 35,719,650 5,100,908 16.66% 1,038,041 80,000 250.80 Total 30,618,742 Store opened during year 2 Student Instructions for Trend Analysis 1. The planning expectation with simple trend analysis is that there will be no change from prior- year sales (predictor) in the current year. Auditors will consider materiality to be an 8 percent change from the prior year. First, examine only the aggregated data (totals) of sales in the planning stage of the audit. As there is not a high level of assurance for this stage, compare current year sales with prior year sales in a simple Excel table as follows: Current Year Prior Year Change % Change Next, eliminate the sales for the new stores opened for the year and compare the adjusted aggregated amount of current year sales compared to the prior year sales. Change % Change Current Year Prior Year Does this seem to make more sense - explain? The auditor must decide whether this percent change is acceptable for the stores open all year - what do you think? 2. Perform trend analysis as a substantive test on only stores that have been open all year. The expectation is that each store sales is predicted by its prior year sales. Use an 8 percent change materiality threshold for each store. From this test, list which stores should be investigated further. 3. What are some additional substantive procedures that might help to explain the differences? 4. Would management responses to your inquiry regarding the differences be sufficient to conclude that material misstatement does not exist? Why is this? Ratio Analysis Ratio analysis compares relationships between financial statement accounts, nonfinancial data, or a comparison of relationships across an industry. Here, background information, costs of goods sold, and sales in Exhibit A-1 provide data used in calculating gross margin (gross profit) percentages. Student Instructions for Ratio Analysis 1. Complete the following three tables by inserting appropriate numerical information and calculating gross margin percentages. Each table represents a store category-all stores, those that sell gas, and those that do not sell gas. Exclude new stores in each category. Expectation is that the gross profit percentage (predictor) for stores that sell gas will be higher than stores that do not sell gas. Ratio Analysis Current Year Prior Year Table 1 All Stores open all year (exclude new): Total sales Cost of goods sold Gross margin Gross margin percentage 21,463,700 21,987,932 Table 2 Stores that sell gas (exclude new): Total sales Cost of goods sold Gross margin Gross margin percentage 16,112,291 16,307,557 Table 3 Stores that do not sell gas (exclude new): Total sales Cost of goods sold Gross margin Gross margin percentage 5,351,409 5,680,375 2. Identify percent differences between current year and prior year gross margin percentages using the calculation (current year%-prior year%) /prior year% and insert this into Table 4. Assume that auditor judgment establishes an acceptable difference as a 10 percent increase. Show the differences for each category of store and determine whether there are unexpected differences that exceed the 10 percent threshold for each category by completing Table 4. Current Year % Prior Year % % Difference Table 4 Store Category All Stores open all year Stores that sell gas Stores that do not sell gas 3. What are some additional substantive procedures that might help to explain the differences? 4. Would management responses to your inquiry regarding the differences be sufficient to conclude that material misstatement does not exist? Why is this? Reasonableness Test A reasonableness test is an analysis of an account balance that involves developing an expectation based on financial data, nonfinancial data, or both. Here, background information, sales data per store (see Exhibit A-1), square footage per store (see Exhibit A-2) and the region's average sales per square foot (see Exhibit A-2) from the National Association of Convenience Stores (NACS) is provided. Student Instructions for Reasonableness Tests 1. Sales per square foot by store (predictor) is used in performing reasonableness tests of On the Go Stores. Before reasonableness testing begins, missing data for Exhibit A-2 is to be completed. Use Excel to help complete Exhibit A-2 and then paste the completed worksheet information to your solution document. Calculate the average sales per square foot and compare it with the region's average sales per square foot. Once this Exhibit is completed, proceed with step 2 where you will perform aggregate reasonableness testing. Exhibit A-2 Reasonableness Test Based on Sales per Square Foot Sales per NACS Current- Square Year Foot Ave, per Sales Square s Square ft. Difference Difference $ Feet Change 5 5 % Store 1 2,500 490 2 2,500 490 3 2,500 490 4 4,000 490 5 4,000 490 6 4,000 490 7 4,000 490 8 4,000 490 9 9 4,000 490 10* 2,500 490 11 2,500 490 12 2,500 490 13* 4,000 490 14 4,000 490 15 4,000 490 16 4,000 490 17 4,000 490 18 4,000 490 19 4,000 490 20 4,000 490 21 4,000 490 22* 2,500 490 23 2,500 490 80,000 490 Total Store opened during year 6 6 2. Perform an aggregate reasonableness test for stores open all year. This test would be appropriate in the planning stage of an audit where less precision is acceptable. Complete Table 5 for this test and use your judgment to interpret the results. Table 5 Reasonableness Testing - aggregate Sales Total Sq. Footage Total sales and square footage for the year Less: sales and square footage for stores opened part of the year (stores 1, 4, 10, 13, 22) Sales and square footage for stores open full year Average NACS sales per square foot x 490 Expected total sales for stores open full year Actual sales for the current year stores open full year) Difference in dollars Percentage Difference Using 15% as a reasonable materiality threshold, what can you determine from the completed table? 3. A higher level of assurance (more precision) is obtained for a second reasonableness test that could be used in the substantive testing phase by using disaggregated data as presented in Exhibit A-2. Using data for each store listed in Exhibit A-2, which stores show unexpected differences in excess of the 15% threshold and should be investigated further? 4. What are some additional substantive procedures that might help to explain the unexpected differences? 5. Would management responses to your inquiry regarding the differences be sufficient to conclude that material misstatement does not exist? Why is this? Regression Analysis The objective using regression analysis is similar to the other types of analytical procedures - to determine which stores should be investigated further for potential misstatement in sales. An advantage over the other types of analysis is that it provides for a more explicit and precise method in forming an expectation. This case illustrates cross-sectional regression because it uses relevant information about each store that act as predictors. Student Instructions for Regression Analysis 1. The objective is to examine sales analytically to determine potential for overstatement. A preliminary assessment of materiality is set as $150,000 for each store. 2. Using data from Exhibit A-3, the dependent variable (DV), is the amount of sales at each store. Next, independent variables (IVs), act as predictors of sales for each store. The five IVs acting as predictors are identified as follows: The amount of inventory at the store in dollars The number of staff at the store in full-time-equivalents Whether the store opened (O) or was not open (1) for the entire year- a binary number Whether the store does not (0) sell gas or does (1) sell gas - a binary number Square footage is 2500 square feet (0) or (1) if 4000 square feet - a binary number Exhibit A-3 Store Inventory s Employees New store Gas Size Sales 1 48,725 11.00 1 0 0 781,793 2 44,171 11.31 0 0 0 1,146,438 3 45,714 12.46 0 0 0 1,195,004 4 37,218 11.86 1 0 1 951,784 5 45,826 10.06 0 1 1 1,981,409 6 53,862 11.10 0 1 1 2,300,671 7 7 49,883 10.71 0 1 1 1,956,481 8 47,016 7.50 0 1 1 1,799,713 9 59,726 14.00 0 0 1 1,820,641 10 35,882 11.20 1 0 0 0 774,954 11 37,664 11.60 0 0 0 1,159,004 12 34,662 12.70 0 0 0 1,139,475 13 44,782 11.86 1 0 1 948,522 14 38,774 12.20 0 1 1 1,984,777 15 55,423 11.10 0 1 1 2,293,847 16 52,884 10.40 0 1 1 1,984,722 17 46,834 8.84 0 1 1 1,798,336 18 53,772 12.10 0 1 1 2,484,503 19 43,982 9.70 0 1 1 1,837,400 20 44,893 7.20 0 1 1 1,609,385 21 37,665 10.50 0 1 1 1,874,229 22 33,826 10.50 1 0 0 698,333 23 44,857 10.90 0 0 0 0 1,198,229 Totals 35,719,650 3. Next, using data from Exhibit A-3, an Excel regression analysis is presented. SUMMARY OUTPUT from this regression analysis is in Exhibit A-4. This output includes several regression statistics but the most important information is the Rsquared value of 0.975 and the standard error, $97,961. Additionally, RESIDUAL OUTPUT for all 23 stores with Predicted Sales (Expected Sales) in one column and Residuals (Differences) in another is shown. Exhibit A-4 SUMMARY OUTPUT 0.987 0.975 Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.967 97,951 23 ANOVA df SS MS F Significance 5.68001E-13 5 131.601901 Regression Residual 6.314E+12 1.631E+11 1.263E+12 9.596E+09 17 Total 22 6.478E+12 Standard Error t Stat P-value Lower 95% Upper 95% 244,813 0.007 (1,262,803) Intercept Inventory s Employees -3.048 4.504 (229,783) 24 4 0.000 9 Coefficients (745,293) 16.1179 105,114 (303 431) 804,866 93 247 17,725 0.000 68,717 5.987 -4.471 New store 67,863 94,751 77,838 (445,509) 604,959 Gas 143,511 (160,253) 1,004,773 0.000 0.000 0.247 8.495 Size 1.198 (70,976) 257,470 Exhibit A-4 continued RESIDUAL OUTPUT Observation Predicted soles S 1 902.875 Z Residuals (121.082) (19,363) (117,598) 49,873 23,453 3 1,165,801 1,312,702 901,911 1,957,946 2,197,829 2,092,311 4 5 6 102.842 7 (135,830) 94,238 8 1,705,475 1,795,209 9 25,432 10 717,095 57.859 11 12 13 67,310 (20,559) (75,305) (85.590) 70,858 14 15 1,091,694 1,150,034 1,023,827 2,071,357 2,222,989 2,107,785 1,844,734 2,302,492 1,890,024 1,639,423 16 15 17 (123,064) (45,398) 182,011 18 19 20 21 (52,624) (30,038) 1,131 88,656 54,878 1,873,098 509,677 1,133,351 35,719,650 22 23 0 Mathematically, the expectation formation for each store is as follows: Sales = (-$746,293) + 16.1179 x inventory + $106,114 x full-time employees - $303,431 x new store + $804,866 x sells gas +93,247 x size. To replicate this output in Excel for store 1, enter the following: =(-746293)+16.1179*48725+106114*11-303431*1+804866*0+93247*0 Pasting this formula to Excel proves the predicted sales of $902,875 for the first observation. The Excel software solves the equation for each store and presents the amounts under Predicted Sales. For the Residuals, a negative number means potential understatement and a positive number means potential over statement. Please note that the auditor is likely only concerned with overstatement of sales. Given data from Exhibit A-3 and the multiple regression output in Exhibit A-4, answer the following questions to the best of your ability (you may need to look up or review information from your statistics courses). Assume materiality threshold of $150,000 per store and a statistical significance (alpha) of .05 or less. 4. What does the RSquare value of 975 as presented in the Regression Statistics mean? 5. The Standard Error is $97,961. What does this indicate? 6. For Analysis of Variance (ANOVA), what does the Significance of the Fstatistic (F-test for the null hypothesis) indicate? 7. For the Coefficients output, what do the t statistic P values indicate? Are any of these insignificant? 8. In this step, you will replicate the regression analysis from step 3 except you will remove the Size variable to potentially improve the SE and t statistics of the four remaining independent variables. Your output should resemble Exhibit A-4, but it will not be exactly the same because you are using only four independent variables. First, copy and paste the information from Exhibit A-3 to an Excel worksheet with the upper left hand cell as A7 where the term Store will appear. In Excel, select the Data Tab and then Data Analysis. Choose Regression from the Data Analysis box and select OK. In this case, select G7:G30 as the range for the dependent variable input and B7:E30 as ranges for the independent variable input. Note that this range will exclude Size as an independent variable. Include in these ranges a row at the top of the workbook, which gives the name of the variable in each column so the regression output will label the variables properly. Select Labels in Excel and then select the output among the report options (in this case, cell A40). To calculate the residual amounts for each item, select the Residual's box. The regression for On the Go Stores should show output starting in cell A40 on your Excel worksheet and while similar to what appeared in Exhibit A-4 be aware the amounts will differ because you are excluding Size as a variable. Label your output Exhibit A-5, Removing Size as a Variable below. It works best to run the Excel regression, convert the output to 9 point font, format the numbers so they appear similar to Exhibit A-4, then copy and paste as a picture to this Word document after following the regression instructions. 9. Examining the regression SUMMARY OUTPUT in Exhibit A-5, what can you say about the statistical measures and is this preferred to statistical measures in Exhibit A-4? 10. As an auditor, what would you expect to do at this point given your understanding of the regression output and specifically, the RESIDUAL OUTPUT in Exhibit A-5? Analytical procedures are a process consisting of four phases: expectation formation, identification, investigation, and evaluation. The most important phase is the first - expectation formation - where the auditor develops expectations about an account balance or a plausible financial relationship. Auditors realize that numerical information of this nature is of little benefit without comparisons to benchmark expectations. Appropriate expectation methods range from simplistic to complex based on auditor judgment that considers the desired level of assurance for the procedure. Analytical procedures illustrated here include trend analysis, ratio analysis, reasonableness testing and regression analysis. This case demonstrates the use of analytical procedures in planning and substantive testing for a fictional chain of convenience stores named On the Go Stores. On the Go Stores has 23 convenience stores including five new stores (Stores 1, 4, 10, 13, and 22) that opened during the year. Operations vary by geographic location and the mix of products sold. The geographical placement of stores depends on several factors, such as competition and the economic environment of the location. Stores 2, 4, 6, 8, 9, 11, 13, 15, 17, 18, 20, 21, and 23 are considered to be in favorable locations. The mix of products and services at each store can vary depending on location and the most important factor is whether the store sells gasoline. Stores 5, 6, 7, 8, 14, 15, 16, 17, 18, 19, 20 and 21 sell gasoline. The mix of products offered for sale affects the volume of customers as well as the number of full-time employees. Trend Analysis Trend analysis considers changes in account balances over time and is most appropriate when the relationships are predictable. Therefore, the auditor must consider volatility of the business environment and corresponding effects on trends. Trend analysis might apply to both audit planning and for evidence gathering; therefore, high disaggregation of data is more likely in the substantive testing phase because of more precise expectation formation. On the Go Stores provides information shown in Exhibit A-1. Exhibit A-1 Relevant Information for On the Go Stores Current- Year Current- Year Prior-Year Dollar Sales Change Current Percent Change Inventory Sales Audited Store Average Number Employees Square Feet HA | 1 781,793 781,793 N/A 48,725 2,500 11.00 2 1,165, 221 1,146,438 (18,783) -1.61% 44,171 2,500 11.31 3 1,147,430 1,195,004 47,574 4.1596 45,714 2,500 12.46 4 951,784 951,784 N/A 37,218 4,000 11.86 5 2,037,463 1,981,409 (56,054) -2.75% 45,826 4,000 10.06 6 2,257,920 2,300,671 42,751 1.8996 53,862 4,000 11.10 7 1,850,354 1,956,481 106,127 5.7496 49,883 4,000 10.71 8 1,916,884 1,799,713 (117,171) -6.11% 47,016 4,000 7.50 9 1,833,209 1,820,641 (12,568) -0.69% 59,726 4,000 14.00 10* 774,954 774,954 N/A 35,882 2,500 11.20 11 980,484 1,159,004 178,520 18.21% 37,664 2,500 11.60 12 1,069,652 1,139,475 69,823 6.5396 34,662 2,500 12.70 13 948,522 948,522 N/A 44,782 4,000 11.86 14 1,795,123 1,984,777 189,654 10.56% 38,774 4,000 12.20 15 2,119,015 2,293,847 174,832 8.2596 55,423 4,000 11.10 16 1,947,303 1,984,722 37,419 1.9296 52,884 4,000 10.40 17 1,705,789 1,798,336 92,547 5.4396 46,834 4,000 8.84 18 2,396,971 2,484,503 87,532 3.6596 53,772 4,000 12.10 19 1,901,631 1,837,400 (54,231) -3.38% 43,982 4,000 9.70 20 1,514,798 1,609,385 94,587 6.2496 44,893 4,000 7.20 21 1,886,587 1,874,229 (12,358) -0.66% 37,665 4,000 10.50 22" 698,333 698,333 N/A 33,826 2,500 10.50 23 1,092,908 1,198,229 105,321 9.6496 44,857 2,500 10.90 35,719,650 5,100,908 16.66% 1,038,041 80,000 250.80 Total 30,618,742 Store opened during year 2 Student Instructions for Trend Analysis 1. The planning expectation with simple trend analysis is that there will be no change from prior- year sales (predictor) in the current year. Auditors will consider materiality to be an 8 percent change from the prior year. First, examine only the aggregated data (totals) of sales in the planning stage of the audit. As there is not a high level of assurance for this stage, compare current year sales with prior year sales in a simple Excel table as follows: Current Year Prior Year Change % Change Next, eliminate the sales for the new stores opened for the year and compare the adjusted aggregated amount of current year sales compared to the prior year sales. Change % Change Current Year Prior Year Does this seem to make more sense - explain? The auditor must decide whether this percent change is acceptable for the stores open all year - what do you think? 2. Perform trend analysis as a substantive test on only stores that have been open all year. The expectation is that each store sales is predicted by its prior year sales. Use an 8 percent change materiality threshold for each store. From this test, list which stores should be investigated further. 3. What are some additional substantive procedures that might help to explain the differences? 4. Would management responses to your inquiry regarding the differences be sufficient to conclude that material misstatement does not exist? Why is this? Ratio Analysis Ratio analysis compares relationships between financial statement accounts, nonfinancial data, or a comparison of relationships across an industry. Here, background information, costs of goods sold, and sales in Exhibit A-1 provide data used in calculating gross margin (gross profit) percentages. Student Instructions for Ratio Analysis 1. Complete the following three tables by inserting appropriate numerical information and calculating gross margin percentages. Each table represents a store category-all stores, those that sell gas, and those that do not sell gas. Exclude new stores in each category. Expectation is that the gross profit percentage (predictor) for stores that sell gas will be higher than stores that do not sell gas. Ratio Analysis Current Year Prior Year Table 1 All Stores open all year (exclude new): Total sales Cost of goods sold Gross margin Gross margin percentage 21,463,700 21,987,932 Table 2 Stores that sell gas (exclude new): Total sales Cost of goods sold Gross margin Gross margin percentage 16,112,291 16,307,557 Table 3 Stores that do not sell gas (exclude new): Total sales Cost of goods sold Gross margin Gross margin percentage 5,351,409 5,680,375 2. Identify percent differences between current year and prior year gross margin percentages using the calculation (current year%-prior year%) /prior year% and insert this into Table 4. Assume that auditor judgment establishes an acceptable difference as a 10 percent increase. Show the differences for each category of store and determine whether there are unexpected differences that exceed the 10 percent threshold for each category by completing Table 4. Current Year % Prior Year % % Difference Table 4 Store Category All Stores open all year Stores that sell gas Stores that do not sell gas 3. What are some additional substantive procedures that might help to explain the differences? 4. Would management responses to your inquiry regarding the differences be sufficient to conclude that material misstatement does not exist? Why is this? Reasonableness Test A reasonableness test is an analysis of an account balance that involves developing an expectation based on financial data, nonfinancial data, or both. Here, background information, sales data per store (see Exhibit A-1), square footage per store (see Exhibit A-2) and the region's average sales per square foot (see Exhibit A-2) from the National Association of Convenience Stores (NACS) is provided. Student Instructions for Reasonableness Tests 1. Sales per square foot by store (predictor) is used in performing reasonableness tests of On the Go Stores. Before reasonableness testing begins, missing data for Exhibit A-2 is to be completed. Use Excel to help complete Exhibit A-2 and then paste the completed worksheet information to your solution document. Calculate the average sales per square foot and compare it with the region's average sales per square foot. Once this Exhibit is completed, proceed with step 2 where you will perform aggregate reasonableness testing. Exhibit A-2 Reasonableness Test Based on Sales per Square Foot Sales per NACS Current- Square Year Foot Ave, per Sales Square s Square ft. Difference Difference $ Feet Change 5 5 % Store 1 2,500 490 2 2,500 490 3 2,500 490 4 4,000 490 5 4,000 490 6 4,000 490 7 4,000 490 8 4,000 490 9 9 4,000 490 10* 2,500 490 11 2,500 490 12 2,500 490 13* 4,000 490 14 4,000 490 15 4,000 490 16 4,000 490 17 4,000 490 18 4,000 490 19 4,000 490 20 4,000 490 21 4,000 490 22* 2,500 490 23 2,500 490 80,000 490 Total Store opened during year 6 6 2. Perform an aggregate reasonableness test for stores open all year. This test would be appropriate in the planning stage of an audit where less precision is acceptable. Complete Table 5 for this test and use your judgment to interpret the results. Table 5 Reasonableness Testing - aggregate Sales Total Sq. Footage Total sales and square footage for the year Less: sales and square footage for stores opened part of the year (stores 1, 4, 10, 13, 22) Sales and square footage for stores open full year Average NACS sales per square foot x 490 Expected total sales for stores open full year Actual sales for the current year stores open full year) Difference in dollars Percentage Difference Using 15% as a reasonable materiality threshold, what can you determine from the completed table? 3. A higher level of assurance (more precision) is obtained for a second reasonableness test that could be used in the substantive testing phase by using disaggregated data as presented in Exhibit A-2. Using data for each store listed in Exhibit A-2, which stores show unexpected differences in excess of the 15% threshold and should be investigated further? 4. What are some additional substantive procedures that might help to explain the unexpected differences? 5. Would management responses to your inquiry regarding the differences be sufficient to conclude that material misstatement does not exist? Why is this? Regression Analysis The objective using regression analysis is similar to the other types of analytical procedures - to determine which stores should be investigated further for potential misstatement in sales. An advantage over the other types of analysis is that it provides for a more explicit and precise method in forming an expectation. This case illustrates cross-sectional regression because it uses relevant information about each store that act as predictors. Student Instructions for Regression Analysis 1. The objective is to examine sales analytically to determine potential for overstatement. A preliminary assessment of materiality is set as $150,000 for each store. 2. Using data from Exhibit A-3, the dependent variable (DV), is the amount of sales at each store. Next, independent variables (IVs), act as predictors of sales for each store. The five IVs acting as predictors are identified as follows: The amount of inventory at the store in dollars The number of staff at the store in full-time-equivalents Whether the store opened (O) or was not open (1) for the entire year- a binary number Whether the store does not (0) sell gas or does (1) sell gas - a binary number Square footage is 2500 square feet (0) or (1) if 4000 square feet - a binary number Exhibit A-3 Store Inventory s Employees New store Gas Size Sales 1 48,725 11.00 1 0 0 781,793 2 44,171 11.31 0 0 0 1,146,438 3 45,714 12.46 0 0 0 1,195,004 4 37,218 11.86 1 0 1 951,784 5 45,826 10.06 0 1 1 1,981,409 6 53,862 11.10 0 1 1 2,300,671 7 7 49,883 10.71 0 1 1 1,956,481 8 47,016 7.50 0 1 1 1,799,713 9 59,726 14.00 0 0 1 1,820,641 10 35,882 11.20 1 0 0 0 774,954 11 37,664 11.60 0 0 0 1,159,004 12 34,662 12.70 0 0 0 1,139,475 13 44,782 11.86 1 0 1 948,522 14 38,774 12.20 0 1 1 1,984,777 15 55,423 11.10 0 1 1 2,293,847 16 52,884 10.40 0 1 1 1,984,722 17 46,834 8.84 0 1 1 1,798,336 18 53,772 12.10 0 1 1 2,484,503 19 43,982 9.70 0 1 1 1,837,400 20 44,893 7.20 0 1 1 1,609,385 21 37,665 10.50 0 1 1 1,874,229 22 33,826 10.50 1 0 0 698,333 23 44,857 10.90 0 0 0 0 1,198,229 Totals 35,719,650 3. Next, using data from Exhibit A-3, an Excel regression analysis is presented. SUMMARY OUTPUT from this regression analysis is in Exhibit A-4. This output includes several regression statistics but the most important information is the Rsquared value of 0.975 and the standard error, $97,961. Additionally, RESIDUAL OUTPUT for all 23 stores with Predicted Sales (Expected Sales) in one column and Residuals (Differences) in another is shown. Exhibit A-4 SUMMARY OUTPUT 0.987 0.975 Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.967 97,951 23 ANOVA df SS MS F Significance 5.68001E-13 5 131.601901 Regression Residual 6.314E+12 1.631E+11 1.263E+12 9.596E+09 17 Total 22 6.478E+12 Standard Error t Stat P-value Lower 95% Upper 95% 244,813 0.007 (1,262,803) Intercept Inventory s Employees -3.048 4.504 (229,783) 24 4 0.000 9 Coefficients (745,293) 16.1179 105,114 (303 431) 804,866 93 247 17,725 0.000 68,717 5.987 -4.471 New store 67,863 94,751 77,838 (445,509) 604,959 Gas 143,511 (160,253) 1,004,773 0.000 0.000 0.247 8.495 Size 1.198 (70,976) 257,470 Exhibit A-4 continued RESIDUAL OUTPUT Observation Predicted soles S 1 902.875 Z Residuals (121.082) (19,363) (117,598) 49,873 23,453 3 1,165,801 1,312,702 901,911 1,957,946 2,197,829 2,092,311 4 5 6 102.842 7 (135,830) 94,238 8 1,705,475 1,795,209 9 25,432 10 717,095 57.859 11 12 13 67,310 (20,559) (75,305) (85.590) 70,858 14 15 1,091,694 1,150,034 1,023,827 2,071,357 2,222,989 2,107,785 1,844,734 2,302,492 1,890,024 1,639,423 16 15 17 (123,064) (45,398) 182,011 18 19 20 21 (52,624) (30,038) 1,131 88,656 54,878 1,873,098 509,677 1,133,351 35,719,650 22 23 0 Mathematically, the expectation formation for each store is as follows: Sales = (-$746,293) + 16.1179 x inventory + $106,114 x full-time employees - $303,431 x new store + $804,866 x sells gas +93,247 x size. To replicate this output in Excel for store 1, enter the following: =(-746293)+16.1179*48725+106114*11-303431*1+804866*0+93247*0 Pasting this formula to Excel proves the predicted sales of $902,875 for the first observation. The Excel software solves the equation for each store and presents the amounts under Predicted Sales. For the Residuals, a negative number means potential understatement and a positive number means potential over statement. Please note that the auditor is likely only concerned with overstatement of sales. Given data from Exhibit A-3 and the multiple regression output in Exhibit A-4, answer the following questions to the best of your ability (you may need to look up or review information from your statistics courses). Assume materiality threshold of $150,000 per store and a statistical significance (alpha) of .05 or less. 4. What does the RSquare value of 975 as presented in the Regression Statistics mean? 5. The Standard Error is $97,961. What does this indicate? 6. For Analysis of Variance (ANOVA), what does the Significance of the Fstatistic (F-test for the null hypothesis) indicate? 7. For the Coefficients output, what do the t statistic P values indicate? Are any of these insignificant? 8. In this step, you will replicate the regression analysis from step 3 except you will remove the Size variable to potentially improve the SE and t statistics of the four remaining independent variables. Your output should resemble Exhibit A-4, but it will not be exactly the same because you are using only four independent variables. First, copy and paste the information from Exhibit A-3 to an Excel worksheet with the upper left hand cell as A7 where the term Store will appear. In Excel, select the Data Tab and then Data Analysis. Choose Regression from the Data Analysis box and select OK. In this case, select G7:G30 as the range for the dependent variable input and B7:E30 as ranges for the independent variable input. Note that this range will exclude Size as an independent variable. Include in these ranges a row at the top of the workbook, which gives the name of the variable in each column so the regression output will label the variables properly. Select Labels in Excel and then select the output among the report options (in this case, cell A40). To calculate the residual amounts for each item, select the Residual's box. The regression for On the Go Stores should show output starting in cell A40 on your Excel worksheet and while similar to what appeared in Exhibit A-4 be aware the amounts will differ because you are excluding Size as a variable. Label your output Exhibit A-5, Removing Size as a Variable below. It works best to run the Excel regression, convert the output to 9 point font, format the numbers so they appear similar to Exhibit A-4, then copy and paste as a picture to this Word document after following the regression instructions. 9. Examining the regression SUMMARY OUTPUT in Exhibit A-5, what can you say about the statistical measures and is this preferred to statistical measures in Exhibit A-4? 10. As an auditor, what would you expect to do at this point given your understanding of the regression output and specifically, the RESIDUAL OUTPUT in Exhibit A-5