Assignment: ON THE GO STORES
This case includes this document and an Excel document with the On the Go exhibits, which is included in this week's folder.
REQUIRED
Answer the questions below in a separate Word file. Number all answers so that they correspond with the questions below. While you are required to use Excel to do calculations and analyses throughout the case, most of these will be easy to copy and paste into the final Word document. Except as noted below, I will expect all conclusions to be fully documented within the Word document. I will be checking your Excel file, however, to ensure that the calculations were done within Excel and done using proper formulas. You should label your Excel calculations clearly so that I can determine to which question they are related; you might want to label the worksheets with the appropriate question numbers. Save the word file with your answers using the naming format: Wk9FirstInitialLastInitial_OTG and save the Excel spreadsheet using the naming format: Wk9FirstInitialLastInitial_OTGExcel. Submit the completed case study using Blackboard by the due date.
NOTE
Some of the requirements below should be quite simple given the data provided in the Excel exhibits provided, while others will require you to do your own additional calculations. Consider using the point values provided for each question as a guide to how much work you should be putting into each question. For example, a question worth 6 points should take you more time and thought than a question worth 1 point.
BACKGROUND INFORMATION
On the Go Stores has twenty-three convenience stores located in the Southeast. Included in the twenty-three stores are five new stores (no. 1, no. 4, no. 10, no. 13, and no. 22) that opened during the year. Operations vary by demographic location and the mix of products sold.
The location of a store is based on several factors, such as competition and the economic environment of the location. Store nos. 2, 4, 6, 8, 9, 11, 13, 15, 17, 18, 20, 21, and 23 are considered to be in favorable locations.
Typically, a store's operations do not change much unless a new product line is introduced, such as selling gas, offering check-cashing services, or selling lottery tickets. The mix of products and services can vary, and the most important factor is whether the store sells gasoline (store nos. 5, 6, 7, 8, 14, 15, 16, 17, 18, 19, 20, and 21 sell gasoline). These additional product lines typically affect the volume of customers as well as the number of full-time employees.
Assume you are the Senior auditor for the audit of On the Go Stores. You have been asked to perform various analytical procedures to test for the existence of sales revenue.
Question 1
Auditors will consider many factors when they develop an estimate of sales revenue for each individual location of On the Go. They will consider both general environmental or industry factors and factors specific to this client. For example, they will probably consider prior year sales as one factor in developing their estimate for current sales. List at least 5 other factors that you would consider when determining your estimate of individual store sales revenue.
TREND ANALYSIS
Trend analysis can be used in the planning phase of an audit or as a substantive test. Trend analysis typically is more appropriate for the planning phase of an audit, because it does not take into consideration changes in specific factors that affect the account. However, considering factors that increase the precision of trend analysis may provide the auditor with an appropriate level of assurance for substantive testing.
Question 2
How would the type of data you use change if you were to perform a planning analytical versus a substantive analytical procedure? Answer this for all types of clients first, and then apply it specifically to On the Go.
Question 3
Consistent with a typical high level analytical procedure that might be used during planning, compare total sales in the current year to total sales in the prior year provided in Exhibit 1 of the Excel file. Based on the comparison, conclude on the likelihood of a material misstatement in sales revenue (low, moderate, or high) being sure to include appropriate numbers in your discussion.
Question 4
The test above does not take into consideration that the number of stores changes each year. Modify the above test to account for this factor using data provided in Exhibit 1 of the Excel file. Do the calculation/comparison within Excel, but then copy and paste the relevant numbers here along with your conclusion of the likelihood of a material misstatement in total sales revenue (low, moderate, or high).
Question 5
Now consider individual stores and identify the ones that you believe may be more or less at risk of having materially misstated sales revenue based on changes in sales revenue from last year to this year (using Exhibit 1). Assume for this test that we have set tolerable misstatement for sales revenue to be $150,000 per store, or an 8% change from the prior year. All calculations can remain in Excel; include only the store numbers here.
Question 6
Develop four potential explanations for the material differences between your estimate and actual revenues for the stores you identified in Question 5 above as being most likely to have misstatements. Categorize those potential explanations as either error or non-error. For each explanation, identify evidence/additional procedures that you would perform to determine if the explanation is the most likely the cause of the difference.
RATIO ANALYSIS
A ratio analysis involves the comparison of relationships between financial statement accounts, a comparison of an account with nonfinancial data, or a comparison of relationships across an industry, such as gross profit comparisons.
Question 7
Using the data on "All Stores" in Exhibit 2, compare the current year gross profit percentage to prior year gross profit percentage to determine how likely there are to be material misstatements in total sales revenue and the related accounts. Conclude on the likelihood of a material misstatement in sales revenue (low, moderate, or high) being sure to include appropriate numbers in your discussion. Note - subtracting two percentages gives you a "change in percentages"; it does not give you the percent change. In order to calculate the percent change of anything, you must subtract the two years' numbers and then divide by the prior year.
Question 8
Because gasoline is sold at a different mark-up than other types of products, we might expect stores that sell gasoline to have a different gross profit percentage than stores that do not sell gasoline. Therefore, using the additional data in Exhibit 2, compare current year and prior year gross profit percentages for the separate groups of stores. Conclude on which stores might be more or less at risk of having materially misstated sales revenue being sure to include appropriate numbers in your discussion. Use 10% as a reasonable tolerable misstatement for this test.
USING NON-FINANCIAL DATA
The management of On the Go Stores has provided you with the amount of square footage per store and sales per stores (see Exhibit 3). The region's average sales per square foot was obtained from information provided by the National Association of Convenience Stores (NACS), which publishes information on the convenience store industry.
Question 9
Using the industry average sales per square foot and data provided in Exhibit 3, develop an estimate of what you would expect total sales to be for On the Go (not by store). Give consideration as to whether all stores should be included in this "total estimate" Compare your estimate to the actual total sales and conclude on the likelihood there are to be material misstatements in sales revenue (low, moderate, or high). Do the calculation/comparison within
1 An example of what I am looking for here not related to this case: say that based on a comparison of prior to current year for one company, you identify accounts receivable as being potentially overstated by a material amount because the percentage increase was exceptionally high. One potential explanation is that the company had a very large sale at the end of the current year to a new client. This would be considered "non-error" since, assuming it is true, accounts receivable is stated correctly in the current year and no adjustment would need to be made. In order to determine if this explanation is true and AR does not need an adjustment, one procedure we might do would be to confirm the year-end sale directly with the new client. You would then need to come up with 3 additional potential explanations and provide a detailed discussion of each consistent with the example here.
Excel, but then copy and paste the relevant numbers here along with clear documentation of your consideration of which stores should be included and your conclusion.
Question 10
Using data provided in Exhibit 3, analyze sales per square foot by store and identify those stores that you believe may be more or less at risk of having materially misstated sales revenue. Use 15% for tolerable misstatement for this test. All calculations can remain in Excel; include only the store numbers here.
REGRESSION ANALYSIS
Regression analysis has the same objective as trend, ratio analysis, and reasonableness testing: to identify the potential for misstatement. The advantage of regression over the other methods is that the regression: (a) provides an explicit, mathematically objective, and precise method for forming an expectation; (b) allows the inclusion of a larger number of relevant independent variables; and (c) provides direct and quantitative measures of the precision of the expectation.
The auditor's specific objective in using regression for On the Go Stores is to determine which stores should be targeted for investigation for potential misstatement in sales. The regression determines which stores have total sales that are most out of line in comparison with the others. This type of analysis is called cross-sectional regression. In predicting sales, the cross-section usually includes relevant predictors, such as the size of the store (as used in the reasonableness testing above), and other features that might affect store sales volume.
Question 11
List six factors you believe might be potential predictors of sales for On the Go stores. Do not limit your ideas to only data you currently have available.
Question 12
Using the data provided in Exhibit 4, perform a regression analysis on the data, and include residuals in your output. Based on these results, which variables are most important in determining sales? You may keep all data within Excel. (If you don't know how to do regression analyses in Excel, see the tips sheet in the Week 8 folder.)
Question 13
What do the residuals mean? What does a negative residual mean in terms of potential errors and what does a positive residual mean in term of potential errors? Which is more of a concern to auditors?
Question 14
Combine the regression analyses results with those from all of the previous questions and discuss the stores you believe should be investigated further by the auditor because they have a higher likelihood of material misstatement in sales revenue. Think critically about this, understanding that any store you highlight will require additional audit hours for investigation.
Attachment:- The_Go_Exhibits.xls