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.

projecthighlights.ppt

project_data_summer_2019.xls

Don't use plagiarized sources. Get Your Custom Essay on

5 step project, In the first step you need a simple explanation of the selected store, in the fourth

Just from $13/Page

requirement.doc

Unformatted Attachment Preview

Analytical Procedures Project

1

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

store data.

• Your analysis should use both financial and non-financial

2

data.

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

3

2. Trend Analysis

• Analysis of changes in an account balance

over time

• (Current Sales – Last Year’s Sales) / Last

Year’s Sales

• Select the 6 stores with the largest positive

changes

4

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

foot

• (Current Sales / Sq. Feet) – 560

• Must adjust equation for new stores

• Select 6 stores

5

4. Regression Analysis

•

•

•

•

R2 (coefficient of determination)

Dependent variable (the Y variable)

Intercept Coefficient

Independent variables (the X variable(s)) and

their coefficient(s)

• t statistic and interpretation

• P –value and interpretation

• Residuals

6

Sample Regression Output

Regression Statistics

Multiple R

R Square

Adjusted R. Square

Standard Error

Observations

0.92894908

0.801

0.789

770.44

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.00

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”)

7

R2

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.

8

Dependent variable (Y variable)

• This is the variable you are trying to

predict.

• In the project it is the current year sales for

each store for the first regression.

9

Intercept Coefficient

• 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.

10

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 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.

11

Significance of Regression results

t Statistic:

• indicates whether the independent variable has

a significant statistical relationship with sales.

• tests the hypothesis that the parameters are not

zero

• 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.

12

Significance of Regression results

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

• It is another statistical conclusion related

to the t statistic.

13

Calculating the Regression Model

Prediction of Sales

• From the regression model results,

calculate the predicted current year sales

given intercept and independent variable

coefficients.

• For example, for any store:

Intercept + (coefficient) (the related independent variable)

10,243 + .474 (2017 sales) + 271.162 (Square Feet of Store) + 2.134

(Average Employees)

• Model residuals provide the result

automatically.

14

Residuals

Example:

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.

15

Residuals (with only 8 stores)—

Excel Output

RESIDUAL OUTPUT

Observation

1

2

Predicted 2018

686796.9

1246556

Residuals

94996.12

-100118

3

4

5

1229932

1095082

2059514

-34928.2

-143298.4

-78105.37

6

7

8

2163868

1970946

2002438

136803.4

-14465.43

-202725.4

16

Residuals (only 8 stores)—Sorted,

Formatted Excel Output

Observation

Predicted 2018

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

17

Residual Questions

1. What are 2018 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?

18

Regression—overall 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’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

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)

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 moreEach 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 moreThanks 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 moreYour 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 moreBy 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