Find below information about a hypothetical audit client named Little Store, Inc. You have been assigned the task of auditing Little Stores Sales account for occurrence and completeness. You will be using substantive analytical procedures to test these assertions.

• Use the information about Little Store to develop expectations regarding Sales at the individual store level (i.e., disaggregated level).
• You will complete three sets of analytical procedures: 1 for each of the three main methods (trend, ratio, and reasonableness test). In each case, compare your expectations to the actual numbers and identify the stores with a material discrepancy.
• Use MS Excel to complete your analyses, highlighting material discrepancies. Suggestion: Start by importing the table in the case to an Excel file.

Deliverable: Excel file containing at least 3 tabs, one for each substantive analytical procedure:

• Trend analysis
• Ratio analysis
• Reasonableness test.

Helpful Notes:

1. Follow the 4 steps for substantive analytical procedures for each test.
2. A trend analysis requires at least two years of information to compute the trend. However, you are only explicitly given prior year sales data, and it is circular (i.e., incorrect) to just use the change in sales from last year to this year to predict sales for this year. Thus, you have (at least) two options. First, you can look to the internet to find industry sales growth trends for the convenience store industry. Alternatively, recall that you have each store's square footage from the prior year if we assume that stores dont spontaneously grow or shrink. Thus, you can compute the prior year sales per square foot. You are then provided with the industrys current year sales per square foot. Voila! You now have two years of information. By comparing the two, you can find a growth rate, which you can then use in your trend analysis.
3. Before you worry about the entire four step process of substantive analytics, I strongly recommend you take a step back and ask yourself this question: what data is presented that would help me predict 2018 sales revenue? After all, that's the overarching goal of substantive analytics--to make your own prediction of 2018 sales and then compare that prediction to management's reported sales. So, with that main question in mind, carefully go through the data provided to you and identify the different pieces of information that you could use to predict revenue. As you do this, you'll find there are multiple data points that give you multiple ways of predicting 2018 revenue. At its core, Step 1 of the process is not an audit task, it's a problem solving task. AFTER you have sketched out those different prediction methods, THEN you can complete steps 2 (calculate tolerable difference), 3 (compare your expectation to management's reported sales figure and identify stores with significant differences), and 4 (evaluate the results) for each of the three prediction methods you've chosen.
4. In terms of setting things up in Excel, each prediction method will have various columns of information required to generate a single column that has your predicted sales for each store; this is Step 1. Then there will be a column where you calculate tolerable threshold, which is Step 2 of the process (we talked in class about 5% of 2018 sales as a reasonable choice). Then there will be a column calculating the difference between your prediction and management's 2018 sales and a column that identifies stores whose difference exceeds the tolerable difference, which together are parts of Step 3. Step 4 requires you to evaluate the results, in which you will state how you would follow up on those stores identified as potentially problematic.
5. Remember that your very purpose is to PREDICT 2018 sales. So, make sure your prediction of 2018 sales isn't based on 2018 sales...otherwise it's a circular prediction.
6. Your documentation will be held to the same replicability standard as part 1. That means that for each analytical procedure you must include not only the calculations that you made, but also four distinct text boxes below those calculations (one for each step of the four-step process) describing what you did to create your expectation, justifying your calculation for tolerable difference, identifying situations where differences exceeded the threshold, and stating what you would do to investigate those difference. Those text boxes SHOULD NOT be lengthy, but they are required to fulfill the documentation requirement.

Little Store, Inc. has eighteen convenience stores located in Oklahoma. Little Store has not opened any new stores for two years, but plans to open at least one during each of the next three years. Operations in the current stores seem to be relatively stable over the past few years, however, they do 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. Stores 1, 4, 6, 7, 8, 11, 13, 14, 16, 17, and 18 are considered to be in favorable locations.

Typically, a stores operations do not change much unless a new product line is introduced, such as selling gasoline, offering check-cashing services, or selling lottery tickets. The mix of products and services can vary, with the most important factor historically being whether the store sells gasoline. Stores 3, 4, 5, 6, 10, 11, 12, 13, 14, 15, 16, and 17 sell gasoline. These additional product lines typically affect the volume of customers as well as the number of full-time employees.

Additional factors to consider:

1. According to the National Association of Convenience Stores (NACS), industry sales should average about $490 per square foot this year.

2. Little Store outlets follow two basic floor plans. Stores 1, 2, 8, 9, and 18 have 2,500 square feet of room, while stores 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, and 17 have 4,000 square feet of room.

3. In prior years, stores that sold gasoline had an average gross profit percentage of 30.1%, while stores that did not sell gasoline had an average gross profit percentage of only 22.1%. The average for all stores was 28.2%. Cost of Sales has already been audited this year as part of the audit of Inventory.

Store Number	Sales 2018 (unaudited)	Cost of Sales 2018 (audited)	Sales 2017	Avg. Inventory 2018	Avg. 2018 Employees
1	1,146,438	888,701	1,165,221	44,171	11.31
2	1,195,004	911,655	1,147,430	45,714	12.46
3	1,981,409	1,398,296	2,037,463	45,826	10.06
4	2,300,671	1,503,717	2,257,920	53,862	11.1
5	1,956,481	1,400,118	1,850,354	49,883	10.71
6	1,799,713	1,266,050	1,916,884	47,016	7.5
7	1,820,641	1,418,278	1,833,209	59,726	14
8	1,159,004	905,618	980,484	37,664	11.6
9	1,139,475	756,449	1,069,652	34,662	12.7
10	1,984,777	1,354,951	1,795,123	38,774	12.2
11	2,293,847	1,657,822	2,119,015	55,423	11.1
12	1,984,722	1,366,050	1,947,303	52,884	10.4
13	1,798,336	1,280,773	1,705,789	46,834	8.84
14	2,484,503	1,600,852	2,396,971	53,772	12.1
15	1,837,400	1,322,089	1,901,631	43,982	9.7
16	1,609,385	1,194,814	1,514,798	44,893	7.2
17	1,874,229	1,311,975	1,886,587	37,665	10.5
18	1,198,229	908,911	1,092,908	44,857	10.9