RapidKnowHow > Develop a Decision Tree Analysis : Shall I Enter a New Market Sector Yes or No > in Excel > Step.by.Step > Decision and Explanation
Creating a Decision Tree Analysis in Excel to determine whether to enter a new market sector involves a series of steps that include defining your decision criteria, constructing the decision tree, and analyzing the potential outcomes. Here’s how you can do it step-by-step:
Step 1: Define Your Objectives and Criteria
- Objective: Determine whether to enter the new market sector.
- Criteria: List factors that will influence your decision, such as:
- Market Growth Potential
- Competitive Landscape
- Entry Costs
- Potential Revenue
- Company Resources
- Regulatory Environment
Step2: List Options and Outcomes
- Options:
- Enter New Market Sector
- Don’t Enter New Market Sector
- Outcomes for Entering: Consider potential outcomes such as:
- High Revenue
- Moderate Revenue
- Low Revenue
- Loss
- Outcomes for Not Entering:
- Maintain Current Revenue
- Potential Loss of Market Share
Step 3: Assign Probabilities and Returns
Next, gather data or estimate the probabilities of each outcome and the financial returns associated with them:
For Enter New Market:
- Probability of High Revenue (e.g., 30%): Return $500,000
- Probability of Moderate Revenue (e.g., 50%): Return $200,000
- Probability of Low Revenue (e.g., 15%): Loss $100,000
- Probability of Loss (e.g.,5%): Loss $300,000
For Not Entering:
- Maintain Current Revenue: No change (e.g., return $400,000)
- Market Share Loss (15%): Change in revenue ($50,000 loss)
Step4: Calculate Expected Values
Now, calculate the expected monetary values (EMV) for entering and not entering.
- EMV for Entering: [ EMV{\text{enter}} = (P{high} \times Return{high}) + (P{moderate} \times Return{moderate}) + (P{low} \times Return{low}) + (P{loss} \times Loss_{loss}) ]
Using values from your probabilities and returns: [ EMV_{\text{enter}} = (0.30 \times 500,000) + (0.50 \times200,000) + (0.15 \times -100,000) + (0.05 \times -300,000) ]
- EMV for Not Entering: [ EMV{\text{not enter}} = (P{maintain} \times Return{maintain}) + (P{lossshare} \times Loss{lossshare}) ] Using: [ EMV{\text{not enter}} = (0.85 \times 400,000) + (0.15 \times -50,000) ]
Step 5: Build the Decision Tree in Excel
- Open Excel.
- Create a Table:
- In a new worksheet, create the structure of your decision tree. Use rows for each decision and its outcomes and the calculated EMVs in adjacent columns.
Example table layout:
Copy Code| Decision (Enter/Not Enter) | Outcome | Probability | Return/Loss | EMV |
|-----------------------------|------------------|-------------|-------------|--------------|
| Enter | High Revenue |0.30 |500,000 | =B3*C3 |
| Enter | Moderate Revenue |0.50 |200,000 | =B4*C4 |
| Enter | Low Revenue |0.15 | -100,000 | =B5*C5 |
| Enter | Loss | 0.05 | -300,000 | =B6*C6 |
| Not Enter | Maintain Revenue | 1.0 | 400,000 | =B7*C7 |
| Not Enter | Loss of Market | 0.15 | -50,000 | =B8*C8 |
- Use Formulas:
- In the EMV column, calculate the value based on the probabilities and returns (as shown above).
- Sum EMVs:
- Create a total row for the EMVs of the decision to enter and not enter:
=SUM(range of EMVs for entering)for the ‘Enter’ option.=SUM(range of EMVs for not entering)for the ‘Not Enter’ option.
Step 6: Analyze and Make a Decision
- Compare EMVs:
- Look at the total EMVs for your decision options:
- If EMV(Enter) > EMV(Not Enter), it might be advisable to enter the new market.
- If EMV(Not Enter) > EMV(Enter), consider staying out of the market.
Step 7: Visual Representation (Optional)
- Insert Shapes: Use shapes (ovals, arrows) to visually represent your decision tree directly on the worksheet.
- Charting: Consider using Excel’s chart features to create a thicker visual representation.
Conclusion
By following these steps, you can structure a decision tree analysis in Excel to guide your decision-making regarding entering a new market sector. Always remember to continuously refine the probabilities and outcomes as you gather more data and insights.
