**Business Analytics**

**Descriptive Analytics**

**Predictive Analytics**

**Prescriptive Analytics**

**© 2016 Cengage**

**Descriptive Statistics**

Data Visualization

Data Visualization

**Statistical Inference**

**Regression Analysis**

**Time Series Forecasting**

**Predictive Data-mining**

**Spreadsheet Modeling**

Why

Spreadsheet

Modeling

There are specialized software packages available for descriptive, predictive, and prescriptive business analytics

They provide the user with numerous options and the capability to perform detailed analyses

Open Source:

R, Python

Commercial:

SAS, Tableau,

(used by HSBC, Barclays, Toyota, Dell, AOL, ...)

(used by Google, CitiBank, ...)

-other options are considerably more expensive

- or require substantial user training

- spreadsheets are less expensive, often come preloaded on computers

- spreadsheets are fairly easy to use

Spreadsheet models are mathematical and logic-based models. Their strength is that they provide easy-to-use, sophisticated mathematical and logical functions, allowing for easy instantaneous recalculation for a change in model inputs. This is why spreadsheet models are often referred to as

what-if

models.

Principles for building reliable spreadsheet models

Build a conceptual

model of a decision problem

Convert the conceptual model

to a mathematical model

Implement the mathematical model in a spreadsheet.

Influence Diagram

A conceptual model that shows the relationships between the various parts of the problem being modeled

Example:

Nowlin Plastics produces a line of cell phone covers. Nowlin’s best-selling cover is its Viper model. The annual fixed cost for the Viper cover is $234,000. This fixed cost includes management time and other costs that are incurred regardless of the number of units eventually produced. In addition, the total variable cost, including labor and material costs, is $2 for each unit produced.

Nowlin is considering outsourcing the production of some products for next year, including the Viper. Nowlin has a bid from an outside firm to produce the Viper for $3.50 per unit. Although it is more expensive per unit to outsource the Viper ($3.50 versus $2.00), the fixed cost can be avoided if Nowlin purchases rather than manufactures the product.

Next year’s exact demand for Viper is not yet known. Nowlin would like to compare the costs of manufacturing the Viper in-house to those of outsourcing its production to another firm, and management would like to do that for various production quantities. Many manufacturers face this type of decision, which is known as a

make-versus-buy

decision.

Conceptual Model for Production

Conceptual Model for Make vs. Buy

Decision Variables:

q

= quantity (number of units) required

Decision Parameters:

FC

= FC the fixed cost of manufacturing

VC

= the per-unit variable cost of manufacturing

Decision Functions:

TMC(q)

= total cost to manufacture

q

units

TPC(q)

= total cost to purchase

q

units

Objective Function:

What-If Analysis

Excel offers a number of tools to facilitate what-if analysis

Data Tables

Goal Seek

General Rules to Follow:

- Document the model and use proper formatting and color as needed

- Use simple formulas

Data Table quantifies the impact of changing the value of a specific input on an output of interest.

Example: Nowlin (One way data table)

Example: Nowlin (Two way data table)

More Useful

Excel Functions

For Modeling

IF and COUNTIF

SUMPRODUCT

VLOOKUP

Example (Foster Generators)

Foster Generators operates plants in Cleveland, Ohio; Bedford, Indiana; and York, Pennsylvania. Production capacities for the

three plants over the next three-month planning period are known.

Spreadsheet Model (using SUMPRODUCT)

Management would like to determine how much of its products should be shipped from each plant to each distribution center via a Whatif analysis.

Example (Granite Insurance)

The director of sales at Granite Insurance needs to award bonuses to her sales force based on performance. There are 15 salespeople, each with his or her own territory. Based on the size and population of the territory, each salesperson has a sales target for the year.

The measure of performance for awarding bonuses is the percentage achieved above the sales target. a salesperson is placed into one of five bonus bands and awarded bonus points.

Spreadsheet Model (using VLOOKUP)

Auditing Spreadsheet

Models

Trace Dependencies

Show Formulas

& Evaluate Formulas

Error Checking

Watch Window

creates arrows pointing to the selected cell from cells that are part of the formula in that cell

The Evaluate Formula button allows you to investigate the calculations of a cell in great detail.

The Error Checking button provides an automatic means of checking for mathematical errors within formulas of a worksheet.

The Watch Window, located in the Formula Auditing group, allows the user to observe the values of cells included in the Watch Window box list.

VLOOKUP(

Value you want to look up

,

range where you want to lookup the value

,

the column number in the range containing the return value

,

Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE

)