5 step project, In the first step you need a simple explanation of the selected store, in the fourth step, you need to explain and answer the questions in detail.Power Points can help better understand the problemPlease read the requirements of the electronic document carefully, there are detailed explanations above, all work is done in excel, to receive the use of multiple regression, know how to use and understand R square, t test, and so on.
Unformatted Attachment Preview
Analytical Procedures Project
Situation – Audit of a Retail Co.
• Background – Assignment Handout
• You have been assigned to use various tools to identify
4 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-in-charge believes the same level of
overstatements are likely again this year.
• Your first step is a discussion with the client controller
regarding the current status of the company and the
stores. This is in the assignment handout.
• Next step is to perform analytical procedures on the
• Your analysis should use both financial and non-financial
1. Judgmental Selection
• Overall—Select 6 stores, much like what
has evidently done in the past.
– Consider controller comments.
– “Eyeball” data.
– Maybe pick one or two randomly – document
basis in your report. Why pick it
2. Trend Analysis
• Analysis of changes in an account balance
• (Current Sales – Last Year’s Sales) / Last
• Select the 6 stores with the largest positive
3. Reasonableness Test
• Analysis of account balances that involves the
development of an expectation based on
financial and/or nonfinancial data
• Industry average is $560 of sales per square
• (Current Sales / Sq. Feet) – 560
• Must adjust equation for new stores
• Select 6 stores
4. Regression Analysis
R2 (coefficient of determination)
Dependent variable (the Y variable)
Independent variables (the X variable(s)) and
• t statistic and interpretation
• P –value and interpretation
Sample Regression Output
Adjusted R. Square
ASSUME WE ARE PREDICTING 2018 SALES (THE “DEPENDENT VARIABLE”)
R-Square (R2 [Coefficient of Determination]):
• This is what we primarily use to help evaluate the overall model (the
particular set of “independent” variables used to predict the
dependent variable, sales).
• Because it is “R” squared, it is always positive.
• Measures “goodness of fit” of the model in predicting the value of
the dependent measure(s) in the sample.
• The closer the coefficient is to 1 (i.e. 100% fit), the better the fit with
the recorded value of sales.
• 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 value—the store may just have done better or
worse than expected.
• Remember, the model doesn’t know the location of the errors.
Dependent variable (Y variable)
• This is the variable you are trying to
• In the project it is the current year sales for
each store for the first regression.
• The “fixed” coefficient
• In our regression, predicting current year sales, the
intercept is the amount of sales there would be if the
value(s) of the X Variable(s) were 0.
• 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.
Independent Variables and
• 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 the example, the independent variables are:
– last year’s sales
– square feet
– average employees
• Meaning of each independent variable coefficient:
• 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.
Significance of Regression results
• indicates whether the independent variable has
a significant statistical relationship with sales.
• tests the hypothesis that the parameters are not
• 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 provides a more precise estimate of
the significance of an independent variable.
Significance of Regression results
• 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
• It is another statistical conclusion related
to the t statistic.
Calculating the Regression Model
Prediction of Sales
• From the regression model results,
calculate the predicted current year sales
given intercept and independent variable
• For example, for any store:
Intercept + (coefficient) (the related independent variable)
10,243 + .474 (2017 sales) + 271.162 (Square Feet of Store) + 2.134
• Model residuals provide the result
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.
Residuals (with only 8 stores)—
Residuals (only 8 stores)—Sorted,
Formatted Excel Output
1. What are 2018 recorded sales for store
2. Which store is most likely to have
3. Which store is most likely to have
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’t .05, but logically seems like it should be included, probably include it. 19 Sample Regression Output Observations 23 ANOVA Regression Residual Total df 1 22 23 SS 29903853.98 4749346.02 34653200 MS 5.39+10 593668.2526 F 88.11 Coefficient Standard Error t-Stat P-value Intercept 10,243 142493 0.07 0.94 2017 Sales 0.474 0.04 11.03 0.01 Square Feet 271.162 47.74 5.68 0.03 Average Employees 2.134 5.22 1.23 0.40 ASSUME WE ARE PREDICTING 2018 SALES (THE "DEPENDENT VARIABLE") 20 More Questions 4. Which independent variable is most significant? 5. If you were going to drop one independent variable from the model based on numbers alone, which would you drop? 6. Using this model, how much would sales increase or decrease if a store increased by 1 square foot (admittedly a crazy idea)? 21 Regression--overall (cont) • Develops an explicit prediction using the auditor’s knowledge of the factors that affect the account balances to develop a model of the account balance. • The coefficients can be used to predict values • Negative coefficients indicate an inverse relationship with the dependent measure (given the other predictors in the model) 22 Regression #4b,c • Run various multiple regressions using more than one independent variable • The independent variables that you are using must be next to each other in an Excel spreadsheet • Answer the following questions: – Which are the best predictors? – Which are the worst? – How can you tell? • Tradeoff—You want high R2, with limited number of variables • Ultimately select 6 stores 23 5. Selection • Select 4 stores based on the results of your analytical procedures. 24 Store # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 2018 Sales 2017 Sales $1,302,859 $2,546,170 $1,272,546 $1,996,153 $1,330,034 $2,553,745 $1,056,480 $1,997,681 $1,326,454 $2,203,041 $1,286,494 $1,514,817 $2,020,912 $2,199,364 $2,330,394 $1,786,417 $860,199 $2,039,514 $2,203,102 $2,757,798 $775,150 $2,171,694 $867,790 $0 $2,161,395 $1,188,525 $1,739,905 $1,114,766 $2,303,078 $0 $1,955,222 $1,170,379 $1,986,249 $1,000,094 $1,091,045 $1,869,873 $2,078,212 $1,924,319 $1,545,094 $0 $1,939,664 $1,831,025 $2,444,910 $0 $1,887,361 $0 2018 Inventory $44,782 $55,423 $44,171 $46,834 $44,857 $53,862 $37,218 $47,016 $45,714 $52,884 $37,664 $34,662 $59,726 $45,826 $37,665 $44,893 $35,882 $43,982 $38,774 $53,772 $33,826 $49,883 $48,725 Square Feet 4,009 4,011 2,503 4,012 2,499 4,010 4,010 4,011 2,498 4,009 2,501 2,502 4,011 4,009 4,015 4,019 2,501 4,014 4,010 4,010 2,503 4,011 2,507 Average # of Employees Sells Gas New Store 11.86 11.10 11.31 8.84 10.90 11.10 11.86 7.50 12.46 10.40 11.60 12.70 14.00 10.06 10.50 7.20 11.20 9.70 12.20 12.10 10.50 10.71 11.00 0 1 0 1 0 1 0 1 0 1 0 0 1 1 1 1 1 1 1 1 1 1 0 1 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 1 Summary Table Step 1 Judgment Store 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Step 2 Trend Step 3 Reas. Step 4 R1 R2 Table Step 4 R3 R4 ROverall Step 5 Decision Fast-Stop, the audit client, has 23 convenience stores located in the southwest region of the United States; information on the stores is summarized in an Excel file Project Data Summer 2019. In addition, there is a Word file titled Getting Started with Excel Regression in case you need help installing the analysis tool-pak: Abbreviations on Excel file: 2018 Sales —Unaudited 2018 totals. 2017 Sales —Audited 2017 totals. 2018 Inventory—Unaudited 2018 Inventory. Square Feet—Square feet of the store. Average Employees—Average number of employees in store during 2018. Sells Gas—Some stores sell gasoline (1=Store sells gasoline; 0=Store does not sell gas) New Store—Store first opened this year (1= yes; 0=no) The partner in charge of the audit (Will Snow) 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 fewer stores. He wants you to apply a more sophisticated “risk based” assessment approach than he has performed in the past to select which stores to examine in further detail. While the variables to be used in selecting stores for further analysis could be either 2018 sales or 2018 inventory, he wants you to use 2018 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, Dewey Cheatum. He raises the following points: • 5 new stores were opened in 2018—all on July 1. While things have "pretty much gone as expected" with them, Cheatum points out that store 17 has been a problem as the manager had to be replaced after two weeks because of fears that arose due to a store robbery the opening night. • He pointed out that several stores are close to a freeway entrance/exit (# 2, 3, 4, 13, and 16). • 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 12 in 2017. We told Bill (the store manager) that he needed to ‘shape up, or ship out.’ He certainly got the message—check out the increase in his store’s sales. • Finally, Cheatum commented: “The economy looks good for convenience stores and a time may come when I will need more help—do you think you would be interested in making the change from public accounting?” Required: The excel file containing your analysis The project report should include a summary table (such as that on the last page of this document). NOTE: You should perform all of the Excel computations required below to assure familiarity with the Excel features. It is important that when the project is complete that you understand the techniques used, including interpretation of the Excel output. As indicated earlier, 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. Perform the following types of analyses and summarize your results in a report to Snow. 1. Judgmental selection. Based on your discussion with Cheatum 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 report. 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 places—That 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 report. Also record the six stores in your Summary Table. 3. Reasonableness test The National Association of Convenience Stores publishes information on stores and reports that convenience stores average $560 of sales per square foot. For simplicity sake, assume that the five new stores were opened on July 1. Snow 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 $560 AMT” column to the spreadsheet and in that column calculate a number that is current year sales divided by square feet minus 560 [(2018 sales / sq. feet) - 560]. This figure represents the difference between a store’s sales per square foot and $560, the industry average. For example, a positive $25 means the store had $585 of sales per square foot ($25 over the $560). Make an appropriate adjustment to the formula for stores open only half of the year (i.e., double sales in the above formula—but not on the spreadsheet in the recorded sales for the 2018 column). Although you may supplement the data provided on the spreadsheet (e.g., with the Over $560 AMT column), don’t change any of the original data provided or bad things may happen. Cut and paste to your report the store # and OVER $560 AMT for the six stores with the highest level of sales over $560 per square foot. In addition, post the stores to your Summary Table. b) Provide in your report the formula you used for stores open only half of the year. 4. Regression The assumption underlying this project is that you learned simple linear 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. We will discuss what the output means in class. The key is to find stores with possibly overstated 2018 sales using the regression results. 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. a) In the past, as might be expected, previous year sales (here, 2017 Sales) have been a good predictor of current year sales. Run a regression with 2017 Sales as the independent variable (Excel calls this the “X Range”) and 2018 sales as the dependent variable (Excel calls this the “Y Range”). (Note—use 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 report the stores with the six largest positive residuals (sales higher than expected by the regression model). The output should have three columns: Observation 21 5 : : : : Predicted 2018 Sales 779,375.48 2,264,638.70 : : : : Residuals 430,528.52 249,678.33 : : : : Also provide the table of results which include the intercept and coefficients etc. for the independent variables (the table right above the “RESIDUAL OUTPUT” table). Post your six stores to your summary table—this is R1 Document the next 2 steps in your report. ii) For Store 14, manually calculate the “Predicted 2018 Sales” Intercept Coefficient + (2017 Sales Coefficient)* (2017 Store #14 Sales) Compare your Predicted 2018 Sales for Store 14 to those of Excel. Include your computation in your report. iii) b) For Store 14, calculate the residual and compare it to Excel’s residual. Include your computation in your report. Run a multiple regression (this is R2 on the summary table) using 2018 Sales as the same dependent variable (it remains the dependent variable in all of your analyses), but include independent variables of (1) 2017 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 2, 3, 4, 13, and 16 and a 0 for the others]. When you run multiple regression with Excel the independent variables must be next to one another in your spreadsheet. i) Cut and paste to your report as you did in 4. a) i) above the largest six residuals. Also provide the table of results which include the intercept and coefficients etc. for the independent variables (the table right above the “RESIDUAL OUTPUT” table). Add the six stores with the largest positive residuals to your Summary Table. ii) Which independent measure is the best predictor? How can you tell? iii) Which is (are) the worst? How can you tell? c) Run other regression(s) that you believe might be helpful in determining which stores to investigate in further detail. For this part, ... Purchase answer to see full attachment
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more