Final Exam Business Analytics

Look up functions are also called

Reference functions

Which of the following is true about constructing PivotTables?

Dragging a field into the Report Filter area allows addition of a third dimension to the analysis

What is a logical function that returns one value if the condition is true and another if the condition is false?

IF(condition, value if true, value if false)

Excel data validation tools are designed to

Reduce the chances that users will make a mistake

If purchase quantities of 25 units or higher are found to be large orders, and orders less than 25 are considered to be small, what IF function should be entered in H4 to be copied to H5:H10 to calculate each other's size?

=IF(D4>=25,"Large"," Small ")

If cell G7 contains the function ________, it states that if the value in cell C3 is 9, the number 7 will be assigned to cell G7; if the value in cell C3 is not 9, the number 4 will be assigned to cell G7.

=IF(C3=9,7,4)

What type of logic is used in computing and programming and business analytics?

Boolean

Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order?

INDEX(array, row_num_col_um)

The function ______________________________ returns a value or reference of the cell at the intersection of a particular row and column in a given range.

INDEX(array, row_num, col_num)

Which of the following functions is a logical function that returns TRUE if any condition is true and FALSE if not?

IF(condition, value if true, value if false)

Observations consisting of pairs of variable data are required to construct a ________________ chart.

scatter

Which of the following charts show three dimensional data?

surface chart

Roger wants to compare values across categories using vertical rectangles. Which of the following charts must Roger use?

clustered column chart

Complete the following sentence: a Dashboard....

Is a visual representation of a set of key business measures

Is derived from the analogy of an automobile's control panel

Provides important summaries of key business information

To select data bars in Excel from the Home tabe, which command would have to be accessed?

Conditional Formatting under Styles group

Phillip wishes to understand the relative proportion of each data source to the total. Which of the following harts must Phillip use?

Pie chart

Peige, a stock broker, has data consisting of price, price/earnings ratio, and market capitalization of seven different stocks on one particular day. She wishes to plot these three variables in two dimensions. Which of the following charts must Peige use?

Bubble chart

How is the color coding of quantitative data commonly called?

Heatmap

How many types of Sparklines are available?

3

What is a key performance indicator?

An important business metric

Explain the syntax of IF functions and write IF functions, how do you use AND and OR when writing IF functions?

IF(condition, value if true, value if false)

AND(condition 1, condition 2, ....)

OR(condition 1, condition 2, ...)

Describe PivotTables and how they are constructed, how can you add a third dimension to an analysis? What do they allow you to create?

PivotTables allows a person to create custom summaries and charts of key information in the data and to drill down into a large set of data in several different ways.

When applying PivotTables select any cell in the database and choose PivotTable under the insert tab and follow the steps of the wizard.

When working with logical functions do the conditions depend upon being True or False or just true?

Depend on whether one or more conditions are true or false. A condition is a statement about the value of a cell, either numeric or text.

What are Excel data validation tools designed to do?

To reduce the chances that users will make a mistake

Explain Boolean logic

is used extensively in computing and programming. The basis of logical functions is analyzing a statement in a binary manner; the statement is either true or false. This seems deceptively easy, but you can create some very sophisticated logic using these functions. The most common of logical functions are IF, AND, and OR.

How do you select a chart type in Excel from the Charts group (which tab)?

The illustration group, click chart.

Line charts

a chart that plots data points which are continuously distributed data to compare trends over time. Place time measurements on the X axis.

clustered column chart

compares values across categories using vertical rectangles. this chart is useful for comparing categorical or original data, for illustrating differences between set of values, and for showing proportions or percentages as a whole.

Pie chart

Displays a categorical variable using slices sized by the counts or percents for the categories

stacked column chart

A column chart that places the data series values within combined columns showing how much is contributed by each series.

What types of observations are required to construct a scatter chart?

we need observations that consist of pairs of variables

What does a surface chart do?

a set of three dimensional data as a mesh of surface

Explain what a bubble chart shows

size of the data makers corresponds to the value of a third variable; consequently, it is a way to plot three variables in two dimensions.

What does a Radar chart allow plotting of?

This is a useful chart to compare survey data from one time period to another or to compare performance of different entities such as factories, companies, and so on using the same criteria.

Explain use of data bars and conditional formatting and how you access them?

Displays colored bars that are scaled to the magnitude of the data values, but placed directly within the cells of a range.

Click on the conditional formatting button in the styles group within the home tab, select dat bars, and choose the fill option and color.

Describe Heatmaps and use of color-coding of quantitive data

often used to display geographic data, such as population densities, or other socioeconomic metrics by nation, state, county, and so on.

When is it best to use column sparklines or to use line sparklines?

Excel has three types of sparklines: line, column, and win/loss. Useful for time series data, while column sparklines are more appropriate for categorical data.

What is meant by a key performance indicator?

an effective dashboard should capture all the key information that the users need for making good decisions.

How is group width calculated when constructing a histogram?

using excel COUNTIF function

What is a histogram?

A graphical depiction of a frequency distribution in the form of a column chart.

Used for categorical and numerical data

How do you identify ratio data from components of a data set?

Continuous and have a natural zero point. Most business and economic data, such as dollars and time, fall into this category.

What is interval data?

continuous data with no absolute zero

What is a disadvantage of ordinal data?

no fixed units of measurement, so we cannot make meaningful numerical statements about differences between categories.

Provide examples of a measure of continuous metrics

Nominal data

Ordinal data

Interval data

Ratio data

What is the purpose of sampling?

Clearly nesscary when data must be obtained from destructive testing or from a continuous production process

What is cross tabulation?

Cross tabulation, or "cross tab," is a method of presenting and analyzing data involving two or more variables to discover relationships in the data. As the most widely used technique for organizing and presenting marketing data, the simple format and flexibility of cross tabulations permit the direct interpretation of and an easy means to communicate the data.

How are elements of a data set typically labeled in statistical notation?

Greek letters

What is interquartile range and variance?

first and third quartiles, Q3 - Q1.

includes the middle 50% of the data is not influenced by extreme values

What is standard deviation?

the square root of the variance

Following are the components of a data set containing purchase details of a shoe manufacturing company. Identify the ratio data

Item cost

Descriptive statistics

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

When constructing frequency distributions for numerical data COUNTIF is used to

Count the frequencies of each discrete value

How is Frequency Distribution calculated for categorical and numerical data?

Calculations involve frequencies that may be expressed as a fraction, or proportion of the total

Involves a table that shows the number of observations in each of several non-overlapping groups

By using a tabular summary of the relative frequencies of all categories

The purpose of sampling is to

obtain sufficient information to draw a valid inference about a population

Which of the following is true about cross-tabulation?

All subcategories together must constitute the complete data set

Nominal data

are sorted into categories according to specified characteristics.

A graphical depiction of a frequency distribution for numerical data in the form of a column chart is called a _______________________.

Histogram

While constructing a histogram, how is group width calculated?

(upper limit of the last group- lower limit of the first group) / number of groups

How does Excel use "bins?"

To define groups

What is nominal data?

Data that can be placed in categories (colours, Type A/B personality, types of attachment style).

What is ordinal data?

Data that can be placed in a sequence (e.g. best, worst, okayish; oldest - youngest).

What is interval data?

Data with numbers that are separated by equal intervals (e.g. temp, grades)

What is categorical data?

Data which has no numbers but can be put into categories (groups)