Fluent Essays

Assessment Description The purpose of this assignment is to show how business decisions can be made by using rigorous decision-making techniques. Using specified data files, chapter example files, and templates from the “Topic 8 Student Data, Template, and Example Files” topic material, complete Chapter 6, Problems 2, 31 (part a), 32, and 33 from the textbook. Use Microsoft Excel to complete Problem 2. Use the Palisade DecisionTools software to complete Problems 31, 32, and 33, and ensure that all Palisade software output is included in your files. The Palisade DecisionTools Excel software needs to be used to create the decision trees. To receive full credit on the assignment, complete the following. Ensure that the Palisade software output is included with your submission. Ensure that Excel files include the associated cell functions and/or formulas if functions and/or formulas are used. Include a written response to all narrative questions presented in the problem by placing it in the associated Excel file. Place each problem in its own Excel file. Ensure that your first and last name are in your Excel file names. 1363025 - Cengage Learning © amework for doing all of the EMV calculations. They allow you to use the following folding-back procedure to find the EMVs and the best decision. Folding-Back Procedure Starting f rom the right of the decision tree and working back to the left: 1. At each probability node, calculate an EMV—a sum of products of monetary values and probabilities. 2. At each decision node, take a maximum of EMVs to identify the optimal decision.3 This is exactly what we did in Figure 6.3. At each probability node, we calculated EMVs in the usual way (sums of products) and wrote them above the nodes. Then at the decision node, we took the maximum of the three EMVs and wrote it above this node. Although this procedure requires more work for more complex decision trees, the same two steps—taking EMVs at probability nodes and taking maximums at decision nodes— are the only arithmetic operations required. In addition, the PrecisionTree add-in discussed later in the chapter performs the folding-back calculations for you. The folding-back process is a systematic way of calculating EMVs in a decision tree and thereby identifying the best decision strategy. Problems Solutions for problems whose numbers appear within a colored box can be found in the Student Solution Files. Level A 1. Several decision criteria besides EMV are suggested in the section. For each of the following criteria, rank 1363025 - Cengage Learning © all three decisions in Figure 6.1 f rom best to worst. a. Look only at the worst possible outcome for each decision. b. Look only at the best possible outcome for each decision. c. Look at the variance of the distribution of outcomes for each decision, which you want to be small. (The variance of a probability distribution is the weighted sum of squared differences f rom the mean, weighted by the probabilities.) 2. For the decision problem in Figure 6.1, use data tables to perform the following sensitivity analyses. The goal in each is to see whether decision 1 continues to have the largest EMV. In each part, provide a brief explanation of the results. a. Let the payoff f rom the best outcome, the value in cell A3, vary f rom $30,000 to $50,000 in increments of $2500. b. Let the probability of the worst outcome for the first decision, the value in cell B5, vary f rom 0.7 to 0.9 in increments of 0.025, and use formulas in cells B3 and B4 to ensure that they remain in the ratio 1 to 2 and the three probabilities for decision 1 continue to sum to 1. c. Use a two-way data table to let the inputs in parts a and b vary simultaneously over the indicated ranges. Level B 3. Some decision makers prefer decisions with low risk, but this depends on how risk is measured. As we mentioned in this section, variance (see the definition in problem 1) is one measure of risk, but it includes both upside and downside risk. That is, an outcome with a large positive payoff contributes to variance, but this type of “risk” is good. Consider a decision with some possible payoffs and some possible costs, with given probabilities. How might you develop a measure of downside risk for such a 1363025 - Cengage Learning © decision? With your downside measure of risk, which decision in Figure 6.1 do you prefer, decision 1 or decision 2? (There is no single correct answer.) 6-4 One-Stage Decision Problems Many decision problems are similar to the simple decision problem discussed in the previous section. You make a decision, then you wait to see an uncertain outcome, and a payoff is received or a cost is incurred. We refer to these as single-stage decision problems because you make only one decision, the one right now. They all unfold in essentially the same way, as indicated by the spreadsheet model in Figure 6.1 or the decision tree in Figure 6.3. The following example is typical of one-stage decision problems. This example is used as a starting point for more complex examples in later sections. EXAMPLE 6.1 NEW PRODUCT DECISIONS AT ACME The Acme Company must decide whether to market a new product. As in many new-product situations, there is considerable uncertainty about the eventual success of the product. The product is currently part way through the development process, and some fixed development costs have already been incurred. If the company decides to continue development and then market the product, there will be additional fixed costs, and they are estimated to be $6 million. If the product is marketed, its unit margin (selling price minus variable cost) will be $18. Acme classifies the possible market results as “great,” “fair,” and “awful,” and it estimates the probabilities of these outcomes to be 0.45, 0.35, and 0.20, respectively. Finally, the company estimates that the corresponding sales volumes (in thousands of units sold) f rom these three outcomes are 600, 300, and 90, respectively. Assuming that Acme is an EMV maximizer, should it finish development and then market the product, or should it stop development at this point and abandon the product?4 Objective To use the EMV criterion to help Acme decide whether to go ahead with the product. Where Do the Numbers Come From? file://view/books/9781473781962/epub/OEBPS/07_9780357109953_contents.html#tch6_4 1363025 - Cengage Learning © Acme’s cost accountants should be able to estimate the monetary inputs: the fixed costs and the unit margin. (Any fixed costs already incurred are sunk and therefore have no relevance to the current decision.) The uncertain sales volume is really a continuous variable but, as in many decision problems, Acme has replaced the continuum by three representative possibilities. The assessment of the probabilities and the sales volumes for these three possibilities might be based partly on historical data and market research, but they almost surely have a subjective component. Solution The elements of the decision problem appear in Figure 6.4. (See the files New Product Decisions - Single-Stage 1a Finished.xlsx and New Product Decisions - Single-Stage 1b Finished.xlsx.) If the company decides to stop development and abandon the product, there are no payoffs, costs, or uncertainties; the EMV is $0. (Actually, this isn’t really an expected value; it is a sure $0.) On the other hand, if the company proceeds with the product, it incurs the fixed cost and receives $18 for every unit it sells. The probability distribution of sales volume given in the problem statement appears in columns A to C, and each sales volume is multiplied by the unit margin to obtain the net revenues in column D. Finally, the formula for the EMV in cell B12 is = SUMPRODUCT(D8:D10,B8:B10)-B4 Because this EMV is positive, slightly over $1 million, the company is better off marketing the product than abandoning it. ■ Figure 6.4 Spreadsheet Model for Single-Stage New Product Decision As before, a decision tree is probably overkill for this problem, but it is shown in Figure 6.5. (All monetary and 1363025 - Cengage Learning © sales volumes are shown in thousands.) This tree indicates one of at least two equivalent ways to show the EMV calculations. The values at the end nodes ignore the fixed cost, which is instead shown under the decision branch as a negative number. Therefore, the 7074 value above the probability node is the expected net revenue, not including the fixed cost. Then the fixed cost is subtracted f rom this to obtain the 1074 value above the decision node. Figure 6.6 shows an equivalent tree, where the fixed cost is still shown under the decision branch but is subtracted f rom each end node. Now the EMV above the probability node is after subtraction of the fixed cost. The two trees are equivalent and either is perfectly acceptable. However, the second tree provides the insight that two of the three outcomes result in a net loss to Acme, even though the weighted average, the EMV, is well in the positive range. (Besides, as you will see in the next section, the second tree is the way the PrecisionTree add-in does it.) Using the spreadsheet model in Figure 6.4, it is easy to perform a sensitivity analysis. Usually, the main purpose of such an analysis is to see whether the best decision changes as one or more inputs change. As an example, we will see whether the best decision continues to be “proceed with marketing” if the total market decreases. Specifically, we let each of the potential sales volumes decrease by the same percentage and we keep track of the EMV f rom marketing the product. The results appear in Figure 6.7. For any percentage decrease in cell G3, the EMV f rom marketing is calculated in cell G4 with the formula = (1-G3)*SUMPRODUCT(D8:D10,B8:B10)-B4 ■ Figure 6.5 Decision Tree for New Product Model 1363025 - Cengage Learning © ■ Figure 6.6 Equivalent Decision Tree Then a data table is used in the usual way, with cell G3 as the column input cell, to calculate the EMV for various percentage decreases. As you can see, the EMV stays positive, so that marketing remains best, for decreases up to 15%. But if the decrease is 20%, the EMV becomes negative, meaning that the best decision is to abandon the product. In this case, the possible gains f rom marketing are not large enough to offset the fixed cost. ■ Figure 6.7 Sensitivity Analysis The Acme problem is a prototype for all single-stage decision problems. When only a single decision needs to be made, and all of the elements of the decision problem have been specified, it is easy to calculate the required EMVs for the possible decisions and hence determine the EMV-maximizing decision in a spreadsheet model. The problem and the calculations can also be shown in a decision tree, although this doesn’t really provide any new information except possibly to give everyone involved a better “picture” of the decision problem. In the next section, we examine a multistage version of the Acme problem, and then the real advantage of decision trees will become evident. 1363025 - Cengage Learning © Problems Solutions for problems whose numbers appear within a colored box can be found in the Student Solution Files. Level A 4. The fixed cost of $6 million in the Acme problem is evidently not large enough to make Acme abandon the product at the current time. How large would the fixed cost need to be to make the abandon option the best option? Explain how the decision tree, especially the version in Figure 6.5, answers this question easily. 5. Perform a sensitivity analysis on the probability of a great market. To do this, enter formulas in cells B9 and B10 (see Figure 6.4) to ensure that the probabilities of “fair” and “awful” remain in the same ratio, 35 to 20, and that all three probabilities continue to sum to 1. Then let the probability of “great” vary f rom 0.25 to 0.50 in increments of 0.05. Is it ever best to abandon the product in this range? 6. Sometimes it is possible for a company to influence the uncertain outcomes in a favorable direction. Suppose Acme could, by an early marketing blitz, change the probabilities of “great,” “fair,” and “awful” f rom their current values to 0.75, 0.15, and 0.10. In terms of EMV, how much would the company be willing to pay for such a blitz? Level B 7. Sometimes a “single-stage” decision can be broken down into a sequence of decisions, with no uncertainty resolved between these decisions. Similarly, uncertainty can sometimes be broken down into a sequence of uncertain outcomes. Here is a typical example. A company has a chance to bid on a government project. The company first decides whether to place a bid, and then if it decides to place a bid, it decides how much to bid. Once these decisions have been made, the uncertainty is resolved. First, the company observes whether there 1363025 - Cengage Learning © are any competing bids. Second, if there is at least one competing bid, the company observes the lowest competing bid. The lowest of all bids wins the contract. Draw a decision tree that reflects this sequence. There should be two “stages” of decision nodes, followed by two “stages” of probability nodes. Then label the tree with some reasonable monetary values and probabilities, and perform the folding back process to find the company’s best strategy. Note that if the company wins the contract, its payoff is its bid amount minus its cost of completing the project minus its cost of preparing the bid, where these costs are assumed to be known. 6-5 The PrecisionTree Add-In Decision trees present a challenge for Excel. The challenge is to take advantage of Excel’s calculation capabilities (to calculate EMVs, for example) and its graphical capabilities (to draw the decision tree). Using only Excel’s built-in tools, this is virtually impossible (or at least very painful) to do. Fortunately, Palisade has developed an Excel add-in called PrecisionTree that makes the process relatively straightforward. This add-in not only enables you to draw and label a decision tree, but it also performs the folding-back procedure automatically and then allows you to perform sensitivity analysis on key input parameters. The first thing you must do to use PrecisionTree is to “add it in.” We assume you have already installed the Palisade DecisionTools Suite. Then to run PrecisionTree, you have two options: • If Excel is not currently running, you can open Excel and PrecisionTree by selecting PrecisionTree f rom the Palisade group in the list of programs on your computer. • If Excel is currently running, the first option will open PrecisionTree on top of Excel. In either case, you will see the Welcome screen in Figure 6.8. Note the Quick Start link. We will come back to this shortly. Once you click OK to dismiss the Welcome screen, you will know that PrecisionTree is loaded because of the new PrecisionTree tab and associated ribbon shown in Figure 6.9. file://view/books/9781473781962/epub/OEBPS/07_9780357109953_contents.html#tch6_5 1363025 - Cengage Learning © ■ Figure 6.8 PrecisionTree Welcome Screen ■ Figure 6.9 PrecisionTree Ribbon Although PrecisionTree is quite easy to use once you are familiar with it, you have to learn the basics. The easiest way to do this is to run a series of Quick Start videos. To do this, you can bring up the Welcome screen in Figure 6.8 at any time through the Precision-Tree Help dropdown list. Then you can click the Quick Start link on the Welcome screen. This opens an example file shown in Figure 6.10. The five buttons on the left each launch a video that explains the basic features of PrecisionTree. Rather than repeat this information here, we urge you to watch the videos and practice the steps—as often as you like. From here on, we assume that you have done so. 1363025 - Cengage Learning © ■ Figure 6.10 PrecisionTree Quick Start Buttons single-stage_decision_ tree video.. node for whether to market the product It is instructive to examine PrecisionTree’s decision tree for Acme’s single stage problem. The completed tree appears in Figure 6.11. (See the file New Product Decisions -Single-Stage - 1c Finished.xlsx.) It is essentially a mixture of the trees in Figures 6.4 and 6.5, and it is equivalent to each of them. As in Figure 6.4, the fixed cost is entered as a negative number below the decision branch, and the net revenues are entered below the probability branches. Then PrecisionTree calculates the net value—the sum of the monetary values on any path through the tree—to the right of the corresponding triangle end nodes. For the folding back process, it uses these net values. Specifically, the 1074 value to the right of the probability node is calculated (automatically) as (4800)(0.45) + (−600) (0.35) + (−4380)(0.20).5 Then the 1074 value to the right of the decision node is calculated as the maximum of 1074 and 0. In other words, PrecisionTree draws essentially the same tree and makes the same calculations that you could do by hand. Its advantages are that (1) it generates a nice-looking tree with all of the relevant inputs displayed, (2) it performs the folding- back calculations automatically, and (3) it permits quick sensitivity analyses on any of the model inputs. Also, you can easily identify the best decisions by following the TRUE branches. We will continue to use PrecisionTree in the rest of the chapter for trees that are considerably more complex than the one in Figure 6.11. 1363025 - Cengage Learning © PrecisionTree Tip Formatting Numbers If you are careful about formatting numbers in Excel, you might spend a lot of time formatting all of the numbers in a decision tree just the way you like them. However, there is a much quicker way in PrecisionTree. From the Settings dropdown on the PrecisionTree ribbon, select Model Settings and then the Format tab. By entering the formats you prefer here, the entire tree is formatted appropriately. ■ Figure 6.11 Decision Tree from PrecisionTree We finish this section with one important reminder discussed in the Quick Start videos. PrecisionTree reserves the cells with colored font (green, red, and blue) for its special formulas, so you should not change these cells. Your entries—probabilities and monetary values—should all be in the cells with black font, and it is a good practice to cell reference these inputs whenever possible. For example, we didn’t enter 45% in cell C12; we entered a link to cell B8. Problems 1363025 - Cengage Learning © Solutions for problems whose numbers appear within a colored box can be found in the Student Solution Files. Level A 8. Explain in some detail how the PrecisionTree calculations in Figure 6.11 for the Acme problem are exactly the same as those for the hand-drawn decision tree in Figure 6.6. In other words, explain exactly how PrecisionTree gets the monetary values in the colored cells in Figure 6.11. 9. Use PrecisionTree’s Sensitivity Analysis tools to perform the sensitivity analysis requested in problem 5 of the previous section. (Watch the Step 5 video in Figure 6.10 if necessary.) Level B 10. Use PrecisionTree to solve problem 7 of the previous section. 6-6 Multistage Decision Problems Many real-world decision problems evolve through time in stages. A company first makes a decision. Then it observes an uncertain outcome that provides some information. Based on this information, the company then makes another decision. Then it observes another uncertain outcome. This process could continue for more stages, but we will limit the number of stages to two: a first decision, a first uncertain outcome, a second decision, and a second uncertain outcome. As time unfolds, payoffs are received and costs are incurred, depending on the decisions made and the uncertain outcomes observed. The objective is again to maximize EMV, but now we are searching for an EMV-maximizing strategy, often called a contingency plan, that specifies which decision to make at each stage. As you will see shortly, a contingency plan tells the company which decision to make at the first stage, but the company won’t know which decision to make at the second stage until the information f rom the first uncertain outcome is known. For example, if the information is bad news about a product, then the company might decide at the second stage to abandon file://view/books/9781473781962/epub/OEBPS/07_9780357109953_contents.html#tch6_6 1363025 - Cengage Learning © the product, but if the news is good, the company might decide to continue with the product. This is the essence of a contingency plan: it specifies what do for each possible uncertain outcome. An important aspect of multistage decision problems is that probabilities can change through time. After you receive the information f rom the first-stage uncertain outcome, you might need to reassess the probabilities of future uncertain outcomes. As an example, if a new product is observed to do very poorly in a regional test market, your assessment of the probability that it will do well in a national market will almost surely decrease. Sometimes this reassessment of probabilities can be done in an informal subjective manner. But whenever possible, it should be done with a probability law called Bayes’ rule. This rule provides a mathematical way of updating probabilities as new information becomes available. We explain how it works in this section. Another important aspect of multistage decision problems is the value of information. Sometimes the first-stage decision is to buy information that will help in making the second-stage decision. The question then is how much this information is worth. If you knew what the information would be, there would be no point in buying it. However, you virtually never know what the information will be; you can only assess the probabilities of various information outcomes. In such cases, the goal is to calculate the expected value of the information— how much better you would be with the information than without it—and then compare this to the actual cost of buying the information to see whether it is worth buying. Again, we explain how it works in this section. We now show one way the Acme decision problem can be extended to two stages. Later in this section, we examine another multistage version of Acme’s problem. EXAMPLE 6.2 NEW PRODUCT DECISIONS AT ACME WITH TECHNOLOGICAL UNCERTAINTY In this version of the example, we assume as before that the new product is still in the development stage. However, we now assume that there is a chance that the product will be a failure for technological reasons, such as a new drug that fails to meet FDA approval. At this point in the development process, Acme assesses the probability of technological 1363025 - Cengage Learning © failure to be 0.2. The $6 million fixed cost f rom before is now broken down into two components: $4 million for addition development costs and $2 million for fixed costs of marketing, the latter to be incurred only if the product is a technological success and the company decides to market it. The unit margin and the probability distribution of the product’s sales volume if it is marketed are the same as before. How should Acme proceed? Objective To use a decision tree to find Acme’s EMV-maximizing strategy for this two-stage decision problem. Where Do the Numbers Come From? The probability of technological failure might be based partly on historical data—the technological failure rate of similar products in the past—but it is probably partly subjective, based on how the product’s development has proceeded so far. The probability distribution of sales volume is a more difficult issue. When Acme makes its first decision, right now, it must look ahead to see how the market might look in the future, after the development stage, which could be quite a while f rom now. (The same issue is relevant in Example 6.1, although we didn’t discuss it there.) This a difficult assessment, and it is an obvious candidate for an eventual sensitivity analysis.6 Solution The reason this is a two-stage decision problem is that Acme can decide right away to stop development and abandon the product, thus saving further fixed costs of development. However, if Acme decides to continue development and the product turns out to be a technological success, a second decision on whether to market the product must still be made. A spreadsheet model such as in Figure 6.1 for the single- stage problem could be developed to calculate the relevant EMVs, but this isn’t as easy as it sounds. A much better way is to use a decision tree, using the PrecisionTree add-in. The finished tree appears in Figure 6.12. (See the file New Product Decisions - Technological Uncertainty Finished.xlsx.) The first decision is whether to continue development. If “Yes,” the fixed development cost is incurred, so it is entered on this branch. Then there is a probability node for the technological success or failure. If 1363025 - Cengage Learning © it’s a failure, there are no further costs, but the fixed development cost is lost. If it’s a success, Acme must decide whether to market the product. From this point, the tree is exactly like the single-stage tree, except that the fixed development cost has been incurred. By following the TRUE branches, you can see Acme’s best strategy. The company should continue development, and if the product is a technological success, it should be marketed. The EMV, again the weighted average of all possible monetary outcomes with this strategy, is $59,200. However, this is only the expected value, or mean, of the probability distribution of monetary outcomes. You can see the full probability distribution by requesting a risk profile f rom PrecisionTree (through the Decision Analysis dropdown). This appears, both in graphical and tabular form, in Figure 6.13. Note that Acme has a 64% chance of incurring a net loss with this strategy, including a possible loss of $4.38 million. This doesn’t sound good. However, the company has a 36% chance of a net gain of $4.8 million and, in an expected value sense, this more than offsets the possible losses. ■ Figure 6.12 Decision Tree with Possible Technological Failure 1363025 - Cengage Learning © ■ Figure 6.13 Risk Profile from Best Strategy PrecisionTree Tip Placement of Results When you request a risk profile or other PrecisionTree reports, they are placed in a new workbook by default. If you would rather have them placed in the same workbook as your decision tree, select Application Settings f rom the Utilities dropdown list on the PrecisionTree ribbon, and change the “Place Reports In” setting to Active Workbook. You only have to do this once. We won’t perform any systematic sensitivity analyses on this model (we ask you to do some in the problems), but it is easy to show that the best strategy is quite sensitive to the probability of technological success. If you change this probability f rom 0.8 to 0.75 in cell B4, the tree automatically recalculates, with the results in Figure 6.14. With just this small change, the best decision changes completely. Now the company should discontinue development and abandon the product. There is evidently not a large enough chance of recovering the fixed development cost. 1363025 - Cengage Learning © ■ Figure 6.14 Decision Tree with Larger Probability of Failure Modeling Issues We return to the probability distribution of eventual sales volume. The interpretation here is that at the time of the first decision, Acme has assessed what the market might look like after the development stage, which could be quite a while f rom now. Again, this is a difficult assessment. Acme could instead break this assessment into parts. It could first assess a probability distribution for how the general market for such products might change—up, down, or no change, for example—by the time development is completed. Then for each of these general markets, it could assess a probability distribution for the sales volume of its new product. By breaking it up in this way, Acme might be able to make a more accurate assessment, but the decision tree would be somewhat more complex. We ask you to explore this in one of the problems. The next example illustrates another possible multistage extension of the Acme decision problem. This example provides an opportunity to introduce two important topics discussed earlier: Bayes’ rule for updating probabilities and the value of information. EXAMPLE 6.3 NEW PRODUCT DECISIONS AT ACME WITH AN OPTION TO BUY INFORMATION Suppose now that Acme has just about finished the development process on the new product, so that fixed development costs are no longer an issue, and technological failure is no longer a possibility. The only question is whether Acme should market the product, given 1363025 - Cengage Learning © the uncertainty about the eventual sales volume. If the company decides to market the product, it will incur fixed marketing costs of $4 million. To keep the model simple, we now assume that there are only two possible market outcomes, good or bad. The sales volumes for these two possible outcomes are 600,000 units and 100,000 units, and Acme assesses that their probabilities are 0.4 and 0.6. However, before making the ultimate decision, Acme has the option to hire a well-respected marketing research firm for $150,000. If Acme decides to use this option, the result will be a prediction of good or bad. That is, the marketing research firm will predict that either “We think the market for this product will be good” or “We think the market for this product will be bad.” Acme has used this firm before, so it has a sense of the prediction accuracy, as indicated in Table 6.1. Each row in this table indicates the actual market outcome, and each column indicates the prediction. If the actual market is good, the prediction will be good with probability 0.8 and bad with probability 0.2. If the actual market is bad, the prediction will be bad with probability 0.7 and good with probability 0.3. What should Acme do to maximize its EMV? Table 6.1 … Topic 8 Assignment Template and Data Files/P06_31.xlsx Data Tire plant decisions Decision\outcome Expand Remain stable Contract Construct a new plant $400,000 -$100,000 -$200,000 Expand existing plant $250,000 -$50,000 -$75,000 Do nothing $50,000 $0 -$30,000 Topic 8 Assignment Template and Data Files/P06_33.xlsx Data Introduction of new products Trend in national economy Decisions\outcomes Strong Fair Weak Introduce neither product $0 $0 $0 Introduce Product 1 only $500,000 $260,000 $120,000 Introduce Product 2 only $420,000 $230,000 $110,000 Introduce both products $820,000 $390,000 $200,000 Topic 8 Assignment Template and Data Files/Template files not provided for the rest of the problems.docx Topic 8 Chapter 6 Examples (Finished)/New Product Decisions - Single-Stage Finished.xlsx Model Acme single-stage new product decision Decision 1: Continue development and market the new product % decrease in all sales volumes 0% Fixed cost $6,000 EMV for decision 1 $1,074 Unit margin $18 Sensitivity analysis to percentage decrease in all sales volumes Market Probability Sales volume Net revenue % decrease EMV for decision 1 Great 0.45 600 $10,800 $1,074 Fair 0.35 300 $5,400 5% $720 Awful 0.20 90 $1,620 10% $367 15% $13 EMV $1,074 20% -$341 Decision 2: Stop development and abandon product No payoffs, no costs, no uncertainty EMV $0 All monetary values (except the unit margin in cell B5) are in $1000s, and all sales volumes are in 1000s of units. Decision Tree 1 Market product Abandon product 0.45 0.35 0.20 600(18) = 10800 0 -6000 1074 300(18) = 5400 90(18) = 1620 7074 Great Fair Awful Decision Tree 2 Market product Abandon product 0.45 0.35 0.20 600(18) -6000 = 4800 0 -6000 1074 300(18) -6000 = -600 90(18) -6000 = -4380 1074 Great Fair Awful Topic 8 Chapter 6 Examples (Finished)/New Product Decisions - Single-Stage with PrecisionTree Finished.xlsx Model Acme single-stage new product decision Inputs Fixed cost $6,000 Unit margin $18 Market Probability Sales volume Net revenue Great 0.45 600 $10,800 Fair 0.35 300 $5,400 Awful 0.20 90 $1,620 45.0% 45.0% $10,800 $4,800 TRUE Sales volume -$6,000 $1,074 35.0% 35.0% $5,400 -$600 20.0% $0 $1,620 -$4,380 Continue with product? $1,074 FALSE 0.0% 0 $0 All monetary values (except the unit margin in cell B5) are in $1000s, and all sales volumes are in 1000s of units. New Product Decision No Yes Great Fair Awful treeCalc_1 Name New Product Decision Ptree1 Compatibility 3 Output Label R-Value Ref. 100 SheetRef ERROR:#REF! Eval. Function 823297 GenInfo 0,1,1,0,0,Exponential, 0,0,-1,0,-1,-1,.0001 Creation Version 6.2.0 Output Value NF <NF> Def. Link = Required Version 5.0.0 Output Prob NF Automatic EXT REFS 0 Recommended Version 5.0.0 Input Value NF <NF> Def. Form Last Modified By Version 6.2.0 Input Prob NF Automatic Calc Macro Highest# 6 Anchor Cell Branch Name bformtype valformula pbformula distribution cumPayoffFunction link ENDNODEFORMULA VAL PB GenInfo IntRefs RefRefs NodeNames Collapsed 1074 New Product Decision 0 DEFAULT 0 0 2,0,0,2,2,3,0,0,0 0 Continue with product? FALSE 1074 Yes 0 DEFAULT -6000 1,0,0,3,4,5,6,1,0,0 0 Sales volume FALSE 0 No 0 DEFAULT DEFAULT 0 4,0,0,0,1,0,0 0 FALSE 4800 Great 0 DEFAULT DEFAULT 10800 0.45 4,0,0,0,2,0,0 0 FALSE -600 Fair 0 DEFAULT DEFAULT 5400 0.35 4,0,0,0,2,0,0 0 FALSE -4380 Awful 0 DEFAULT DEFAULT 1620 0.2 4,0,0,0,2,0,0 0 FALSE Topic 8 Chapter 6 Examples (Finished)/Simple Decision Problem Finished.xlsx Model Decision 1 Decision 2 Decision 3 Payoff/Cost Probability Payoff/Cost Probability Payoff/Cost Probability $50,000 0.1 $5,000 0.6 $3,000 1 $10,000 0.2 -$1,000 0.4 -$5,000 0.7 EMV $3,500 EMV $2,600 EMV $3,000 Decision Tree Decision 1 Decision 2 Decision 3 0.1 0.2 0.7 0.6 0.4 50000 10000 -5000 5000 -1000 3000 2600 3500 3500 TopicInfo test test GlobalInfo Countries Priority Smiley Arrows Stop Go Progress Emphasis US Priority 1 Happy Left Go Not Done Question Canada Priority 2 Sad Right Stop Quarter Done Exclamation Mexico Priority 3 Angry Up Caution Half Done Light Bulb Brazil Priority 4 Frustrated Down Go Three Quarters Done Pin Argentina Priority 5 Happy Revert Stop Task Done Question Colombia Priority 1 Sad Left Caution Not Done Exclamation UK Priority 2 Angry Right Quarter Done Light Bulb France Priority 3 Frustrated Up Half Done Pin Germany Priority 4 Down Three Quarters Done Spain Priority 5 Revert Task Done Italy Australia China Japan US Canada Mexico Brazil Argentina Colombia UK France Germany Spain Italy Australia China Japan Topic 8 Chapter 6 Examples (Templates,Data)/New Product Decisions - Single-Stage.xlsx Model Acme single-stage new product decision Decision 1: Continue development and market the new product Fixed cost $6,000 Unit margin $18 Market Probability Sales volume Net revenue Great 0.45 600 $10,800 Fair 0.35 300 $5,400 Awful 0.20 90 $1,620 EMV Decision 2: Stop development and abandon product No payoffs, no costs, no uncertainty EMV All monetary values (except the unit margin in cell B5) are in $1000s, and all sales volumes are in 1000s of units. Topic 8 Chapter 6 Examples (Templates,Data)/Simple Decision Problem.xlsx Model Decision 1 Decision 2 Decision 3 Payoff/Cost Probability Payoff/Cost Probability Payoff/Cost Probability $50,000 0.1 $5,000 0.6 $3,000 1 $10,000 0.2 -$1,000 0.4 -$5,000 0.7 EMV EMV EMV TopicInfo test test GlobalInfo Countries Priority Smiley Arrows Stop Go Progress Emphasis US Priority 1 Happy Left Go Not Done Question Canada Priority 2 Sad Right Stop Quarter Done Exclamation Mexico Priority 3 Angry Up Caution Half Done Light Bulb Brazil Priority 4 Frustrated Down Go Three Quarters Done Pin Argentina Priority 5 Happy Revert Stop Task Done Question Colombia Priority 1 Sad Left Caution Not Done Exclamation UK Priority 2 Angry Right Quarter Done Light Bulb France Priority 3 Frustrated Up Half Done Pin Germany Priority 4 Down Three Quarters Done Spain Priority 5 Revert Task Done Italy Australia China Japan US Canada Mexico Brazil Argentina Colombia UK France Germany Spain Italy Australia China Japan