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. Record the stores you select in the last column of the summary table.You should select 4 stores.)

Circle L Project -20 points Your 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 file that you need titled Circle L Data Fall 2016. In addition, there is a Word file 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 file: 20X4 Sales Unaudited 20X4 totals. 20X3 Sales Audited 20X3 totals. 20X4 InventoryUnaudited 20X4 Inventory. Square FeetSquare feet of the store. Average EmployeesAverage number of employees in store during 20X4. Sells GasSome stores sell gasoline (1=Store sells gasoline; 0=Store does not sell gas) New StoreStore first 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 efficient 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 20X4 sales or 20X4 inventory, he wants you to use only 20X4 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 frequently 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 understatements of sales this year. You then discuss the client's year with its controller, Wilson Wilsen. He raises the following points: 5 new stores were openedall on July 1. While things have "pretty much gone as expected" with them, Wilson points out that store 10 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. He pointed out that several stores are close to a freeway entrance/exit (#5, 6, 11, 12, and 19). Total revenues from gas sales have increased due to an increase in the sales price per gallon as compared to the preceding year. When you asked him "Do you think this company has a problem with fraud?" he said, "Not really, we've been able to hire honest people. Our problem is more with managers who sometimes don't work hard enough. For example, notice how bad the sales were for store 7 in 20X3. We told Bill (the store manager) that he needed to 'shape up, or ship out.' He certainly got the messagecheck out the increase in his store's sales. Finally, Wilson commented: \"The economy looks good for convenience stores and a time may come when we will need more helpdo you think you would be interested in making the change from public accounting?\" 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 application we would consider the risks of misstatement and determine whether overstatements, understatements, or both are significant risks. This is a GROUP PROJECT. Ideally, you should work in groups of three (or four). This project should be in hard copy form and turned in during class 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 stapled together and turned in as one document. Projects turned in with another format (e.g., e-mail) will be penalized 2 points. In addition, 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. Very briefly 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 Judg.\" Column and leave the other 17 stores blank). 2. 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. Also record the six stores in your Summary Table. 3. 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 first year or so. a) 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 [(20X4 sales / sq. feet) - 540]. This figure 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 $565). 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 20X4 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 bad things may happen. 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 Summary Table. b) 4. Provide in your Word write-up the formula for stores open only half of the year. 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 find stores with possibly overstated 20X4 sales using whatever legitimate means possible. Note: Make certain that whenever you run a regression in your Excel file the stores are in the original numerical order (i.e., store 1, 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, 20X3 Sales) have been a good predictor of current year sales. Run a regression with 20X3 Sales as the independent variable (Excel calls this the \"X Range\") and 20X4 sales as the dependent variable (Excel calls this the \"Y Range\"). (Noteuse 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 three columns (I formatted the last two columns \Stores 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 20X4 Sales 1,997,681 1,326,454 2,203,041 1,286,494 1,272,546 1,996,153 1,330,034 2,553,745 1,056,480 1,302,859 2,020,912 1,986,417 2,199,364 2,330,394 860,199 2,171,694 867,790 2,039,514 2,546,170 1,514,817 2,203,102 2,757,798 775,150 20X3 Sales 1,955,222 1,170,379 1,986,249 1,000,094 1,188,525 1,739,905 1,114,766 2,303,078 1,869,873 1,545,094 2,078,212 1,924,319 1,887,361 1,939,664 2,161,395 1,091,045 1,831,025 2,444,910 - Square Feet 4,011 2,498 4,009 2,501 2,503 4,012 2,499 4,010 4,010 4,009 4,011 4,019 4,009 4,015 2,501 4,011 2,507 4,014 4,011 2,502 4,010 4,010 2,304 Close to Average freeway New Store 20X4 Inventory Employees 0 0 0 0 1 1 0 0 0 0 1 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 1 0 1 0 0 0 0 0 1 47,016 45,714 52,884 37,664 44,171 46,834 44,857 53,862 37,218 44,782 59,726 44,893 45,826 37,665 35,882 49,883 48,725 43,982 55,423 34,662 38,774 53,772 33,826 7.50 12.46 10.40 11.60 11.31 8.84 10.90 11.10 11.86 11.86 14.00 7.20 10.06 10.50 11.20 10.71 11.00 9.70 11.10 12.70 12.20 12.10 10.50 Sells Gas 1 0 1 0 0 1 0 1 0 0 1 1 1 1 1 1 0 1 1 0 1 1 1 Circle L Case 1 Circle L Case The following items are needed (1 and 2) or helpful (3) for this case: 1. Assignment itself (Word File) 2. Data on the stores (Excel file) 3. This presentation (PowerPoint file). - We will go through quickly in class; it should be a helpful guide when you are doing the work. 2 Circle L Case - Analysis Partner has asked you to use analytical tools to identify 5 stores to examine in \"greater detail\". Prior audits have found large overstatements in sales (3 to 4 stores with errors of $250,000 and over) and the partner believes the same overstatements are likely again this year. First step should be a discussion with the client controller regarding the current status of the company and the stores. This is in the Word assignment itself. Next step is to perform analytical procedures on the store data. Important to use both financial and nonfinancial information in your analysis. 3 1. Judgmental Selection OverallSelect 5 stores, much like Will B. Slow has evidently done in the past. - Consider what Wilson Wilsen said. - \"Eyeball\" data. - Maybe pick one or two randomly. 4 2. Trend Analysis Analysis of changes in an account balance over time (Current Sales - Last Year's Sales) / Last Year's Sales Select the 5 stores with the largest positive changes 5 3. Reasonableness Test Analysis of account balances that involves the development of an expectation based on financial and/or nonfinancial data Industry average of sales per square foot (Current Sales / Sq. Feet) - industry average Must adjust equation for new stores, but don't change sales numbers in spreadsheet (just use a formula to fix for the fact a store is only open year). Select 5 stores 6 4. RegressionWhat are the main things you need to understand? R2 (coefficient of determination) Dependent variable (the Y variable) Intercept Coefficient Independent variables (the X variable(s)) and their coefficient(s) t statistic P-value Residual 7 Regression Regression OutputPretend OutputPretend Numbers Numbers 8 R 2 R-Square (R2 [Coefficient of Determination]): This is what we primarily use to help evaluate the overall model (the particular set of \"independent\" variables you have included to predict the dependent variable, sales). Because it is \"R\" squared, it is always positive. R-Square measures how good the fit is of using the model you are testing (see first bullet) in predicting the recorded values of the dependent measure in the sample. Unfortunately, that doesn't mean the higher it is, the better it is at finding errors. This is because a big \"residual\" isn't necessarily an error in the book valuethe store may just have done better or worse than expected. Remember, the model doesn't know the location of the errors. The closer the coefficient is to 1 (i.e. 100% fit), the better the fit with the recorded value of sales. A.K.A \"Goodness of Fit\" 9 Regression Regression OutputPretend OutputPretend Numbers NumbersAgain Again 10 Dependent variable (Y variable) This is the variable you are trying to predict. In our assignment it is the current year sales for each store. 11 Intercept Coefficient The \"fixed\" coefficient In our regression, predicting current year sales, the intercept is the amount of sales there would be if there were zero of the X Variable(s). This is often a number with a questionable meaning. For example if the intercept were $10,243 it is saying that if sales last year were zero, sales this year would be $10,243. This admittedly doesn't make a lot of sense, which is usually the case for the intercept coefficient. 12 Independent Variables and Coefficients The X variable(s)used to predict the dependent variable (the Y variable) The X Variable Coefficient is used in the prediction of each year's store's sales. In our example, the independent variables are last year's sales, square feet and average employees. We are using those three independent variables to predict this year's sales Meaning of an independent variable coefficient: For example, if the coefficient is 271, that means for each increase of 1 unit of the independent variable, the dependent variable goes up by 271. In our example, if Square Feet goes up 1, sales goes up 271. 13 Other useful output (for coefficients) t Stat: indicates whether the independent variable has a statistical relationship with sales. tests the hypothesis that the parameters are not zero. The higher the absolute value of t, the more significant. (A negative value means an inverse relationship between the independent and dependent variables.) For example, if the t stat for square feet is 5.68, it is significant, since it greater than 2 or so. The P-value gives you a more precise estimate of how significant the independent variable is. 14 Other useful output (for coefficients) P-value: For example, assume that the p. value for Square Feet is about .03. This means that Square Feet is a statistically significant predictor of sales in our model. Usually, anything less than .05 is considered a significant contributor. P values are always positive. It is the statistical conclusion related to the t statistic. 15 Calculating by hand the regression's prediction of sales You should be able to calculate the predicted current year sales if given intercept and independent variable coefficients. For example, for any store you would predict sales as follows: Intercept + (coefficient) (the related independent variable) 10.243 + .474 (20X3 sales) + 271.162 (Square Feet of Store) + 2.134 (Average Employees) But you can't complete the calculation here since you don't have the needed information for the stores (i.e., 20X3 sales, Square Feet of Store, Average Employees). 16 Residuals ASSUME (PRETEND NUMBERS) Actual sales from store data= $ 1,987,177 Predicted sales from model = $ 1,815,455 Difference is residual amount = $ 171,722 The positive residual means the regression model predicts a lower level of sales than the actual number - a potential overstatement of sales. We are primarily looking for this sort of thing. 17 Residuals (assume there are only 8 stores)Excel Output RESIDUAL OUTPUT Observation Predicted 20X4 Residuals 1 686796.9 94996.12 2 1246556 -100118 3 1229932 -34928.2 4 1095082 -143298.4 5 2059514 -78105.37 6 2163868 136803.4 7 1970946 -14465.43 8 2002438 202725.4 18 Residuals (assume there are only 8 stores) Sorted, Formatted Excel Output Observation Predicted 20X4 Residuals 6 2,163,868 136,803.40 1 686796.90 94,996.12 7 1970946.43 -14,465.43 3 1229932.20 -34,928.20 5 2059514.37 -78,105.37 2 1246556.03 -100,118.03 4 1095082.45 -143,298.45 8 2002438.37 -202,725.37 19 Questions 1 1. What are 20X4 recorded sales for store 6? 2. Which store is most likely to have overstated sales? 3. Which store is most likely to have understated sales? 20 Regressionoverall summary Instructions for performing regression Referring to the example (average employees) - R2 is the overall measure of a model's ability to explain variation in the dependent measure - the p-value and T-stat are measures of the independent variables contribution to the model - the lower the p-value, and the higher the /t-stat/, the better the predictor - General rule, want t-stat > 2 and p-value <.05 but if an independent variable isn .05 logically seems like it should be included probably include it. regression outputpretend numbers numbersagain again more questions which is most significant you were going to drop one from the model based on alone would using this how much sales increase or decrease a store increased by square foot crazy idea regression--overall develops explicit prediction auditor knowledge of factors that affect account balances develop balance. coefficients can used predict values negative indicate inverse relationship with dependent measure other predictors in part assignment run various multiple regressions than variables are must next each excel spreadsheet answer following questions: best worst tell do use r2 tradeoffyou want high limited number ultimately select stores. combine all your results determine stores think have risk material misstatement. answers has largest positive residual highest t-stat measures and lowest p-value could either question average employees because insignificant value coefficient getting started wish work at home will find installation process did not make possible for tool. way out whether appropriate tools go menu see analysis last top group data tab attempt through regression. works good shape. microsoft office button maybe disk enable function. pages walk necessary procedures. key remember need adjacent another. move columns accomplish this. among things means: before after variables. b say three another their them adjacent. statistics book our class help interpret results. also function provides some interpreting output. install get into excel. then click file left corner. options. add-ins manage box add-ins. go. available toolpak check ok. tip listed browse locate prompted currently installed computer yes load command tab. window. tips inputting parameters. input y rangethis predicting haven changed format b1:b21. x ones being only coded as c1:c21. inventory feet step judgment trend reas. r1 r3 r4 roverall decision close freeway new change sells gas over amt summary output r adjusted standard error observations anova total df intercept ss ms f significance observation predicted residuals t stat lower upper e observatio coefficientsstandard fee em27072.7802>