it wouldn't let me upload the documents could email them to you This is an auditing project,
Question:
it wouldn't let me upload the documents could email them to you
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. 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 2020.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:
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 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 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 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 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.
He pointed out that several stores are close to a freeway entrance/exit (# 2, 5, 12, 17, and 19).
Total revenues from gas sales have decreased due to an decrease 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 13 in 2018.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 he 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 project should be submitted on Canvas by the 8AM 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 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.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 [(2019 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 $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 Summary Table.
b)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 find stores with possibly overstated 2019 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, 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").(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 "," to make them easier to read):
Observation
Predicted 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 coefficients 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.
Includeyour computation in your Word write-up.
iii)For Store 10, calculate the residual and compare it to Excel's residual.
Includeyour 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 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?
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). 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 salesadd 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.
5.Overall - Combine the results of your entire analysis (Regressions 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 find 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.
Summary Table
(Place "X" in the top 6 for all columns except Decision where only 4 are selected)
Store
Step 1
Judgment
Step 2
Trend
Step 3
Reas.
Step 4
Step 5
Decision
R1
R2
R3
R4
ROverall
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Expert Answer:
