This is an auditing project, it contains excel regression (make sure you have the data analysis function in excel) and 1)you have to post the stores to yourSummary Table(which is on the last page of project instructions), 2)aword write-upexplain how you selected the stores. (Combine the results of your entire analysis to select stores for investigation and discuss how you selected them. That is, don't just rely on any one of the above methods, unless you really have faith in it
A B C D E F G H Square Average P Stores 2019 Sales 2018 Sales 2019 Inventory Feet Employees Sells Gas New Store 1,289,570 43,886 3,929 11.62 0 V OUT A W N 2,000,298 1,850,800 58,531 3,931 13.72 2,176,930 2,057,014 44,909 3,929 9.86 o O 2,306,624 1,904,691 36,912 3,935 10.29 0 1,259,566 1,176,402 43,288 2,453 11.08 O O 1,977,305 1,935,278 46,076 3,931 7.35 1,312,925 1,158,441 44,800 2,448 12.21 00 2,729,669 2,419,972 52,697 3,930 11.86 O 767,243 33,149 2,453 10.29 10 2,149,543 1,868,110 48,885 3,931 10.50 N 11 858,939 47,751 2,457 10.78 13 12 2,520,199 2,139,349 54,315 3,931 10.88 14 13 1,316,468 1,103,396 43,960 2,449 10.68 15 14 2,527,697 2,279,587 52,785 3,930 10.88 16 15 1,045,704 36,474 3,930 11.62 17 16 2,018,711 1,919,879 43,102 3,934 9.51 18 17 1,768,196 1,529,334 43,995 3,939 7.06 19 18 851,425 35,164 2,451 10.98 20 19 1,975,792 1,722,158 45,897 3,932 8.66 20 2,180,570 1,965,989 51,826 3,929 10.19 22 21 1,273,372 989,893 36,911 2,451 11.37 O 23 22 1,499,366 1,079,916 33,969 2,452 12.45 O 24 23 2,180,631 1,812,349 37,999 3,930 11.96 O OO OO HOO HOO O HO HOOC 25Your audit client, Circle L, has 23 convenience stores located in the southwest region of the United States; information on the stores is summarized in an Excel le that you need titled Circle L Data Fall 2020. In addition, there is a Word le titled Getting Started With Excel Regression in case you need help installing regression and getting going. Also, the following support is available: Abbreviations on Excel le: 2019 Sales Unaudited 2019 totals. 2018 Sales Audited 2018 totals. 2019 InventoryUnaudited 2019 Inventory. Square FeetSquare feet of the store. Average EmployeesAverage number of employees in store during 2019. Sells GasSome stores sell gasoline (1=Store sells gasoline; 0=Store does not sell gas) New StoreStore rst opened this year (1= yes; 0=no) The partner in charge of the audit (Will B. Slow) suggested that while audit procedures are applied to sales and inventory of each store each year, his audit approach in the past has been to select eight stores to examine in \"greater detail\" than the rest. He says that each year he has just judgmentally selected the eight, but that he hired you in part because of your analytical skills and because he wants you to use trend analysis, reasonableness tests, and regression to help in selection of stores; he suggests that maybe your skills will even help him create a more efcient audit by examining less stores. He wants you to apply a more sophisticated \"risk based\" assessment approach than he has performed to select which stores to examine in further detail. While the \"dependent\" variables to be used in selecting stores for further analysis could be either 2019 sales or 2019 inventory, he wants you to use only 2019 sales to simplify your task. In the past the audit has periodically turned up big overstatements in sales (sometimes up to three or four stores with about $250,000 each) and much less 'equently large understatements of sales. He suggests and you agree, that based on his analysis of the risks of misstatement and the results of tests of controls performed, that you need not perform additional procedures related to t t m n f l thi ar You then discuss the client's year with its controller, Wilson Mm. He raises the following points: I 5 new stores were openedall on July 1. While things have "pretty much gone as expected" with them, Wilson points out that store 18 has been a problem as the manager had to be replaced after two weeks due to his fears arising due to a store robbery the opening night. I He pointed out that several stores are close to a 'eeway entrance/exit (# 2, 5, 12, 17, and 19). a Total revenues from gas sales have decreased due to a_n decrease in the sales price per gallon as compared to the preceding year. Required: NOTE: Each student should perform all of the Excel computations required below so as to assure familiarity with the Excel features. (We consider spreadsheet skills important, and practice such as this may help.) It is important that when the project is complete that you understand the techniques used, including interpretation of Excel output. Important often translates to questions on the exam. Throughout, as indicated earlier, we will emphasize stores with potentially overstated sales. In an actual audit m we would consider the risks of misstatement and determine whether overstatements, understatements, or both are signicant risks. This project should be submitted on Canvas by the SAM on the assigned day. There are two deliverablesa WORD write-up and a WORD summary table (such as that on the last page of this document). The write-up and summary table should be combined and turned in as one document. A 10% penalty per day is given for late projects. Perform the following types of analyses and summarize your results in a report to Slow. This report should be comprehensible to a guy like him who doesn't know much about anything. 1. Judgmental selection. Based on your discussion with Wilson Wilsen, and simply looking over the spreadsheet for other information that looks interesting, judgmentally select six stores to examine in greater detail than the others. Remember what you are trying to identify (overstatement, understatement, etc) Very briey mention why you selected each store in your Word write-up. Post the six stores to your Summary Table (that is, place an \"X\" next to six stores in the \"Step 1 J udg.\" Column and leave the other 17 stores blank). Trend analysis For this part ignore the new stores. Add a \"%CHG\" percentage column to the spreadsheet and calculate increases (decreases) in store sales by year and for the total of the year. Format as percentages with two decimal placesThat is, a decimal of .0233 would be 2.33%. Sort to get the six stores with the largest increase in sales percentage and cut and paste the store number and percentage sales increase for those six stores (from high to low) in your Word write-up. Record the six stores in your Summary Table. Reasonableness test Assume that the National Association of Convenience Stores publishes information on stores in that industry and that it shows that convenience stores average approximately $540 of sales per square foot. For simplicity sake, assume that the five new stores were opened on July 1. Slow suggests that while sales of continuing stores occur fairly evenly throughout the year, when a new store opens its sales may be particularly good or bad for the rst year or so. a) b) Add an \"OVER $540 AMT\" column to the spreadsheet and in that column calculate a number that is current year sales divided by square feet minus 540 [(2019 sales / sq. feet) - 540]. This gure represents the difference between a store's sales per square foot and $540, the industry average. For example, a positive $25 means the store had $565 of sales per square foot ($25 over the $540 industry average). Make an appropriate adjustment to the formula for stores open only half of the year (e.g., double sales in the above formulabut not on the spreadsheet in the recorded sales for 2019 column). Although you may supplement the data provided on the spreadsheet (e.g., with the Over $540 AMT column), don't change any of the data provided or regressions will not be based on the appropriate data. Cut and paste to your Word write-up the store # and OVER $540 AMT for the six stores with the highest level of sales over $540 per square foot. Post the stores to your Surmnary Table. Provide in your Word write-up the formula for stores open only half of the year. 4. Regression We 've been assured that you 've had simple regression in a previous course, but that you may not have had multiple regression. The only change for multiple regression when using Excel is to use more than one \"independent \" variable to predict the one dependent variable. You can handle it, and we will talk about what the output means in class. The key is to nd stores with possibly overstated 2019 sales using whatever legitimate means possible. Note: Make certain that whenever you run a regression in your Excel le the stores are in the original numerical order (i.e., store I, followed by store 2%). Bad things happen if you don 't do this. Many points may be lost. a) In the past, as might be expected, previous year sales (here, 2018 Sales) have been a good predictor of current year sales. Run a regression with 2018 Sales as the independent variable (Excel calls this the \"X Range\") and 2019 sales as the dependent variable (Excel calls this the \"Y Range\"). (N oteuse all 23 stores in your regression analysis even though some of the stores are new stores.) i) For this and other regressions, place a check in \"Residuals.\" When you get the output, sort the residuals by size (descending), cut and paste to your Word write-up the stores with the six largest positive residuals (sales higher than expected by the regression model). The output should have u ,5 three columns (I formatted the last two columns , to make them easier to read): For this and other regressions, place a check in \"Residuals.\" When you get the output, sort the residuals by size (descending), cut and paste to your Word write-up the stores with the six largest positive residuals (salu higher than expected by the regression model). The output should have \" 9, three columns (I formatted the last two columns , to make them easier read): Predicted Observation 2019 Sales Residuals 21 779,375.48 430,528.52 5 2,264,638.70 249,678.33 Also provide the table of results which include the intercept and coefcients etc. for the independent variables (the table right above the \"RESIDUAL OUTPUT\" table). Post your six stores to your summary tablethis is R1 Document the next 2 steps in the WORD write-up. ii) For Store 10, manually calculate the "Predicted 2019 Sales" Intercept Coefficient + (2018 Sales Coefficient) (2018 Store #10 Sales) Compare your Predicted 2019 Sales for Store 10 to those of Excel. Include your computation in your Word write-up. iii) For Store 10, calculate the residual and compare it to Excel's residual. Include your computation in your Word write-up. Include a full page figure in the WORD write-up. iv) For the regression results plot a figure using your preferred visualization software (e.g., Excel, Tableau, Stata). Include data points, and the line plotted by your regression output. Also highlight at least 5 of the stores that are identified as having overstated sales based on this regression. Tip: this should be easy as they can generally be found in two clusters. Ensure that the figure is clearly labeled with the values you are plotting (Actual Sales for 2019 or 2018, Predicted Sales for 2019, Residuals, Store Numbers, etc). Do NOT plot only Store numbers and residuals. V ) Provide a short paragraph describing your observations of the figure you have generated that you would want to communicate to your manager.b) Run a multiple regression (this is R2 on the summary table) using 2019 Sales as the same dependent variable (it remains the dependent variable in all of your analyses), but include independent variables of (1) 2018 Sales, (2) Square Feet and (3) Close to Freeway [which you need to code in as a new independent variable with a 1 for stores 1, 12, 18, 21, and 23 and a 0 for the others]. When you run multiple regression with Excel the independent variables must be next to one another on the spreadsheet. i) Cut and paste to your Word write-up as per 4. a) i) above the largest six residuals. Also provide the table of results which include the intercept and coefcients etc. for the independent variables (the table right above the \"RESIDUAL OUTPUT\" table). Add the six stores with the largest positive residuals to your Sunnnary Table. ii) Which independent measure is the best predictor? How can you tell? iii) Which is (are) the worst? How can you tell? 1V) For the regression results plot a figure using your preferred visualization software (e.g., Excel, Tableau, Stata). This will be more challenging than the first regression, given this has multiple independent variables. Highlight at least 5 of the stores that are identified as having overstated sales based on this regression. Tip: Take time to consider which information would ease a manager's ability to understand the data. Ensure that the figure is clearly labeled with the values you are plotting (Actual Sales for 2019 or 2018, Predicted Sales for 2019, Residuals, Store Numbers, Close to Freeway, Square Feet). Do NOT plot only Store numbers and residuals. Remember you are plotting the full model output, not just a single predictor. V) Provide a short paragraph describing your observations of the figure you have generated that you would want to communicate to your manager. c) Run other regression(s) that you believe might be helpful in determining which stores to investigate in further detail. For this part, you need to document in your Word write-up which variables are in each model that you have utilized. You need only include the residual output (top six) from two of these additional regressions in your Word write-up (but can include more as to test consistency of results, etc., you probably want to run more). Be thoughtful in the variables you select. Do not exclude variables that the previous regressions have shown to be important predictors of 2019 Sales. Post your stores selected by the various regressions in R3 and R4 in your Summary Table. d) Select six stores based on all the regression analyses (ONLY) work that "look interesting," again emphasizing possible overstated sales-add this to the ROVERALL column of your Summary Table. In your Word write-up explain how you selected the stores based on all the regressions you ran.Overall Combine the results of your entire analysis (gegressions and other approaches} to select stores for investigation and discuss how you selected them. That is, don't just rely on any one of the above methods, unless you really have faith in it. Record the stores you select in the last column of the summary table. You should select 4 stores. Deciding which stores to investigate in detail is very important since the additional procedures you follow for those stores, while costly, will likely nd misstatements Yet, examining them all in detail is cost prohibitive. Points are allotted for the overall accuracy and the quality of the write-up. The analysis should be easy for a person to follow, There are three stores that actually have large overstated sales. (Place "X" in the top 6 for all columns except Decision where only 4 are selected) + Step 1 Step 2 Step 3 Store Step 4 Judgment Trend Reas. Step 5 R1 R2 R3 R4 Roxerall Decision 2 w A 5 8 0 10 11 12 13 14 15 16 17 18 19 20 21 22 23 O
