Circle Project Assignment

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 CircleL 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 Inventory-Unaudited 20X4 Inventory.
Square Feet-Square feet of the store.
Average Employees-Average number of employees in store during 20X4.
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 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 yearhe 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 opened-all on July 1. While things have "pretty much gone as expected" with them, Wilson points out that store 10has 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 message-check 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 help-do 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 deliverables-a WORDwrite-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 selectsix 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 tosix stores in the "Step 1 Judg." Column and leave the other 17stores 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 thesix stores with the largest increase in sales percentageand cut and paste the store number and percentage sales increase for thosesix stores (from high to low) in your Word write-up. Also record thesix stores in yourSummary 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 formula-but noton 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 thesix stores with the highest levelof sales over $540 per square foot. Post the stores to your SummaryTable.

b) Provide in your Word write-upthe 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 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"). (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 Word write-up the stores with thesix 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):

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 yoursix stores to your summary table-this is R1

Document the next 2 steps in the WORD write-up.

ii) For Store 12, manually calculate the "Predicted 20X4 Sales"

Intercept Coefficient + (20X3 Sales Coefficient) (20X3 Store12Sales)

Compare your Predicted 20X4 Sales for Store 12 to those of Excel.
Include your computation in your Word write-up.

iii) For Store12, calculate the residual and compare it to Excel's residual.
Include your computation in your Word write-up.

b) Run a multiple regression(this is R2 on the summary table) using 20X4 Sales as the same dependent variable (it remains the dependent variable in all of your analyses), but include independent variables of (1) 20X3 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 5, 6, 11, 12, and 19and 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 the largest six residuals to your Word write-up,as per4. a) i) above.Also provide the table of results which includes the intercept and coefficients etc. for the independent variables (the table right above the "RESIDUAL OUTPUT" table). Add thesix stores with the largest positive residuals to your Summary Table.

ii) Which independent measureis 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. You need only include the residual output (top six)from two of these additional regressions in your Word write-up (but can include moreto test consistency of results, etc.). Post your stores selected by the various regressions in R3 and R4 in your Summary Table. In addition, for this part, you need to document in your Word write-up which variables are in each model in your write-up.

d) Selectsix stores based on all the regression analyses 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.

5. Overall- 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 thesummary table.You should select4stores.

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.

There are 17 points that are allotted for the overall accuracy and the quality of the write-up.The analysis should be easy for a person to follow. There is one point available for each store that you correctly select. Since there are three stores that have large overstated sales, the maximum score you can receive is 20 points.

Summary Table

Attachment:- Attachments.rar