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 cente
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 na
ow 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 co
elated?
TMIN, TMAX
How should snow and soup sales be co
elated?
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 cu
ent 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 co
elation) or in the same direction (positive co
elation)
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 XXXXXXXXXXYou 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 colo
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, a
ows, 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/cu
ent/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 reviewe
PCAOB inspecto
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 reviewe
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 cu
ent 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
usiness headquartered in Maine with a fiscal year end of December 31. The company has been in
usiness 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,
cu
ied 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 Fe
uary 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
y 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