Business Analytics - Midterm
A Nonlinear Demand Prediction Model
a non-linear graph that assumes price elasticity is constant • There's a constant ratio of % change in demand to % change in price
absolute references
a reference for a spreadsheet cell that prevents the column or row from changing when dragging a function or formula that has that cell reference, (ex. $A$5) • full URLs • Notation: "$". This means a row/column will remain fixed
80/20 rule
a rule-of-thumb that suggests that 80% of a firm's sales are obtained from 20% of its customers • a small number of customers hold most of the purchasing power • This rule is generally indicitive of real life. • basis of Pareto analysis
present value
the amount of money one would need to deposit in the present in order to have a desired amount in the future (given interest rates)
col_index_num
the column in the table that contains the information you wish to have returned
Analysis Toolpak
An add-in program that contains tools for performing complex statistical analysis, such as ANOVA, Correlation, and Histogram • Analysis Toolpak VBA
Measures
numerical values associated with a metric • in, cm, L
How do you create a chart in EXCEL?
1) Highlight the range (EX: C3:K6), which includes the headings and data for each category. 2) Click on the Column Chart button and then on a chart type in the list (i.e. a clustered column chart). 3) Select data to choose which data to show, and add chart element.
How do you hide data in EXCEL?
1) Open Excel. 2) Select the row(s) you wish to hide. Select an entire row by clicking on its number on the left hand side of the spreadsheet. 3) Right-click anywhere in the selected row. 4) Click "Hide." • Or press the SHIFT key
mean (average)
=AVERAGE(data range) • EX: =SUM(B2:B95)/COUNT(B2:B95) =AVERAGE (b2:B95
Basic Excel Functions
=MIN(range) =MAX(range) =SUM(range) =AVERAGE(range) =COUNT(range) =COUNTIF(range,criteria) • COUNTA: counts the number of nonblank cells in a range • COUNTBLANK: counts the number of blank cells in a range • COUNTIFS(range1, criterion1, range2, criterion2,...range_n, criterion_n): finds the number of cells within multiple ranges that meet specific criteria for each range
AutoFilter
A built-in set of filtering capabilities • Advanced Filter: filtering for more complex criteria 1) Select Data 2) Sort & Filter 3) Filter 4) Click on the dropdown arrow in a cell Select the Bolt-nut package to filter out all of the other items. • Click "Clear filter" to restore the original data.
Value Field
A field that contains summary data in a PivotTable Active Field > Analyze > Field Settings • Change summarization method in Value Field • Settings dialog box • Select Count Modifying PivotTables Uncheck the boxes in the PivotTable Field List or drag the field names to different areas. You may easily add multiple variables in the fields to create different views of the data. • EX: drag the Source field into the Row Labels area • EX: Drag a field into the Report Filter area in the PivotTable Field list allows you to add a third dimension to your analysis.
PivotChart
A graphical representation of the data in a PivotTable. They can be created in a simple one-click fashion: 1) Select the PivotTable 2) From the analyze tab, click PivotChart. 3) Excel will display an Insert Chart dialog that allows you to choose the type of chart you wish to display.
spreadsheets
A grid that organizes data • used with commercial software packages • Microsoft Excel • flexible modeling enviornment • manipulation of data, use of models
INDEX Function
A lookup and reference value or reference to a value within a range =INDEX(array, row_num, col_num) • a lookup procedure that returns the value in a particular row and column of an array
Simulation and Risk Analysis
A methodology that relies on spreadsheet models and statistical analysis to examine the impact of uncertainty in the estimates and their potential interaction with one another on the output variable of interest
model
A representation of an object or event • descriptive, predictive, prescriptive
Operations research (OR) (Management Science) (MS)
A scientific discipline that seeks to apply the scientific method and mathematical models to the solution of a variety of business and industry problems • After WWII, scientists recognized that the mathematical tools and techniques developed for military applications could be applied successfully to problems in business and industry.
F4
Absolute ($) shortcut
Solver
An add-in application that manipulates variables based on constraints to find the optimal solution to a problem.
Decision Support Systems (DSS)
Computer-based interactive systems that help decision makers use data and models to solve unstructured problems 1. Data management. The data management component includes databases for storing data and allows the user to input, retrieve, update, and manipulate data. 2. Model management. The model management component consists of various statistical tools and management science models and allows the user to easily build, manipulate, analyze, and solve models. 3. Communication system. The communication system component provides the interface necessary for the user to interact with the data and model manage-ment components.
range
EXCEL: =MAX(data range) - MIN(data range) • The range is affected by outliers, and is often used only for very small data sets.
Logical Functions
Functions that display text or values if certain conditions exist =IF(condition, value if true, value if false) • a returns one value if the condition is true and another if the condition is false =AND(condition1, condition2, ...) • returns TRUE if all conditions are true and FALSE if not =OR(condition1, condition2, ...) • returns TRUE if any condition is true and FALSE if not.
information systems (IS)
a modern term that encompasses a broad category of modern applications, technologies, and processes for gathering, storing, accessing, and analyzing data to help business users make better decisions • modern version of Business Intelligence
Homework 3
Problems and exercises: 18, 20, 37 Please use the Example 3.5's data file to answer question 18 Please use the Example 3.7's data file to answer question 20 and 37
cross-tabulation
a method of analyzing data that lets the analyst look at the responses to one question in relation to the responses to one or more other questions • cross-tabulation table
Operations Research/Management Science (OR/MS)
The analysis and solution of complex decision problems using mathematical or computer-based models • modeling and optimization • spreadsheets
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.
Excel Ribbon
The ribbon- a feature of microsoft excel- is the row of icons and short cuts that perform commands and function, depending on what ribbon tab you have selected ANATOMY • tabs: Home, Insert, Page Layout, Formulas, ... • groups: Font, Alignment, Number, Styles, ... • buttons / menus: Buttons appear as small icons. Menus of additional choices are indicated by small triangles.
big data
a broad term for datasets so large or complex that traditional data processing applications are inadequate • heavy reliance on online and technology • gigabytes, terabytes, petabytes, etc. • volume • variety • velocity • veracity: a measure of how reliable some data is
visual chart
a chart that conveys data of multiple entities convieniently and for comparison • easily compare overall sales of different products • identify trends (EX: sales of Product D are increasing), other patterns, and exceptions
Line Charts
a chart that plots data points which are continuously distributed data to compare trends over time • Place time variable on the X-axis • It's also possible to plot multiple data series in line charts, but they can sometimes be difficult to interpret • Another option is to create separate charts for each data series.
data set
a collection of data • EX: table of historical stock prices, collection of measurements, dimensions of a good
database
a collection of organized data that allows access, retrieval, and use of data • EX: records of people, places, or things • entities: the storage units of a database (people, hard drive, etc.) ANATOMY • fields (attributes): columns of a database that correspond to each individual element of data
visualization
a conveyed message or significant conclusion that becomes apparent in light of statistics, business intelligence information systems, modeling, and optimization
objective function
a function being maximized or minimized in order to find the best possible outcome for a problem • optimal solution: the minimum or maximum quantity of interest for an objective function that conveys the most extreme quantity of a (desired/undesired) factor
COUNTIFS function
a function that counts the number of cells in a range that match one supplied criteria • used for constructing Cross-Tabulation table
IF function
a function that is used to make a decision based on a comparison and criteria • EX: =IF(F4>=10,000, "Large", "Small")
Pie Charts
a graph in which a circle is divided into sectors that each represent a portion of a whole data series • can add data labels ("+" on the right)
linear demand prediction model
a linear graph that illustrates as price increases, demand falls
mortgage loan
a loan secured by a mortgage on property in which the property itself serves as a collateral • houses, condiminiums
decisional model
a logical or mathematical representation of a problem or business situation that can be used to understand, analyze, or facilitate making a decision INPUTS • data • uncontrollable variables: quantities that are subject to change but nor directly influenced by the experimentor. • decision variables: variables that can be controlled at the discretion of the experimentor OUTPUT: measurements of performance or behavior
metric
a measuring system that quantifies a trend, dynamic, or characteristic • used for objective quantification of performance TYPES • discrete • continuous
Analytic Solver Platform
a software package developed by Frontline Systems offering robust support of optimization, simulation, and other analytic techniques within Excel as well as other platforms
cross-tabulation (contingency) table
a table in which data are compared using a row and column format • must be mutually exclusive and exhaustive data
relative frequency distributions
a table which display the proportion of observations of each class relative to the total number of observations
tag cloud
a visual depiction of keywords related to the search, with font size and position indicating relevance.
measurements
act of obtaining data associated with a metric
cell references
address of a spreadsheet cell, in which the name indicates its column and row location respectively • relative references (A2, C5, D10, ...) • absolute references ($A$2, $C5, D$10, ...)
Spatial Data
all of the information that can be tied to a specific physical location(s)
frequency distribution
an arrangement of categorical data that indicates how often a particular score or observation occurs • Excel f(x): =COUNTIF(x:y, cell_reference) • cell reference: a cell that contains the name of the list or item
Vilfredo Pareto (1848-1923)
an eminent Italian economist that observed that a large proportion of the wealth in Italy was owned by a small proportion of the people • MODERN APPLICATION: businesses often find that a large proportion of sales come from a small percentage of customers, a large percentage of quality defects stems from just a couple of sources, or a large percentage of inventory value corresponds to a small percentage of items • Pareto analysis • Pareto efficiency
Business Intelligence (BI)
an older traditional term that denotes a broad category of applications, technologies, and processes for gathering, storing, accessing, and analyzing data to help business users make better decisions • a precurser to Information Systems
Prescriptive Analytics
analytical techniques that are used to find yield the most optimal course of action • optimization, minimization, maximization • Many problems contain too much information for a human to effectively analyze and interpret meaning.
statistics
collection of methods for planning experiments, obtaining data, organizing, summarizing, presenting, analyzing, interpreting, and drawing conclusions based on data • finds unknown and noteworthy relationships of the data
exhaustive
complete, comprising everything • exhaustive data: data in a set in which the set contains all the data of an event, without excluding any information
Model Assumptions
conditions or relationships assumed as true when model was formulated • convenience, simple, coherent
Reliability
consistency of measurements
fill handle
crosshair on a small black square in the lower right corner of a selected cell that autofills cells with data as it's dragged across a row or column
ordinal data
data exists in categories that are ordered but their differences can't be determined or aren't significant • EX: rankings, service ratings
interval data
data in which the differences between values can be found and are significant, but there is no absolute zero or the zero value is arbitrary • time, temperature
information
data that has been analyzed and converted into a meaningful and useful context
ratio data
data that's characterized with absolute zero points and ratios of data are meaningful • length, width, weight, distance, monetary value
Categorical (nominal) data
data that's sorted into categories according to specified characteristics that aren't quantitative • EX: geographical regions, chain of command, names, proportions/percentages
tabular data
descriptive information, usually alphanumeric, that is stored in rows and columns in a database and can be linked to spatial data. used to compare and sort data. • EX: Shows how many units of a certain product were sold in a particular month, or to compare one month to another. • EX: Shows students, their grades, the courses they take
mutually exclusive
events that cannot occur at the same timeP(A or B) = P(A) + P(B).
filtering
extracting a set of records having certain characteristics • Excel provides a convenient way of formatting databases to facilitate analysis using sorting and filtering: 1) Find the SORT button found under the Data tab in the Sort & Filter group. 2) Select a single cell in the column you want to sort on and click the "AZ down arrow" button to sort from smallest to largest or the "AZ up arrow" button to sort from largest to smallest. 3) You may also click the Sort button to specify criteria for more advanced sorting capabilities.
future value
the amount of money in the future that an amount of money in the present will yield, given prevailing interest rates
data
facts and statistics collected together via some kind of measurement process for reference or analysis
IF function
function that's used to make a decision based on a comparison =IF(condition, value if true, value if false) • = equal • <> not equal to • > greater than • >= greater than or equal to • < less than • <= less than or equal • EX: =IF(A8 =2,(IF(B3 =5,"YES"," ")),15)
Lookup Functions for Database Queries
functions used for finding specific data in a spreadsheet =VLOOKUP(lookup_value, table_array, col_index_num, [range lookup]) • looks up a value in the leftmost column of a table and returns a value in the same row from a column you specify =HLOOKUP(lookup_value, table_array, row_index_num, [range lookup]) • looks up a value in the top row of a table and returns a value in the same column from a row you specify. =INDEX(array, row_num, col_num) • returns a value or reference of the cell at the intersection of a particular row and column in a given range =MATCH(lookup_value, lookup_array, match_type) • returns the relative position of an item in an array that matches a specified value in a specified order
IF-Type Functions
funtions of EXCEL that allows you to make logical comparisons between a value and what you expect. An IF statement can have two results: TRUE or FALSE • SUMIF(range, criterion, [sum range]) - "Sum range" is an optional argument that allows you to add cells in a different range. • AVERAGEIF • SUMIFS • AVERAGEIFS
slicers
graphical objects that contain buttons a user can click to filter the data in PivotTables and PivotCharts? • To create a slicer for any of the columns in the database, click on the PivotTable and choose Insert Slicer from the Analyze tab in the PivotTable Tools ribbon.
MATCH Function
looks for a value within a range and returns the position of that value within the range • If match_type = 1, then the function finds the largest value that is less than or equal to lookup_value. The values in the lookup_array must be placed in ascending order. • If match_type = 0, MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array can be in any order. • If match_type = -1, then the function finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array must be placed in descending order.
prescriptive decision models
models indented to find the most optimal solution to a problem or the paths necissary to achieve such solutions • optimization • objective function • contraints • optimal solution
data visualization
process of illustrating data (often in large quantities) in a meaningful model to provide insights that will support better decisions
Add-Ins
programs that can be added to excel to provide enhanced functionality • To install them, click the File tab and then Options in the left column. Choose Add-Ins from the left column. At the bottom of the dialog, make sure Excel Add-ins is selected in the Manage: box and click Go. In the Add-Ins dialog, if Analysis Toolpak, Analysis Toolpak VBA, and Solver Add-in are not checked, simply check the boxes and click OK.
lookup_value
refers to the information that will use to pull a value from the reference table
Pension Fund Management
responsibility for the investment of employee pension fund contributions. • pension: a retirement plan that requires an employer to make contributions to a pool of funds set aside for a worker's future benefit. The pool of funds is invested on the employee's behalf, and the earnings on the investments generate income to the worker upon retirement.
range lookup
returns a value only if an exact/approximate match is found • FALSE = exact match • TRUE = approximate match
Excel Formulas
spreadsheets can allow you to quickly perform automated calculations and find totals of multiple cells, rows, or columns. • Operators: (+, -, *, /, ^) • begin a function with "=" • EX: "=a-b*P^5+c/d"
PivotTables
tables used to summarize large quantities of data in a compact layout • fast summaries of key information • quickly creates cross-tabulations 1) Insert 2) Tables 3) PivotTable 4) Wizard PivotTable Field List: Select and drag the fields to one of the PivotTable areas: - Report Filter - Column Labels - Row Labels - Σ Values • The PivotTable defaults to a sum of the field in the Values area.
Pareto Analysis
technique for classifying problems according to the degree of importance of the problems, and focusing on the most important areas first; sorting data and calculating cumulative proportions • With Pareto analysis, each problem or benefit is given a numerical score based on the level of impact on the company; the higher the score, the greater its impact. • 80/20 rule: "80% of a problem can be solved by performing 20% of the work."
validity
the extent to which a test measures or predicts what it's supposed to
Predictive Analytics
the extraction of information from data for use in predicting future trends and identify behavioral patterns • patterns, relationships
median
the middle score in a distribution; half the scores are above it and half are below it • not affected by outliers
mode
the most frequently occurring score(s) in a distribution • Excel function: =MODE.SNGL(data range) • For multiple modes: =MODE.MULT(data range)
what-if analysis (sensitivity analysis)
the process of changing the values in EXCELL cells to see how those changes will affect the outcome of formulas on the worksheet
data mining
the process of the process of finding anomalies, patterns and correlations within large data sets to predict outcomes; analyzing data to extract information not apparent or offered by the raw data alone
cumulative proportions
the proportion of observations in a distribution that lie at or below a specified value
Net Present Value (NPV)
the sum of the present values of expected future cash flows from an investment, minus the cost of that investment
Net Present Value (NPV)
the sum of the present values of expected future cash flows from an investment, minus the cost of that investment • see diagram for equation EXCEL: NPV(rate,value1,value2...) • F: cash flow ($) • Rate (i): discount rate • "value1, value2, ...": equally-spaced payments or income values • t: a time period
Descriptive Analytics
the use of data to understand past and current business performance and make informed decisions • categorizing, summarizing • answers questions pertaining to past performance • EX: "How much did the company sell last year?"
midrange
the value midway between the maximum and minimum values in the original data set • (max + min) / 2 • Extreme values easily distort the results, because the midrange uses only two pieces of data, whereas the mean uses all the data • a much rougher estimate than the mean and is often used for only small sample sizes • Use EXCEL "MIN" and "MAX" functions
Business Analytics (BA)
use of data and statistical methods to gain insight into the data and provide decision makers with useful information
What are common uses of Decision Support Systems (DDS)?
• pension fund management • portfolio management • work-shift scheduling • global manufacturing • facility location • advertising-budget allocation • media planning • distribution planning • airline operations planning • inventory control • library management • classroom assignment • nurse scheduling • blood distribution • water pollution control • ski-area design • police-beat design • energy planning.
What are factors of a business that they can manipulate using relevant analytics to improve their success?
• pricing • customer segmentation: identifying demographics and target markets) • merchandising: the process of a company selecting merchandise to sell) • location • social media Manipulating these kinds of factors with an understanding of analytics can lead to better revenue, profitability, shareholder return