Description
In this video I'll show you how to make a decision tree in Microsoft Excel. A decision tree lays out your choices, the possible outcomes of each one, and how likely they are, so you can compare options and see which one pays off best on average.
Timestamps:
Introduction: 0:00
Calculate the Payoffs: 0:18
Draw the Decision Tree: 2:03
Customize the Decision Tree: 3:40
Conclusion: 4:13
Check out my website for other great tutorials about all kinds of technology: https://maxdalton.how/
Support this channel by becoming a member: https://www.youtube.com/channel/UCBbDWMccTJCL0WmbMHNLZIw/join
#excel #microsoftexcel #exceltutorial #techtips #tutorial
Video Transcript:
Step 1. Open your Excel spreadsheet. For this example, I'm using a comic book shop deciding whether to order 500 copies of a variant cover, order 250 copies, or skip it. Each option has an upfront cost, and possible high, medium, or low demand outcomes with a probability and revenue for each.
Step 2. Click in cell F2, the first cell in the Net Payoff column, and type =E2-B2 to subtract the upfront cost from the revenue. Then press "Enter."
Step 3. Click in cell G2, the first cell in the Expected Value column, and type =D2*F2 to multiply the payoff by its probability. Then press "Enter."
Step 4. Click and drag to select cells F2 and G2, and then double-click the fill handle in the bottom right corner of the selection to copy both formulas down to row 8.
Step 5. Add up the expected values for each option. Click in cell H2 and type =SUM(G2:G4), click in cell H5 and type =SUM(G5:G7), and click in cell H8 and type =G8. Ordering 500 copies comes out to $1,215, ordering 250 copies comes out to $1,032.50, and skipping it comes out to $0, so ordering 500 copies is the best choice on average.
Now let's turn those numbers into a tree you can see.
Step 1. Click the "Insert" tab at the top of the screen, click "Illustrations," and then click "SmartArt." The Choose a SmartArt Graphic window opens.
Step 2. Click "Hierarchy" in the menu on the left side of the window, click "Horizontal Hierarchy," and then click "OK." The graphic appears on your sheet, along with a text pane on its left side.
Step 3. Click the first bullet in the text pane, and type the decision, like Variant Cover Order.
Step 4. Click the next bullet, and type your first option along with its expected value, like Order 500, $1,215. Select the empty bullets below it, and press "Delete" to remove them. Then press "Enter" to add a new bullet, and type your next option at the same level, like Order 250, $1,033, and Skip it, $0.
Step 5. To add outcomes under an option, click at the end of that option's line, press "Enter," and then press "Tab" to move the new bullet one level in. Type the outcome, its probability, and its payoff, like High demand, 35 percent, $2,750. Repeat this for each outcome. The tree updates as you type, branching from your decision to each option, and then to each outcome.
Once the tree is built, click the "SmartArt Design" tab to change the colors or style. To highlight the winning option so it stands out, click its box, click the "Format" tab, click "Shape Fill," and choose a different color. Keep in mind SmartArt text doesn't update automatically when your numbers change, so if you'd rather have a tree that's linked to your data, you can draw it with shapes and connector lines from the "Shapes" menu under "Illustrations" on the Insert tab instead.