Discussion 5 -

Chase@2000

Camm_4e_Ch10_PPT-Module5.pptx

Home >Education homework help >Discussion 5 -

Business Analytics

Spreadsheet Models

Chapter 10

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.

Nowlin Plastics produces a line of cell phone covers. Nowlin’s best-selling cover is its Viper model, a slim but very durable black and gray plastic cover. The annual fixed cost for the Viper cover is $234,000.

This fixed cost includes management time, advertising, 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.

The exact demand for Viper for next year 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.

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.

Building Good Spreadsheet Models (Slide 3 of 11)

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

Figure 10.1 shows an influence diagram for Nowlin’s total cost of production for the Viper.

Total manufacturing cost depends on fixed cost and variable cost, which in turn depends on the variable cost per unit and the quantity required.

Building Good Spreadsheet Models (Slide 4 of 11)

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

Figure 10.2. shows expanded influence diagram that includes an outsourcing option.

Note that the influence diagram in Figure 10.1 is a subset of the influence diagram in Figure 10.2.

This modular approach of building an influence diagram for a portion of the problem and then expanding it until the total problem is conceptually modeled simplifies the process and reduces the likelihood of error.

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.

Building Good Spreadsheet Models (Slide 6 of 11)

Building a Mathematical Model (cont.):

Building Good Spreadsheet Models (Slide 7 of 11)

Building a Mathematical Model (cont.):

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, 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 and so are uncontrollable inputs to the model; hence, we refer to FC, VC, and P as parameters.

Building Good Spreadsheet Models (Slide 10 of 11)

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

Figure 10.3 shows a spreadsheet model for the Nowlin Plastics make-versus-buy decision.

Column A is reserved for labels, including cell A1 where we have named the model “Nowlin Plastics.”

The input parameters (FC, VC, and P) are placed in cells B4, B5, and B7, respectively.

We offset P from FC and VC because it is for outsourcing. We have created a parameters section in the upper part of the sheet. Below the parameters section, we have created the Model section.

The first entry in the Model section is the quantity q, which is the number of units of Viper produced or purchased in cell B11, and shaded it to signify that this is a decision variable.

We have placed the formulas corresponding to equations TMC(q) = FC + (VC × q), TPC(q) = Pq, and S(q) = TMC(q) – TPC(q) in cells B13, B15, and B17, respectively.

In cell B11 of Figure 10.3, we have set the value of q to 10,000 units. The model shows that the cost to manufacture 10,000 units is $254,000, the cost to purchase the 10,000 units is $35,000, and the savings from outsourcing is $219,000.

We see that at a quantity of 10,000 units, it is better to incur the higher variable cost ($3.50 versus $2) than to manufacture and have to incur the additional fixed cost of $234,000. It will take a value of q larger than 10,000 units to make up the fixed cost incurred when Nowlin manufactures the product.

At this point, we could increase the value of q, by placing a value higher than 10,000 in cell B11, and see how much the savings in cell B17 decreases and continue doing this until the savings are close to zero. This is called a trial-and-error approach.

Fortunately, Excel has what-if analysis tools that will help us use our model to further analyze the problem.

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.

Let us consider how savings due to outsourcing changes as the quantity of Vipers required changes. This should help us answer the question, “For which values of q is outsourcing more cost-effective?”

A one-way data table changing the value of quantity and reporting savings due to outsourcing would be very useful.

We will use the previously developed Nowlin spreadsheet for this analysis.

What-If Analysis (Slide 2 of 14)

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

The first step in creating a one-way data table is to construct a sorted list of the values you would like to consider for the input.

Let us investigate the quantity q over a range from 0 to 300,000 in increments of 25,000 units.

Figure 10.4 shows we have entered these data in cells D5 through D17, with a column label in D4.

This column of data is the set of values that Excel will use as inputs for q.

Since the output of interest is savings due to outsourcing (located in cell B17), we have entered the formula =B17 in cell E4.

In general, set the cell to the right of the label to the cell location of the output variable of interest.

Once the basic structure is in place, we invoke the Data Table tool using the following steps:

Step 1. Select cells D4:E17

Step 2. Click the DATA tab in the Ribbon

Step 3. Click What-If Analysis in the Forecast group, and select Data Table

Step 4. When the Data Table dialog box appears, enter B11 in the Column input cell: box

Click OK

What-If Analysis (Slide 3 of 14)

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

As shown in Figure 10.5, the table will be populated with the value of savings due to outsourcing for each value of quantity of Vipers in the table.

For example, when q = 25,000 we see that S(25,000) = $196,500, and when q = 250,000, S(250,000) = –$141,000.

A negative value for savings due to outsourcing means that manufacturing is cheaper than outsourcing for that quantity.

Not only have we quantified the savings due to outsourcing for a number of quantities, we know too that, for quantities of 150,000 units or less, outsourcing is cheaper than manufacturing and that, for quantities of 175,000 units or more, manufacturing is cheaper than outsourcing.

At 150,000 units, the savings due to outsourcing is only $9,000. That might not justify outsourcing if quality assurance of the outsource firm is not at an acceptable level.

We have provided management valuable information that they may use to decide whether to make or buy.

What-If Analysis (Slide 4 of 14)

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

Suppose that Nowlin has now received five different bids on the per-unit cost for outsourcing the production of the Viper. Clearly, the lowest bid provides the greatest savings.

However, the selection of the outsource firm—if Nowlin decides to outsource—will depend on many factors, including reliability, quality, and on-time delivery.

So it would be instructive to quantify the differences in savings for various quantities and bids. The five current bids are $2.89, $3.13, $3.50, $3.54, and $3.59.

We may use the Excel Data Table to construct a two-way data table with quantity as a column and the five bids as a row, as shown in Figure 10.6.

In Figure 10.6, we have entered various quantities in cells D5 through D17, as in the one-way table. These correspond to cell B11 in our model.

In cells E4 through I4, we have entered the bids. These correspond to B7, the outsourcing cost per unit.

In cell D4, above the column input values and to the left of the row input values, we have entered the formula =B17, the location of the output of interest, in this case, savings due to outsourcing. Once the table inputs have been entered into the spreadsheet, we perform the following steps to construct the two-way Data Table.

Step 1. Select cells D4:I17

Step 2. Click the Data tab in the Ribbon

Step 3. Click What-If Analysis in the Forecast group, and select Data Table

Step 4. When the Data Table dialog box appears:

Enter B7 in the Row input cell: box

Enter B11 in the Column input cell: box

Click OK

Figure 10.6 shows the selected cells and the Data Table dialog box.

What-If Analysis (Slide 5 of 14)

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

The results are shown in Figure 10.7.

We now have a table that shows the savings due to outsourcing for each combination of quantity and bid price.

For example, for 75,000 Vipers at a cost of $3.13 per unit, the savings from buying versus manufacturing the units is $149,250.

We can also see the range for the quantity for each bid price that results in a negative savings.

For these quantities and bid combinations, it is better to manufacture than to outsource.

Using the Data Table allows us to quantify the savings due to outsourcing for the quantities and bid prices specified. However, the table does not tell us the exact quantity where the transition occurs from outsourcing being cheaper to manufacturing being cheaper.

For example, although it is clear from the table that for a bid price of $3.50 the savings due to outsourcing goes from positive to negative at some quantity between 150,000 units and 175,000 units, we know only that this transition occurs somewhere in that range.

As we illustrate next, the what-if analysis tool Goal Seek can tell us the precise quantity where this transition occurs.

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.

For example, we see from the table in Figure 10.7 that, for a bid price of $3.50 and some quantity between 150,000 units and 175,000 units, savings due to outsourcing goes from positive to negative.

Somewhere in this range of quantity, the savings due to outsourcing is zero, and that is the point where Nowlin would be indifferent to manufacturing and outsourcing.

What-If Analysis (Slide 7 of 14)

Figure 10.8: Goal Seek Dialog Box for Nowlin Plastics

We may use Goal Seek to find the quantity of Vipers that satisfies the goal of zero savings due to outsourcing for a bid price of $3.50. The following steps describe how to use Goal Seek to find this point.

Step 1. Click the Data tab in the Ribbon

Step 2. Click What-If Analysis in the Forecast group, and select Goal Seek

Step 3. When the Goal Seek dialog box appears (Figure 10.8):

Enter B17 in the Set cell: box

Enter 0 in the To value: box

Enter B11 in the By changing cell: box

Click OK

Step 4. When the Goal Seek Status dialog box appears, click OK

The completed Goal Seek dialog box is shown in Figure 10.8.

What-If Analysis (Slide 8 of 14)

Figure 10.9: Results from Goal Seek for Nowlin Plastics

The results from Goal Seek are shown in Figure 10.9. The savings due to outsourcing in cell B17 is zero, and the quantity in cell B11 has been set by Goal Seek to 156,000.

When the annual quantity required is 156,000, it costs $546,000 either to manufacture the product or to purchase it.

We have already seen that lower values of the quantity required favor outsourcing.

Beyond the value of 156,000 units it becomes cheaper to manufacture the product.

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.

What-If Analysis (Slide 10 of 14)

Figure 10.10: Middletown Amusement Park Daily Profit Model

Consider the case of the Middletown Amusement Park. John Miller, the manager at Middletown, has developed a simple spreadsheet model, shown in Figure 10.10, of the park’s daily profit.

John’s model calculates the profit to be $81,500.

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

As shown in Table 10.1, John has developed three weather-based scenarios: Partly Cloudy, Rain, and Sunny.

The weather has a direct impact on four input parameters: the number of season-pass holders who enter the park, the number of non-season-pass holders (admissions) who enter the park, the amount each of these groups spends on average and the cost of operations.

The Scenario Manager allows us to generate a report that gives an output variable or set of output variables of interest for each scenario.

In this case, Scenario Manager will provide a report that gives the profit for each scenario.

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

What-If Analysis (Slide 14 of 14)

Figure 10.15: Scenario Summary for Middletown Amusement Park

The Scenario Summary report appears on a separate worksheet as shown in Figure 10.15.

The summary includes the values currently in the spreadsheet, along with the specified scenarios.

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.

Illustration:

Consider a transportation problem faced by Foster Generators.

This problem involves the transportation of a product from three plants to four distribution centers.

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

Foster has forecasted demand for the three-month period for each of the distribution centers.

The per-unit shipping cost from each plant to each distribution center is also known.

Management would like to determine how much of its production should be shipped from each plant to each distribution center.

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

Figure 10.16: What-If Model for Foster Generators

The what-if model is shown in Figure 10.16.

To minimize cost, capacity at the plants must not be exceeded, and forecasted demand must be satisfied at each of the four distribution centers.

The parameters section is rows 2 through 10.

Cells B5 through E7 contain the per unit shipping cost from each origin (plant) to each destination (distribution center).

For example, it costs $2.00 to ship one generator from Bedford to St. Louis.

The plant capacities are given in cells F5 through F7, and the distribution center demands appear in cells B8 through E8.

The model is in rows 11 through 20. Trial values of shipment amounts from each plant to each distribution center appear in the shaded cells, B17 through E19.

The total cost of shipping for this proposed plan is calculated in cell B13 using the SUMPRODUCT function.

The general form of the SUMPRODUCT function is =SUMPRODUCT(array1, array2)

The function pairs each element of the first array with its counterpart in the second array, multiplies the elements of the pairs together, and adds the results.

In cell B13, =SUMPRODUCT(B5:E7,B17:E19) pairs the per-unit cost of shipping for each origin-destination pair with the proposed shipping plan for that and adds their products: B5*B17 + C5*C17 + D5*D17 + E5*E17 + B6*B18 + . . . . . + E7*E19

In cells F17 through F19, the SUM function is used to add up the amounts shipped for each plant. The general form of the SUM function is =SUM(range) where range is a range of cells.

For example, the function in cell F17 is =SUM(B17:E17), which adds the values in B17, C17, D17, and E17: 5000 + 0 + 0 + 0 = 5000.

The SUM function in cells B20 through E20 does the same for the amounts shipped to each distribution center.

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.

Stereos are composed of a variety of components that the company must carry in inventory to keep production running smoothly.

Order-up-to policy:

If the current number of units in inventory, denoted by H, drops below M units, enough inventory is ordered to get the level back up to M units.

M is called the order-up-to point.

Mathematically, if Q is the amount we order, then Q = M – H

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

Figure 10.17: Gambrell Manufacturing Component Ordering Model

An inventory model for Gambrell Manufacturing appears in Figure 10.17.

In the upper half of the worksheet, the component ID number, inventory on hand (H), order-up-to point (M), and cost per unit are given for each of four components.

Also given in this sheet is the fixed cost per order.

The fixed cost of $120 is incurred whenever an order is placed, regardless of how many units are ordered.

The model portion of the worksheet calculates the order quantity for each component.

Depending on the number of units ordered, Gambrell receives a discount on the cost per unit.

If 50 or more units are ordered, there is a quantity discount of 10 percent on every unit purchased.

For example, for component 741, the cost per unit is $4.50, and 95 units are ordered. Because 95 exceeds the 50-unit requirement, there is a 10 percent discount, and the cost per unit is reduced to $4.50 – 0.1($4.50) = $4.50 – $0.45 = $4.05. Not including the fixed cost, the cost of goods purchased is then $4.05(95) = $384.75.

The Excel functions used to perform these calculations are shown in Figure 10.11.

The IF function is used to calculate the purchase cost of goods for each component in row 17.

The general form of the IF function is =IF(condition, result if condition is true, result if condition is false)

For example, in cell B17 we have =IF(B16 >5 $B$10, $B$11*B6, B6)*B16. This statement says that, if the order quantity (cell B16) is greater than or equal to minimum amount required for a discount (cell B10), then the cost per unit is B11*B6 (there is a 10 percent discount, so the cost is 90 percent of the original cost); otherwise, there is no discount, and the cost per unit is the amount given in cell B6.

The COUNTIF function in cell B19 is used to count how many times we order.

The range is the range to search for the condition.

The condition is the test to be counted when satisfied.

In the Gambrell model in Figure 10.11, cell B19 counts the number of cells that are greater than zero in the range of cells B16:E16 via the syntax =COUNTIF(B16:E16, “>0”).

Note that quotes are required for the condition with the COUNTIF function.

In the model, because only cells B16, C16, and E16 are greater than zero, the COUNTIF function in cell B19 returns 3.

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.

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

Figure 10.18: Granite Insurance Bonus Model

As shown in cell E3 in Figure 10.18, the bonus pool is $250,000 for this year.

The bonus bands are in cells A7:C11. In this table, column A gives the lower limit of the bonus band, column B the upper limit, and column C the bonus points awarded to anyone in that bonus band.

For example, salespeople who achieve a 56 percent above their sales target would be awarded 15 bonus points.

As shown in Figure 10.18, the name and percentage above the target achieved for each salesperson appear below the bonus-band table in columns A and B.

In column C, the VLOOKUP function is used to look in the bonus band table and automatically assign the number of bonus points to each salesperson.

VLOOKUP assumes that the first column of the table is sorted in ascending order.

The VLOOKUP function for salesperson Choi in cell C18 is as follows: = VLOOKUP(B18,$A$7:$C$11,3,TRUE)

This function uses the percentage above target sales from cell B18 and searches the first column of the table defined by A7:C11.

Because the range is set to TRUE indicating a search for the first approximate match, Excel searches in the first column of the table from the top until it finds a number strictly greater than the value of B18.

B18 is 44 percent, and the first value in the table in column A larger than 44 percent is in cell A9 (51 percent).

It then backs up one row (to row 8).

Because a 3 is in the third argument of the VLOOKUP function, it takes the element in row 8 of the third column of the table, which is 10 bonus points.

In summary, the VLOOKUP with range set to TRUE takes the first argument and searches the first column of the table for the last row that is strictly less than the first argument.

It then selects from that row the element in the column number of the third argument.

Once all salespeople are awarded bonus points based on VLOOKUP and the bonus band table, the total number of bonus points awarded is given in cell C30 using the SUM function.

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.

Auditing Spreadsheet Models (Slide 2 of 13)

Figure 10.19: The Formula Auditing Group

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.

Auditing Spreadsheet Models (Slide 4 of 13)

Figure 10.20: Trace Precedents for Foster Generator

An example of Trace Precedents is shown in Figure 10.20. Here we have opened the Foster Generators Excel file, selected cell B13, and clicked the Trace Precedents button in the Formula Auditing group.

Recall that the cost in cell B13 is calculated as the SUMPRODUCT of the per-unit shipping cost and units shipped.

In Figure 10.20, to show this relationship, arrows are drawn to these areas of the spreadsheet to cell B13.

These arrows may be removed by clicking on the Remove Arrows button in the Formula Auditing group.

Auditing Spreadsheet Models (Slide 5 of 13)

Figure 10.21: Trace Dependents for the Foster Generators Model

An example of Trace Dependents is shown in Figure 10.21. We have selected cell E18, the units shipped from Bedford to Lexington, and clicked on the Trace Dependents button in the Formula Auditing group.

As shown in Figure 10.21, units shipped from Bedford to Lexington impacts the cost function in cell B13, the total units shipped from Bedford given in cell F18, as well as the total units shipped to Lexington in cell E20.

These arrows may be removed by clicking on the Remove Arrows button in the Formula Auditing group.

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.

As an example, let us investigate cell B17 of the Gambrell Manufacturing model (Figure 10.17).

Recall that we are calculating cost of goods based on whether there is a quantity discount. We follow these steps:

Step 1. Select cell B17

Step 2. Click the Formulas tab in the Ribbon

Step 3. Click the Evaluate Formula button in the Formula Auditing group

Step 4. When the Evaluate Formula dialog box appears (Figure 10.22), click the Evaluate button

Step 5. Repeat Step 4 until the formula has been completely evaluated

Step 6. Click Close

Auditing Spreadsheet Models (Slide 8 of 13)

Figure 10.22: The Evaluate Formula Dialog Box for Gambrell Manufacturing

Auditing Spreadsheet Models (Slide 9 of 13)

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

Figure 10.23 shows the Evaluate Formula dialog box for cell B17 in the Gambrell Manufacturing spreadsheet model after four clicks of the Evaluate button.

The Evaluate Formula tool provides an excellent means of identifying the exact location of an error in a formula.

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.

Auditing Spreadsheet Models (Slide 11 of 13)

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

An example for a hypothetical division by zero error is shown in Figure 10.24.

From this box, the formula can be edited, the calculation steps can be observed (as in the previous section on Evaluate Formulas), or help can be obtained through the Excel help function.

The Error Checking procedure is particularly helpful for large models where not all cells of the model are visible.

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.

The user can monitor how the listed cells change with a change in the model without searching through the worksheet or changing from one worksheet to another.

Auditing Spreadsheet Models (Slide 13 of 13)

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

A Watch Window for the Gambrell Manufacturing model is shown in Figure 10.25.

The following steps were used to add cell B17 to the watch list:

Step 1. Click the Formulas tab in the Ribbon

Step 2. Click Watch Window in the Formula Auditing group to display the Watch Window

Step 3. Click Add Watch. . .

Step 4. Select the cell you would like to add to the watch list (in this case B17)

As shown in Figure 10.25, the list gives the workbook name, worksheet name, cell name (if used), cell location, cell value, and cell formula.

To delete a cell from the watch list, click on the entry from the list, and then click on the Delete Watch button that appears in the upper part of the Watch Window.

The Watch Window, as shown in Figure 10.25, allows us to monitor the value of B17 as we make changes elsewhere in the worksheet.

Furthermore, if we had other worksheets in this workbook, we could monitor changes to B17 of the worksheet even from these other worksheets.

The Watch Window is observable regardless of where we are in any worksheet of a workbook.

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.

End of Chapter 10

(

)

total cost to manufacture units.

TMCqq

(

)

(

)

(

)

, , , .

TMCqPTPCqSq