MSIS 3223 Exam 1

Cell references

\_____ in formulas can be written with either relative addresses or absolute addresses.

Relative

A(n) \_____ address uses just the row and column label in the cell reference (for example A4 or C23).

Absolute

A(n) \_____ address uses a dollar sign ($) before either the row or column or both ($A2, C$23, or $B$15).

B$4

Using a dollar sign ($) before a row label (for example \_____) keeps the reference fixed to row 4 but allows the column reference to change if the formula is copied to another cell.

$B$4

Using a dollar sign ($) before both the row and column (for example \_____) will keep the reference fixed for both the row and column.

Insert

The easiest way to locate a particular function is to select a cell and click the \_____ function button (fx) which can be found under the ribbon next to the formula bar.

MIN(range)

Function in Excel:finds the smallest value in a range of cells.

MAX(range)

Function in Excel:finds the largest value in a range of cells.

SUM(range)

Function in Excel:finds the sum of values in a range of cells.

AVERAGE(range)

Function in Excel:finds the average of the values in a range of cells.

COUNT(range)

Function in Excel:finds the number of cells in a range that contains numbers.

COUNTIF(range, criteria)

Function in Excel:finds the number of cells within a range that meet a specified criterion

Net Present Value (NPV)

Measures the worth of a stream of cash flows, taking into account the time value of money.

Discount Rate

The \_____ reflects the opportunity costs of spending funds now versus achieving a return through another investment, as well as the risks associated with not receiving returns until a later time.

positive NPV

A \_____ means that the investment will provide added value because the projected return exceeds the discount rate.

Descriptive Analytics

The most commonly used and most-well understood type of analytics. Summarizes data into meaningful charts & reports, for example, about budgets, sales, revenues, or cost.

Predictive Analytics

Seeks to predict the future by examining historical data, detecting patterns or relationships in these data, & then extrapolating these relationships forward in time.

Prescriptive Analytics

Uses optimization to identify the best alternatives to minimize or maximize some objective.

decision

Many \_____ models can be formalized using a model.

Model

A \_____ is an abstraction or representation of a real system, idea, or object. They capture the most important feature of a problem & present them in a form that is easy to interpret.

description

A model can be as simple as a written or verbal \_____ of some phenomenon, a visual representation (visual model) such as a graph or flowchart, or a mathematical or spreadsheet representation.

1. Data


2. Uncontrollable inputs
3. Decision options

The 3 types of input in a Decision Model:

Data

Assumed to be constant for purposes of the decision model.

Uncontrollable inputs

Quantities that can change but cannot be directly controlled by the decision maker.

Decision options

Controllable, and can be selected at the discretion of the decision maker. They are often called decision variables.

Descriptive Models

Explain behavior & allow users to evaluate potential decisions by asking "what-if?" questions. They describe relationships allowing a manager to make a decision, without telling a manager what to do.

Predictive Models

Focus on what will happen in the future.

Prescriptive Models

Help decision makers identify the best solution to decision problems. They use optimization - the process of finding a set of values for decision options that minimize or maximize some quantity of interest.

help managers solve problems & make decisions.

The fundamental purpose of analytics is to

1. Recognizing the problem
2. Defining the problem
3. Structuring the problem
4. Analyzing the problem
5. Interpreting results & making a decision

Phases of problem solving:

Logical

\_____ functions depend on whether one or more conditions are true or false.

condition

A \_____ is a statement about the value of a cell, either numeric or text.

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

Function in Excel: a logical function that returns one value if the condition is true & another if the condition is false.

AND(condition 1, condition 2...)

Function in Excel: a logical function that returns TRUE if all conditions are true and FALSE if not.

OR(condition 1, condition 2...)

Function in Excel:a logical function that returns TRUE if any condition is true and FALSE if not.

VLOOKUP

LOOKUP Function: looks up a value in the leftmost column of a table & returns a value in the same row from a column you specify.

HLOOKUP

LOOKUP Function:looks up a value in the top row of a table & returns a value in the same column from a row you specify.

INDEX

LOOKUP Function: returns a value or reference of the cell at the intersection of a particular row & column in a given range.

MATCH

LOOKUP Function: returns the relative position of an item in any array that matches a specified value in a specified order.

CHOOSE

LOOKUP Function: returns a value from a list based on the position in the list, specified by index_num.

Pivot Tables

Excel provides a powerful tool for distilling a complex database into meaningful information: \_____. They allow you to create custom summaries & charts of key information in the data to drill down into a large set of data in numerous ways.

filters

Dragging a field into the \_____ area in the Pivot Table field list allows you to add a third dimension to your analysis.

percentage

You may express the data in a PivotTable in various \_____ views, such as the percentage of the grand total, percent of row total, percent of column total, and other options.

Column

bar

Excel distinguishes between vertical and horizontal charts, calling the former _____ charts and the latter _____ charts.

Clustered column

A \_____ chart compares values across categories using vertical rectangles.

Stacked column

A \_____ chart displays the contribution of each value to the total by stacking the rectangles.

Line

\_____ charts provide a useful means for displaying data over time.

Pie

For many types of data, we are interested in understanding the relative proportion of each data source to the total. A \_____ chart displays this by partitioning a circle into pie-shaped areas showing the relative proportions.

Area

An \_____ chart combines the features of a pie chart with those of line charts.

Scatter

A \_____ chart shows the relationship between two variables and consists of observations of pairs of variable data.

Bubble

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

Combination

A \_____ chart displays multiple data series on the same chart using different chart types such as a column chart and a line chart.

Radar

\_____ charts show multiple metrics on a spider web, thus allowing plotting of multiple dimensions of several data series. This is a useful chart to compare performance of different entities such as factories, companies, and so on using the same criteria.

Stock

A \_____ chart allows you to plot stock prices, such as daily high, low, and close values.

Dashboard

A \_____ 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. They provide important summaries of key business info to help manage a business process or function.

1. Categorical (Nominal)
2. Ordinal
3. Interval
4. Ratio

Data is classified into 4 groups:

Categorical (Nominal) Data

Data which are sorted into categories according to specified characteristics.

Ordinal Data

Data which can be ordered or ranked according to some relationship to one another.

Interval Data

Data which are ordinal but have constant differences between observations & have arbitrary zero points. Allow meaningful comparison of ranges, averages & other statistics.

Ratio Data

Data which are continuous & have a natural zero point. Most business and economic data, such as dollars and time, fall into this category.

Frequency

Categorical data naturally define the groups in a \_____ distribution.

Frequency Distribution

A \_____ is a table that shows the number of observations in each of several non-overlapping groups.

Histogram

A graphical depiction of a frequency distribution in the form of a column chart is called a \_____.

COUNTIF

To construct a frequency distribution, you need only count the number of observations that appear in each category. This can be done using the Excel \_____ function.

Relative frequency

Frequencies may be expressed as a fraction, or proportion of the total; this is called \_____.

Cross-tabulation

A \_____ is a tabular method that displays the number of observations in a data set in different subcategories of two categorical variables.

Contingency table

A cross-tabulation table is often called a \_____.

Mutually exclusive

The subcategories of the variable must be \_____ and exhaustive, meaning that each observation can be classified into only 1 subcategory, and taken together over all subcategories, they must constitute the complete data set.

Population

A \_____ consists of all items of interest for a particular decision or investigation.

Sample

A \_____ is a subset of the population.

Sampling

Most populations are too large to deal with, thus \_____ is necessary.

inference

The purpose of sampling is to obtain sufficient information to draw a valid \_____ about a population.

Subscripted variables

In statistical notation, the elements of a data set are typically labeled as \_____.

centering

Measures of location provide estimates of a single value in some fashion represents the "\_____" of a set of data. The most common is the average.

Average

The \_____ is formally called the arithmetic mean (or simply the mean) which is the sum of the observations divided by the number of observations.

Median

The measure of location that specifies the middle value when the data are arranged from least to greatest is the \_____. Half of the data are below it, and half of the data are above it.

Mode

The \_____ is the observation that occurs most frequently. It is most useful for data sets that contain a relatively small number of unique values.

Midrange

The average of the largest and smallest values in the dataset is the measure of location known as the \_____.

Dispersion

Refers to the degree of variation in the data, that is, the numerical spread (or compactness) of the data. Several statistical measures characterize this: the range, interquartile range, variance, & standard deviation.

Range

The \_____ is the easiest to compute between the measures of dispersion, and is the difference between the maximum value & the minimum value in the data set.

Interquartile range

The difference between the 1st & 3rd quartiles is often called the \_____, or the midspread.

Variance

A more commonly used measure of dispersion is the \_____, whose computation depends on all of the data. The larger is is, the more the data is spread out from the mean.

deviations

Essentially, the variance is the average of the squared \_____ of the observations from the mean.

Standard Deviation

The \_____ is the square root of the variance. It is a popular measure of risk, particularly in financial analysis.

Coefficient of Variation (CV)

The \_____ provides a relative measure of the dispersion in data relative to the mean & is defined as CV \= Standard Deviation/Mean

risk to return

The CV provides a relative measure of \_____. The smaller the CV, the smaller the relative risk is for the return period.

reciprocal

The \_____ of the coefficient of variation, called return to risk, is often used because it is easier to interpret.

Histograms

\_____ of sample data can take on a variety of different shapes.

Skewness

\_____ describes the lack of symmetry of data.

positively

Histograms that have more mass of data on the left and tail off to the right are called \_____ skewed.

negatively

Histograms that have more mass on the right and tail off to the left are called \_____ skewed.

coefficient of skewness

The \_____ measures the degree of asymmetry of observations around the mean.

Kurtosis

\_____ refers to the peakedness or flatness of a histogram.

Proportion

Statistics such as means and variances are not appropriate for categorical data. Instead, we are interested in the fraction of data that have certain characteristics. The formal statistical measure is called \_____.

move

Two variables have a strong statistical relationship with one another if they appear to \_____ together.

Covariance

\_____ is a measure of the linear association between two variables, X & Y.

Correlation

\_____ is a measure of the linear relationship between two variables, X & Y, which does not depend on the units of measurement. It is measured by the correlation coefficient.

Correlation coefficient

For two variables, a positive \_____ indicates a linear relationship exists for which one variable increases as the other also increases.

keeps the reference to column fixed, but allows the row reference to change

Using a $ sign before a column label \________.

detects patterns in historical data and extrapolates them forward in time.

Predictive analytics: