Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

324-10 Spreadsheet Modeling

No description
by

M Karimi

on 21 November 2018

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of 324-10 Spreadsheet Modeling

Business Analytics
Descriptive Analytics
Predictive Analytics
Prescriptive Analytics
© 2016 Cengage
Descriptive Statistics
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
)
Full transcript