OM Reid Sanders 3 Ed LP SM Supp A

ancmat01.qxd 11/16/06 3:24 PM Page A1 SUPPLEMENT Spreadsheet Modeling: An Introduction Before studying this supplement, you should know or, if necessary, review A 1. Familiarity with spreadsheets. 2. Knowledge of basic Excel commands. LEARNING OBJECTIVES After completing this supplement you should be able to 1 2 3 4 5 6 7 8 9 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. SUPPLEMENT OUTLINE What Are Models? A2 The Spreadsheet Modeling Process A4 Evaluating Spreadsheet Models A5 Useful Spreadsheet Tips A26 Important Excel Formulas A27 Spreadsheet Modeling within OM: Putting It All Together A28 WHAT’S IN ACC OM FOR ME? FIN MKT OM HRM MIS A1 ancmat01.qxd 11/16/06 3:24 PM Page A2 A2 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION H ave 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. Models can help executives make strategic decisions about acquisitions and expansions, for example. They can also help clarify tactical decisions such as production and employee 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 development 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 spreadsheet 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. WHAT ARE MODELS? Mental model A decision-making process we conduct in our heads. Visual model A model in which graphics or diagrams are used to convey real objects or situations. Examples are a map or a graph. 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 situation calls for a mental model. Visual models use graphics or diagrams to represent real objects or situations. For example, a road atlas represents a system of roads and other key land features. Physical models involve objects that represent other objects, such as an architect’s scale model of a new building. Mathematical models use equations and relationships among quantities to represent situations. Many of the concepts in this textbook are shown through the use of mathematical models. Spreadsheet models are a means of implementing mathematical models. Although there are a number of different model types, commonalities exist among them. First, the use of models is motivated by a decision that needs to be made, for ancmat01.qxd 11/16/06 3:24 PM Page A3 WHAT ARE MODELS? • A3 example, whether to stop or go at a yellow light or how much of a product to order at one time. Second, all models rely upon inputs. Inputs are quantities or factors that affect the situation. Inputs can be controllable or uncontrollable. Controllable inputs, also called decision variables, are quantities or factors that a decision maker can change (usually within limits) for the current situation. For example, in the trafficlight situation, you obviously have control over whether to stop or go, how much pressure to apply to the brakes (if you decide to stop), or how fast to accelerate (if you decide to go). In an order-quantity situation, the decision maker has control over how much quantity to order. Uncontrollable inputs, sometimes called parameters, are quantities or factors that are important to the situation but are outside the decision maker’s direct control. Obviously, the presence of a police officer is outside your control in the traffic-light situation, but it may affect your decision to stop or go. In an order-quantity situation, uncontrollable inputs might represent quantities such as the cost to hold inventory of the material, the cost to place an order for the material, and the demand or usage rate of the material. Models also have outputs. An output is a quantity or a factor that depends on how the inputs are related to one another. In the traffic-light example, an output might include the probability of an accident. An output generally changes if one or more of the inputs change. For example, an accident is probably more likely under bad weather conditions than good weather conditions. In the order-quantity situation, the primary output would be the total cost of the ordering policy. Secondary outputs would be the total inventory holding cost and the total ordering cost. Some models have several outputs, but usually one or two are considered primary. What models do, then, is to transform inputs into outputs. In the traffic-light example, 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, we would use the mathematical relationships among the input quantities to calculate the total cost. Then we might consider different possible order quantities and choose the one that produces the least total cost. A model, however, needs to do more than transform inputs to outputs; it must have 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 of the 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 situation, thereby ignoring other variables. In the order-quantity situation we did not directly consider whether demand was seasonal. This could certainly be included, but it would make the model more complicated. Whether or not we include a particular factor can be a difficult modeling decision. The benefits of including it must be weighed against the increased complexity of the model. We can now more fully define a model. A model is a purposeful representation of the key factors in a situation and the relationships among them. It is an abstraction of the real situation, and should incorporate enough detail so the results meet the current needs, but omit unnecessary details. As Albert Einstein said, “Everything should be made as simple as possible, but not simpler.” This statement applies perfectly to modeling. Figure A-1 shows a generic diagram of a mathematical model. Note the two input types; the “model” box, which is really a set of relationships among the inputs; and the outputs. Thinking back to this diagram will be useful as we begin to develop and use spreadsheet models. Physical model A model in which physical objects are used to represent the real objects or situation, usually on a smaller scale. Examples are model cars and buildings. Mathematical model A model in which quantitative relationship are used to represent a real situation or phenomena. An example is a weatherprediction model. Spreadsheet model A mathematical model implemented in the form of a computer spreadsheet. Inputs Quantities or factors that affect the decision-making situation. Controllable inputs (decision variables) Quantities or factors that a decision maker can change for the current situation. An example is the order quantity in an inventory planning situation. Uncontrollable inputs (parameters) Quantities or factors that a decision maker cannot control for the current situation. An example is the unit cost of a raw material that must be purchased to produce a product. Output A quantity or factor that is calculated from the inputs of a model and is of interest to the decision maker. Model A purposeful representation of the key factors in a situation and the relationships among them. ancmat01.qxd 11/16/06 3:24 PM Page A4 A4 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION Uncontrollable Inputs (Parameters) Mathematical Model: set of relationships (spreadsheet formulas) Controllable Inputs (Decision Variables) Outputs FIGURE A-1 Basic mathematical model THE SPREADSHEET MODELING PROCESS 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 to better understand the situation. Identify the uncontrollable inputs, the decision variables, and the outputs. Define the logic necessary to transform the inputs into the outputs. 2. On paper, sketch out an overall plan for the model. In general, group the inputs together. Determine where the inputs, intermediate calculations, and outputs will go. Plan to highlight the key inputs and outputs to make the model easier to use for what-if analysis. Determine the formulas relating the inputs to the intermediate calculations and outputs. This can be very simple for some models (i.e., Profit revenue expenses), or it may be quite complicated. In general, the time spent planning a model in this step is normally much less than the time spent debugging an unplanned, completed model. 3. Develop the base case spreadsheet model. Group the inputs together logically. It usually helps to use a color-coding scheme so the user can quickly determine what are the inputs and outputs of the model. Break down the intermediate calculations so that each formula is relatively simple. You can then more easily spot and correct errors. Research has shown that most spreadsheet-model developers believe their products to be error-free, but this assessment is usually wrong! Thus, you need to scrutinize formulas and results during and after the spreadsheet development effort. Use specific text labels, including units of measure, so that others reading the model can follow your thought process. The outputs should also be clearly labeled and color-coded. For large models (loosely speaking, those that do not fit in a window screen), it is often very helpful to provide a summary of the outputs next to the inputs. 4. Test the spreadsheet model using trial values. Verify the results by hand, if possible. If you have broken down the intermediate calculations into relatively simple formulas, this step is much easier. 5. Use the model to perform the needed analysis. This may involve a relatively simple calculation, preparation of a chart, or more substantial analysis. Two common types of analysis are scenario analysis and sensitivity analysis. A scenario is a specific set of conditions that could occur in a real situation. A common practice is to look at the base-case, best-case, and worst-case scenarios. Scenario analysis helps a decision manager gain additional insight into a situation. Sensitivity 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 analysis helps to identify the inputs that cause the most change in the output. Since the values for the inputs are often just estimates, it is important to understand this Base case The model containing the “default” or “given” values for the inputs. This is normally the starting point for the analysis. ancmat01.qxd 11/16/06 3:24 PM Page A5 EVALUATING SPREADSHEET MODELS • A5 sensitivity. Break-even analysis is one special case of sensitivity analysis. No matter how simple or complex the analysis, the time invested developing a highquality 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 will provide an overview of some of these later in this supplement. 6. Document the model so that others can easily understand it. Remember, others may not think of the problem in exactly the way you do, so descriptive labels and a logical layout are extremely important. Indeed, ideal spreadsheet models are almost “self-documenting” as a result of the way you organize and label them. 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 your model 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 leads to better decisions than you would make had you not developed a model. Sometimes the things you learn about the real situation during the modeling process are even more important than the numerical results of the model. In any event, models often need substantial revision before you’re pleased with the results. EVALUATING SPREADSHEET MODELS After you develop a spreadsheet model, how do you evaluate it? One useful way to think about the quality of a spreadsheet is to assess it along three dimensions. Specifically, a spreadsheet model should be correct, flexible, and documented. A spreadsheet model should produce the correct answer for the information given. Usually we think of this in the context of the base case: for the “given” values for the inputs, does the model calculate the correct results? However, a spreadsheet model needs to do more than simply calculate the correct answer. A spreadsheet model must be flexible in producing accurate results even if the user changes 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 model that includes a unit cost as an input. This value, say $3.50, would be entered into a single cell, for instance, B8. Any other cells using unit cost in their calculations would then reference cell B8 rather than having the $3.50 value “hard-coded” inside its formula. In this way, the user only needs to change the data item in a single cell to analyze a new problem. Flexibility is often ignored by people developing a spreadsheet they think will only be used once. Most models in the real world are used repeatedly, with different 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 and only 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, and that 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 understand the ability of a model, follow these guidelines: ● Correct model A model is correct if it produces the numerically correct values for the outputs for the current values of the inputs. Flexible model A model is flexible if it produces the numerically correct values for the outputs for any legitimate values of the inputs, without making any changes to the formulas in the model. Documented model A model is documented if someone else generally familiar with the situation can understand the model without having the model developer explain it in detail. A documented model can be put away for months at a time, and when viewed again by the modeler, is readily understandable. Include descriptive text labels for all numerical inputs and calculations. Include in the label the units of measure of the quantity (e.g., money, hours, pounds, square feet). ancmat01.qxd 11/16/06 3:24 PM Page A6 A6 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION ● ● ● ● Use numerical formatting (Format/Cells/Number) to display the numerical information in the model. The most common useful formats are number (fixed number of decimal places), currency, and percentage. Others are also useful in certain 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 outputs of the model. A judicious use of coloring can make certain items in a model stand out for the user. Do not overformat, however, as too much is distracting. In the examples provided in this supplement, we use a standard formatting convention. Insert Cell Comments for key cells. To use Excel’s Comment feature, right-click any cell. From the submenu, click Insert Comment. You can type anything you want into the box that pops up. This box will remain “attached” to the cell and can even be printed with the model. Use comments to add explanatory information about 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 for a particular value of an input. In many of the spreadsheet examples in this supplement, 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 be little work involved in documenting a spreadsheet model if you adopt a logical structure for the data and calculations, add descriptive labels that include the units of measure, use numerical and cell formatting appropriately, and provide additional comments 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 use mental models every day to make many decisions. Visual and physical models help people to better understand a situation. Mathematical models comprise a set of relationships linking inputs to outputs. Mathematical models are often implemented using spreadsheets. The spreadsheet modeling process consists of understanding the problem, planning the spreadsheet on paper, developing the base-case model, testing the model, using the model for analysis, and documenting the model. Scenario and sensitivity analysis are useful tools used to gain more insight into the problem. Spreadsheet models should be correct, flexible, and documented. EXAMPLE A.1 Modeling the Break-even Point Sports Feet Manufacturing is considering whether to produce a new line of footwear. The company has considered both the processing needs for the new product and the market potential. The company also estimated that the variable cost for each product manufactured and sold is $9 and the fixed cost 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 data and that you understand the quantitative relationships. Pay particular attention to the instruction provided 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 be made and sold (assuming it can sell all that it makes) in order for the total revenue to equal the total ancmat01.qxd 11/16/06 3:24 PM Page A7 EVALUATING SPREADSHEET MODELS • A7 cost. This situation is simple enough that you can easily calculate this quantity. Therefore, we will use this example to demonstrate some of the key spreadsheet modeling concepts and guidelines. Furthermore, 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 structure 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 analysis and result to others (e.g., management). Planning the Model Recall that the first step in developing a good spreadsheet model is to understand the problem and to determine the inputs and the outputs. Part of this step, then, is obviously determining what main question the model will answer. Although we will eventually want to find the break-even point, the model should answer a more fundamental question: “For some quantity made and sold, what profit (or loss) will be attained?” Thus, the primary output of the model (at least initially) will be the annual profit (loss). In order to compute the profit, we will need the annual revenue and annual total cost. Our inputs include the annual fixed cost ($52,000), the unit variable cost ($9), and the unit selling price ($25). In the context of the current situation, these are uncontrollable inputs. We also have as an input the quantity made and sold. The company can control this quantity (at least to a certain extent), so we classify this as a controllable input. Notice that sometimes the distinction between a controllable and an uncontrollable input is not always clear. Figure A-2 contains a “black box” diagram of 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. Our spreadsheet should clearly identify the inputs and the outputs. We separate the inputs from the outputs, so the user knows exactly which values to change in order to perform what-if analysis. This model is small enough so that everything will fit onto one worksheet nicely. If it were a larger problem, the organizational question would be something we would need to consider carefully in order to make a useful, coherent model. The mathematical relationships of the model essentially become the formulas in the spreadsheet. We will write them out first, however. When developing models, writing out some of the key formulas before committing them to the spreadsheet provides a helpful debugging tool. In the relationships listed next, the yellow color-coding indicates an input value to the model and a green color indicates the primary output of the model. Unit Sales Price Annual Fixed Cost Unit Variable Cost Quantity Made and Sold Model: Set of relationships (formulas) to translate the inputs into the outputs Annual Revenue Annual Total Cost Annual Profit (Loss) FIGURE A-2 Black box diagram for Sports Feet Manufacturing ancmat01.qxd 11/16/06 3:24 PM Page A8 A8 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION Annual profit Annual revenue annual revenue annual total cost quantity made and sold annual variable cost quantity made and sold unit selling price annual fixed cost Annual total cost Annual variable cost unit variable cost Since we have written out four equations, our spreadsheet model will essentially contain four formulas in cells. Note that we try to keep each equation as simple as possible, 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 implemented correctly, 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 interested in both annual revenue and annual total cost, in addition to annual profit. The spreadsheet should show these values explicitly. To avoid errors. Mistakes are commonly introduced when writing longer formulas. Further, you can more easily spot any errors if you have broken down complex calculations into smaller pieces. Thus, it is better to write a larger number of simple formulas, rather than a smaller number of more complex formulas. Implementing the Model in Excel We’ll now turn to the spreadsheet model construction itself. First, use a descriptive label for the worksheet (as opposed to saving it as “Sheet1”), and include a short description of the problem in the upper left of the sheet (see Figure A-3). We’ll walk through the logic of the model here, which follows directly from the identification of inputs, outputs, and relationships we’ve already done. The cells containing formulas have cell comments attached to them (Insert/Comment) to help document the model. FIGURE A-3 A 1 Example A.1 B C Spreadsheet model for Sports Feet Manufacturing 2 Profit/Loss Analysis for Sports Feet Manufacturing 3 4 Inputs Unit Sales Price $25.00 5 Annual Fixed Cost $52,000 6 Unit Variable Cost $9.00 7 8 9 Quantity Made and Sold 2500 (user-specified quantity) 10 11 Calculations and Outputs B12: =B5*B9 12 Annual Revenue $62,500 B13: =B9*B7 13 Annual Variable Cost $22,500 B14: =B6 14 Annual Fixed Cost $52,000 B15: =B13+B14 15 Annual Total Cost $74,500 B16: =B12-B15 16 Annual Profit (Loss) ($12,000) (profit or loss at the user-specified quantity) ancmat01.qxd 11/16/06 3:24 PM Page A9 EVALUATING SPREADSHEET MODELS • A9 In the spreadsheet, we clearly separated the inputs from the outputs. The value inserted for the “Quantity Made and Sold” in cell B9 is just a test value at this point. Recall that this quantity is a controllable input (decision variable), so the decision maker can choose the value for this cell. By making this an input quantity, the user can easily use 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 translate 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 that this is not a calculation, nor is this formula absolutely required, as the fixed cost is already entered as an input. However, having the model repeat the value here helps to make the logical flow clear. When you do this, be sure to enter the formula “ 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, we want 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 components of cost, then the total cost, and then profit. Assessing the Model At this point, we should now assess our spreadsheet model. Specifically, is the model correct, flexible, and documented? To verify correctness, carefully scrutinize the formulas. In addition, verify the calculations of the model by hand. For the input values we used: Annual revenue Annual total cost Annual profit (loss) ($25/pair) $52,000 $62,500 (2500 pairs) $22,500 $74,500 $62,500 $22,500 Annual variable cost ($9/pair) (2500 pairs) $74,500 $12,000 We have now verified the correctness of the model (for the current input quantities). 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 input values. We now ask our last assessment question, “Is the model relatively self-documenting?” Referring back to the previous discussion, our model should include appropriately formatted numerical values, descriptive and specific text labels, and clearly designated input and output cells. As Figure A-3 shows, our model meets these requirements. To format cells as dollars, the “Currency” format is recommended (Format/Cells/Number). For “large” dollar amounts, show whole-dollar amounts. For smaller values, such as the unit sales price and the unit variable cost, show the cents for greater precision. Note that when entering formulas (such as for revenue), Excel sometimes uses the formatting of one of the cells involved in the formula. If this formatting is not appropriate, change it to something that is! ancmat01.qxd 11/16/06 3:24 PM Page A10 A10 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION As a further way to document our model, we should create a page footer containing important information about the file. The recommended footer includes the file name, the worksheet name, author, date, and time. As Figure A-4 shows, you can easily create a custom footer in the File/Page Setup/Header and Footer dialog box. Every time 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 up the spreadsheet to print showing “Gridlines” and “Row and Column Headings.” The gridlines are the fine horizontal and vertical lines that separate the cells of the worksheet on the screen, but these do not print by default. The row and column headings are the (1,2,3, . . .) and (A,B,C, . . .) identifiers, respectively. Without these, you will find evaluating a printed spreadsheet more difficult. To set gridlines and row and column 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-click the worksheet tab, click “Move or Copy,” and then in the resulting box, be sure to check “Create a copy.” An exact copy of the current worksheet will be created. On the FIGURE A-4 Custom footer dialog box FIGURE A-5 Page setup dialog box to set useful printing options ancmat01.qxd 11/16/06 3:24 PM Page A11 EVALUATING SPREADSHEET MODELS • A11 FIGURE A-6 Options dialog box to show formulas copy, go to Tools/Options/View, and check the “Formulas” box (see Figure A-6). Excel then 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 Analysis Until 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 of the specific numerical values for sales price, fixed cost, variable cost, and quantity. We now turn to using the model to help guide decision making. We know the values for unit sales price ($25), unit variable cost ($9), and annual fixed cost ($52,000). We can now perform “what-if ” analysis; that is, we can enter any value into the Quantity Made cell (cell B9), and the model will calculate the annual A 1 Example A.1 B C FIGURE A-7 2 Profit/Loss Analysis for Sports Feet Manufacturing 3 4 Inputs Unit Sales Price 25 5 Annual Fixed Cost 52000 6 Unit Variable Cost 9 7 8 9 Quantity Made and Sold 2500 (user-specified quantity) 10 11 Calculations and Outputs 12 Annual Revenue =B5*B9 13 Annual Variable Cost =B9*B7 14 Annual Fixed Cost =B6 15 Annual Total Cost =B13+B14 16 Annual Profit (Loss) =B12-B15 (profit or loss at the user-specified quantity) 17 18 Breakeven Point =B6/(B5-B7) (quantity at which revenue = cost) Printout of formulas for Sports Feet Manufacturing ancmat01.qxd 11/16/06 3:24 PM Page A12 A12 • SUPPLEMENT A TABLE A-1 SPREADSHEET MODELING: AN INTRODUCTION What-If Analysis: Profit as a Function of Quantity Made and Sold Quantity 0 1000 2000 3000 4000 5000 6000 7000 8000 Profit $52,000 $36,000 $20,000 $4,000 $12,000 $28,000 $44,000 $60,000 $76,000 revenue, cost, and profit. For that matter, our what-if analysis can also explore changes to 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 increases by $16,000. Where does this number originate? Note that from the original problem data, the selling price is $25 and the variable cost is $9. Therefore, the contribution to profit is $25 $9 $16 per pair, or $16,000 for 1000 pairs. Obviously this finding does not require a spreadsheet model. More complex situations that involve many factors can make it very difficult to find this type of “marginal” result from a direct analysis of the input data. However, if you create and use a flexible spreadsheet model, you can 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 between 3000 and 4000 pairs. One quick method of finding the exact value is to use Excel’s Goal Seek feature. We’ll then check the results using the algebraic approach. Goal Seek An analysis tool in Excel that allows one to search for the value of an input that results in an output becoming a specific value. Goal Seek Approach Excel’s Goal Seek feature does exactly what we need here. You access Goal Seek by going to Tools/Goal Seek. A dialog box appears as shown in Figure A-8. The basic idea is that you provide Goal Seek a “set cell,” a “value,” and “by changing cell.” The set cell is the cell in your model that you want to force to a certain value. In our case, we want to force the profit cell (B16) to the value zero. We want to do this by changing (more precisely, allowing Excel to change) the value of the Quantity Made and Sold (cell B9). In the Goal Seek dialog box, type “B16” into the Set Cell field (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 here shows absolute cell references ($ signs in the references). Excel puts these in when you click on the cells in the worksheet rather than when you type them directly into the FIGURE A-8 Goal Seek dialog box (empty) FIGURE A-9 Goal Seek dialog box (completed) ancmat01.qxd 11/16/06 3:24 PM Page A13 EVALUATING SPREADSHEET MODELS • A13 box. For the Goal Seek feature, it doesn’t matter whether you use absolute or relative references. After clicking “OK,” you should notice that Excel changes the value in cell B9 to 3250. As a result of this, the profit (cell B16) is equal to $0. When Goal Seek finishes, 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 results may not be exactly 3250 and $0, respectively, but will be very close. Algebraic Approach As discussed in the textbook, the break-even point can be F found using the formula Q SP VC, where F fixed cost, SP unit selling price, and $52,000 VC unit variable cost. In this case, Q $25 $9 3250 pairs. If Sports Feet Manufacturing makes and sells more than this quantity, the company makes an overall profit; if less, it incurs a loss. We can certainly add this formula to our Excel model. The model not only allows a user to change the trial production quantity but it also calculates the break-even quantity for any values of the fixed cost, selling price, and variable cost. As Figure A-11 shows, the formula “ B6/(B5 B7)” has been entered into cell B18 (note the parentheses in the formula; they are important). Thus, we have two ways to find the break-even quantity. You may wonder why we would want to use the Goal Seek method. The algebraic approach when implemented FIGURE A-10 Goal Seek completion message A 1 Example A.1 B C FIGURE A-11 2 Profit/Loss Analysis for Sports Feet Manufacturing 3 4 Inputs Unit Sales Price $25.00 5 Annual Fixed Cost $52,000 6 Unit Variable Cost $9.00 7 8 9 Quantity Made and Sold 2500 (user-specified quantity) 10 11 Calculations and Outputs B12: =B5*B9 12 Annual Revenue $62,500 B13: =B9*B7 13 Annual Variable Cost $22,500 B14: =B6 14 Annual Fixed Cost $52,000 B15: =B13+B14 15 Annual Total Cost $74,500 B16: =B12-B15 16 Annual Profit (Loss) ($12,000) (profit or loss at the user-specified quantity) 17 18 Breakeven Point 3,250 (quantity at which revenue = cost) 19 B16: =B6/(B5-B7) 20 Expanded model with breakeven point calculation ancmat01.qxd 11/16/06 3:24 PM Page A14 A14 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION 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 difficulty for Goal Seek! Also, we can use Goal Seek for more than the simple break-even quantity found here. For example, suppose we want to find the quantity that will allow us 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 Seek dialog box. The result (which you can verify) is that we need to make and sell 3875 pairs 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 sell 4000 pairs at a price of $25 each, but we are unsure about our estimate of the $9 per pair variable cost. This uncertainty is realistic, especially when dealing with new products or costs subject to market uncertainty. Now the question is, “To what value can our 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 as high as $12 before we start incurring a loss. These are just a couple of ways we can use Goal Seek to perform additional interactive analysis with our flexible spreadsheet model. As you continue building spreadsheet models, you will find many imaginative uses for Goal Seek if you understand its capabilities. Adding Data Tables Data Table An analysis tool in Excel that allows one to vary one (or two, in a two-way Data Table) input and automatically calculate the value of an output. Another extremely valuable feature of Excel is called the Data Table, which performs repetitive what-if analysis quickly and easily. For example, the what-if analysis shown in Table A-1 can be set up and run automatically with a Data Table. We will also create a graph, driven directly from the Data Table results. Best of all, when you change one or more of the model inputs, Excel automatically updates both the Data Table and graph. Briefly, you provide a list of input values to the Data Table. It then inserts this range of input values one at a time into a cell you specify. Excel calculates the spreadsheet and remembers the results. Then the Data Table inserts the next input number in the range, repeating the process. The user completely controls the values. The best way to understand Data Tables is to work through an example. We encourage you to work through this example at the computer. It takes a couple of times to 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 Sold from 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 this Data Table on our spreadsheet. Because we will end up with two columns, one for quantities and the other for profit, we will put the table in columns A and B, starting in row 22. Figure A-12 shows the final Data Table. Let’s review the steps of how to get there. 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. Skip the row between the label “Quantity” in cell A23 and the value 0 in cell A25. To enter these values, you can enter 0 in cell A25, enter the formula “ A25 500” ancmat01.qxd 11/16/06 3:24 PM Page A15 EVALUATING SPREADSHEET MODELS • A15 A B C 22 Data Table to Show Profit as Function of Quantity Sales Quantity Profit 23 24 ($12,000) B24: =B16 25 0 ($52,000) 26 500 ($44,000) 27 1000 ($36,000) 28 1500 ($28,000) 29 2000 ($20,000) 30 2500 ($12,000) 31 3000 ($4,000) 32 3500 $4,000 33 4000 $12,000 34 4500 $20,000 35 5000 $28,000 36 5500 $36,000 37 6000 $44,000 38 6500 $52,000 39 7000 $60,000 40 7500 $68,000 41 8000 $76,000 FIGURE A-12 Completed Data Table showing profit as a function of quantity 3. 4. 5. 6. into cell A26, and copy this formula down. Alternatively, you can use Excel’s fill feature (Edit/Fill/Series). In cell B24, enter the formula “ B16” (don’t worry about the cell comment shown in Figure A-12). This formula is simply a cell reference to the profit. In other words, whatever profit is calculated in B16 will automatically be shown in B24 as well. The formula in B24 is what the Data Table command uses to know what you want to keep track of as the “output.” Be careful, this step can be tricky! Select the range from A24:B41. Include the top row, which contains an empty cell in cell A24, and the simple cell reference formula in cell B24. Also, include all rows for which you entered a quantity value. Leave this range selected as you move to Step 5. See Figure A-13. With the A24:B41 range selected, click Data/Table from the Excel menu. The dialog 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 cell B9). That is, you’re entering the “Quantity Made and Sold” cell as the column input cell. See Figure A-15. As with Goal Seek, when you click on a cell to enter it into the dialog, Excel shows it as an absolute reference; however, you can simply type the relative cell reference in the dialog box. 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 numerical format. How does the Data Table automatically calculate the output values? Excel substitutes 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 “output” (the value in cell B24, which is the Profit value) into the corresponding righthand 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 ancmat01.qxd 11/16/06 3:24 PM Page A16 A16 • SUPPLEMENT A FIGURE A-13 SPREADSHEET MODELING: AN INTRODUCTION Range to select for Data Table FIGURE A-14 Data Table dialog box (empty) FIGURE A-15 Data Table dialog box (completed) ancmat01.qxd 11/16/06 3:24 PM Page A17 EVALUATING SPREADSHEET MODELS • A17 calculated, and the resulting profit ( $44,000) is put into cell B26. Excel repeats this process for all input values in the left-hand column (based on the range you selected in Step 4). Problem-Solving Tip: If your Data Table works incorrectly: formula in cell B24. Many people just • Check that you enteredtoa B24; these procedures are both wrong.type a value in this cell, or copy the formula from B16 sure you selected • Makeremain selected in the entire data table range, A24:B41, inisStep 4 (see Figure A-13); this must Step 5 when the Data Table dialog box 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, especially initially, but once set up, you have added a very useful tool to your model. Not only 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 other input quantities. For example, if you want to see what effect a different sales price would have on profitability, simply change the Unit Sales Price value. The Data Table will automatically update with no further work. Graphing the Model Results Using tables to list quantities and profits is useful, but a graph can sometimes convey information 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 do not review the mechanics of Charting here (consult Excel’s on-line help or any standard 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 horizontal 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 we have a quantitative variable, we use an XY graph. The graph (Figure A-16) shows how profit varies as a function of quantity. For a quantity of 0, the profit is $52,000 (equal to the fixed cost), and profit rises as a function 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. Using pencil and paper, we developed a picture of the model, identifying the inputs and outputs. Then we determined the relationships to transform the inputs to the outputs. Turning to the spreadsheet, we organized the inputs together, calculated the outputs in a logical sequence, and used formatting and color coding to help highlight key information. The model should be correct, flexible, and documented. Turning to the analysis, we used simple what-if analysis (to find the values in Table A-1). Then we demonstrated the Goal Seek, Data Table, and Charting capabilities of Excel. You should apply these basic modeling principles and analysis tools to other problems you encounter in your study of operations management and other topics. ancmat01.qxd 11/16/06 3:24 PM Page A18 A18 • SUPPLEMENT A FIGURE A-16 SPREADSHEET MODELING: AN INTRODUCTION Profit as a Function of Quantity Made and Sold $100,000 $80,000 $60,000 $40,000 XY graph of Data Table results Profit $20,000 $0 0 ($20,000) ($40,000) ($60,000) 1000 2000 3000 4000 5000 6000 7000 8000 9000 Quantity Made and Sold EXAMPLE A.2 Modeling Multiple-Criteria Decision Making Antonio’s Italian Restaurant is evaluating three options for a new location. Costs are comparable at all three locations. The company has identified seven factors considered important and has used factor rating (another name for multiple-criteria decision making) to evaluate the three location alternatives based on a 5-point scale, with 1 being poor and 5 excellent. Table A-2 lists the factors, the weights (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. Look at the different factors and their relative weights considered in this decision. Design your worksheet such that the spreadsheet model is flexible, well documented, and correct. Table A-2 Factor Weights and Scores for Antonio’s Italian Restaurant Factor Factor Score at Each Location (1–5 scale) Factor Weight Location 1 Location 2 Location 3 Appearance 20 5 3 2 Ease of expansion 10 4 4 2 Proximity to market 20 2 3 5 Customer parking 15 5 3 3 Access 15 5 2 3 Competition 10 2 4 5 Labor supply 10 3 3 4 Planning the Model As before, we first spend time thinking about the problem from a modeling standpoint before delving into the spreadsheet. What are the inputs to this problem? For this problem it is fairly clear: inputs consist of the factors, a weight (importance) for each factor, and scores for how well each location does on each factor. These inputs appear to be uncontrollable, but they are subjective in nature, rather than “hard data” such as selling prices. ancmat01.qxd 11/16/06 3:24 PM Page A19 EVALUATING SPREADSHEET MODELS • A19 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 factor weights times the factor scores for a location and adds up the results to obtain an overall score for each location. Therefore, the primary output of the model will be the overall scores for each location. We will have some intermediate calculations, and we will also use Excel to automatically extract the best location based on the results. Constructing the Model in Excel The data presented are already in a tabular format, which is very compatible with Excel. Sometimes you will need to seriously consider the best way to organize the data of a problem. Experience will make this decision easier, but a primary guideline when developing spreadsheet models is that once you have determined a structure for the data to be stored in Excel, the formulas of the model should stay consistent with that structure. We will see that even though there are many Excel formulas, we need to write only a very small number of them. Instead, we will take advantage of relative and absolute cell referencing and the ability to copy formulas. The first part of the model, containing the data, is shown in Figure A-17. The data colored yellow indicates input values. The SUM formula in cell E13 simply checks the factor weights, as they should total 100. After constructing the model, users will be able to change any of these values to see the effect on the results. For example, if we decide that Appearance is really worth 30 and Customer parking only 5, these changes need 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 score for Location 1 will be 5*20 4*10 2*20 3*10. We will break down this calculation 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 across the 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 littleknown but very handy Excel shortcut is that you can copy text labels by using formulas. 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 into cell A17. If you later decide to change the word “Factor” to “Criteria,” you only need to A 1 Example A.2 B C D E F G FIGURE A-17 2 Factor Rating for Antonio's Italian Restaurant 3 Factor Scores (1-5 scale) 4 Location Location Factor Location 1 2 3 Weight 5 Factor 5 3 2 20 6 Appearance 4 4 2 10 7 Ease of expansion 2 3 5 20 8 Proximity to market 5 3 3 15 9 Customer parking 5 2 3 15 10 Access 2 4 5 10 11 Competition 3 3 4 10 12 Labor supply Total 100 13 Input section of model for Antonio’s Italian Restaurant E13: =SUM(E6:E12) ancmat01.qxd 11/16/06 3:24 PM Page A20 A20 • SUPPLEMENT A FIGURE A-18 SPREADSHEET MODELING: AN INTRODUCTION A B C D E F 15 Compute Weighted Factor Scores and Overall Scores for Each Location 16 A17: =A5 Weighted Factor Scores Location Location B18: =B6*$E6 (copied to B18:D24) 17 Factor Location 1 2 3 18 Appearance 100 60 40 19 Ease of expansion 40 40 20 20 Proximity to market 40 60 100 B25: =SUM(B18:B24) 21 Customer parking 75 45 45 (copied right) 22 Access 75 30 45 23 Competition 20 40 50 24 Labor supply 30 30 40 25 Totals 380 305 340 Model calculations for Antonio’s Italian Restaurant change it in cell A5. This formula in cell A17 was copied down to cell A24, and across to cell D17. For the quantitative portion of the calculations, we have seven factors and three locations; therefore, twenty-one formulas need to be entered in the range B18:D24. Although we could certainly write each one individually, this would take time and might introduce errors. Fortunately, we need to write only a single formula in cell B18 and copy it to all the other cells. We do this through the use of relative and absolute cell referencing. Reviewing Relative and Absolute Cell Referencing Relative and absolute cell referencing is probably the most important Excel concept if you want to become an efficient and effective modeler of business problems in the spreadsheet environment. Having a good understanding of cell referencing will save you time in model development and will make your models less error-prone. Models often contain a repetitive structure that requires a similar formula be applied to a range of cells. Relative and absolute cell referencing allows you to write one formula and then copy it, rather than retyping a formula many times. As a result, we’ll spend some time here explaining relative and absolute referencing and the difference between 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 important concept. You enter formulas in Excel by hitting the “ ” key. Suppose for cell B18, we enter the formula “ B6*E6”. This formula takes the factor score for Appearance (Location 1), which is 5, and multiplies it by the Factor Weight for Appearance, which is 20, resulting in 100 “points.” This is correct for cell B18. Now, suppose we copy this formula down one cell, to cell B19. Will the formula remain the same, or will it change? In this case, it will change, because the original formula was entered with relative references. Relative references are indicated by the lack of a dollar-sign character (“$”) preceding either the column or the row reference. Relative references change when you copy them. Therefore, the formula in cell B19, after the copy, would be “ B7*E7” because we copied the formula one row down within the same column. If we copy the formula in cell B18 down three rows and over two columns, to cell D21, what would the formula in D21 be? Since we have relative references, the row numbers would increase by three, and column letters would “increase” by two. Therefore, cell D21 would contain the formula “ D9*G9”. This is the wrong formula for cell D21 in the model, but it shows how relative referencing works. Relative reference A cell reference that changes when it is copied. ancmat01.qxd 11/16/06 3:24 PM Page A21 EVALUATING SPREADSHEET MODELS • A21 Suppose, instead, we enter the formula “ $B$6*$E$6” in cell B18 and then, as before, copy this formula down to cell B19. What will the formula in cell 19 be now? The formula will not change when we copy it. The reason is that the formula in cell B18 has absolute references. Absolute references are indicated by a dollar sign (“$”) preceding the column and row reference. Absolute references do not change when you copy them. In fact, we could have copied this formula to any other cell in the worksheet, and it would still be “ $B$6*$E$6”. This appears straightforward: if you want the cell references in a formula to change when you copy them, use relative references; if you want the cell references to stay the same, use absolute references. Unfortunately, it’s not quite that simple. Relative and absolute references can be applied to any cell reference within a formula, not solely to the entire formula. Therefore, part of a formula’s references can change while another part can stay the same. Furthermore, you can even make the column reference absolute and the row relative, or vice versa. For example, suppose a formula contains the reference “D$38”. If you copy this formula down (or up), within the same column, the reference will remain “D$38”. In fact, no matter where the formula is copied, the row reference will always be “$38” because the dollar sign makes the row reference absolute. However, if this formula is copied to a different column, the column reference will change, because there is no dollar sign in front of the “D.” Suppose you copy the formula three cells to the right and five cells down. Then the reference in the destination will become “G$38” because column G is three columns to the 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 will always refer to column E. However, the row is relative, so it will change as the formula is copied to different rows. Absolute reference A cell reference that stays the same when copied. Entering Formulas in the Model Let 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 want to multiply a factor score (contained in the range B6:D12; see Figure A-17) by the Factor Weight, contained in the range E6:E12. The factor weights do not change for the different 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 to 7,8,9, . . . , corresponding to the different factors, which is correct. When we copy the formula 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” will remain at “$E6” because the column is absolute. Hence, as the formula is copied to the right, the factor scores for a given location will always be multiplied by the factor weights. Problem-Solving Tip: To edit formulas in Excel, you can either double-click the cell or hit the F2 key. Excel also provides an easy way to toggle between the different possible absolute and relative referencing options. When you are editing a formula, place the cursor somewhere in the cell reference you want to change and hit F4. Repeatedly hitting F4 will change the relative/absolute referencing of the cell reference. For example, suppose you have entered “ B6*E6” in cell B18. Hit F2 to edit this formula and place the cursor between the “E” and the “6.” Then hit F4. The “E6” reference will change to “ $E$6”. Repeatedly hitting F4 will result in “E$6”, “$E6”, and back to “E6”. The F4 key essentially toggles among the four possible relative/absolute referencing combinations for a particular cell reference. ancmat01.qxd 11/16/06 3:24 PM Page A22 A22 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION We are nearly done with the calculations for the example. We only need to add up the weighted scores for each location. We can do this using the SUM function in cell B25, with this formula copied over to D25. As you can see, Location 1 has the highest overall score (380). Considering these factors, factor weights, and scores, Location 1 is the best location for the new restaurant. We decide to also create a chart, shown in Figure A-19, to provide a more visual impression of the three location alternatives. This figure, a Column Chart in Excel, provides a quick summary of the overall results. The range graphed is B25:D25. Location 1 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 could also develop a chart that shows the contribution of each factor to each location’s overall score. Excel has a Stacked Column chart that we can use here. As Figure A-20 shows, the total height of the columns exactly matches those in Figure A-19, but now FIGURE A-19 Overall Scores of Alternative Restaurant Locations 400 350 300 250 200 150 100 50 0 Column chart of overall scores Location 1 Location 2 Location 3 FIGURE A-20 Weighted Factor and Overall Scores of Alternative Restaurant Locations 400 350 300 250 200 150 100 50 0 Location 1 Labor supply Competition Stacked column chart showing individual contributions from each factor Location 2 Access Customer parking Proximity to market Ease of expansion Location 3 Appearance ancmat01.qxd 11/16/06 3:24 PM Page A23 EVALUATING SPREADSHEET MODELS • A23 the relative strengths and weaknesses of each location are observable. For example, Location 1 scores well in terms of appearance, customer parking, and access. Location 3 provides good proximity to market. Location 2, however, lacks any advantages. The range graphed for the Stacked Column chart is A17:D24, and “Series in rows” is selected in the Chart Wizard. Note that we do not include the “Total Score” row, but Excel automatically stacks the individual scores so the column heights represent the total score for each location. By including column A and row 17 in the selection, Excel automatically generates the X-axis labels and the labels for the legend. An enhancement to the model will be demonstrated here. As you become more comfortable and familiar with modeling, you will want to make your models more user-friendly. This enhancement will automatically have the model extract the best total score from among the three locations and then identify the best location. You can easily accomplish the first part by using the Excel MAX function. The MAX function takes a range of values as its input and returns the maximum value 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 maximum overall score, 380 in this case. That part was easy. Now, how do we have the model determine that the best overall score belongs to Location 1? Keep in mind that for different values of the Factor Weights and Factor Scores, the best location may not be Location 1. Therefore, we want a formula that will provide the best location even when the input values change. In this case we use two “lookup” functions in Excel. The first is the MATCH function. 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 precisely, it is the maximum value of the overall scores. This is what we just found in cell B27. Therefore, enter “B27” as the Lookup_value. The second argument is Lookup_array. Lookup array defines the range of values for which you want to look for a match. In our example, we want to look in the range B25:D25, because these cells contain 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 exact match of Lookup_value in the Lookup_array. Figure A-22 shows a completed MATCH dialog box. As shown in Figure A-22, the result of this particular MATCH function is the value 1. What does this mean? It means that the MATCH function found the Lookup_value in the first position of the Lookup_array. Indeed this is the case; 380 is the maximum score, and it is the first value in the range B25:D25 (which contains the overall scores). FIGURE A-21 MATCH function dialog box (empty) ancmat01.qxd 11/16/06 3:24 PM Page A24 A24 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION FIGURE A-22 MATCH function dialog box (completed) We are almost finished. We know that the maximum overall score is in the first position, but how can we get Excel to display, in a user-friendly way, that this corresponds to Location 1? The answer is to use the INDEX function. The INDEX function takes up to three arguments (although our example needs only two). The first argument is an array (range), and the second is a column number. The function returns the entry from the range corresponding to the column number you specify. In this case, we will look up the first position in the array B17:D17, which contains the Location labels. Figure A-23 shows the completed Function Arguments dialog box for the INDEX function. For now, a “1” has just been entered in the Column_num field, to look up the first entry in the array. Finally, Figure A-24 illustrates an updated section of the model containing the calculations (rows 27 and 28). Cell B27 finds the maximum value, and cell B28 uses the INDEX function (with a nested MATCH function) to determine that the 380 overall score is produced by Location 1. In this model, we first multiplied the factor weights by the factor scores for each alternative 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 more direct way to do the same calculation, which is useful for larger models. The SUMPRODUCT function takes as inputs two separate ranges (each with the same number of cells). It multiplies each pair of numbers in the ranges (i.e., the first number of the first range by the first number of the second range, and so on) and then FIGURE A-23 INDEX function dialog box (completed) ancmat01.qxd 11/16/06 3:24 PM Page A25 EVALUATING SPREADSHEET MODELS • A25 A B C D E F 15 Compute Weighted Factor Scores and Overall Scores for Each Location 16 A17: =A5 Weighted Factor Scores Location Location B18: =B6*$E6 (copied to B18:D24) 17 Factor Location 1 2 3 18 Appearance 100 60 40 19 Ease of expansion 40 40 20 20 Proximity to market 40 60 100 B25: =SUM(B18:B24) 21 Customer parking 75 45 45 (copied right) 22 Access 75 30 45 23 Competition 20 40 50 24 Labor supply 30 30 40 25 Totals 380 305 340 26 B27: =MAX(B25:D25) 27 Best Total Score 380 B28: =INDEX(B17:D17,MATCH(B27,B25:D25,0)) 28 Best Location Location 1 FIGURE A-24 Completed model with enhancement to automatically report best location adds up all these individual products. Think about it: this is exactly what the calculations in Figure A-18 do, except they do it in two steps instead of one. Figure A-25 shows the model using SUMPRODUCT to perform the calculations. In cell B17, the formula “ SUMPRODUCT($E6:$E12,B6:B12)” is entered and is copied across to cells 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 the SUMPRODUCT function can make you a more efficient modeler. Use of it also tends to reduce errors with these long, repetitive calculations. The SUMPRODUCT function is very useful in models involving tables of values where you need to multiply the values in one column (row) by the values in another column (row) and add the results. The optimization models in Supplement B make use of the SUMPRODUCT function. A 1 Example A.2 B C D E F G FIGURE A-25 2 Factor Rating for Antonio's Italian Restaurant 3 4 Factor Scores (1-5 scale) 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Factor Appearance Ease of expansion Proximity to market Customer parking Access Competition Labor supply Location 1 Location 2 Location 3 5 3 2 4 4 2 2 3 5 5 3 3 5 2 3 2 4 5 3 3 4 Total Factor Weight 20 10 20 15 15 10 10 100 Antonio’s Restaurant model using SUMPRODUCT function E13: =SUM(E6:E12) B17: =SUMPRODUCT($E6:$E12,B6:B12) Compute Overall Scores for Each Location Location 1 Location 2 Location 3 Overall Score 380 305 340 B19: =MAX(B17:D17) Best Total Score 380 Best Location Location 1 B20: =INDEX(B16:D16,MATCH(B19,B17:D17,0)) ancmat01.qxd 11/16/06 3:24 PM Page A26 A26 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION Before You Go On The multiple-criteria decision model provides a useful framework for many personal and business-related decisions. The process of defining the alternatives and the criteria, along with assigning weights and scores, helps you to learn more about the situation and come to a more informed decision. From a technical modeling standpoint, this example shows the importance of being efficient in the spreadsheet model development process. We could have written twenty-one formulas for the main calculations of the model, but with wise use of relative and absolute cell referencing, we only needed to write one formula that was then copied to the other cells (Figure A-18). The Column and Stacked Column charts are good ways to show the comparison of a relatively small number of alternatives. Finally, we illustrated an alternative, more streamlined way to perform the calculations using the SUMPRODUCT function. USEFUL SPREADSHEET TIPS This section provides a brief list of tips that will help you to be a more effective and efficient spreadsheet modeler. Some of these have been mentioned in the context of the previous examples. 1. Lay out the data and the model in a logical and consistent way. This allows you to take advantage of the ability to copy formulas. Many seemingly complex spreadsheet 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 unused ones. 3. Learn to use Relative and Absolute References for cells. This is vital to being productive 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 correct in the formula(s) you are moving. Copying and Pasting will consider the relative and absolute referencing of cells within the formula and adjust the references accordingly. 5. Using the Shift Arrow keys will select cells in a range, just like the mouse Click Drag. Using the keyboard is easier for many people when precision is needed. 6. Learn to use keyboard shortcuts to select cells and menu items. While the mouse is a great tool, keyboard shortcuts can greatly cut the time it takes to develop a model and perform routine operations. Find out more about keyboard shortcuts by searching in Excel’s help system for “keyboard shortcut.” 7. Set up printouts to print with “Row and column headings” and “Gridlines.” Set these options in the File/Page Setup/Sheet box. Also set Comments to print “As displayed on sheet.” 8. Use Print Preview, Page Setup, and Page Break Preview to get your model to print exactly the way you want. Unlike word processing documents, data moved to a separate page can sometimes dramatically change the usefulness of a 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 recommend including the file name, the worksheet name, author, date, and time in the footer. This can be created as a “custom footer” in the File/Page Setup/Header and Footer dialog box. ancmat01.qxd 11/16/06 3:24 PM Page A27 IMPORTANT EXCEL FORMULAS • A27 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 to Tools/Options/View, and select Formulas (a keyboard shortcut is Ctrl ‘). Adjust column widths as needed and print. IMPORTANT EXCEL FORMULAS The examples in this supplement demonstrate sound spreadsheet modeling and analysis practices. Two features of Excel that are probably new to most of you were introduced: Goal Seek and Data Table. These two features are valuable when analyzing quantitative models with Excel. Another key feature that many people have not used extensively is Relative and Absolute Referencing. Our examples used only a few Excel functions. You should get familiar with the Insert 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 as a 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 to modeling with spreadsheets is to understand what is possible. You don’t have to memorize how to do everything, but if you know what is possible, you can figure the details out when needed. TABLE A-3 Selected Important Excel Functions Function SUM(range) SUMPRODUCT (range1, range2) Brief Description Returns the sum of all the numbers in the range. Multiplies corresponding numbers in the two ranges and returns the sum of those products. Example SUM(B10:B17) SUMPRODUCT (B10:B17, C10:C17) (returns the value of B10*C10 B11*C11 B17*C17) AVERAGE(B10:B17) MIN(B10:B17) MAX(B10:B17) COUNT(B10:B17) STDEV(B10:B17) AVERAGE(range) MIN(range) MAX(range) COUNT(range) STDEV(range) SQRT(number) ABS(number) Returns the average of all the numbers in the range. Returns the minimum value of all the numbers in the range. Returns the maximum value of all the numbers in the range. Returns the number of cells containing numerical values. Returns the sample standard deviation (n 1 denominator) of the values in the range. Returns the square root. Returns the absolute value. SQRT(E13) ABS(E13) (Continued ) ancmat01.qxd 11/16/06 3:24 PM Page A28 A28 • SUPPLEMENT A TABLE A-3 SPREADSHEET MODELING: AN INTRODUCTION (Continued ) Function IF(logical test, value if true, value if false) Brief Description Checks whether “logical test” is true or false. If true, the result of the function is the “value if true.” If false, the result is the “value if false.” Returns the relative position in the array that matches the lookup value. Example IF(F22 69.5,”Pass”,”Fail”) MATCH(lookup_value, lookup_array, match type) INDEX(range, position) NORMSDIST(z) NORMSINV(p) Returns the value from the range in the position specified. Can also be used with two-dimensional ranges. Returns the standard normal (mean of 0, standard deviation of 1) cumulative probability for the value of z. Returns the z-value of the standard normal distribution corresponding to a cumulative probability of p. MATCH (B10, B15:B20, 0) (looks in B15:B20 for a value that exactly equals the value in B10, returns the relative position in the array) INDEX (B10:B17, 3) (returns the contents of the third cell in the range, in this case, B12) NORMSDIST(1.96) (result is 0.975) NORMSINV(0.975) (result is 1.96) SPREADSHEET MODELING WITHIN OM: PUTTING IT ALL TOGETHER The models in this chapter have been fairly basic, but you should begin to see some of the powerful uses of models and quantitative analysis. Heery International is a construction firm with an office in Nashville, Tennessee. The average budget for a project is about $2,000,000, varying from $50,000 to $50,000,000. Heery has developed a spreadsheet-based model to help it better assign managers to construction projects. As a result of this model, the company reports that the project assignment process takes much less time than before, assignments are perceived as being more fair among the managers, and travel costs (from managers’ homes to job sites) have been reduced considerably, allowing for increased productivity. A problem in the airline industry is the recovery from unexpected events, such as weather delays, mechanical problems, and crew unavailability. When such a disruption occurs, significant costs to the airlines and inconveniences to passengers can result. 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 developed, in association with CALEB Technologies, a system to help the airline recover from such events. While it is not spreadsheet-based, it demonstrates the power of modeling to help solve operational problems. CALEB Technologies and Continental Airlines recently won the Franz Edelman Award for Achievement in Operations Research and the Management Sciences. ancmat01.qxd 11/16/06 3:24 PM Page A29 DISCUSSION QUESTIONS • A29 Supplement Highlights 1 A model is a purposeful representation of the key factors in a situation and of the relationships among them. It abstracts the real situation, incorporating those factors that are important to the decision it was designed to address. The main types of models are mental models, visual models, physical models, and mathematical models. Spreadsheet models are essentially mathematical models and are the focus of this supplement. Mathematical models translate inputs into outputs through a set of relationships. Inputs consist of uncontrollable inputs and controllable inputs, sometimes called decision variables. There can be many outputs of mathematical models, but often we are interested in a relatively small number of primary outputs. The recommended spreadsheet modeling process consists of understanding the problem, drawing a sketch of the model, developing a base-case spreadsheet, testing the spreadsheet, using the model to perform analysis, and documenting the model. Models should be correct, flexible, and documented. Correctness implies that the numerical calculations are correct for the current situation. Being flexible implies that the user can change any of the input values and the results will be correctly calculated. A well-documented spreadsheet can be understood by someone else without a detailed explanation from the developer. 6 This supplement focused on the construction of models by applying sound modeling principles. You should invest time applying the principles to problems in this supplement as well as other problems in this text. Key Excel formulas and functions were addressed in this supplement. A critical skill is the correct use of Relative and Absolute References. Mastery allows you to develop a model in a fraction of the time it would take otherwise. Several important 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 of an input that causes an output to be equal to a value you specify. For example, it allows you to determine the quantity (an input) at which profit (an output) will equal $0. A Data Table allows you to vary one (or two) inputs and automatically calculate the value of an output for each of the input values in the range. We covered Data Tables in which one input was varied, but you can also create twoway Data Tables, in which two input values are varied. Several different chart types were used to illustrate model results. These were the XY chart, the Column chart, and the Stacked Column chart. Other useful chart types for presentation of model results are Pie charts, Line charts, and Bar charts. Excel has many other, more specialized chart types that can be useful in certain situations. 2 7 3 8 4 5 9 Key Terms mental model A2 visual model A2 physical model A2 mathematical model A2 spreadsheet model A2 inputs A3 controllable inputs (decision variables) A3 uncontrollable inputs (parameters) A3 output A3 model A3 base case A4 correct model A5 flexible model A5 documented model A5 Goal Seek A12 Data Table A14 relative reference A20 absolute reference A21 Discussion Questions 1. Give an example (besides the ones noted in the text) of a mental 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) of when using Goal Seek would be helpful. 8. Give an example (besides the one used in the text) of when 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 references? 11. When are tables of numbers useful in the decision-making process? When are graphs of those numbers useful? ancmat01.qxd 11/16/06 3:24 PM Page A30 A30 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION Problems 1. Kaizer Plastics produces a variety of plastic items for packaging and distribution. One item, container #145, has had a low contribution to profits. Last year, 20,000 units of container #145 were produced and sold. The selling price of the container was $20 per unit, with a variable cost of $18 per unit and a fixed cost of $70,000 per year. (a) Construct a correct, flexible, and documented base-case spreadsheet model that allows the user to easily vary the inputs 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-even quantity. (c) Construct a Data Table and chart showing profit versus quantity. (d) The company is considering ways to improve profitability by either stimulating sales volume or reducing variable costs. Management believes sales can be increased by 35 percent of their current levels or that variable costs can be reduced to 90 percent of their current level. Assuming all other costs stay at the base-case levels, use the model to determine which alternative would lead to a higher profit contribution. (e) Suppose the two alternatives in (d) are being considered, but that to increase sales by 35 percent, fixed costs would rise by $5000 annually to cover additional promotional expenses. Similarly, to reduce variable costs to 90 percent of their base-case level would require $10,000 annually to cover costs of new equipment. Make any changes needed and repeat the comparison. 2. Harrison Hotels is considering adding a spa to its current facility in order to improve the list of amenities. Operating the spa would require a fixed cost of $25,000 per year. Variable cost is estimated at $35 per customer. The hotel wants to break even if 12,000 customers use the spa facility. (a) Construct a spreadsheet model, assuming that selling price is an input to be specified by the user. (b) Use Goal Seek to find the selling price for which the hotel will break even. 3. Gabriela Manufacturing must decide whether to insource (make) or outsource (buy) a new toxic-free carpet cleaner that works with its Miracle Carpet Cleaning Machine. If it decides to insource the product, the process would incur $300,000 of annual fixed costs and $1.50 per unit of variable costs. If it is outsourced, a supplier has offered to make it for an annual fixed cost of $120,000 and a variable cost of $2.25 per unit in variable costs. (a) Construct a base-case spreadsheet model that shows both of these alternatives side-by-side. Use the same number of units (stored in a single cell) to drive the calculations. (b) If the expected annual demand for the new cleaner is 300,000 units, what would you recommend that Gabriela Manufacturing do? Provide evidence to support your recommendation. (c) Use Goal Seek to find the indifference point (where total costs are equal) between these two alternatives. (Hint: Goal Seek can work only with a single “Set cell.”) Create a cell in your model that computes the difference between the total costs 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 Data Table to generate the values for the chart. (Hint: The Data Table will need to track two output values; the total costs of the two alternatives. When setting up the Data Table, it will simply have an additional column, compared to the example presented in the text.) 4. Henri of Henri’s French Cuisine (HFC), a chain of twelve restaurants, is trying to decide if it makes sense to outsource the purchasing function. Currently, Henri employs two buyers at an annual fixed cost of $85,000. Henri’s estimates that the variable cost of each placed purchase order is $15. Value-Buy (VB), a group of purchasing specialists, will perform the purchasing function for a fixed annual fee of $100,000 plus $5 for each purchase order placed. Last year, HFC placed 1450 purchase orders. (a) Construct a base-case spreadsheet model that shows both of these alternatives side-by-side. Use the same number of orders (stored in a single cell) to drive the calculations. (b) Using last year’s quantity of purchase orders, which alternative would have been the better? (c) Use Goal Seek to find the indifference point (where total costs are equal) between these two alternatives. (Hint: Goal Seek can work only with a single “Set cell.” Create a cell in your model that computes the difference between the total costs 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 Data Table to generate the values for the chart. (Hint: The Data Table will need to track two output values; the total costs of the two alternatives. When setting up the Data Table, it will simply have an additional column, compared to the example presented in the text.) 5. Sue and Joe are a young married couple who are considering purchasing a new home. Their search has been reduced to three homes that they both like, at different locations. They have identified the factors they consider important and assigned a factor score to each location based on a 5-point scale. The information is shown in the table. Factor Score at Each Location Location Location Location Factor 1 2 3 Weight 5 4 2 5 3 1 2 2 5 3 5 2 1 5 5 1 2 4 10 25 15 10 20 20 Factor Proximity to work Proximity to family Size of home Transportation system Neighborhood Cost ancmat01.qxd 11/16/06 3:24 PM Page A31 CASE: DIET PLANNING • A31 (a) Construct a spreadsheet model to help Sue and Joe make their decision. What is your recommendation? (b) Construct a stacked column chart to illustrate the alternatives. 6. The manager of a small health clinic needs to forecast demand for laboratory services in the facility. Data from the last six weeks are available and are, respectively, 330, 350, 320, 370, 368, and 343 tests. The manager decides to use a forecasting technique known as three-period moving average. For this technique, the most recent three weeks’ demands are averaged together to produce the forecast for the upcoming week. For example, weeks 1–3 are used to produce the forecast for week 4. (a) Construct a spreadsheet model to help the manager forecast demand. Assume that this technique has been in place in the previous weeks as well, and generate the forecasted demand for week 7. You will not be able to generate forecasted demands for weeks 1–3. (b) Expand the model to calculate the error in the forecast each week. The error should be calculated as the actual demand for the week minus the forecasted demand. Which week had the largest error? Which had the smallest error? What does it mean that some errors are positive and some are negative? 7. Burger Lover Restaurant forecasts weekly sales of cheeseburgers. The last five weeks of actual sales and forecasted sales are provided in the table. Week 1 2 3 4 5 Actual Sales 354 345 367 322 356 Forecast Sales 320 340 380 366 330 (d) Expand the model to calculate what is known as the root mean squared error, or RMSE, which is a common measure of forecast accuracy. RMSE is calculated as RMSE ei2/n, where ei is the forecast error for week i and n is the total number of forecast errors calculated. (Hint: To raise a value to a power in Excel, use the carat (“∧”) symbol. For example, the formula” B16∧2” squares the value in cell B16. Also, you will need to use Excel’s SQRT function.) (e) Expand the model to calculate what is known as the mean absolute percent error, or MAPE, which is a common measure of forecast accuracy. MAPE is essentially the average percentage forecast error and is calculated as ( ei /yi) MAPE , where ei is the forecast error for week n i, yi is the actual value for week i, and n is the total number of forecast errors calculated. 8. Custom Computers, Inc. assembles custom home computer systems. The heat sinks for the central processing units are bought for $12 each and are ordered in quantities of 1300 units. Annual demand is 5200 heat sinks, the annual inventory holding cost is $3 per unit, and the cost to place an order is estimated to be $50. Assume that demand occurs continuously through the year and that a new order is placed in time so the order arrives at the same time as the previous inventory runs out. (a) Construct a base-case spreadsheet model to help analyze different order quantities. The primary output of interest is the total annual inventory-related cost, comprising total ordering cost and total holding cost. Holding cost can be applied to the average inventory level through the year. Given the assumption about continuously occurring demand and that orders arrive just in time, the average inventory level will be half of the order quantity. (b) For the base-case order quantity of 1300 heat sinks, what is the average inventory level, the number of orders placed 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. Repeat for 1950 heat sinks. (d) The Economic Order Quantity (EOQ) is defined as the order quantity that minimizes total inventory-related costs. Construct a Data Table and an XY chart and find the EOQ for this situation. (e) How much would ordering the EOQ save Custom Computers on an annual basis, compared to its current order quantity? (a) Construct a spreadsheet model to compute the forecast error for each week. The forecast error is defined to be the actual sales minus the forecasted sales and can be positive or negative. (b) Explain the meaning of positive and negative forecast errors. (c) Expand the model to calculate what is known as the mean absolute deviation, or MAD, which is a common measure of forecast accuracy. MAD is calculated as MAD ee /n, where ei is the forecast error for week i and n is the total number of forecast errors calculated. (Hint: You will need to use Excel’s ABS function to compute the absolute values of the errors.) CASE: Diet Planning Given your busy schedule, you eat many meals on the go. Lately, you’ve started to get concerned about the nutritional value of all those burger and fries lunches. You decide to build a model to help you decide ahead of time on a reasonably nutritious diet for 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 on the Web. In some cases these are essentially electronic versions of their printed nutritional brochures; in other cases they are in a form that you can copy and paste into an Excel spreadsheet. ancmat01.qxd 11/16/06 3:24 PM Page A32 A32 • SUPPLEMENT A SPREADSHEET MODELING: AN INTRODUCTION (c) Build the logic of the model to allow the user to enter the number of each menu item to consume in a day and have the nutritional statistics calculated automatically. If the model is organized well, you will be able to use the SUMPRODUCT function to easily calculate the totals for each of the nutritional categories, given the user’s input of the number of each item to consume. Your model should also include goal or target values for the nutritional items. For example, you might want a diet that has between 1800 and 2200 calories, with no more than 30 percent of the calories from fat. Some facts you will need are that 1 gram of fat contains 9 calories, 1 gram of protein contains 4 calories, and 1 gram of carbohydrate contains 4 calories. (d) Experiment with different values of items to consume, and develop your planned diet for the day. Are you willing to implement that diet? Discuss advantages and disadvantages of the diet, considering nutritional, taste, and cost factors. Explore the Web sites of several different restaurants to find available data. Choose a single restaurant for this case. Alternatively, you can use foods that you might prepare at home. The U.S. Department of Agriculture maintains a Web site containing documents that list the nutritional value of foods. See http://www.nal.usda.gov/fnic/foodcomp/. (a) You want to construct a model so that you can enter the number of each menu item you would choose to consume in one day (e.g., 1 egg and sausage sandwich, 2 cheeseburgers, 3 chocolate shakes), and have the model automatically calculate the total nutritional statistics for that particular choice of items. You will then be able to use the model to adjust your food and drink choices to help 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 the nutritional information (e.g., calories, fat, protein, vitamins, and minerals) as well. You should have at least fifteen items to choose from. 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 about spreadsheets 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.htm MrExcel.com: http://www.mrexcel.com/learnexcel.shtml The Spreadsheet Detective: http://www.uq.net.au/detective/ home.html (a) Using the listed sites and any others you find on your own, identify at least five features of Excel that you were not aware of or familiar with. Briefly explain the feature and how it might be useful in the modeling and analysis of business problems. (b) Find at least one more Excel-related site (beside Microsoft’s) that provides useful information for those doing business modeling. Briefly describe what is available there and how it could be useful to modelers. Selected Bibliography Friedrichsen, Lisa. New Perspectives on Microsoft Excel 2002 with Visual Basic for Applications: Advanced. Boston: Course Technology, 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, Fourth Edition. Stamford, Conn.: Thomson, 2004. Walkenbach, John. Excel 2003 Bible. New York: Wiley, 2003. Winston, Wayne L., and S. Christian Albright. Practical Management Science (with CD-ROM Update), Second Edition. Duxbury, Mass.: 2003.