This case emphasizes the importance of data analysis through the usage of data visualization software to help you gain an unders
minimum of four paragraphs and should be a minimum of 400 and 450 words. The font is Times New Roman, font size should be 12, and the paragraphs are single-spaced. There should be a minimum of three references supporting your observations. Citations and references are to follow APA 7.0.
Contents lists available at ScienceDirect
Journal of Accounting Education
journal homepage: www.elsevier.com/locate/jaccedu
Using Tableau to visualize data and drive decision-making☆
Jamie Hoelschera,⁎, Amanda Mortimerb
a School of Business, Southern Illinois University Edwardsville, Box 1104, Edwardsville, IL 62026, United States b PricewaterhouseCoopers, United States
A R T I C L E I N F O
Keywords: Data analytics Data visualization software AACSB Accounting Standard A7 Business decision-making Accounting educational resource
A B S T R A C T
This case emphasizes the importance of data analysis through the usage of data visualization software to help you gain an understanding of data and how it can be transformed into in- formation that can enhance the decision-making process. In the data visualization software, Tableau, you will be asked to connect to an Access data file to analyze six months of sales transaction data of a small start-up ice cream manufacturer. Consistent with AACSB Accounting Standard A7, the case focuses on familiarization with data visualization software to “convey data, results, and insights” (AACSB, 2013) and apply higher-order thinking. Upon familiarization with the data and data visualization software, you will be required to perform an exploratory analysis to identify key trends in the data to prepare and report that information to enhance the business decision-making process. This case is intended to be utilized in an undergraduate accounting information systems course; an introductory managerial course; or a course focusing on data analytics as a basic introduction to data visualization software.
1. Case
Ivana’s Ice Cream just finished its first six months of manufacturing and selling ice cream. The company has two main product lines, ice cream cups and ice cream bars, both of which are available in vanilla or chocolate flavors. Ivana Cone, the sole owner of Ivana’s Ice Cream, has carefully recorded basic accounting data, including sales transaction data over the company’s first two quarters of operations using Microsoft Access. Unfortunately, Ivana lacks formal business and accounting training. You have been hired as a consultant to utilize Tableau data visualization software to analyze the company’s data from the first two quarters of operations and to provide a detailed breakdown of sales, product mix, and gross profit by product line and geographic region. In addition, Ivana would like to expand the business in future years and you have been asked to provide insights and recommendations. First, you will need to understand the business and operations.
The first step in any data analytics project is to thoroughly understand the data and its current format. To learn more about data analytics in the field of accounting and the benefits and uses of data visualization software see Appendices A and B, respectively. When creating visualizations, it is important to consider which type of graph or chart best fits the data. Although there are numerous types of visualizations (graphs and charts) from which to choose, some of the most common include bar charts, line graphs, pie charts, and maps. The data can help you determine which visualizations would help convey the information most effectively. For example, bar charts are useful when data are separated into different categories, while line graphs are useful to show changes in data over time. Pie charts display categories as a portion of a total, and maps represent data geographically and should only be used if the
https://doi.org/10.1016/j.jaccedu.2018.05.002 Received 11 October 2017; Received in revised form 22 May 2018; Accepted 23 May 2018
☆ This manuscript is one in a series of manuscripts that will appear in the virtual Special Issue 2017 Teaching, Learning and Curriculum Midyear Colloquium. In the virtual SI, this will appear under the heading “Educational Case”. ⁎ Corresponding author. E-mail addresses: [email protected] (J. Hoelscher), [email protected] (A. Mortimer).
Journal of Accounting Education 44 (2018) 49–59
Available online 14 July 2018 0748-5751/ © 2018 Elsevier Ltd. All rights reserved.
T
data contains location information. Furthermore, Tableau has the ability to suggest a visualization based on the types of fields that you select; this can be a very convenient feature when learning to create visualizations.
Before you examine the Access database Ivana has given you, she would like to tell you a little about the current state of her startup company and the three tables she created to store the company data. She created individual tables for customers, orders, and company products. Currently, Ivana’s Ice Cream only sells boxes of ice cream cups and ice cream bars, and each come in either vanilla or chocolate. Below is the unit cost and sales price for each item Ivana’s Ice Cream currently sells (see Exhibit 1):
Ivana’s Ice Cream currently has twenty-two customers. In general, these customers are smaller retailers, including convenience stores and local supermarkets. Currently, Ivana’s Ice Cream only sells to retailers in the Midwest states of Missouri and Illinois. Below is a list of all of the customers with which Ivana’s Ice Cream has done business with since its inception (see Exhibit 2).
Ivana’s Ice Cream currently has four sales representatives. For simplicity, Ivana has assigned Levi Adams to western Missouri; Gabby Bennet to eastern Missouri; Russell Bishop to western Illinois; and Carly Marshall to eastern Illinois (see Exhibit 3).
Your instructor will provide you with the Microsoft Access data files to complete the requirements of this case.1 Your instructor may also assign you specific training videos from the Tableau website (www.tableau.com) to familiarize you with the data visua- lization software, as well as information related to downloading your free student copy of Tableau. Watching the first few training videos at http://tableau.com.learn/training will help better prepare you for the requirements of this case study.
Before beginning the following case requirements, download the Microsoft Access file provided to you by your instructor and save it to your desktop or a personal flash drive. Open the Access file and examine each of the tables, as well the data types of each of the fields, and any primary keys identified. You will connect to Ivana’s data and analyze the data using Tableau, as described below.
2. Requirements
Being new to Tableau, a colleague has provided the following tips to help you begin your analysis of Ivana’s Ice Cream using Tableau:
1. Ensure you have downloaded the latest version of Tableau. Visit https://www.tableau.com/academic/students to obtain a free one-year Tableau license for students.
2. Ensure you have saved the Ivana’s Ice Cream Access file provided by your instructor to your desktop or personal flash drive. 3. Double click on the Tableau Desktop icon to open Tableau. 4. Under “Connect To a File,” select “Access.” 5. Browse for the Ivana’s Ice Cream file and select “Open.”2
6. Drag each table to the open pane.3
7. Select Sheet 1 to go to your first worksheet.
Before you conduct your own exploratory analysis, Ivana Cone would like to understand some basic information related to her enterprise. Specifically, she would like you to answer the following questions to be included and returned in a formal memo to her (below, your colleague has provided step-by-step instructions to enable you to successfully address each of Ivana’s questions in Tableau):
1. What is the total quantity sold of chocolate ice cream cups?
• From the Dimensions pane, drag Flavor and Product to the Rows pane (by holding the Ctrl button down after you select Flavor and before you select the Product dimension, you can drag and drop both dimensions at the same time).
• From the Measures pane, drag Quantity to the Columns pane. • Hover your pointer over the bar for chocolate ice cream cups and record the quantity of chocolate ice cream cups sold during the period.
• Click on the top of the graph where it is currently titled “Sheet 1.” Rename the graph “Quantity Sold by Product.”
ProductID Product Flavor Unit cost Sales price 1 Ice Cream Cup Chocolate $2.55 $4.00 2 Ice Cream Cup Vanilla $2.50 $4.00 3 Ice Cream Bar Chocolate $3.00 $5.00 4 Ice Cream Bar Vanilla $2.85 $5.00
Exhibit 1. Product table.
1 Please e-mail [email protected] to request the Microsoft Access file for this case. 2 If you are unable to connect to your Access database in Tableau, please go to https://www.tableau.com/support/drivers and search for Microsoft Access to
download the driver necessary to connect to your Access file. 3 Drag the tables over in order so they may properly join. Notice how the Customer table and Orders table are inner joined on the Customer ID field, and the Orders
table and Products table are inner joined on the ProductID field.
J. Hoelscher, A. Mortimer Journal of Accounting Education 44 (2018) 49–59
50
ID Customer name Address City State ZIP Region
C00101 Grandma’s Grocery 6524 Oaktree Dr.
Springfield MO 65082 W MO
C00102 K & K Quick Mart 4817 Main St.
Joliet IL 60403 E IL
C00103 Fresh Foods 436 Whitedge Blvd.
Peoria IL 61602 W IL
C00104 Sandy’s Superstore 1525 Parkview Dr.
Rolla MO 64801 W MO
C00105 Midwest Market 6401 Manor Circle
Galesburg IL 61401 W MO
C00106 Food N Stuff 9452 Bridgeport Ln.
Joplin MO 64801 W MO
C00107 Capital City Grocery
684 First Street
Jefferson City
MO 65101 E MO
C00108 John’s Supermarket 2174 Washington Ave.
Warrensburg MO 64093 W MO
C00109 Mini Mart 3355 President Ct.
Aurora IL 60503 E IL
C00110 MJC Deli 42 Greenview Blvd.
Champaign IL 61820 E IL
C00111 Paul’s General Store
5551 Market St.
Mount Vernon
IL 62864 E IL
C00112 Family Pantry 985 Wilson Dr.
Jacksonville IL 62650 W IL
C00113 Garry’s Mini Mart 7931 North 1st St.
Sedalia MO 65301 W MO
C00114 Market Deli 1 Harvard Dr.
Belleville IL 62223 W IL
C00115 B&B Convenience Store
160 Grand Ave.
Columbia MO 65202 E MO
C00116 Express Grocery 252 Washington St.
Bloomington IL 61704 E IL
C00117 Bargain Mart 9956 Maple St.
De Kalb IL 60115 E IL
C00118 Quality Foods 112 Henley Ln
Jefferson City
MO 65109 E MO
C00119 Local’s Cupboard 1411 West Park Dr.
Fulton MO 65251 E MO
C00120 Salem Wholesale 25 Willow Dr.
Salem MO 65560 E MO
C00121 Gas ‘N Go 9852 Peach St.
Nashville IL 62263 W IL
C00122 Dollar Deals 333 Main St.
Poplar Bluff MO 63901 E MO
Exhibit 2. Customer table.
J. Hoelscher, A. Mortimer Journal of Accounting Education 44 (2018) 49–59
51
• At the bottom of the toolbar where it also shows “Sheet 1,” right click on the tab and select “Rename Sheet.” Rename the sheet “Quantity Sold by Product.” 4,5
2. On what date were the most products sold (quantity-wise)?
• From the Measures pane, drag Quantity to the Rows pane. • From the Dimensions pane, drag Order Date to the Columns pane. • The order date is being aggregated at the yearly level. To disaggregate and get a daily sum, click on the + in the YEAR (Order Date) to expand to a quarterly measure. Continue to expand until you get a daily measure.
• View the line graph to identify the date with the largest quantity of products sold. • Name the graph “Quantity Sold by Date.” • Rename the sheet “Quantity Sold by Date.”
3. What is the total dollar amount of sales for vanilla ice cream bars?6
• In your new sheet, create a new calculated field, select “Analysis” from the tool bar, and then select “Create Calculated Field.”7
• Name the newly calculated field “Sales Total.” o The sales total should be calculated by multiplying Quantity by Sales Price. [Sales Price] ∗ [Quantity] o Notice you are told “the calculation is valid.” Select “OK.”
• From the Dimensions pane, drag Flavor and Product to the Rows pane. • From the Measures pane, drag your newly created field, Sales Total, to the Columns pane. • Select the bar for vanilla ice cream bars to examine the sales total for this product line. • Name the graph “Sales by Product.” • Rename the sheet “Sales by Product.”
4. Which item has the highest sales for the period? What is the amount of sales for that item?
• This answer can be determined by examining the “Sales by Product” visualization. • Document which item has the highest dollar sales for the period and what the amount of sales were for that item.
5. Which flavor has the lowest sales for the period? What is the amount of sales for that flavor?
• This answer can also be determined by examining the “Sales by Product” visualization. • As this question only asks about flavors, and not a specific product, remove Product from the Rows pane by dragging and dropping Product back into the Dimensions pane.8
• To make the visualizations accurately color coded, drag Flavor from the Dimension pane and drop it on Color in the Marks pane. o Click on Color and then select “Edit Colors.” o Make chocolate brown and vanilla an off-white color. o Select “OK.”
• Identify which flavor has the lowest sales for the period and the amount of sales for that flavor. • Rename the visualization and the sheet “Sales by Flavor.”
6. How much of the chocolate flavor sales are attributed to sales of ice cream bars?
• This answer can be determined by examining the “Sales by Flavor” sheet. • From the Dimensions tab, select “Product” and drag and drop it on Color in the Marks pane. • You will now see how much of each flavor’s sales are attributable to which product. • Hover over the chocolate bar to determine how much of the chocolate flavor sales are attributed to the sale of ice cream bars.
7. Which graph type would be recommended to show sales by day of the week? Which day of the week has the highest dollar amount of sales? What is the total dollar value of sales on that day of the week?
• In a new sheet, select Sales Total from the Measures pane, Ctrl and Day of the Week from the Dimensions Pane. • From the Show Me toolbar, see the variety of visualizations that can be used, but note which chart is recommended (bordered) to visualize your specific data.
Sales representative Region Levi Adams Western Missouri Gabby Bennet Eastern Missouri Russell Bishop Western Illinois Carly Marshall Eastern Illinois
Exhibit 3. Sales representative table.
4 Click on the tab next to your newly created sheet name (with the + sign) to create a new sheet and begin work to answer the next question. 5 Changing the sheet name first will automatically update the graph title to the same name. 6 You currently only have measures based on quantity, unit cost per item, and sales price per item. To capture the total sales price, a new measure must be created. 7 You can also right click in the Measures pane, select “Create,” and then select “Calculated Field.” 8 Alternatively, to keep the existing visualization intact, you can copy the existing sheet and begin working from the copy. Simply right click on the sheet tab of the
sheet you would like to copy and select “Copy Sheet.” Then, on the tab for a new sheet, right click again and select “Paste Sheet.”
J. Hoelscher, A. Mortimer Journal of Accounting Education 44 (2018) 49–59
52
• Double click on the suggested visualization type.9 • Verify the Sales Total measure was brought to the Columns pane and the Day of Week dimension was brought to the Rows pane to illustrate your visualization.
• Examine which day of the week has the highest dollar amount of sales. • Rename the graph and sheet “Sales by Day.”
8. Which sales representative has the lowest dollar amount of sales on Mondays? What is the total dollar amount of that sales representative on Mondays?
• This answer can be determined by examining the “Sales by Day” sheet. • From the Dimensions pane, drag Sales Representative to the Rows pane. • Examine which sales representative has the lowest dollar amount of sales on Mondays. • Rename the graph and sheet “Sales Rep Sales by Day.”
9. What are the total sales for the period?
• In a new sheet, drag Sales Total from the Measures tab to the Columns pane.10 • Examine total sales for the period. • Rename the graph and sheet “Total Sales.”
10. Which sales representative has the highest dollar amount of sales, and what was the dollar amount?
• In the same sheet, change the chart type from Automatic to Pie in the Marks card. • Drag Sales Total from the Rows pane to Angle in the Marks card. • Drag Sales Representative from the Dimensions pane to Color in the Marks card. • For reference, in the Marks card, click on Label. Select “Show Label” and under “Marks to Label,” select “All” to show the total sales revenue for each sales representative.
• Examine which sales representative has the highest dollar amount of sales. • Rename the graph and sheet “Sales by Rep.”
11. What is the total gross profit for Ivana’s Ice Cream?
• In a new sheet, create a new calculated field. o COGS = [Cost] ∗ [Quantity] o Select “OK”
• Create another calculated field. o GP = [Sales Total] − [COGS]
• Drag your newly created measure, GP, to the Columns pane. • Examine the total gross profit for Ivana’s Ice Cream.
12. What is the average gross profit among all sales representatives?
• From the Dimensions pane, drag and drop Sales Representative into the Rows pane.11 • To determine the average gross profit per sales representative, select the Analytics tab and drag and drop the average line into the Graphic pane.
• Examine the average gross profit per sales representative. • Rename the graph and sheet “Gross Profit.”
13. Which geographic region has the least customers?
• If Tableau does not already recognize the ZIP code field as a geographical field (shown by the globe icon to the left of the dimension), you will have to assign a geographic role.
• Click on the down arrow next to ZIP code in the Dimensions pane. • Hover your mouse over Geographic Role, and then select “ZIP Code/Postal Code.”
• In a new sheet, hold down Ctrl while selecting Latitude (generated) and Longitude (generated) from the Measures pane and ZIP Code from the Dimensions pane.
• Select the recommended graph from the Show Me toolbar. • Drag Region from the Dimensions pane and drop it on Color in the Marks card. • Rename the graph “Customers by Region.” • Examine the customers by geographic region.
14. Which ZIP code generates the highest gross profit?
• In the same sheet, drag Gross Profit from the Measures pane to Size on the Marks card. • Identify the ZIP code which has the largest gross profit.
9 Depending upon the type of data you are trying to visualize (i.e., how many dimensions and measures you have), it is important to know which type of chart is preferred to display your data. For example, bar charts are very intuitive and enable the user to quickly examine highs and lows in the data when you have categorical variables, whereas line charts enable a user to see changes or trends over time. Pie charts are helpful when you would like the user to examine relative proportions. Based on the dimensions and measures you are wanting to capture in a given visualization, Tableau recommends the type of visualization that would best display your given data. See the Tableau Whitepaper entitled, “Which chart or graph is right for you?” for further information regarding how to select the appropriate visualization for your data (Tableau, 2017a). 10 Note that for one measure, Tableau recommends a horizontal bar graph. 11 Note that for one measure and one dimension, Tableau recommends a horizontal bar graph. To have a vertical bar graph, you can simply move the dimension
(Sales Representative) to the Columns pane and the measure (GP) to the Rows pane.
J. Hoelscher, A. Mortimer Journal of Accounting Education 44 (2018) 49–59
53
Compile each of your answers on a memo to Ivana in numerical form.
2.1. Additional case requirements
Before you updated Ivana with the results of your previous analysis, Ivana emails you with a few additional questions. Unfortunately, your colleague who is a Tableau expert is on vacation and unable to provide you with guidance for the follow-up analysis. Address each of the following additional questions posed by Ivana in your memo:
Ivana: “I want to make sure we treat our biggest customers the best, as it is vital we do not lose their business. I might consider offering discounts to our best customers in the future. Who is our biggest customer, and what is the total revenue from that customer so far? Also, what is the total gross profit from that customer?”
“I want to make sure my best sales representatives are rewarded. Since Ivana’s Ice Cream opened, I have been paying my sales representatives a flat rate, but I need to start paying them based on their production levels. Who sold the most units in September 2016? How many units did that sales representative sell?”
“In your analysis, did you notice any trends in the data or general areas of concern that warrant my attention?”
3. Teaching notes
3.1. Learning objectives
The case uses sales transaction data to engage students in the utilization of data analytics to drive decision-making. The case has several objectives consistent with AACSB Accounting Standard A7 (AACSB, 2013) to stimulate student learning, including:
1. Applying data analytics to understand company data.12
2. Utilizing data visualization software to present results needed to make key business decisions. 3. Applying data analytics to observe key data trends.13
4. Applying data analytics to enhance the business decision-making process. 5. Enhancing verbal and written communication skills to effectively disseminate data trends and business recommendations.
Paralleling these objectives, the case also aligns with the AICPA Core Competency Framework (AICPA, 2017) as shown in Table 1.
3.2. Implementation guidance
The case was created to provide an introduction to data visualization software and the basics of data analytics. Apostolou, Dorminey, Hassell, and Rebele (2014) emphasize the need for instructional educational cases that address emerging technologies and their usage in the accounting profession. While few accounting educational case studies have been developed to focus on data analytics (Igou & Coe 2016; Janvrin, Raschke, & Dilla, 2014; Kokina, Pachamanova, & Corbett 2017), many focus more on the accounting task and less specifically on the data visualization software functionality and user interface. More specifically, many cases (Igou & Coe, 2016; Janvrin et al., 2014; Kokina et al., 2017) do not focus on understanding the underlying data set, understanding dimensions and measures or understanding when different types of graphs are suitable for use. For example, Brazelton (2000) finds Bloom’s revised taxonomy of learning to be directly applicable to accounting students, but lower-level skill sets such as remembering,
Table 1 Alignment with AICPA Accounting and Professional Competencies.
Accounting competencies: Technology and tools Students leverage data visualization software to better understand transaction data from a relational database, create new calculated
fields, aggregate fields, data mine, observe key trends and document and present results Professional competencies: Decision making Students perform various data analytic techniques to examine and visualize data trends to instill the link between data, knowledge and
insight to enhance the decision making process and make optimal recommendations Communication Whether done individually or in small groups, students fine-tune their written (and verbal skills if done in a group or presented to the
class) communication skills to effectively deliver and convey data insights in a professional memo Collaboration If done in small groups, students work productively with diverse peers to achieve a common goal
12 Results of case efficacy related to students’ ability to apply data analytics to understand company data and utilize data visualization software to present results is shown in Panel A of Table 2. Additional results of indirect evidence related to case efficacy related to case objectives is shown in Table 3. 13 Results of case efficacy related to students’ ability to apply data analytics to observe key data trends and enhance verbal and written communication skills to
effectively disseminate data trends and business recommendations to enhance the decision-making process are shown in Panel B of Table 2. Additional results of indirect evidence related to case efficacy related to case objectives is shown in Table 3.
J. Hoelscher, A. Mortimer Journal of Accounting Education 44 (2018) 49–59
54
understanding, and applying must first be acquired before higher-level skills such as analyzing, evaluating, and creating can be mastered. Oftentimes, students in undergraduate accounting information systems courses have not yet completed that course se- quence and are in differing levels of the accounting curriculum. This case study provides a basic introduction of data visualization software and encourages students to gain a thorough understanding of the data in order to determine which type of visualization would be most useful to display the underlying data to the end user, and thus work sequentially through Bloom’s taxonomy.14
The case can be used as a standalone case in or out of the classroom. Unlike many other data analytic cases (Igou & Coe 2016; Janvrin et al., 2014,15 Kokina et al., 2017) that require anywhere from two hours up to four 75-min class periods, this case can be structured to be completed within the confines of a normal 75-min class setting (or as a standalone homework assignment).
For our initial pilot study, the case was assigned to an undergraduate accounting information systems class upon learning basic database design skills within both Microsoft Excel and Access. The students had no previous exposure to data visualization software or Tableau.16 The case was assigned as an individual, take-home assignment. Students were required to submit their answers to each question, as well as the follow-up and exploratory analysis in a memorandum (sample memorandum shown in the case solutions). Students were not required to present their findings to the class. Upon completion of this introductory case study, students completed other, more advanced data analytic and data visualization cases with more ease and success than previous sections of the course where this case had not been administered.
In later semesters, the case was administered in class, in small groups of two to three students. Students were instructed to read the case in its entirety, download Tableau to a PC (if not in a lab setting), and download the Access data files from the course website prior to class. Students were given the entire class period to work through the case. Most groups were able to complete the guided questions and exploratory portions of the case, but many groups did have to finalize the formal memo outside of class as homework.
If the instructor is able to devote more class time to the case, the instructor may require students to prepare a formal presentation to the class to present their visualizations and memo. This will also facilitate class discussion, as it is unlikely all students will identify every key data trend that could be discove
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.