Excel in Data Visualization Worksheet

Step 0: Understand the data

Step 1: Define tables and change table names

Step 2: Set relationships among tables : is to link the tables using identifiers.

Primary Key: an attribute (column) that uniquely identifies each row (observation) in a table

Foreign Key: the other table’s primary key that is in current table and will be used to link the other table.

Step 3: Set and modify attributes for Visualizations using pivot table and pivot chart

Step 4: Output bar chart for Total sales amount by store locations

Step 5: Output bar chart for sales amount of Hypericum by store locations

Excel HW#4 – Ch 11
Purpose: Use excel in data visualization (book p.300-309) Mac users need to use Virtual PC to complete
this assignment.
Data: Ch11 Data file on canvas
Required:
Perform and present an analysis of total sales amount by store locations using excel.
Step 0: Understand the data
Step 1: Define tables and change table names
Step 2: Set relationships among tables : is to link the tables using identifiers.
Primary Key: an attribute (column) that uniquely identifies each row (observation) in a table
Foreign Key: the other table’s primary key that is in current table and will be used to link the
other table.
Step 3: Set and modify attributes for Visualizations using pivot table and pivot chart
Step 4: Output bar chart for Total sales amount by store locations
Step 5: Output bar chart for sales amount of Hypericum by store locations
To be submitted:
Please submit your assignment in WORD file. Put your screen shot in a WORD file, clearly label your
screenshot such as “Step1”. Put the homework title, your section, and your name in the Title of the file
and name the file the same way “HWTitle_01-JamesBond”. Use your name and section number as the
header in the Word file, such as “01_JamesBond”. Insert page numbers. Points will be deducted if not
following the instructions and you will receive a 0 if not submitted in a WORD file.
1. Screenshot after you finish each step.
– Include your name inside your screenshot
– for bar charts, include your names in the title.
– when you screenshot a large dataset, just screen shot the first 10 or more rows.
Chapter 11
Data Analytics in Accounting
Tools and Practice
VALERIE LI
SAN DIEGO STATE UNIVERSITY
Data Visualization
The process of presenting information graphically.
One way of sharing the story and turning data into
information.
Presenting relevant information to decision makers.
Information Value Chain from Chapter 1
© McGraw Hill
11a-2
Data Visualization Process
1
1. Understand the data.
2. Select the data visualization tool.
• Excel.
• Tableau.
• Power BI (not covered）
• Others.
3. Develop and present the visualization.
• Create or reinforce knowledge.
• Choose the right chart.
© McGraw Hill
11a-3
Data Visualization Process
© McGraw Hill
2
Potential
Problems/Suggested
Limits
Type of Data
Type of Chart
Purpose
Examples
Categorical
Vertical bar,
horizontal bar,
treemaps, bubble
charts
Comparisons of
performance
metrics
Revenue or profit comparisons
among divisions or stores
Too many categories;
limit number of bars
(fewer for vertical than
horizontal)
Univariate
Histograms
Frequencies, range
of values, most
likely values
Stock returns, stock betas for an
industry
Bins too large to show
detail
Multivariate
relationships
Scatter plots
Relationships,
correlations
Comparing return on equity and
stock returns
Too many trees to see
forest (too much detail
and lack of clear
relationship)
Geospatial
Maps, symbol maps
Comparisons among
locations
Relative sales by state
Too few symbols too
spread out; too many
symbols on the map
Time trends
Line charts
Comparison over
time
Sales by year and quarter
Too many lines; limit to
five or less
Proportional
Pie charts, doughnut
charts, treemaps
Comparison of parts
to a whole
Division net profit slices in total;
company net profit pie
All slices similar size; too
many slices; limit to five
slices or less
11a-4
Categorical Charts
Vertical or horizontal bar charts present categorical information.
© McGraw Hill
11a-5
Univariate Data
Histograms show the distribution of a single variable across a range of values, grouped
into bins that show the frequency or percentage of values in that bin.
© McGraw Hill
11a-6
Multivariate Relationships
Scatter plots show correlations between two continuous variables, such as height and
weight, or GPA and SAT scores. Scatter plots present detailed data for the two
variables; each dot represents a single data point.
© McGraw Hill
11a-7
Time Trends
Line charts are used to show values over time for one or more categories.
© McGraw Hill
11a-8
Proportional
Proportion charts, such as pie or doughnut charts, show shares of a total at a single
point in time. These charts should be limited to a few slices with clear differences in size.
© McGraw Hill
11a-9
Takeaways
•Data visualization is the process of presenting information graphically to share the story and
turning data into information, the last step of the AMPS model in Chapter 10.
•We will show two common visualization tools: Excel and Tableau
•We can use different ways to graph different type of data.