I have added everything in the files, The deliverable must include responses to Q1, Q2 and completing the Memo (Q3)(See Case)
• I expect the deliverable to be between 5-8 pages long
• I expect 4-5 Tableau visualizations to be included to guide your analysis of anomalies in
the data submission should be just in a Word file, for the
Tableau visualizations you can take a screenshot or copy it to the Word file.TEAM ASSIGNMENT #1: DATA ANALYTICS & VISUALIZATION TEAM ASSIGNMENT #1: DATA ANALYTICS & VISUALIZATION TEAM ASSIGNMENT #1 TO DO’S: Read the Student Case PDF You must Download the Data file & Tableau Read the 2 research articles on Data Analytics & the Audit If you have no familiarity with Tableau, spend a little time watching the video tutorials through LinkedIn Learning…they are GREAT! Meet with your team and have your thinking cap on & ready to work! 5-STEP AUDIT DECISION MAKING PROCESS ANALYTICS TO IDENTIFY UNUSUAL TRENDS Ensure data sources given to you are accurate and complete: ‘foot’ the detailed listing given to you and ensure that it agrees to the GL. If it does not, management may have recorded a fictitious ‘top-side’ entry (Dr. AR, Cr. Sales, without supporting details) Develop an expectation: Document why there is a plausible, predictable relationship (e.g., we expect sales to be comparable to prior year, by location; or we expect sales to fluctuate with non-financial trends) Complete analysis and identify unusual trends/observations Make inquiries and get supporting details Document conclusion of findings (including thresholds used for identifying ‘significant’ fluctuations or changes) Consider whether you should go back for more testing, adjust your internal control conclusions, etc. This project focuses on steps #1 – 3. The auditors’ next steps after identifying unusual trends/observations would be steps #4 – 6. 4 CASE INTRODUCTION Souper Bowl, Inc. You have Daily Revenues by Store Location You’ve also pulled Weather Data for the closest weather center Based on your knowledge from prior years, you know that sales fluctuate with: 1) temperature 2) snowfall You also know that not all stores produce the same volume: northern Maine (Type 1), mid-Maine (Type 2), and coastal Maine (Type 3). No major changes to operations, no new locations CASE INTRODUCTION Your objective: Last year, your team performed random sampling to test revenues This year, your team wants to use visualization to help narrow in on specific days at specific locations that should be subjected to substantive testing due to heightened risks. The remainder of the population would then be sampled using a random sampling approach. EXPECTED TRENDS How should temperature and soup sales be correlated? TMIN, TMAX How should snow and soup sales be correlated? SNWD How will you identify unusual trends? Start with 2016 only (audited from last year): use Tableau to find the visualization that best captures the relationship in the data. Linear relationships Non-linear relationships Once you find visualizations that you like, add in the 2017 data – then look to see where patterns appear to be violated – something is outside your expectation (i.e., doesn’t behave like it did last year). These are higher risk areas. 7 LINEAR TRENDS IN DATA In a linear relationship, you’ll see that the data roughly forms a line. On the left: a single Store type, so all observations follow approximately the same line. On the right: your project has three Store Types, so you’ll potentially have three different lines. LINEAR TRENDS IN DATA Once you’ve established what type of relationship a certain data item is, you can then add in the current year’s data Notice how there are now observations that don’t fall into ‘line’ with the normal pattern of data. NON-LINEAR TRENDS IN DATA If you encounter something like this, it implies that it is not a linear relationship – we know this because there’s not a clear predictable pattern – the observations are scattered all over the place. Instead, you might have a non-linear relationship on your hands… Non-linear relationships are often seen as ‘jumps’ in the data – either moving in opposite directions (negative correlation) or in the same direction (positive correlation) NON-LINEAR TRENDS IN DATA Notice how for two stores 2015 vs. 2016, Store 1068 (bottom) continues to exhibit predictable non-linear “jumps” in the data: when one thing goes up, another thing comes down. However, in Store 1067 (top), the data doesn’t jump in 2016. You would identify this as a potentially suspicious location, and you could use your mouse in Tableau to hover over the data to identify the exact data in question. TABLEAU WILL HAVE LOTS OF OPTIONS… These are examples of many of the features available in Tableau. 12 It’s your job to… think about what you want to show (practice drawing it on paper first if needed), find the right color/chart/graph types, and then clearly document in the memo your expectation (helps to show a 2016 screenshot of what you expect it to look like), a screenshot of the suspicious location/date for 2017, and text boxes, arrows, circles, boxes, tickmarks, etc. explaining the ‘anomalies’ in the screenshot TABLEAU WILL HAVE LOTS OF OPTIONS… TABLEAU Import Step 1: Open Tableau and Connect to the Student Data Excel file 14 Import Step 2: Drag each worksheet over to the “Drag sheets here”. It should then look like the joined circle in the second screenshot. 15 Import Step 3: Tell Tableau how to connect the two sets of data. It’s important that you join on BOTH weather center location AND date. Joining on only one (which is the Tableau default) will produce a dataset with duplicate observations. This is a good place to stop and check control totals for your dataset. Compare the total dataset values in Tableau to the total dataset values in the file that you imported (Excel). 16 You have to tell Tableau that some of your data should be a ‘dimension’ and not a ‘measure’, so grab and drag Latitude, Longitude, Store Location, and Store Type from ‘measures’ and put in ‘dimensions’ up above. Import Step 4: Setting up the data for Dimensions vs. Measures 17 To create your first visualization, start dragging measures and dimensions into the “Columns” and “Rows” and explore the “Show Me” options. Explore the ordering (Location, YEAR) vs. (YEAR, Location) and also use the “+” sign to disaggregate your data Year ? Quarter ? Month, etc. Also explore the ordering of Columns vs. Rows. All these choices change the way your data look. Now that the data is imported, the remaining slides provide some helpful hints for using Tableau to create visualizations. 18 When you find a set-up you like, save it as a unique name, and then start another Sheet for your next visualization. This will save time so you can come back and edit later when you’re working on your memo or getting help. Disaggregate data when it’s too much to see on one screen…location, day (vs. year), etc. This will make it so that you scroll through to see all your data, and then you can screenshot only the parts you need to show your conclusions in your memo. You can also disaggregate data by changing the unit of measure – dragging different dimensions into the main data area (e.g., location, date, etc.) will change the unit of observation from one per location to one per location-day, etc. Change colors to help separate your data Explore the ordering of Sales, SNWD, TMAX, and TMIN until you find the best visualization for you. Also take advantage of filters to zoom in on data when there’s too much in one screen… For example, drag “Date” into the “Filters” box. A dialog will pop up letting you pick the granularity you need (e.g., months, quarters, etc.) Once you’ve selected Next ? OK, the filter will appear in the filter area. To use the filter, click the drop-down by the Filter and then select “Show Filter” Also, you don’t have to always use the “+” option with dates. You can also use the drop-down option to switch from Months ? Days, etc. OTHER TABLEAU SUGGESTIONS Once you find something unusual…hover over the suspect item, or “Display” the data underlying a workbook (Lynda.com Tableau training, Chapter 3) You can learn how to do “Dual Axes” (which is important if measures are in different scales) here: https://help.tableau.com/current/pro/desktop/en-us/multiple_measures.htm RECOMMENDATIONS Stay Focused! There is not one “RIGHT” answer. What are you trying to accomplish? Locations & Dates (or specific date ranges) that appear to be anomalous in 2017 data and require additional investigation You expect some similarities to prior year’s data (e.g., nature of operations haven’t changed) but you also know that changes in weather patterns can change revenues, so it won’t be enough to just compare revenues year-over-year – that might be a starting point (or final ending check for yourself), but a thoughtful analysis will consider revenue, weather, and store types. Remember that your reviewer/PCAOB inspector/etc. might not have access to your Tableau workbook, so your memo should clearly document your conclusions and provide enough evidence for the reviewer to see that your conclusions appear to be accurate and complete. STEPS FOR GOOD DOCUMENTATION Clearly explain your expectation Show an example of what it should look like Explain how you identified deviations Provide screenshots for the suspect locations/days Hint: If multiple suspect locations/days for a specific analysis, you can show one or two examples of a certain type of suspicious activity and then just explain in words all the other locations/days that look the same Clearly label the suspicious activity for the reviewer Ensure your ‘conclusion’ lists all the suspect location-days you identified & the figure or analysis reference number so your reviewer can check that you got them all. GOOD DOCUMENTATION EXAMPLES Showing both a zoomed out version and a zoomed in version Instructional Note: To avoid having students copy from the ‘good examples’, these examples are from a different dataset that was examining ice cream sales and temperature + rainfall. These examples will make the project easier for students, so instructors can decide to include (omit) these slides to decrease (increase) the level of difficulty for the project. 29 GOOD DOCUMENTATION EXAMPLES Clear explanations of the expectations and the visualization of data Instructional Note: To avoid having students copy from the ‘good examples’, these examples are from a different dataset that was examining ice cream sales and temperature + rainfall. These examples will make the project easier for students, so instructors can decide to include (omit) these slides to decrease (increase) the level of difficulty for the project. 30 GOOD DOCUMENTATION EXAMPLES Showing side-by-side ‘good’ and ‘bad’ for one location and clearly labeling the suspect days in the text & image Instructional Note: To avoid having students copy from the ‘good examples’, these examples are from a different dataset that was examining ice cream sales and temperature + rainfall. These examples will make the project easier for students, so instructors can decide to include (omit) these slides to decrease (increase) the level of difficulty for the project. 31 GOOD DOCUMENTATION EXAMPLES Another example of clear labeling – they took the locations that were suspect for the current year’s audit and showed how they were not suspicious in the prior year by only showing in bold the suspect locations in both periods. This person started with this ‘aggregate’ data (total revenues & average high temp. for the whole year) and then followed up with details for specific dates driving the annual trends. Instructional Note: To avoid having students copy from the ‘good examples’, these examples are from a different dataset that was examining ice cream sales and temperature + rainfall. These examples will make the project easier for students, so instructors can decide to include (omit) these slides to decrease (increase) the level of difficulty for the project. 32 SOME REMINDERS… Submit through Canvas only The deliverable must include responses to Q1, Q2 and completing the Memo (Q3) (See Case) I expect the deliverable to be between 5-8 pages long I expect 4-5 Tableau visualizations to be included to guide your analysis of anomalies in the data Look at “Good Documentation Examples” You will be graded on (1) Response to Requirement 1 (5 pts), (2) Response to Requirement 2 (5 pts), (3) Data analysis (10 pts), (4) Discussion (10 pts), Conclusions (5 pts), and Writing & Organization (5 pts) 1 THE CASE1 Company Background You were recently promoted to audit senior at your firm, Aoife & Josephine LLP, and one of your primary clients is Souper Bowl Inc. Souper Bowl (“the company”) is a privately-held business headquartered in Maine with a fiscal year end of December 31. The company has been in business for ten years and prides itself on offering creative soups made with locally sourced ingredients at a reasonable price. The most popular soups include sweet potato corn chowder, curried root vegetable and lentil, and maple-roasted butternut squash. Souper Bowl typically experiences increased sales during winter months since soup hits the spot on a cold and snowy day. To further encourage sales on days when customers often avoid venturing outside, the company provides a delivery service and guarantees that soup can be delivered to anyone no matter the weather. The company found this strategy to be particularly successful in 2015 when New England (including Maine) experienced record snowfall during February and March. Souper Bowl sells soup out of several restaurant locations throughout Maine. The company employs three managers that direct the day-to-day operations for a set of stores that are organized by approximate geographic region: northern Maine (store type 1), mid-Maine (store type 2), and coastal Maine (store type 3). Appendix A provides a map of these store locations. Each manager knows their local market well and has the flexibility to advertise and offer promotions with the overall goal of increasing sales year over year. Because of warmer weather and less snowfall in 2016, the company developed a new incentive plan for 2017 to boost