Analytics Quiz 2 (Ch. 3)

PivotTables

PivotTables allows you to create custom summaries and charts of key information in the data. PivotTables can be used to quickly create cross-tabulations and to drill down into a large set of data in numerous ways.

Data Visualization

The process of displaying data (often in large quantities) in a meaningful fashion to provide insights that will support better decisions. Data visualization improves decision-making, provides managers with better analysis capabilities that reduce reliance on IT professionals, and improves collaboration and information sharing.

Excel Camera Tool

This tool allows you to create live pictures of various ranges from different worksheets that you can place on a single page, size them, and arrange them easily. They are simply linked pictures of the original ranges, and the advantage is that as any data are changed or updated, the camera shots are also.

Slicers

Tools for drilling down to "slice" a PivotTable and display a subset of data.

Ogive

A chart for the cumulative relative frequency.

Bubble Chart

A bubble chart is a type of scatter chart in which the size of the data marker corresponds to the value of a third variable; consequently, it is a way to plot three variables in two dimensions.

Excel Histogram Tool

A graphical depiction of a frequency distribution for numerical data in the form of a column chart is called a histogram. Frequency distributions and histograms can be created using the Analysis Toolpak in Excel.

Pie Charts

A pie chart displays this by partitioning a circle into pie-shaped areas showing the relative proportion. Data visualization professionals don't recommend using pie charts. In a pie chart, it is difficult to compare the relative sizes of areas; however, the bars in the column chart can easily be compared to determine relative ratios of the data.

Miscellaneous Excel Charts

A stock chart allows you to plot stock prices, such as the daily high, low, and close. It may also be used for scientific data such as temperature changes. A surface chart shows 3-D data. A doughnut chart is similar to a pie chart but can contain more than one data series. A radar chart allows you to plot multiple dimensions of several data series.

Visual Data

A visual chart provides the means to easily compare overall sales of different products and identify trends.

Area Charts

An area chart combines the features of a pie chart with those of line charts. Area charts present more information than pie or line charts alone but may clutter the observer's mind with too many details if too many data series are used; thus, they should be used with care.

Quartiles

Breaks the data into four parts.

Data Profiles or Fractiles

Deciles divide the data into 10 sets, the 10th percentile , 20th percentile, and so on.

Data Bars

Display colored bars that are scaled to the magnitude of the data values (similar to a bar chart) but placed directly within the cells of a range.

Column and Bar Charts

Excel distinguishes between vertical and horizontal bar charts, calling the former column charts and the latter bar charts. A clustered column chart compares values across categories using vertical rectangles; a stacked column chart displays the contribution of each value to the total by stacking the rectangles; a 100% stacked column chart compares the percentage that each value contributes to a total. Column and bar charts are useful for comparing categorical or ordinal data, for illustrating differences between sets of values, and for showing proportions or percentages of a whole.

Pareto Analysis

Often called the "80-20" Rule It relies on sorting data and calculating the cumulative percentage of the characteristic of interest.

Frequency Distributions for Numerical Data

For numerical data that consist of a small number of discrete values, we may construct a frequency distribution similar to the way we did for categorical data; that is, we simply use COUNTIF to count the frequencies of each discrete value.

Using Bin Ranges

If you do not specify a Bin Range, Excel will automatically determine bin values for the frequency distribution and histogram, which often results in a rather poor choice. If you have discrete values, set up a column of these values in your spreadsheet for the bin range and specify this range in the Bin Range field.

Kth Percentile

Is a value at or below which at least k percent of the observations lie.

Line Charts

Line charts provide a useful means for displaying data over time. You may plot multiple data series in line charts; however, they can be difficult to interpret if the magnitude of the data values differs greatly. In that case, it would be advisable to create separate charts for each data series.

Geographic Data

Many applications of business analytics involve geographic data. Visualizing geographic data can highlight key data relationships, identify trends, and uncover business opportunities. In addition, it can often help to spot data errors and help end users understand solutions, thus increasing the likelihood of acceptance of decision models.

Icon Sets

Provide similar information using various symbols such as arrows or stoplight colors.

Descriptive statistics

Refers to methods of describing and summarizing data using tabular, visual, and quantitative techniques.

Cumulative Relative Frequency

Represents the proportion of the total number of observations that fall at or below the upper limit of each group. A tabular summary of cumulative relative frequencies is called a cumulative relative frequency distribution.

Scatter Charts

Scatter charts show the relationship between two variables. To construct a scatter chart, we need observations that consist of pairs of variables.

Color scales

Shade cells based on their numerical value using a color palette. Color-coding of quantitative data is commonly called a heatmap

Statistic

Summary measure of data.

Tabular Data

Tabular data can be used to determine exactly how many units of a certain product were sold in a particular month, or to compare one month to another.

PivotTable Dashboards

The camera tool is useful for creating PivotTable-based dashboards. If you create several different PivotTables and charts, you can easily use the camera tool to take pictures of them and consolidate them onto one worksheet. In this fashion, you can still make changes to the PivotTables and they will automatically be reflected in the camera shots.

Cross-tabulation

a tabular method that displays the number of observations in a data set for different subcategories of two categorical variables. A cross-tabulation table is often called a contingency table. The subcategories of the variables must be mutually exclusive and exhaustive, meaning that each observation can be classified into only one subcategory, and, taken together over all subcategories, they must constitute the complete data set.

Sparklines

are graphics that summarize a row or column of data in a single cell. Excel has three types of sparklines: line, column, and win/loss. Line sparklines are clearly useful for time-series data Column sparklines are more appropriate for categorical data. Win-loss sparklines are useful for data that move up or down over time. Generally you need to expand the row or column widths to display them effectively.

Statistics

both the science of uncertainty and the technology of extracting information from data.

Frequency Distribution

is a table that shows the number of observations in each of several nonoverlapping groups. Categorical variables naturally define the groups in a frequency distribution. To construct a frequency distribution, we need only count the number of observations that appear in each category. This can be done using the Excel COUNTIF function.

Dashboard

is a visual representation of a set of key business measures. It is derived from the analogy of an automobile's control panel, which displays speed, gasoline level, temperature, and so on. Dashboards provide important summaries of key business information to help manage a business process or function.

Relative Frequency

is the fraction, or proportion, of the total. We often multiply the relative frequencies by 100 to express them as percentages. A relative frequency distribution is a tabular summary of the relative frequencies of all categories.

PivotCharts

visualize data in PivotTables.