Discussion 15 - 205

wtfbm69

Ch7-PPT.pdf

Home >Business & Finance homework help >Operations Management homework help >Discussion 15 - 205

Spreadsheet Models Chapter 7

Introduction • Spreadsheet models are mathematical and logic-based models.

• Referred to as what-if models: • Provide easy-to-use, sophisticated mathematical and logical functions.

• Allow for easy instantaneous recalculation for a change in model inputs.

• Are less expensive.

• Often come preloaded on computers.

• Are fairly easy to use.

• The most used business analytics tool.

Building Good Spreadsheet Models Influence Diagrams

Building a Mathematical Model

Spreadsheet Design and Implementing the Model in a Spreadsheet

Building Good Spreadsheet Models (Slide 1 of 11) • Total cost of manufacturing a product is the sum of two costs:

• Fixed cost: Portion of the total cost that does not depend on the production quantity and remains the same no matter how much is produced.

• Variable cost: Portion of the total cost that is dependent on and varies with the production quantity.

• Make-versus-buy decision: comparing the costs of manufacturing in-house to the costs of outsourcing production to another firm.

Building Good Spreadsheet Models (Slide 2 of 11) Influence Diagrams:

• An influence diagram is a visual representation that shows which entities influence others in a model.

• Parts of the model are represented by circular or oval symbols called nodes, and arrows connecting the nodes show influence.

Figure 10.1: An Influence Diagram for Nowlin’s Manufacturing Cost

Building Good Spreadsheet Models (Slide 3 of 11)

Figure 10.2: An Influence Diagram for Comparing Manufacturing Versus Outsourcing Cost for Nowlin Plastics

Building Good Spreadsheet Models (Slide 4 of 11)

Building Good Spreadsheet Models (Slide 5 of 11) Building a Mathematical Model: • Consider the cost of manufacturing the required units of the Viper. • As the influence diagram shows, this cost is a function of the fixed cost,

the variable cost per unit, and the quantity required. • Define notation for every node in the influence diagram:

q = quantity (number of units) required.

FC = the fixed cost of manufacturing.

VC = the per-unit variable cost of manufacturing.

( )TMC q = total cost to manufacture q units.

Building Good Spreadsheet Models (Slide 6 of 11) Building a Mathematical Model (cont.):

• The cost-volume model for producing q units is:

• For the Viper, FC = $234,000 and VC = $2, so:

( ) $234,000 $2TMC q q= +

• Mathematical model for purchasing q units is:

• P = the per unit purchase cost: = the total cost to outsource or purchase q units

• For the Viper, since

( )TPC q

( )$3.50, $3.5 .P TCP q q= =

Building Good Spreadsheet Models (Slide 7 of 11) Building a Mathematical Model (cont.):

• Mathematical model for the savings associated with outsourcing:

the savings due to outsourcing

• Nowlin has to decide: For what quantities is it more cost-effective to outsource rather than produce the Viper?

• Mathematically, this question is: For what values of q is

( )S q =

( ) 0?S q 

Building Good Spreadsheet Models (Slide 8 of 11) Spreadsheet Design and Implementing the Model in a Spreadsheet:

• For the Nowlin Plastics problem, we have defined the following components: q, FC, VC, ( ) ( ) ( ), , , .TMC q P TPC q S q

• TMC, TPC, and S are the functions of other components, whereas q, FC, VC, and P are not.

• TMC, TPC, and S will be formulas involving other cells in the spreadsheet model, whereas q, FC, VC, and P will just be entries in the spreadsheet.

Building Good Spreadsheet Models (Slide 9 of 11) Spreadsheet Design and Implementing the Model in a Spreadsheet (cont.):

• The number of Vipers to make or buy for next year is really a decision Nowlin gets to make, hence we refer to quantity q as a decision variable.

• FC, VC, and P are measurable factors that define characteristics of the process we are modelling; hence, we refer to FC, VC, and P as parameters.

Figure 10.3: Nowlin Plastics Make-Versus-Buy Spreadsheet Model

Building Good Spreadsheet Models (Slide 10 of 11)

Building Good Spreadsheet Models (Slide 11 of 11) Spreadsheet Design and Implementing the Model in a Spreadsheet (cont.): • The general principles of spreadsheet model design and construction are:

• Separate the parameters from the model: This enables the user to update the model parameters without the risk of mistakenly creating an error in a formula.

• Document the model and use proper formatting and color as needed: A good spreadsheet model is well documented. Clear labels and proper formatting and alignment facilitate navigation and understanding.

• Use simple formulas: Clear, simple formulas can reduce errors and make maintaining the spreadsheet easier. Long and complex calculations should be divided into several cells.

What-If Analysis Data Tables

Goal Seek

Scenario Manager

What-If Analysis (Slide 1 of 14) Data Tables:

• Data Table: Excel tool which quantifies the impact of changing the value of a specific input on an output of interest.

• One-way data table: Summarizes a single input’s impact on the output.

• Two-way data table: Summarizes two inputs’ impact on the output.

Figure 10.4: The Input for Constructing a One-Way Data Table for Nowlin Plastics

What-If Analysis (Slide 2 of 14)

Figure 10.5 Results of One- Way Data Table for Nowlin Plastics

What-If Analysis (Slide 3 of 14)

Figure 10.6: The Input for Constructing a Two-Way Data Table for Nowlin Plastics

What-If Analysis (Slide 4 of 14)

Figure 10.7: Results of Two-Way Data Table for Nowlin Plastics

What-If Analysis (Slide 5 of 14)

What-If Analysis (Slide 6 of 14) Goal Seek:

• Goal Seek: Excel tool that allows the user to determine the value of an input cell that will cause the value of a related output cell to equal some specified value (the goal).

• In the case of Nowlin Plastics, suppose we want to know the value of the quantity of Vipers where it becomes more cost effective to manufacture rather than outsource.

Figure 10.8: Goal Seek Dialog Box for Nowlin Plastics

What-If Analysis (Slide 7 of 14)

Figure 10.9: Results from Goal Seek for Nowlin Plastics

What-If Analysis (Slide 8 of 14)

What-If Analysis (Slide 9 of 14) Scenario Manager:

• Scenario Manager: Excel tool that quantifies the impact of changing multiple inputs (a setting of these multiple inputs is called a scenario) on one or more outputs of interest.

• Scenario Manager extends the data table concept to cases when you are interested in changing more than two inputs and want to quantify the changes these inputs have on one or more outputs of interest.

Figure 10.10: Middletown Amusement Park Daily Profit Model

What-If Analysis (Slide 10 of 14)

What-If Analysis (Slide 11 of 14) Table 10.1: Weather Scenarios for Middletown Amusement Park

Scenarios

Partly Cloudy Rain Sunny

Season-pass Holders 3000 1200 8000

Admissions 1600 250 2400 Average Expenditure –

Season-Pass Holders $15 $10 $18 Average Expenditure –

Admissions $45 $20 $57

Cost of Operations $33,000 $27,000 $37,000

What-If Analysis (Slide 12 of 14)

Figure 10.11: Scenario Manager Dialog Box

Figure 10.12: Add Scenario Dialog Box

What-If Analysis (Slide 13 of 14)

Figure 10.13: Scenario Values Dialog Box

Figure 10.14: Scenario Summary Dialog Box

Figure 10.15: Scenario Summary for Middletown Amusement Park

What-If Analysis (Slide 14 of 14)

Some Useful Excel Functions for Modeling SUM and SUMPRODUCT

IF and COUNTIF

VLOOKUP

Some Useful Excel Functions for Modeling (Slide 1 of 6)

SUM and SUMPRODUCT:

• SUM: Function that adds up all of the numbers in a range of cells.

• SUMPRODUCT: Function that returns the sum of the products of elements in a set of arrays.

Figure 10.16: What-If Model for Foster Generators

Some Useful Excel Functions for Modeling (Slide 2 of 6)

Some Useful Excel Functions for Modeling (Slide 3 of 6)

IF and COUNTIF: • =IF(condition, result if condition is true, result if condition is false). • =COUNTIF(range, condition).

• Counts the number of components having a positive order quantity.

Illustration: • Gambrell Manufacturing produces car stereos. • Gambrell likes to keep its components inventory to a minimum. • Hence, it uses an inventory policy known as an order-up-to policy. • Order-up-to policy: Whenever the inventory on hand drops below a certain

level, enough units are ordered to return the inventory to that predetermined level.

Figure 10.17: Gambrell Manufacturing Component Ordering Model

Some Useful Excel Functions for Modeling (Slide 4 of 6)

Some Useful Excel Functions for Modeling (Slide 5 of 6)

VLOOKUP • This function allows the user to pull a subset of data from a larger table

of data based on some criterion. • General form =VLOOKUP(value, table, index, range).

where, value = the value to search for in the first column of the table. table = the cell range containing the table. index = the column in the table containing the value to be returned. range = TRUE if looking for the first approximate match of value and

FALSE if looking for an exact match of value.

Figure 10.18: Granite Insurance Bonus Model

Some Useful Excel Functions for Modeling (Slide 6 of 6)

Auditing Spreadsheet Models Trace Precedents and Dependents

Show Formulas

Evaluate Formulas

Error Checking

Watch Window

Auditing Spreadsheet Models (Slide 1 of 13) • Excel contains a variety of tools to assist you in the development

and debugging of spreadsheet models.

• These tools are found in the Formula Auditing group of the Formulas tab.

Figure 10.19: The Formula Auditing Group

Auditing Spreadsheet Models (Slide 2 of 13)

Auditing Spreadsheet Models (Slide 3 of 13) Trace Precedents and Dependents:

• Trace Precedents button: After selecting cells, this button creates arrows pointing to the selected cell from cells that are part of the formula in that cell.

• Trace Dependents button: Shows arrows pointing from the selected cell to cells that depend on the selected cell.

• Both of the tools are excellent for quickly ascertaining how parts of a model are linked.

Figure 10.20: Trace Precedents for Foster Generator

Auditing Spreadsheet Models (Slide 4 of 13)

Figure 10.21: Trace Dependents for the Foster Generators Model

Auditing Spreadsheet Models (Slide 5 of 13)

Auditing Spreadsheet Models (Slide 6 of 13) Show Formulas:

• To see the formulas in a worksheet, simply click on any cell in the worksheet and then click on Show Formulas—you will see the formulas residing in that worksheet.

• To revert to hiding the formulas, click again on the Show Formulas button.

Auditing Spreadsheet Models (Slide 7 of 13) Evaluate Formulas:

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

• Provides an excellent means of identifying the exact location of an error in a formula.

Figure 10.22: The Evaluate Formula Dialog Box for Gambrell Manufacturing

Auditing Spreadsheet Models (Slide 8 of 13)

Figure 10.23: The Evaluate Formula Dialog Box for Gambrell Manufacturing Cell B17 after Four Clicks of the Evaluate Button

Auditing Spreadsheet Models (Slide 9 of 13)

Auditing Spreadsheet Models (Slide 10 of 13) Error Checking:

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

• Clicking on the Error Checking button causes Excel to check every formula in the sheet for calculation errors.

• If an error is found, the Error Checking dialog box appears.

Figure 10.24: The Error Checking Dialog Box for a Division by Zero Error

Auditing Spreadsheet Models (Slide 11 of 13)

Auditing Spreadsheet Models (Slide 12 of 13) Watch Window:

• 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.

• Useful for large models when not all of the model is observable on the screen or when multiple worksheets are used.

Figure 10.25: The Watch Window for Cell B17 of the Gambrell Manufacturing Model

Auditing Spreadsheet Models (Slide 13 of 13)

Predictive and Prescriptive Spreadsheet Models

Predictive and Prescriptive Spreadsheet Models (Slide 1 of 2) • Decision making is difficult because of uncertainty and an overwhelming

number of choices. • Spreadsheet what-if models are descriptive models. • Basic what-if spreadsheet models can be extended to help deal with

uncertainty or the many alternatives a decision maker may face. • Predictive models can be estimated from data in spreadsheets using tools

provided in Excel: • The Regression tool and other Data Analysis tools such as Exponential

Smoothing and Moving Average allow us to develop predictive models based on data in the spreadsheet.

• What-if models help us deal with uncertainty is simulation.

Predictive and Prescriptive Spreadsheet Models (Slide 2 of 2) • Optimization models can be used to help make smart decisions. • Optimization models are prescriptive models:

• Characterized by having an objective to be maximized or minimized. • Usually have constraints that limit the options available to the decision maker.

• Optimization models are one type of prescriptive analytics. • Excel includes a special tool called Solver that solves optimization models. • Solver is used to extend a what-if model to find an optimal (or best) course

of action that maximizes or minimizes an objective while satisfying the constraints of the decision problem.