om reid sanders 3 ed lp sm supp a

A1

Spreadsheet Modeling:An Introduction

A

What Are Models? A2The Spreadsheet Modeling Process A4Evaluating Spreadsheet Models A5Useful Spreadsheet Tips A26

Important Excel Formulas A27Spreadsheet Modeling within OM:

Putting It All Together A28

Spreadsheet Modeling:An Introduction

WHAT’S IN FOR ME?OM

S U P P L E M E N T

ACC FIN MKT OM HRM MIS

Before studying this supplement, you should know or, if necessary, review

1. Familiarity with spreadsheets.

2. Knowledge of basic Excel commands.

After completing this supplement you should be able to

Explain what models are and why they are used.

Identify the main types of models.

Describe the different components of mathematical models.

Identify the recommended steps in the spreadsheet modeling process.

Explain the importance of model correctness, flexibility, and documentation.

Construct spreadsheet models applying sound modeling principles.

Enter key Excel formulas and functions in models.

Use the Goal Seek and Data Table features of Excel to perform meaningful analysis.

Develop meaningful charts representing the results of analysis.9

8

7

6

5

4

3

2

1

LEARNING OBJECTIVES

SUPPLEMENT OUTLINE

ancmat01.qxd 11/16/06 3:24 PM Page A1

� Mental modelA decision-making processwe conduct in our heads.

� Visual modelA model in which graphics or diagrams are used toconvey real objects orsituations. Examples are a map or a graph.

WHAT ARE MODELS?

A2 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION

Have you ever approached an intersection as the traffic light changed from green to

yellow? What do you do? You need to make a quick decision, whether to stop or

go. Further, you probably need to consider several factors before making this decision,

such as your current speed or the presence of police cars. In an instant of time, you

need to transform these factors into measures such as the odds of an accident if you

stop or go, or being stopped by a police officer if you go through the light. How can

you accomplish transforming these factors into a decision? Whether you realize it or

not, you use a model.

In business, models are used every day to aid in the decision-making process. Mod-

els can help executives make strategic decisions about acquisitions and expansions, for

example. They can also help clarify tactical decisions such as production and em-

ployee scheduling, route planning for vehicles, and product mixes. Sometimes models

are embedded in complex information systems, and other times they are simpler and

implemented separately. Spreadsheets have become a common platform for the devel-

opment and use of many business models because they provide the end-user with

tremendous flexibility and analytical tools.

What is a model? It turns out that everyone uses models every day of their life.

Most models are very informal, and we use them without thinking about it, such as

for the traffic-light decision. In this supplement, we focus on spreadsheet models as a

decision-making tool. Note that although we use Microsoft Excel in the examples with

this text, most of the software’s capabilities are available in competing spreadsheet

packages. This is not an Excel tutorial; it assumes you already have some familiarity

with spreadsheets. The main purpose is to help you learn to develop and use spread-

sheet models more effectively and efficiently in addressing quantitative problems. A

number of spreadsheet concepts and skills will be addressed, but if you need more of

an introduction to the use of spreadsheets themselves, specific keystrokes, and menu

choices, you should consult an Excel-specific reference.

A number of different types of models exist. The most common are mental models,which we “build” in our heads and use to make decisions. The traffic-light situationcalls for a mental model. Visual models use graphics or diagrams to represent real ob-jects or situations. For example, a road atlas represents a system of roads and other keyland features. Physical models involve objects that represent other objects, such as anarchitect’s scale model of a new building. Mathematical models use equations and re-lationships among quantities to represent situations. Many of the concepts in thistextbook are shown through the use of mathematical models. Spreadsheet models area means of implementing mathematical models.

Although there are a number of different model types, commonalities exist amongthem. First, the use of models is motivated by a decision that needs to be made, for


example, whether to stop or go at a yellow light or how much of a product to order atone time. Second, all models rely upon inputs. Inputs are quantities or factors that af-fect the situation. Inputs can be controllable or uncontrollable. Controllable inputs,also called decision variables, are quantities or factors that a decision maker canchange (usually within limits) for the current situation. For example, in the traffic-light situation, you obviously have control over whether to stop or go, how much pres-sure to apply to the brakes (if you decide to stop), or how fast to accelerate (if youdecide to go). In an order-quantity situation, the decision maker has control over howmuch quantity to order. Uncontrollable inputs, sometimes called parameters, arequantities or factors that are important to the situation but are outside the decisionmaker’s direct control. Obviously, the presence of a police officer is outside your con-trol in the traffic-light situation, but it may affect your decision to stop or go. In anorder-quantity situation, uncontrollable inputs might represent quantities such as thecost to hold inventory of the material, the cost to place an order for the material, andthe demand or usage rate of the material.

Models also have outputs. An output is a quantity or a factor that depends on howthe inputs are related to one another. In the traffic-light example, an output might in-clude the probability of an accident. An output generally changes if one or more of theinputs change. For example, an accident is probably more likely under bad weatherconditions than good weather conditions. In the order-quantity situation, the primaryoutput would be the total cost of the ordering policy. Secondary outputs would be thetotal inventory holding cost and the total ordering cost. Some models have severaloutputs, but usually one or two are considered primary.

What models do, then, is to transform inputs into outputs. In the traffic-light ex-ample, you mentally process the inputs (including the stop-or-go decision variable),“calculate” the outputs, and then make a decision. In the order-quantity situation, wewould use the mathematical relationships among the input quantities to calculate thetotal cost. Then we might consider different possible order quantities and choose theone that produces the least total cost.

A model, however, needs to do more than transform inputs to outputs; it musthave a purpose. That is, we need to know how a model will help us make a decision.The mental model for the traffic-light situation represents, in a sense, a prediction ofthe future course of events and helps you to make the decision whether to stop or go.The model in the order-quantity situation represents our future orders, holding costs,and ordering costs. Models should focus on those factors most important to the situa-tion, thereby ignoring other variables. In the order-quantity situation we did not di-rectly consider whether demand was seasonal. This could certainly be included, but itwould make the model more complicated. Whether or not we include a particular fac-tor can be a difficult modeling decision. The benefits of including it must be weighedagainst the increased complexity of the model.

We can now more fully define a model. A model is a purposeful representation ofthe key factors in a situation and the relationships among them. It is an abstraction ofthe real situation, and should incorporate enough detail so the results meet the cur-rent needs, but omit unnecessary details. As Albert Einstein said, “Everything shouldbe made as simple as possible, but not simpler.” This statement applies perfectly tomodeling.

Figure A-1 shows a generic diagram of a mathematical model. Note the two inputtypes; the “model” box, which is really a set of relationships among the inputs; and theoutputs. Thinking back to this diagram will be useful as we begin to develop and usespreadsheet models.

WHAT ARE MODELS? • A3

� Physical modelA model in which physicalobjects are used to representthe real objects or situation,usually on a smaller scale.Examples are model cars and buildings.

� Mathematical modelA model in whichquantitative relationship are used to represent a realsituation or phenomena.An example is a weather-prediction model.

� Spreadsheet modelA mathematical modelimplemented in the form of a computer spreadsheet.

� InputsQuantities or factors thataffect the decision-makingsituation.

� Controllable inputs(decision variables)Quantities or factors that adecision maker can changefor the current situation. Anexample is the order quantityin an inventory planningsituation.

� Uncontrollable inputs(parameters)Quantities or factors that a decision maker cannotcontrol for the currentsituation. An example is theunit cost of a raw materialthat must be purchased to produce a product.

� OutputA quantity or factor that iscalculated from the inputs of a model and is of interestto the decision maker.

� ModelA purposeful representationof the key factors in a situationand the relationships amongthem.


THE SPREADSHEET MODELING PROCESS


� Base caseThe model containing the“default” or “given” values forthe inputs. This is normallythe starting point for theanalysis.

Uncontrollable Inputs(Parameters)

Mathematical Model:set of relationships

(spreadsheet formulas)Controllable Inputs(Decision Variables) Outputs

Basic mathematical model

FIGURE A-1

You need to follow basic steps in order to develop an effective spreadsheet model.

1. Ironically, the first step is to turn off the computer and instead draw a picture tobetter understand the situation. Identify the uncontrollable inputs, the decisionvariables, and the outputs. Define the logic necessary to transform the inputsinto the outputs.

2. On paper, sketch out an overall plan for the model. In general, group the inputstogether. Determine where the inputs, intermediate calculations, and outputswill go. Plan to highlight the key inputs and outputs to make the model easier touse for what-if analysis. Determine the formulas relating the inputs to the inter-mediate calculations and outputs. This can be very simple for some models (i.e.,Profit � revenue � expenses), or it may be quite complicated. In general, thetime spent planning a model in this step is normally much less than the timespent debugging an unplanned, completed model.

3. Develop the base case spreadsheet model. Group the inputs together logically. Itusually helps to use a color-coding scheme so the user can quickly determinewhat are the inputs and outputs of the model. Break down the intermediate cal-culations so that each formula is relatively simple. You can then more easily spotand correct errors. Research has shown that most spreadsheet-model developersbelieve their products to be error-free, but this assessment is usually wrong!Thus, you need to scrutinize formulas and results during and after the spread-sheet development effort. Use specific text labels, including units of measure, sothat others reading the model can follow your thought process. The outputsshould also be clearly labeled and color-coded. For large models (loosely speak-ing, those that do not fit in a window screen), it is often very helpful to provide asummary of the outputs next to the inputs.

4. Test the spreadsheet model using trial values. Verify the results by hand, if possi-ble. If you have broken down the intermediate calculations into relatively simpleformulas, this step is much easier.

5. Use the model to perform the needed analysis. This may involve a relatively sim-ple calculation, preparation of a chart, or more substantial analysis. Two com-mon types of analysis are scenario analysis and sensitivity analysis. A scenario isa specific set of conditions that could occur in a real situation. A common prac-tice is to look at the base-case, best-case, and worst-case scenarios. Scenarioanalysis helps a decision manager gain additional insight into a situation. Sensi-tivity analysis involves studying the changes to the output of the model (e.g.,profit) as one or more of the inputs (e.g., demand) change. Sensitivity analysishelps to identify the inputs that cause the most change in the output. Since thevalues for the inputs are often just estimates, it is important to understand this


sensitivity. Break-even analysis is one special case of sensitivity analysis. No mat-ter how simple or complex the analysis, the time invested developing a high-quality base-case model will pay off when you need to do additional analysis.Spreadsheet programs contain a number of tools to assist the analyst; we willprovide an overview of some of these later in this supplement.

6. Document the model so that others can easily understand it. Remember, othersmay not think of the problem in exactly the way you do, so descriptive labelsand a logical layout are extremely important. Indeed, ideal spreadsheet modelsare almost “self-documenting” as a result of the way you organize and labelthem. Cell comments can also be used to document and explain key formulas.

Do not become discouraged if, after you follow these steps, you find that yourmodel does not work or appear the way you intended. All modelers experience this.Realize that modeling is a learning process, and this learning process itself often leadsto better decisions than you would make had you not developed a model. Sometimesthe things you learn about the real situation during the modeling process are evenmore important than the numerical results of the model. In any event, models oftenneed substantial revision before you’re pleased with the results.

EVALUATING SPREADSHEET MODELS • A5

� Correct modelA model is correct if itproduces the numericallycorrect values for the outputsfor the current values of theinputs.

� Flexible modelA model is flexible if itproduces the numericallycorrect values for the outputsfor any legitimate values ofthe inputs, without makingany changes to the formulasin the model.

� Documented modelA model is documented ifsomeone else generallyfamiliar with the situationcan understand the modelwithout having the modeldeveloper explain it in detail.A documented model can be put away for months at atime, and when viewed againby the modeler, is readilyunderstandable.

EVALUATING SPREADSHEET MODELSAfter you develop a spreadsheet model, how do you evaluate it? One useful way tothink about the quality of a spreadsheet is to assess it along three dimensions. Specifi-cally, a spreadsheet model should be correct, flexible, and documented. A spreadsheetmodel should produce the correct answer for the information given. Usually we thinkof this in the context of the base case: for the “given” values for the inputs, does themodel calculate the correct results? However, a spreadsheet model needs to do morethan simply calculate the correct answer.

A spreadsheet model must be flexible in producing accurate results even if the userchanges any of the inputs (controllable or uncontrollable). To provide this flexibility,users should enter each input only once in the model. For example, consider a modelthat includes a unit cost as an input. This value, say $3.50, would be entered into a sin-gle cell, for instance, B8. Any other cells using unit cost in their calculations wouldthen reference cell B8 rather than having the $3.50 value “hard-coded” inside its for-mula. In this way, the user only needs to change the data item in a single cell to analyzea new problem. Flexibility is often ignored by people developing a spreadsheet theythink will only be used once. Most models in the real world are used repeatedly, withdifferent input data. Even if you think yours is a model that will be used only once,your model will be easier to explain to others if every input is shown explicitly andonly once. It is vital to get into the habit of building flexible models from the start.The key is to keep all the inputs of the model separate from the formulas of the model.That is, never embed a numerical input inside a spreadsheet formula. If you do, andthat problem input changes, then the user must (1) know how to edit formulas, and(2) remember to edit the formula in order for the solution to be correct.

Finally, a spreadsheet model should be well documented. To ensure that others un-derstand the ability of a model, follow these guidelines:

● Include descriptive text labels for all numerical inputs and calculations. Includein the label the units of measure of the quantity (e.g., money, hours, pounds,square feet).


● Use numerical formatting (Format/Cells/Number) to display the numerical in-formation in the model. The most common useful formats are number (fixednumber of decimal places), currency, and percentage. Others are also useful incertain situations (e.g., date, time, custom formats).

● Apply appropriate cell formatting, such as fill colors, font colors, text attributes(e.g., bold, italic), and cell borders. As noted, color-code the inputs and the outputsof the model. A judicious use of coloring can make certain items in a model standout for the user. Do not overformat, however, as too much is distracting. In the ex-amples provided in this supplement, we use a standard formatting convention.

● Insert Cell Comments for key cells. To use Excel’s Comment feature, right-clickany cell. From the submenu, click Insert Comment. You can type anything youwant into the box that pops up. This box will remain “attached” to the cell and caneven be printed with the model. Use comments to add explanatory informationabout a calculation or assumption that does not have to be in the model itself.They can help remind you of the logic behind a calculation or the justification fora particular value of an input. In many of the spreadsheet examples in this supple-ment, we used the Cell Comment feature to show the formula in a cell.

● Print a copy of the spreadsheet itself with row and column headings, gridlines,and a footer. Also, print a copy of the spreadsheet formulas.

Ideally, a spreadsheet model should be self-documenting. That is, there should belittle work involved in documenting a spreadsheet model if you adopt a logical struc-ture for the data and calculations, add descriptive labels that include the units ofmeasure, use numerical and cell formatting appropriately, and provide additionalcomments to highlight or explain key, or possibly more difficult, aspects of the model.


Before You Go On

A model is a purposeful representation of a real situation, designed to address a particular situation. We usemental models every day to make many decisions. Visual and physical models help people to better understanda situation. Mathematical models comprise a set of relationships linking inputs to outputs. Mathematical mod-els are often implemented using spreadsheets. The spreadsheet modeling process consists of understanding theproblem, planning the spreadsheet on paper, developing the base-case model, testing the model, using themodel for analysis, and documenting the model. Scenario and sensitivity analysis are useful tools used to gainmore insight into the problem. Spreadsheet models should be correct, flexible, and documented.

EXAMPLE A.1

Modeling theBreak-even Point

Sports Feet Manufacturing is considering whether to produce a new line of footwear. The companyhas considered both the processing needs for the new product and the market potential. The com-pany also estimated that the variable cost for each product manufactured and sold is $9 and the fixedcost per year is $52,000. The selling price is $25 per pair.

• Before You Begin: This example demonstrates the spreadsheet model development process.Before beginning to develop the spreadsheet model, make sure that you have the necessary input dataand that you understand the quantitative relationships. Pay particular attention to the instructionprovided on the use of Excel tools.

• Solution:The company is mainly interested in the break-even point; that is, how many pairs of shoes must bemade and sold (assuming it can sell all that it makes) in order for the total revenue to equal the total


Planning the ModelRecall that the first step in developing a good spreadsheet model is to understand theproblem and to determine the inputs and the outputs. Part of this step, then, is obvi-ously determining what main question the model will answer. Although we willeventually want to find the break-even point, the model should answer a morefundamental question: “For some quantity made and sold, what profit (or loss) will beattained?” Thus, the primary output of the model (at least initially) will be the annualprofit (loss). In order to compute the profit, we will need the annual revenue and an-nual total cost. Our inputs include the annual fixed cost ($52,000), the unit variablecost ($9), and the unit selling price ($25). In the context of the current situation, theseare uncontrollable inputs. We also have as an input the quantity made and sold. Thecompany can control this quantity (at least to a certain extent), so we classify this as acontrollable input. Notice that sometimes the distinction between a controllable andan uncontrollable input is not always clear. Figure A-2 contains a “black box” diagramof the model, showing the inputs and the outputs.

Before starting Excel, we need to think about the organization of the spreadsheet,as well as the relationships (formulas) inside the “black box” of Figure A-2. Ourspreadsheet should clearly identify the inputs and the outputs. We separate the inputsfrom the outputs, so the user knows exactly which values to change in order to per-form what-if analysis. This model is small enough so that everything will fit onto oneworksheet nicely. If it were a larger problem, the organizational question would besomething we would need to consider carefully in order to make a useful, coherentmodel.

The mathematical relationships of the model essentially become the formulas inthe spreadsheet. We will write them out first, however. When developing models, writ-ing out some of the key formulas before committing them to the spreadsheet providesa helpful debugging tool.

In the relationships listed next, the yellow color-coding indicates an input value tothe model and a green color indicates the primary output of the model.

cost. This situation is simple enough that you can easily calculate this quantity. Therefore, we will usethis example to demonstrate some of the key spreadsheet modeling concepts and guidelines. Fur-thermore, there are other reasons a spreadsheet model is appropriate in this case:

• If we design the model to be flexible, then we can solve any problem with this same basic struc-ture very quickly using the spreadsheet model.

• Spreadsheets provide a good environment for what-if, sensitivity, and scenario analysis.

• Spreadsheets provide the ability to create graphs, which can help to communicate the analysisand result to others (e.g., management).


Model:

Set of relationships(formulas) to

translate the inputsinto the outputs

Annual RevenueUnit Sales Price

Annual Fixed Cost

Unit Variable Cost

Quantity Made and Sold

Annual Total Cost

Annual Profit (Loss)

Black box diagram for Sports FeetManufacturing

FIGURE A-2


Annual profit � annual revenue � annual total cost

Annual revenue � unit selling price � quantity made and sold

Annual total cost � annual fixed cost � annual variable cost

Annual variable cost� unit variable cost � quantity made and sold

Since we have written out four equations, our spreadsheet model will essentiallycontain four formulas in cells. Note that we try to keep each equation as simple aspossible, rather than combine many calculations in the same formula. For example,with some substitution, you could instead calculate the annual profit as follows:

Annual profit � [unit selling price � quantity made and sold] � annual fixed cost � [unit variable cost � quantity made and sold]

This formula is certainly correct from an algebraic standpoint, and if implementedcorrectly, the spreadsheet model would calculate the correct annual profit. However,you should break up larger calculations into smaller ones for the following reasons:

● To determine intermediate values. For example, management is often interestedin both annual revenue and annual total cost, in addition to annual profit. Thespreadsheet should show these values explicitly.

● To avoid errors. Mistakes are commonly introduced when writing longer formu-las. Further, you can more easily spot any errors if you have broken down com-plex calculations into smaller pieces. Thus, it is better to write a larger number ofsimple formulas, rather than a smaller number of more complex formulas.

Implementing the Model in ExcelWe’ll now turn to the spreadsheet model construction itself. First, use a descriptivelabel for the worksheet (as opposed to saving it as “Sheet1”), and include a short de-scription of the problem in the upper left of the sheet (see Figure A-3). We’ll walkthrough the logic of the model here, which follows directly from the identification ofinputs, outputs, and relationships we’ve already done. The cells containing formulashave cell comments attached to them (Insert/Comment) to help document the model.


1

23456789

10111213141516

A B C

Example A.1Profit/Loss Analysis for Sports Feet Manufacturing

InputsUnit Sales Price $25.00

Annual Fixed Cost $52,000Unit Variable Cost $9.00

Quantity Made and Sold 2500 (user-specified quantity)

Calculations and OutputsAnnual Revenue $62,500

Annual Variable Cost $22,500Annual Fixed Cost $52,000Annual Total Cost $74,500

Annual Profit (Loss) ($12,000) (profit or loss at the user-specified quantity)

B12: =B5*B9

B13: =B9*B7

B14: =B6

B15: =B13+B14

B16: =B12-B15

Spreadsheet model for SportsFeet Manufacturing

FIGURE A-3


In the spreadsheet, we clearly separated the inputs from the outputs. The value in-serted for the “Quantity Made and Sold” in cell B9 is just a test value at this point. Re-call that this quantity is a controllable input (decision variable), so the decision makercan choose the value for this cell. By making this an input quantity, the user can easilyuse the model as a “what-if” tool. Based on the inputs, the calculations are performed.We have used four equations to represent the relationships of the model, which trans-late into the following five formulas in this spreadsheet:

● Cell B12: Enter the formula “�B5*B9” to calculate the annual revenue.

● Cell B13: Enter the formula “�B9*B7” to calculate the annual variable cost.

● Cell B14: Enter the formula “�B6” to “calculate” the annual fixed cost. Note thatthis is not a calculation, nor is this formula absolutely required, as the fixed costis already entered as an input. However, having the model repeat the value herehelps to make the logical flow clear. When you do this, be sure to enter the for-mula “�B6”. Do not use Excel’s copy and paste feature to copy cell B6 to cell B14.If you use copy and paste, you will lose the link back to the input. Remember, wewant to allow the user to change only the input cells.

● Cell B15: Enter the formula “�B13�B14” to calculate the annual total costs.

● Cell B16: Enter the formula “�B12�B15” to calculate the annual profit.

Note the logical flow of the calculations. First we calculate revenue, then the componentsof cost, then the total cost, and then profit.

Assessing the ModelAt this point, we should now assess our spreadsheet model. Specifically, is the modelcorrect, flexible, and documented? To verify correctness, carefully scrutinize the for-mulas. In addition, verify the calculations of the model by hand. For the input valueswe used:

We have now verified the correctness of the model (for the current input quanti-ties). But is the model flexible? Yes, because we have entered each input exactly once(cells B5:B7, and B9), and used cell references everywhere else to refer to them.Therefore, the model will correctly calculate revenue, cost, and profit for other inputvalues.

We now ask our last assessment question, “Is the model relatively self-document-ing?” Referring back to the previous discussion, our model should include appropri-ately formatted numerical values, descriptive and specific text labels, and clearlydesignated input and output cells. As Figure A-3 shows, our model meets these re-quirements. To format cells as dollars, the “Currency” format is recommended (For-mat/Cells/Number). For “large” dollar amounts, show whole-dollar amounts. Forsmaller values, such as the unit sales price and the unit variable cost, show the centsfor greater precision. Note that when entering formulas (such as for revenue), Excelsometimes uses the formatting of one of the cells involved in the formula. If this for-matting is not appropriate, change it to something that is!

Annual profit (loss) � $62,500 � $74,500 � �$12,000

Annual total cost � $52,000 � $22,500 � $74,500

Annual variable cost � ($9/pair) � (2500 pairs) � $22,500

Annual revenue � ($25/pair) � (2500 pairs) � $62,500



As a further way to document our model, we should create a page footer containingimportant information about the file. The recommended footer includes the filename, the worksheet name, author, date, and time. As Figure A-4 shows, you can easilycreate a custom footer in the File/Page Setup/Header and Footer dialog box. Everytime you print the worksheet, Excel will automatically update the footer information,so you will always know which copy you printed last.

Finally, print a copy of the spreadsheet and the spreadsheet formulas. First, set upthe spreadsheet to print showing “Gridlines” and “Row and Column Headings.” Thegridlines are the fine horizontal and vertical lines that separate the cells of the work-sheet on the screen, but these do not print by default. The row and column headingsare the (1,2,3, . . .) and (A,B,C, . . .) identifiers, respectively. Without these, you willfind evaluating a printed spreadsheet more difficult. To set gridlines and row and col-umn headings to print, go to File/Page Setup/Sheet, and then click the “Gridlines” and“Row and column headings” boxes. See Figure A-5.

To print the spreadsheet formulas, first make a copy of the worksheet. Right-clickthe worksheet tab, click “Move or Copy,” and then in the resulting box, be sure tocheck “Create a copy.” An exact copy of the current worksheet will be created. On the


Custom footer dialog box

FIGURE A-4

Page setup dialog box to set usefulprinting options

FIGURE A-5


copy, go to Tools/Options/View, and check the “Formulas” box (see Figure A-6). Excelthen doubles all column widths, so you may need to adjust the width of columns.Print this worksheet as a record of the formulas. Figure A-7 shows this printout.

Using the Model for AnalysisUntil now, we focused on the logic of the model, as well as ensuring that it is correct,flexible, and documented. All of this could have been done without knowing any ofthe specific numerical values for sales price, fixed cost, variable cost, and quantity. Wenow turn to using the model to help guide decision making.

We know the values for unit sales price ($25), unit variable cost ($9), and annualfixed cost ($52,000). We can now perform “what-if” analysis; that is, we can enter anyvalue into the Quantity Made cell (cell B9), and the model will calculate the annual


Options dialog box to show formulas

FIGURE A-6

Printout of formulas forSports Feet Manufacturing

FIGURE A-71

23456789

101112131415161718

A B C


InputsUnit Sales Price 25

Annual Fixed Cost 52000Unit Variable Cost 9


Calculations and OutputsAnnual Revenue =B5*B9

Annual Variable Cost =B9*B7Annual Fixed Cost =B6Annual Total Cost =B13+B14

Annual Profit (Loss) =B12-B15 (profit or loss at the user-specified quantity)

Breakeven Point =B6/(B5-B7) (quantity at which revenue = cost)


revenue, cost, and profit. For that matter, our what-if analysis can also explore changesto the uncontrollable inputs. Here we’ll vary the order quantity from 0 to 8000 pairs.Table A-1 shows the results provided by the model.

For every additional 1000 pairs that the company makes and sells, profit increasesby $16,000. Where does this number originate? Note that from the original problemdata, the selling price is $25 and the variable cost is $9. Therefore, the contribution toprofit is $25 � $9 � $16 per pair, or $16,000 for 1000 pairs. Obviously this findingdoes not require a spreadsheet model. More complex situations that involve many fac-tors can make it very difficult to find this type of “marginal” result from a direct analy-sis of the input data. However, if you create and use a flexible spreadsheet model, youcan easily find the results.

What is the break-even point, that is, the sales level at which profit is equal to $0?From our what-if analysis, we can see that the break-even point lies somewhere be-tween 3000 and 4000 pairs. One quick method of finding the exact value is to useExcel’s Goal Seek feature. We’ll then check the results using the algebraic approach.

Goal Seek Approach Excel’s Goal Seek feature does exactly what we need here. Youaccess Goal Seek by going to Tools/Goal Seek. A dialog box appears as shown in Fig-ure A-8. The basic idea is that you provide Goal Seek a “set cell,” a “value,” and “bychanging cell.” The set cell is the cell in your model that you want to force to a certainvalue. In our case, we want to force the profit cell (B16) to the value zero. We want todo this by changing (more precisely, allowing Excel to change) the value of the Quan-tity Made and Sold (cell B9). In the Goal Seek dialog box, type “B16” into the Set Cellfield (or click on cell B16), type “0” into the “To value” field, and type “B9” into the“By changing cell” field (or click on cell B9). See Figure A-9. The dialog box hereshows absolute cell references ($ signs in the references). Excel puts these in when youclick on the cells in the worksheet rather than when you type them directly into the


� Goal SeekAn analysis tool in Excel thatallows one to search for thevalue of an input that resultsin an output becoming aspecific value.

0 �$52,000

1000 �$36,000

2000 �$20,000

3000 �$4,000

4000 $12,000

5000 $28,000

6000 $44,000

7000 $60,000

8000 $76,000

What-If Analysis: Profitas a Function of QuantityMade and Sold

TABLE A-1

Quantity Profit

Goal Seek dialog box (empty)

FIGURE A-8

Goal Seek dialog box (completed)

FIGURE A-9


box. For the Goal Seek feature, it doesn’t matter whether you use absolute or relativereferences. After clicking “OK,” you should notice that Excel changes the value in cellB9 to 3250. As a result of this, the profit (cell B16) is equal to $0. When Goal Seek fin-ishes, a box similar to Figure A-10 appears. If you want to keep the results, click “OK”;if you want to revert to the previous values, click “Cancel.” In some cases, the resultsmay not be exactly 3250 and $0, respectively, but will be very close.

Algebraic Approach As discussed in the textbook, the break-even point can befound using the formula where F � fixed cost, SP � unit selling price, andVC � unit variable cost. In this case, � 3250 pairs. If Sports Feet Manufac-turing makes and sells more than this quantity, the company makes an overall profit; ifless, it incurs a loss. We can certainly add this formula to our Excel model. The modelnot only allows a user to change the trial production quantity but it also calculates thebreak-even quantity for any values of the fixed cost, selling price, and variable cost. AsFigure A-11 shows, the formula “�B6/(B5�B7)” has been entered into cell B18 (notethe parentheses in the formula; they are important).

Thus, we have two ways to find the break-even quantity. You may wonder why wewould want to use the Goal Seek method. The algebraic approach when implemented

Q � $52,000

$25 � $9

Q � FSP � VC,


1

23456789

1011121314151617181920

A B C


InputsUnit Sales Price $25.00

Annual Fixed Cost $52,000Unit Variable Cost $9.00


Calculations and OutputsAnnual Revenue $62,500

Annual Variable Cost $22,500Annual Fixed Cost $52,000Annual Total Cost $74,500

Annual Profit (Loss) ($12,000) (profit or loss at the user-specified quantity)

Breakeven Point 3,250 (quantity at which revenue = cost)

B16: =B6/(B5-B7)

B12: =B5*B9

B13: =B9*B7

B14: =B6

B15: =B13+B14

B16: =B12-B15

Goal Seek completion message

FIGURE A-10

Expanded model with break-even point calculation

FIGURE A-11


in Excel automatically calculates the break-even quantity. However, for many real,complex business situations, no formula can be easily found, but this poses no diffi-culty for Goal Seek! Also, we can use Goal Seek for more than the simple break-evenquantity found here. For example, suppose we want to find the quantity that will allowus to earn a profit of $10,000, not zero. While we could adjust the algebraic formula,an easier method is to change the “To value” field from “0” to “10000” in the Goal Seekdialog box. The result (which you can verify) is that we need to make and sell 3875pairs in order to make a profit of $10,000. This required no additional algebraic work,just a simple running of Goal Seek.

Goal Seek provides other advantages. Suppose we are fairly confident we can sell4000 pairs at a price of $25 each, but we are unsure about our estimate of the $9 perpair variable cost. This uncertainty is realistic, especially when dealing with new prod-ucts or costs subject to market uncertainty. Now the question is, “To what value canour unit variable cost rise and still allow us to make a profit?” To address this, we enter“4000” into cell B9 (Quantity). Then using Goal Seek, we enter “B16” and “0” into the“Set cell” and “To value” fields as before, but we enter “B7” (unit variable cost) into the“By changing cell” field. Clicking “OK,” we find that our unit variable cost can be ashigh as $12 before we start incurring a loss.

These are just a couple of ways we can use Goal Seek to perform additional interac-tive analysis with our flexible spreadsheet model. As you continue building spread-sheet models, you will find many imaginative uses for Goal Seek if you understand itscapabilities.

Adding Data TablesAnother extremely valuable feature of Excel is called the Data Table, which performsrepetitive what-if analysis quickly and easily. For example, the what-if analysis shownin Table A-1 can be set up and run automatically with a Data Table. We will also createa graph, driven directly from the Data Table results. Best of all, when you change oneor more of the model inputs, Excel automatically updates both the Data Table andgraph.

Briefly, you provide a list of input values to the Data Table. It then inserts this rangeof input values one at a time into a cell you specify. Excel calculates the spreadsheetand remembers the results. Then the Data Table inserts the next input number in therange, repeating the process. The user completely controls the values.

The best way to understand Data Tables is to work through an example. We en-courage you to work through this example at the computer. It takes a couple of timesto get used to the process of setting up and running Data Tables.

Let’s say we want to create a Data Table that varies the Quantity Made and Soldfrom 0 to 8000, in 500-unit increments (see Figure A-11). For each of these quantities,we want to record the annual profit. First, we need to determine the location of thisData Table on our spreadsheet. Because we will end up with two columns, one forquantities and the other for profit, we will put the table in columns A and B, startingin row 22. Figure A-12 shows the final Data Table. Let’s review the steps of how to getthere.

1. Enter the text labels into rows 22 and 23, as shown in Figure A-12.

2. In cells A25:A41, enter the quantities 0 to 8000, in 500-unit increments. Skipthe row between the label “Quantity” in cell A23 and the value 0 in cell A25. Toenter these values, you can enter 0 in cell A25, enter the formula “�A25�500”


� Data TableAn analysis tool in Excel that allows one to vary one(or two, in a two-way Data Table) input andautomatically calculate the value of an output.


into cell A26, and copy this formula down. Alternatively, you can use Excel’s fillfeature (Edit/Fill/Series).

3. In cell B24, enter the formula “�B16” (don’t worry about the cell commentshown in Figure A-12). This formula is simply a cell reference to the profit. Inother words, whatever profit is calculated in B16 will automatically be shown inB24 as well. The formula in B24 is what the Data Table command uses to knowwhat you want to keep track of as the “output.”

4. Be careful, this step can be tricky! Select the range from A24:B41. Include thetop row, which contains an empty cell in cell A24, and the simple cell referenceformula in cell B24. Also, include all rows for which you entered a quantityvalue. Leave this range selected as you move to Step 5. See Figure A-13.

5. With the A24:B41 range selected, click Data/Table from the Excel menu. The di-alog box shown in Figure A-14 appears. Leave the “Row input cell” field blank.Put the cursor in the “Column input cell” field and type “B9” (or click on cellB9). That is, you’re entering the “Quantity Made and Sold” cell as the columninput cell. See Figure A-15. As with Goal Seek, when you click on a cell to enterit into the dialog, Excel shows it as an absolute reference; however, you can sim-ply type the relative cell reference in the dialog box.

6. Click “OK” on the Data Table dialog box. The second column of the Data Table(column B) should fill in with different values of profit, as shown in Figure A-12. In this figure, the profit values have been formatted with the Currency nu-merical format.

How does the Data Table automatically calculate the output values? Excel substi-tutes each of the values in the “input” range (the left column, cells A24:A41) into the“Column input cell” (cell B9). Then, Excel calculates the worksheet and puts the “out-put” (the value in cell B24, which is the Profit value) into the corresponding right-hand column cell. For example, Excel inserts the first input quantity (0) into cell B9,and calculates the worksheet. The resulting profit (�$52,000) is placed in cell B25.Then the next input quantity (500) is inserted into cell B9, the worksheet is


2223242526272829303132333435363738394041

A B CData Table to Show Profit as Function of Quantity

Sales Quantity Profit($12,000)

0 ($52,000)500 ($44,000)

1000 ($36,000)1500 ($28,000)2000 ($20,000)2500 ($12,000)3000 ($4,000)3500 $4,0004000 $12,0004500 $20,0005000 $28,0005500 $36,0006000 $44,0006500 $52,0007000 $60,0007500 $68,0008000 $76,000

B24: =B16

Completed Data Table showing profit as a function of quantity

FIGURE A-12



Range to select for Data Table

FIGURE A-13

Data Table dialog box (empty)

FIGURE A-14

Data Table dialog box (completed)

FIGURE A-15


calculated, and the resulting profit (�$44,000) is put into cell B26. Excel repeats thisprocess for all input values in the left-hand column (based on the range you selectedin Step 4).

Problem-Solving Tip: If your Data Table works incorrectly:

• Check that you entered a formula in cell B24. Many people just type a value in this cell, or copythe formula from B16 to B24; these procedures are both wrong.

• Make sure you selected the entire data table range, A24:B41, in Step 4 (see Figure A-13); thismust remain selected in Step 5 when the Data Table dialog box is showing.

• The default calculation mode of Excel may have been altered. Go to Tools/Options/Calcula-tion. Set the calculation option to “Automatic.”

The Data Table is now complete. As you can see, it may involve more time, espe-cially initially, but once set up, you have added a very useful tool to your model. Notonly have you just done a sensitivity analysis by varying the Quantity Made and Sold,the Data Table now recalculates itself automatically if you change one of the otherinput quantities. For example, if you want to see what effect a different sales pricewould have on profitability, simply change the Unit Sales Price value. The Data Tablewill automatically update with no further work.

Graphing the Model ResultsUsing tables to list quantities and profits is useful, but a graph can sometimes conveyinformation better. For example, Figure A-16 shows a graph of the Data Table results(the range A24:B41 was selected, and Excel’s Chart Wizard was used). Although we donot review the mechanics of Charting here (consult Excel’s on-line help or any stan-dard Excel reference book), you do need to understand the difference between an XY(Scatter) graph (shown here) and a Line graph. For an XY graph, the X (horizontal)axis contains a quantitative variable (in our case, Quantity). In a Line graph, the hori-zontal axis serves as a category axis. For example, if you have sales data by region (e.g.,east, west, north, south), use a Line graph to display the data. However, because wehave a quantitative variable, we use an XY graph.

The graph (Figure A-16) shows how profit varies as a function of quantity. For aquantity of 0, the profit is �$52,000 (equal to the fixed cost), and profit rises as afunction of quantity. We can see that breakeven occurs between 3000 and 4000 pairs(3250, as was found earlier).


Before You Go On

With a relatively simple example, we have demonstrated a sound spreadsheet model development process. Usingpencil and paper, we developed a picture of the model, identifying the inputs and outputs. Then we determinedthe relationships to transform the inputs to the outputs. Turning to the spreadsheet, we organized the inputs to-gether, calculated the outputs in a logical sequence, and used formatting and color coding to help highlight keyinformation. The model should be correct, flexible, and documented. Turning to the analysis, we used simplewhat-if analysis (to find the values in Table A-1). Then we demonstrated the Goal Seek, Data Table, and Chart-ing capabilities of Excel. You should apply these basic modeling principles and analysis tools to other problemsyou encounter in your study of operations management and other topics.


Planning the ModelAs before, we first spend time thinking about the problem from a modeling stand-point before delving into the spreadsheet. What are the inputs to this problem? Forthis problem it is fairly clear: inputs consist of the factors, a weight (importance) foreach factor, and scores for how well each location does on each factor. These inputsappear to be uncontrollable, but they are subjective in nature, rather than “hard data”such as selling prices.


Profit as a Function of Quantity Made and Sold

Quantity Made and Sold

Pro

fit

10000 2000 4000 5000 6000 7000 8000 90003000

$100,000

$80,000

$60,000

$40,000

$20,000

$0

($20,000)

($40,000)

($60,000)

XY graph of Data Table results

FIGURE A-16

EXAMPLE A.2

ModelingMultiple-CriteriaDecision Making

Antonio’s Italian Restaurant is evaluating three options for a new location. Costs are comparable atall three locations. The company has identified seven factors considered important and has used fac-tor rating (another name for multiple-criteria decision making) to evaluate the three location alter-natives based on a 5-point scale, with 1 being poor and 5 excellent. Table A-2 lists the factors, theweights (importance) assigned to each factor, and the score for each factor at each location.

• Before You Begin: This example uses Excel to model a location selection problem. Lookat the different factors and their relative weights considered in this decision. Design your worksheetsuch that the spreadsheet model is flexible, well documented, and correct.

Table A-2 Factor Weights and Scores for Antonio’s Italian RestaurantFactor Factor Score at Each Location (1–5 scale)

Factor Weight Location 1 Location 2 Location 3Appearance 20 5 3 2Ease of expansion 10 4 4 2Proximity to market 20 2 3 5Customer parking 15 5 3 3Access 15 5 2 3Competition 10 2 4 5Labor supply 10 3 3 4


What should be the output of this model? We need to determine which location is“best.” We will do this with a multiple-criteria scoring model, which takes the factorweights times the factor scores for a location and adds up the results to obtain anoverall score for each location. Therefore, the primary output of the model will be theoverall scores for each location. We will have some intermediate calculations, and wewill also use Excel to automatically extract the best location based on the results.

Constructing the Model in ExcelThe data presented are already in a tabular format, which is very compatible withExcel. Sometimes you will need to seriously consider the best way to organize the dataof a problem. Experience will make this decision easier, but a primary guideline whendeveloping spreadsheet models is that once you have determined a structure for thedata to be stored in Excel, the formulas of the model should stay consistent with thatstructure. We will see that even though there are many Excel formulas, we need towrite only a very small number of them. Instead, we will take advantage of relative andabsolute cell referencing and the ability to copy formulas.

The first part of the model, containing the data, is shown in Figure A-17. The datacolored yellow indicates input values. The SUM formula in cell E13 simply checks thefactor weights, as they should total 100. After constructing the model, users will beable to change any of these values to see the effect on the results. For example, if wedecide that Appearance is really worth 30 and Customer parking only 5, these changesneed to be made only in this section of the model.

Now, the overall score for a given location will be the factor scores for that location,multiplied by the factor weights, and summed together. For example, the overall scorefor Location 1 will be 5*20 � 4*10 � 2*20 � � � � � 3*10. We will break down thiscalculation in the model, as shown in Figure A-18.

After entering the labels in cells A15 and B16, you can center the label in B16 acrossthe columns B, C, and D. Select B16:D16, go to Format/Cells/Alignment, and click“Center across selection” from the Horizontal Alignment drop-down box. A little-known but very handy Excel shortcut is that you can copy text labels by using formu-las. Look at cell A17, which contains the formula “�A5”. Cell A5 contains the text“Factor.” When this formula is put into cell A17, the contents of cell A5 are put intocell A17. If you later decide to change the word “Factor” to “Criteria,” you only need to


Input section of model forAntonio’s Italian Restaurant

FIGURE A-171

234

56789

10111213

A B C D E F G

Example A.2Factor Rating for Antonio's Italian Restaurant

Factor Location 1Location

2Location

3Factor Weight

Appearance 5 3 2 20Ease of expansion 4 4 2 10Proximity to market 2 3 5 20Customer parking 5 3 3 15Access 5 2 3 15Competition 2 4 5 10Labor supply 3 3 4 10

Total 100

Factor Scores (1-5 scale)

E13: =SUM(E6:E12)


change it in cell A5. This formula in cell A17 was copied down to cell A24, and acrossto cell D17.

For the quantitative portion of the calculations, we have seven factors and three lo-cations; therefore, twenty-one formulas need to be entered in the range B18:D24. Al-though we could certainly write each one individually, this would take time and mightintroduce errors. Fortunately, we need to write only a single formula in cell B18 andcopy it to all the other cells. We do this through the use of relative and absolute cellreferencing.

Reviewing Relative and Absolute Cell ReferencingRelative and absolute cell referencing is probably the most important Excel concept ifyou want to become an efficient and effective modeler of business problems in thespreadsheet environment. Having a good understanding of cell referencing will saveyou time in model development and will make your models less error-prone. Modelsoften contain a repetitive structure that requires a similar formula be applied to arange of cells. Relative and absolute cell referencing allows you to write one formulaand then copy it, rather than retyping a formula many times. As a result, we’ll spendsome time here explaining relative and absolute referencing and the difference be-tween them. However, you should review the information in the Excel on-line help,and experiment on your own with Excel until you have a firm grasp of this importantconcept.

You enter formulas in Excel by hitting the “�” key. Suppose for cell B18, we enterthe formula “�B6*E6”. This formula takes the factor score for Appearance (Location1), which is 5, and multiplies it by the Factor Weight for Appearance, which is 20, re-sulting in 100 “points.” This is correct for cell B18. Now, suppose we copy this formuladown one cell, to cell B19. Will the formula remain the same, or will it change? In thiscase, it will change, because the original formula was entered with relative references.Relative references are indicated by the lack of a dollar-sign character (“$”) precedingeither the column or the row reference. Relative references change when you copythem. Therefore, the formula in cell B19, after the copy, would be “�B7*E7” becausewe copied the formula one row down within the same column. If we copy the formulain cell B18 down three rows and over two columns, to cell D21, what would the for-mula in D21 be? Since we have relative references, the row numbers would increase bythree, and column letters would “increase” by two. Therefore, cell D21 would containthe formula “�D9*G9”. This is the wrong formula for cell D21 in the model, but itshows how relative referencing works.


� Relative referenceA cell reference that changeswhen it is copied.

1516

171819202122232425

A B C D E FCompute Weighted Factor Scores and Overall Scores for Each Location


2Location

3Appearance 100 60 40Ease of expansion 40 40 20Proximity to market 40 60 100Customer parking 75 45 45Access 75 30 45Competition 20 40 50Labor supply 30 30 40Totals 380 305 340

Weighted Factor ScoresB18: =B6*$E6(copied to B18:D24)

B25: =SUM(B18:B24)(copied right)

A17: =A5Model calculations forAntonio’s Italian Restaurant

FIGURE A-18


Suppose, instead, we enter the formula “�$B$6*$E$6” in cell B18 and then, as be-fore, copy this formula down to cell B19. What will the formula in cell 19 be now? Theformula will not change when we copy it. The reason is that the formula in cell B18has absolute references. Absolute references are indicated by a dollar sign (“$”) pre-ceding the column and row reference. Absolute references do not change when youcopy them. In fact, we could have copied this formula to any other cell in the work-sheet, and it would still be “�$B$6*$E$6”.

This appears straightforward: if you want the cell references in a formula tochange when you copy them, use relative references; if you want the cell references tostay the same, use absolute references. Unfortunately, it’s not quite that simple. Rela-tive and absolute references can be applied to any cell reference within a formula, notsolely to the entire formula. Therefore, part of a formula’s references can changewhile another part can stay the same. Furthermore, you can even make the columnreference absolute and the row relative, or vice versa. For example, suppose a formulacontains the reference “D$38”. If you copy this formula down (or up), within thesame column, the reference will remain “D$38”. In fact, no matter where the formulais copied, the row reference will always be “$38” because the dollar sign makes therow reference absolute. However, if this formula is copied to a different column, thecolumn reference will change, because there is no dollar sign in front of the “D.” Sup-pose you copy the formula three cells to the right and five cells down. Then the refer-ence in the destination will become “G$38” because column G is three columns tothe right of column D. A similar example could be explored for a reference such as“$E44”. Since the column is absolute, no matter where the formula is copied, it willalways refer to column E. However, the row is relative, so it will change as the formulais copied to different rows.

Entering Formulas in the ModelLet us now return to the example and Figure A-18. The formula shown for cell B18 is“�B6*$E6”. Why did we enter the formula this way? The reason is that we always wantto multiply a factor score (contained in the range B6:D12; see Figure A-17) by the Fac-tor Weight, contained in the range E6:E12. The factor weights do not change for thedifferent locations; hence, they must be “absolute.” By entering the formula as shown,when we copy the formula down within column B, the row numbers will change to7,8,9, . . . , corresponding to the different factors, which is correct. When we copy theformula to the right (for the different locations), the reference to “B6” will change to“C6” for Location 2 and to “D6” for Location 3. However, the reference to “$E6” willremain at “$E6” because the column is absolute. Hence, as the formula is copied to theright, the factor scores for a given location will always be multiplied by the factorweights.

Problem-Solving Tip: To edit formulas in Excel, you can either double-click the cell or hit the F2key. Excel also provides an easy way to toggle between the different possible absolute and relative ref-erencing options. When you are editing a formula, place the cursor somewhere in the cell referenceyou want to change and hit F4. Repeatedly hitting F4 will change the relative/absolute referencing ofthe cell reference. For example, suppose you have entered “�B6*E6” in cell B18. Hit F2 to edit thisformula and place the cursor between the “E” and the “6.” Then hit F4. The “E6” reference willchange to “�$E$6”. Repeatedly hitting F4 will result in “E$6”, “$E6”, and back to “E6”. The F4 key es-sentially toggles among the four possible relative/absolute referencing combinations for a particularcell reference.


� Absolute referenceA cell reference that stays thesame when copied.


We are nearly done with the calculations for the example. We only need to add upthe weighted scores for each location. We can do this using the SUM function in cellB25, with this formula copied over to D25. As you can see, Location 1 has the highestoverall score (380). Considering these factors, factor weights, and scores, Location 1 isthe best location for the new restaurant.

We decide to also create a chart, shown in Figure A-19, to provide a more visual im-pression of the three location alternatives. This figure, a Column Chart in Excel, pro-vides a quick summary of the overall results. The range graphed is B25:D25. Location1 is the best, with Location 3 next, and Location 2 the worst.

As the overall scores are really sums of individual weighted factor scores, we couldalso develop a chart that shows the contribution of each factor to each location’s over-all score. Excel has a Stacked Column chart that we can use here. As Figure A-20shows, the total height of the columns exactly matches those in Figure A-19, but now


Stacked column chartshowing individualcontributions from each factor

FIGURE A-20

Location 1 Location 2 Location 3

400

350

300

250

200

150

100

50

0

Overall Scores of Alternative RestaurantLocations

Weighted Factor and Overall Scores of Alternative Restaurant Locations

0

50

100

150

200

250

300

350

400

Location 1 Location 2 Location 3

Labor supplyCompetition

Access

Customer parking

Proximity to market

Ease of expansionAppearance

Column chart of overall scores

FIGURE A-19


the relative strengths and weaknesses of each location are observable. For example,Location 1 scores well in terms of appearance, customer parking, and access. Location3 provides good proximity to market. Location 2, however, lacks any advantages. Therange graphed for the Stacked Column chart is A17:D24, and “Series in rows” is se-lected in the Chart Wizard. Note that we do not include the “Total Score” row, butExcel automatically stacks the individual scores so the column heights represent thetotal score for each location. By including column A and row 17 in the selection, Excelautomatically generates the X-axis labels and the labels for the legend.

An enhancement to the model will be demonstrated here. As you become morecomfortable and familiar with modeling, you will want to make your models moreuser-friendly. This enhancement will automatically have the model extract the besttotal score from among the three locations and then identify the best location. You caneasily accomplish the first part by using the Excel MAX function.

The MAX function takes a range of values as its input and returns the maximumvalue from that range (there is a corresponding MIN function that does the opposite).Therefore, in cell B27, we enter the formula “�MAX(B25:D25)” to return the maxi-mum overall score, 380 in this case. That part was easy. Now, how do we have themodel determine that the best overall score belongs to Location 1? Keep in mind thatfor different values of the Factor Weights and Factor Scores, the best location may notbe Location 1. Therefore, we want a formula that will provide the best location evenwhen the input values change.

In this case we use two “lookup” functions in Excel. The first is the MATCH func-tion. As Figure A-21 shows, MATCH takes three arguments. The first argument,Lookup_value, is the value that we want to test. In this case, it is 380, but more pre-cisely, it is the maximum value of the overall scores. This is what we just found in cellB27. Therefore, enter “B27” as the Lookup_value. The second argument isLookup_array. Lookup array defines the range of values for which you want to lookfor a match. In our example, we want to look in the range B25:D25, because these cellscontain the total overall scores, one of which is the maximum. The third argument,Match_type, should be set to zero to force the MATCH function to look for an exactmatch of Lookup_value in the Lookup_array. Figure A-22 shows a completedMATCH dialog box.

As shown in Figure A-22, the result of this particular MATCH function is the value1. What does this mean? It means that the MATCH function found the Lookup_valuein the first position of the Lookup_array. Indeed this is the case; 380 is the maximumscore, and it is the first value in the range B25:D25 (which contains the overall scores).


MATCH function dialog box (empty)

FIGURE A-21


We are almost finished. We know that the maximum overall score is in the first po-sition, but how can we get Excel to display, in a user-friendly way, that this corre-sponds to Location 1? The answer is to use the INDEX function. The INDEX functiontakes up to three arguments (although our example needs only two). The first argu-ment is an array (range), and the second is a column number. The function returnsthe entry from the range corresponding to the column number you specify. In thiscase, we will look up the first position in the array B17:D17, which contains the Loca-tion labels. Figure A-23 shows the completed Function Arguments dialog box for theINDEX function. For now, a “1” has just been entered in the Column_num field, tolook up the first entry in the array.

Finally, Figure A-24 illustrates an updated section of the model containing the cal-culations (rows 27 and 28). Cell B27 finds the maximum value, and cell B28 uses theINDEX function (with a nested MATCH function) to determine that the 380 overallscore is produced by Location 1.

In this model, we first multiplied the factor weights by the factor scores for eachalternative and then added to get the total score for each alternative (Figure A-18).This required that we have a second matrix of weighted factor scores. There is a moredirect way to do the same calculation, which is useful for larger models. TheSUMPRODUCT function takes as inputs two separate ranges (each with the samenumber of cells). It multiplies each pair of numbers in the ranges (i.e., the first num-ber of the first range by the first number of the second range, and so on) and then


MATCH function dialog box(completed)

FIGURE A-22

INDEX function dialog box(completed)

FIGURE A-23


adds up all these individual products. Think about it: this is exactly what the calcula-tions in Figure A-18 do, except they do it in two steps instead of one. Figure A-25shows the model using SUMPRODUCT to perform the calculations. In cell B17, theformula “�SUMPRODUCT($E6:$E12,B6:B12)” is entered and is copied across tocells C17:D17. This formula is equivalent to the much longer formula “�$E6*B6 �$E7*B7 � $E8*B8 � $E9*B9 � $E10*B10 � $E11*B11 � $E12*B12”. Using theSUMPRODUCT function can make you a more efficient modeler. Use of it also tendsto reduce errors with these long, repetitive calculations. The SUMPRODUCT func-tion is very useful in models involving tables of values where you need to multiplythe values in one column (row) by the values in another column (row) and add theresults. The optimization models in Supplement B make use of the SUMPRODUCTfunction.


Antonio’s Restaurantmodel usingSUMPRODUCTfunction

FIGURE A-25

1516

171819202122232425262728

A B C D E FCompute Weighted Factor Scores and Overall Scores for Each Location


2Location

3Appearance 100 60 40Ease of expansion 40 40 20Proximity to market 40 60 100Customer parking 75 45 45Access 75 30 45Competition 20 40 50Labor supply 30 30 40Totals 380 305 340

Best Total Score 380Best Location Location 1

Weighted Factor ScoresB18: =B6*$E6(copied to B18:D24)

B25: =SUM(B18:B24)(copied right)

B27: =MAX(B25:D25)

B28: =INDEX(B17:D17,MATCH(B27,B25:D25,0))

A17: =A5

1

234

56789

1011121314151617181920

A B C D E F G

Example A.2Factor Rating for Antonio's Italian Restaurant

Factor Location 1 Location 2 Location 3Factor Weight

Appearance 5 3 2 20Ease of expansion 4 4 2 10Proximity to market 2 3 5 20Customer parking 5 3 3 15Access 5 2 3 15Competition 2 4 5 10Labor supply 3 3 4 10

Total 100

Compute Overall Scores for Each LocationLocation 1 Location 2 Location 3

Overall Score 380 305 340

Best Total Score 380Best Location Location 1

Factor Scores (1-5 scale)

E13: =SUM(E6:E12)

B17: =SUMPRODUCT($E6:$E12,B6:B12)

B19: =MAX(B17:D17)

B20: =INDEX(B16:D16,MATCH(B19,B17:D17,0))

Completed model withenhancement to automaticallyreport best location

FIGURE A-24


USEFUL SPREADSHEET TIPS


Before You Go On

The multiple-criteria decision model provides a useful framework for many personal and business-related deci-sions. The process of defining the alternatives and the criteria, along with assigning weights and scores, helpsyou to learn more about the situation and come to a more informed decision. From a technical modeling stand-point, this example shows the importance of being efficient in the spreadsheet model development process. Wecould have written twenty-one formulas for the main calculations of the model, but with wise use of relativeand absolute cell referencing, we only needed to write one formula that was then copied to the other cells (Fig-ure A-18). The Column and Stacked Column charts are good ways to show the comparison of a relatively smallnumber of alternatives. Finally, we illustrated an alternative, more streamlined way to perform the calculationsusing the SUMPRODUCT function.

This section provides a brief list of tips that will help you to be a more effective and ef-ficient spreadsheet modeler. Some of these have been mentioned in the context of theprevious examples.

1. Lay out the data and the model in a logical and consistent way. This allows youto take advantage of the ability to copy formulas. Many seemingly complexspreadsheet models really comprise only a handful of truly distinct formulas.

2. Name each worksheet of the workbook. Do not leave them as “Sheet1,”“Sheet2,” etc. Give each worksheet a descriptive name, and delete the unusedones.

3. Learn to use Relative and Absolute References for cells. This is vital to beingproductive when modeling.

4. Use Cut (Ctrl-X) and Paste (Ctrl-V) to move formulas, not Copy(Ctrl-C) and Paste. Cutting and Pasting will keep the cell references correctin the formula(s) you are moving. Copying and Pasting will consider the rela-tive and absolute referencing of cells within the formula and adjust the refer-ences accordingly.

5. Using the Shift � Arrow keys will select cells in a range, just like the mouseClick � Drag. Using the keyboard is easier for many people when precisionis needed.

6. Learn to use keyboard shortcuts to select cells and menu items. While themouse is a great tool, keyboard shortcuts can greatly cut the time it takes to de-velop a model and perform routine operations. Find out more about keyboardshortcuts by searching in Excel’s help system for “keyboard shortcut.”

7. Set up printouts to print with “Row and column headings” and “Gridlines.” Setthese options in the File/Page Setup/Sheet box. Also set Comments to print “Asdisplayed on sheet.”

8. Use Print Preview, Page Setup, and Page Break Preview to get your model toprint exactly the way you want. Unlike word processing documents, datamoved to a separate page can sometimes dramatically change the usefulness ofa printout. You can control all of this relatively easily with these features.

9. Create and use a standard footer for all your spreadsheet printouts. We rec-ommend including the file name, the worksheet name, author, date, andtime in the footer. This can be created as a “custom footer” in the File/PageSetup/Header and Footer dialog box.


10. Print a copy of the spreadsheet formulas in addition to the regular printout.First make a copy of the worksheet (right-click the worksheet tab). Then go toTools/Options/View, and select Formulas (a keyboard shortcut is Ctrl � ‘).Adjust column widths as needed and print.

IMPORTANT EXCEL FORMULAS • A27

IMPORTANT EXCEL FORMULASThe examples in this supplement demonstrate sound spreadsheet modeling andanalysis practices. Two features of Excel that are probably new to most of you were in-troduced: Goal Seek and Data Table. These two features are valuable when analyzingquantitative models with Excel. Another key feature that many people have not usedextensively is Relative and Absolute Referencing.

Our examples used only a few Excel functions. You should get familiar with theInsert Function box as a way to learn about the many different functions(Insert/Function from menu, or click the fx button on the toolbar). Table A-3 serves asa reference for some of the most often used Excel functions in quantitative modeling.You may be familiar with some of these already. One of the most important aspects tomodeling with spreadsheets is to understand what is possible. You don’t have to mem-orize how to do everything, but if you know what is possible, you can figure the detailsout when needed.

SUM(range) Returns the sum of all the numbers in �SUM(B10:B17)the range.

SUMPRODUCT Multiplies corresponding numbers in the two �SUMPRODUCT (B10:B17,(range1, range2) ranges and returns the sum of those products. C10:C17) (returns the value of

B10*C10 � B11*C11 � � � � �B17*C17)

AVERAGE(range) Returns the average of all the numbers �AVERAGE(B10:B17)in the range.

MIN(range) Returns the minimum value of all the �MIN(B10:B17)numbers in the range.

MAX(range) Returns the maximum value of all the �MAX(B10:B17)numbers in the range.

COUNT(range) Returns the number of cells containing �COUNT(B10:B17)numerical values.

STDEV(range) Returns the sample standard deviation �STDEV(B10:B17)(n � 1 denominator) of the values in the range.

SQRT(number) Returns the square root. �SQRT(E13)

ABS(number) Returns the absolute value. �ABS(E13)

Selected Important Excel Functions

TABLE A-3

Function Brief Description Example

(Continued )


SPREADSHEET MODELING WITHIN OM: PUTTING IT ALL TOGETHER


IF(logical test, value Checks whether “logical test” is true or false. �IF(F22��69.5,”Pass”,”Fail”)if true, value if false) If true, the result of the function is the

“value if true.” If false, the result is the “value if false.”

MATCH(lookup_value, Returns the relative position in the array �MATCH (B10, B15:B20, 0)lookup_array, that matches the lookup value. (looks in B15:B20 for a value that match type) exactly equals the value in B10,

returns the relative position in thearray)

INDEX(range, position) Returns the value from the range in the �INDEX (B10:B17, 3) (returnsposition specified. Can also be used with the contents of the third cell intwo-dimensional ranges. the range, in this case, B12)

NORMSDIST(z) Returns the standard normal (mean of 0, �NORMSDIST(1.96) (result isstandard deviation of 1) cumulative 0.975)probability for the value of z.

NORMSINV(p) Returns the z-value of the standard normal �NORMSINV(0.975) (result isdistribution corresponding to a cumulative 1.96)probability of p.

(Continued )

TABLE A-3

Function Brief Description Example

The models in this chapter have been fairly basic, but you should begin to see some ofthe powerful uses of models and quantitative analysis. Heery International is a con-struction firm with an office in Nashville, Tennessee. The average budget for a projectis about $2,000,000, varying from $50,000 to $50,000,000. Heery has developed aspreadsheet-based model to help it better assign managers to construction projects. Asa result of this model, the company reports that the project assignment process takesmuch less time than before, assignments are perceived as being more fair among themanagers, and travel costs (from managers’ homes to job sites) have been reducedconsiderably, allowing for increased productivity.

A problem in the airline industry is the recovery from unexpected events, such asweather delays, mechanical problems, and crew unavailability. When such a disrup-tion occurs, significant costs to the airlines and inconveniences to passengers can re-sult. For example, when a flight is canceled, that aircraft does not get to its destination,which causes an aircraft shortage at that location. Continental Airlines recently devel-oped, in association with CALEB Technologies, a system to help the airline recoverfrom such events. While it is not spreadsheet-based, it demonstrates the power ofmodeling to help solve operational problems. CALEB Technologies and ContinentalAirlines recently won the Franz Edelman Award for Achievement in Operations Re-search and the Management Sciences.


DISCUSSION QUESTIONS • A29

Supplement HighlightsA model is a purposeful representation of the key factors ina situation and of the relationships among them. It abstractsthe real situation, incorporating those factors that areimportant to the decision it was designed to address.

The main types of models are mental models, visual models,physical models, and mathematical models. Spreadsheetmodels are essentially mathematical models and are thefocus of this supplement.

Mathematical models translate inputs into outputs through aset of relationships. Inputs consist of uncontrollable inputs andcontrollable inputs, sometimes called decision variables. Therecan be many outputs of mathematical models, but often we areinterested in a relatively small number of primary outputs.

The recommended spreadsheet modeling process consists ofunderstanding the problem, drawing a sketch of the model,developing a base-case spreadsheet, testing the spreadsheet,using the model to perform analysis, and documenting themodel.

Models should be correct, flexible, and documented.Correctness implies that the numerical calculations arecorrect for the current situation. Being flexible implies thatthe user can change any of the input values and the resultswill be correctly calculated. A well-documented spreadsheetcan be understood by someone else without a detailedexplanation from the developer.

5

4

3

2

1 This supplement focused on the construction of models byapplying sound modeling principles. You should invest timeapplying the principles to problems in this supplement aswell as other problems in this text.

Key Excel formulas and functions were addressed in thissupplement. A critical skill is the correct use of Relative andAbsolute References. Mastery allows you to develop a modelin a fraction of the time it would take otherwise. Severalimportant functions were shown in Table A-3.

Two useful Excel analysis tools, Goal Seek and Data Table,were illustrated. Goal Seek allows you to find the value ofan input that causes an output to be equal to a value youspecify. For example, it allows you to determine thequantity (an input) at which profit (an output) will equal$0. A Data Table allows you to vary one (or two) inputsand automatically calculate the value of an output for eachof the input values in the range. We covered Data Tables inwhich one input was varied, but you can also create two-way Data Tables, in which two input values are varied.

Several different chart types were used to illustrate modelresults. These were the XY chart, the Column chart, and theStacked Column chart. Other useful chart types forpresentation of model results are Pie charts, Line charts, andBar charts. Excel has many other, more specialized charttypes that can be useful in certain situations.

9

8

7

6

Key Termsmental model A2visual model A2physical model A2mathematical model A2spreadsheet model A2inputs A3

flexible model A5documented model A5Goal Seek A12Data Table A14relative reference A20absolute reference A21

controllable inputs (decision variables) A3uncontrollable inputs (parameters) A3output A3model A3base case A4correct model A5

Discussion Questions1. Give an example (besides the ones noted in the text) of a men-

tal model, a visual model, and a mathematical or spreadsheet model.2. Give an example of a decision-making situation in which

you used a mental model.3. Give an example of a decision-making situation in which

you used a mathematical or spreadsheet model.4. Discuss the importance of model flexibility.5. Discuss the importance of a model being well documented.6. Explain the steps of the spreadsheet modeling process.

7. Give an example (besides the one used in the text) ofwhen using Goal Seek would be helpful.

8. Give an example (besides the one used in the text) ofwhen using a Data Table would be helpful.

9. What are absolute and relative cell references?10. Why is it important to use absolute and relative cell refer-

ences?11. When are tables of numbers useful in the decision-making

process? When are graphs of those numbers useful?



Problems1. Kaizer Plastics produces a variety of plastic items for pack-

aging and distribution. One item, container #145, has had a lowcontribution to profits. Last year, 20,000 units of container #145were produced and sold. The selling price of the container was$20 per unit, with a variable cost of $18 per unit and a fixed costof $70,000 per year.

(a) Construct a correct, flexible, and documented base-casespreadsheet model that allows the user to easily vary theinputs to the problem and see the resulting revenue,costs, and profit. What is the profit level for the base case?

(b) Using the model and Goal Seek, find the break-evenquantity.

(c) Construct a Data Table and chart showing profit versusquantity.

(d) The company is considering ways to improve profitabilityby either stimulating sales volume or reducing variablecosts. Management believes sales can be increased by 35percent of their current levels or that variable costs canbe reduced to 90 percent of their current level. Assumingall other costs stay at the base-case levels, use the modelto determine which alternative would lead to a higherprofit contribution.

(e) Suppose the two alternatives in (d) are being considered,but that to increase sales by 35 percent, fixed costs wouldrise by $5000 annually to cover additional promotionalexpenses. Similarly, to reduce variable costs to 90 percentof their base-case level would require $10,000 annually tocover costs of new equipment. Make any changes neededand repeat the comparison.

2. Harrison Hotels is considering adding a spa to its currentfacility in order to improve the list of amenities. Operating thespa would require a fixed cost of $25,000 per year. Variable cost isestimated at $35 per customer. The hotel wants to break even if12,000 customers use the spa facility.

(a) Construct a spreadsheet model, assuming that sellingprice is an input to be specified by the user.

(b) Use Goal Seek to find the selling price for which the hotelwill break even.

3. Gabriela Manufacturing must decide whether to insource(make) or outsource (buy) a new toxic-free carpet cleaner thatworks with its Miracle Carpet Cleaning Machine. If it decides toinsource the product, the process would incur $300,000 of an-nual fixed costs and $1.50 per unit of variable costs. If it is out-sourced, a supplier has offered to make it for an annual fixed costof $120,000 and a variable cost of $2.25 per unit in variable costs.

(a) Construct a base-case spreadsheet model that shows bothof these alternatives side-by-side. Use the same numberof units (stored in a single cell) to drive the calculations.

(b) If the expected annual demand for the new cleaner is300,000 units, what would you recommend that GabrielaManufacturing do? Provide evidence to support yourrecommendation.

(c) Use Goal Seek to find the indifference point (where totalcosts are equal) between these two alternatives. (Hint: Goal

Seek can work only with a single “Set cell.”) Create a cell inyour model that computes the difference between the totalcosts of the two alternatives before running Goal Seek.

(d) Construct an XY chart of total cost versus quantity.Graph both alternatives on the same chart. Use a DataTable to generate the values for the chart. (Hint: The DataTable will need to track two output values; the total costsof the two alternatives. When setting up the Data Table, itwill simply have an additional column, compared to theexample presented in the text.)

4. Henri of Henri’s French Cuisine (HFC), a chain of twelverestaurants, is trying to decide if it makes sense to outsource thepurchasing function. Currently, Henri employs two buyers at anannual fixed cost of $85,000. Henri’s estimates that the variablecost of each placed purchase order is $15. Value-Buy (VB), agroup of purchasing specialists, will perform the purchasingfunction for a fixed annual fee of $100,000 plus $5 for eachpurchase order placed. Last year, HFC placed 1450 purchaseorders.

(a) Construct a base-case spreadsheet model that shows bothof these alternatives side-by-side. Use the same numberof orders (stored in a single cell) to drive the calculations.

(b) Using last year’s quantity of purchase orders, which alter-native would have been the better?

(c) Use Goal Seek to find the indifference point (where totalcosts are equal) between these two alternatives. (Hint: GoalSeek can work only with a single “Set cell.” Create a cell inyour model that computes the difference between the totalcosts of the two alternatives before running Goal Seek.)

(d) Construct an XY chart of total cost versus quantity.Graph both alternatives on the same chart. Use a DataTable to generate the values for the chart. (Hint: The DataTable will need to track two output values; the total costsof the two alternatives. When setting up the Data Table, itwill simply have an additional column, compared to theexample presented in the text.)

5. Sue and Joe are a young married couple who are consider-ing purchasing a new home. Their search has been reduced tothree homes that they both like, at different locations. They haveidentified the factors they consider important and assigned a fac-tor score to each location based on a 5-point scale. The informa-tion is shown in the table.

Factor Score at Each LocationLocation Location Location Factor

Factor 1 2 3 WeightProximity to work 5 2 1 10Proximity to family 4 2 5 25Size of home 2 5 5 15Transportation system 5 3 1 10Neighborhood 3 5 2 20Cost 1 2 4 20


CASE: DIET PLANNING • A31

(a) Construct a spreadsheet model to help Sue and Joe maketheir decision. What is your recommendation?

(b) Construct a stacked column chart to illustrate the alter-natives.

6. The manager of a small health clinic needs to forecast de-mand for laboratory services in the facility. Data from the last sixweeks are available and are, respectively, 330, 350, 320, 370, 368,and 343 tests. The manager decides to use a forecasting techniqueknown as three-period moving average. For this technique, themost recent three weeks’ demands are averaged together to pro-duce the forecast for the upcoming week. For example, weeks 1–3are used to produce the forecast for week 4.

(a) Construct a spreadsheet model to help the manager fore-cast demand. Assume that this technique has been inplace in the previous weeks as well, and generate the fore-casted demand for week 7. You will not be able to gener-ate forecasted demands for weeks 1–3.

(b) Expand the model to calculate the error in the forecast eachweek. The error should be calculated as the actual demandfor the week minus the forecasted demand. Which week hadthe largest error? Which had the smallest error? What does itmean that some errors are positive and some are negative?

7. Burger Lover Restaurant forecasts weekly sales of cheese-burgers. The last five weeks of actual sales and forecasted sales areprovided in the table.

(a) Construct a spreadsheet model to compute the forecasterror for each week. The forecast error is defined to bethe actual sales minus the forecasted sales and can bepositive or negative.

(b) Explain the meaning of positive and negative forecasterrors.

(c) Expand the model to calculate what is known as themean absolute deviation, or MAD, which is a commonmeasure of forecast accuracy. MAD is calculated asMAD � ��ee�/n, where ei is the forecast error for week iand n is the total number of forecast errors calculated.(Hint: You will need to use Excel’s ABS function to com-pute the absolute values of the errors.)

Week Actual Sales Forecast Sales1 354 3202 345 3403 367 3804 322 3665 356 330

(d) Expand the model to calculate what is known as the rootmean squared error, or RMSE, which is a common mea-sure of forecast accuracy. RMSE is calculated as RMSE �

where ei is the forecast error for week i and n isthe total number of forecast errors calculated. (Hint: Toraise a value to a power in Excel, use the carat (“∧”) symbol.For example, the formula” �B16∧2” squares the value incell B16. Also, you will need to use Excel’s SQRT function.)

(e) Expand the model to calculate what is known as themean absolute percent error, or MAPE, which is a com-mon measure of forecast accuracy. MAPE is essentiallythe average percentage forecast error and is calculated as

where ei is the forecast error for week

i, yi is the actual value for week i, and n is the total num-ber of forecast errors calculated.

8. Custom Computers, Inc. assembles custom home computersystems. The heat sinks for the central processing units arebought for $12 each and are ordered in quantities of 1300 units.Annual demand is 5200 heat sinks, the annual inventory holdingcost is $3 per unit, and the cost to place an order is estimated tobe $50. Assume that demand occurs continuously through theyear and that a new order is placed in time so the order arrives atthe same time as the previous inventory runs out.

(a) Construct a base-case spreadsheet model to help analyzedifferent order quantities. The primary output of interestis the total annual inventory-related cost, comprisingtotal ordering cost and total holding cost. Holding costcan be applied to the average inventory level through theyear. Given the assumption about continuously occurringdemand and that orders arrive just in time, the averageinventory level will be half of the order quantity.

(b) For the base-case order quantity of 1300 heat sinks, whatis the average inventory level, the number of ordersplaced per year, the total annual inventory holding cost,the total annual ordering cost, and the total annual cost?

(c) Repeat (b) for an order quantity of 650 heat sinks. Repeatfor 1950 heat sinks.

(d) The Economic Order Quantity (EOQ) is defined as theorder quantity that minimizes total inventory-relatedcosts. Construct a Data Table and an XY chart and findthe EOQ for this situation.

(e) How much would ordering the EOQ save Custom Com-puters on an annual basis, compared to its current orderquantity?

MAPE � �(�ei �/yi)

n ,

��e 2i /n,

CASE: Diet PlanningGiven your busy schedule, you eat many meals on the go. Lately,you’ve started to get concerned about the nutritional value of allthose burger and fries lunches. You decide to build a model tohelp you decide ahead of time on a reasonably nutritious dietfor a single day at your favorite restaurant.

A number of restaurants (e.g., Burger King, McDonalds,Wendy’s) have nutritional data for their menu items available onthe Web. In some cases these are essentially electronic versions oftheir printed nutritional brochures; in other cases they are in aform that you can copy and paste into an Excel spreadsheet.


Explore the Web sites of several different restaurants to findavailable data. Choose a single restaurant for this case. Alter-natively, you can use foods that you might prepare at home.The U.S. Department of Agriculture maintains a Web site con-taining documents that list the nutritional value of foods. Seehttp://www.nal.usda.gov/fnic/foodcomp/.

(a) You want to construct a model so that you can enter thenumber of each menu item you would choose to con-sume in one day (e.g., 1 egg and sausage sandwich, 2cheeseburgers, 3 chocolate shakes), and have the modelautomatically calculate the total nutritional statistics forthat particular choice of items. You will then be able touse the model to adjust your food and drink choices tohelp plan a more nutritious diet.

(b) Choose a number of menu items as possible breakfast,lunch, and dinner items from the data available on-line.Enter data for these into a spreadsheet model. Enter thenutritional information (e.g., calories, fat, protein, vita-mins, and minerals) as well. You should have at least fif-teen items to choose from.

(c) Build the logic of the model to allow the user to enterthe number of each menu item to consume in a day andhave the nutritional statistics calculated automatically. Ifthe model is organized well, you will be able to use theSUMPRODUCT function to easily calculate the totalsfor each of the nutritional categories, given the user’sinput of the number of each item to consume. Yourmodel should also include goal or target values for thenutritional items. For example, you might want a dietthat has between 1800 and 2200 calories, with no morethan 30 percent of the calories from fat. Some facts youwill need are that 1 gram of fat contains 9 calories, 1gram of protein contains 4 calories, and 1 gram of car-bohydrate contains 4 calories.

(d) Experiment with different values of items to consume,and develop your planned diet for the day. Are you will-ing to implement that diet? Discuss advantages and dis-advantages of the diet, considering nutritional, taste, andcost factors.


On-line Resources

1. Spreadsheets for the two examples in this chapter are available on the CD.

2. Internet Challenge:Web-Based Excel Resources

There are many Internet resources available to learn more aboutspreadsheets and spreadsheet modeling. Four sites are listed here,but you are not limited to these for this challenge.The Spreadsheet Page, by John Walkenbach: http://j-walk.com/ss/Pearson Software Consulting, LLC: http://www.cpearson.com/

excel.htmMrExcel.com: http://www.mrexcel.com/learnexcel.shtmlThe Spreadsheet Detective: http://www.uq.net.au/detective/

home.html

(a) Using the listed sites and any others you find on yourown, identify at least five features of Excel that you werenot aware of or familiar with. Briefly explain the featureand how it might be useful in the modeling and analysisof business problems.

(b) Find at least one more Excel-related site (besideMicrosoft’s) that provides useful information for thosedoing business modeling. Briefly describe what is avail-able there and how it could be useful to modelers.

Selected BibliographyFriedrichsen, Lisa. New Perspectives on Microsoft Excel 2002 with

Visual Basic for Applications: Advanced. Boston: Course Tech-nology, 2002.

Parsons, June Jamrich, Dan Oja, Roy Ageloff, and Patrick Carey.New Perspectives on Microsoft Excel 2002: Comprehensive.Boston: Course Technology, 2002.

Ragsdale, Cliff T. Spreadsheet Modeling & Decision Analysis, FourthEdition. Stamford, Conn.: Thomson, 2004.

Walkenbach, John. Excel 2003 Bible. New York: Wiley, 2003.Winston, Wayne L., and S. Christian Albright. Practical Man-

agement Science (with CD-ROM Update), Second Edition.Duxbury, Mass.: 2003.


om reid sanders 3 ed lp sm supp a

Documents