1- If you want to test how an increase in percentage of cost of good sold affects the contribution margin, you would use __.
- A combination of sensitivity and scenario analysis
Sensitivity analysis
Either sensitivity or scenario analysis
Scenario analysis
2- When performing a scenario analysis, which of the following tools/functions in Excel is used to create a dropdown list where we can select the live case?
- INDEX MATCH MATCH
Tornado Chart
Data Table
Data Validation
3- You should perform sensitivity analysis when:
- You need to change multiple inputs at once
You want to demonstrate several business cases
You want to reach more accurate forecast results
You need to determine which assumptions matter the most
4- The Pic is attached called Q4:
The formula contained in the yellow highlighted cell (I39) is = ( , I9,I24). This formula can be copied over to the entire live scenario section without any manual modification. (Hint: do not forget to use absolute references.)
Please download and open the attached file. This file will be used for following questions from Q5- Q10
5- What is the share price when the discount rate is 13% and revenue growth is 15%?
- 36.70
35.47
39.70
36.83
6- What is the share price when COGS increases by 5%?
- 29.29
30.47
22.71
26.87
7- The Pic is attached called Q7:
The formula contained in the YELLOW cell (G179) is _ Hint: do not forget the $ when using absolute references. Use absolute reference so this formula can be copied over to other cells in this section without any manual modification.
- =SMALL(F177:F180,$E$179)
=SMALL(E177:E180,$F$179)
=SMALL($E$177:$E$180,F179)
=SMALL($F$177:$F$180,E179)
8- The Pic is attached called Q8:
The formula contained in the ORANGE cell (H178) is = ($A$177:$G$180, ( ,$F$177:$F$180,0),MATCH($A$177,$A$177: $F$177,0))
Hint: do not forget the $ when using absolute references.
9- What type of chart did we use to build a tornado chart?
- Column chart
Clustered bar chart
Tornado chart
Stacked bar chart
10- Rank the assumptions (drivers) according to sensitivity from lowest to highest.
- 1 Or 2 or 3 or 4 Discount rate +/-5%
- 1 Or 2 or 3 or 4 COGS +/-5%
- 1 Or 2 or 3 or 4 Revenue growth +/-5%
- 1 Or 2 or 3 or 4 EV/EBITDA Exit +/-5%