7/9/2021 Assessment Review - Corporate Finance Institute

Scenario & Sensitivity Analysis in Excel

Below is a scored review of your assessment. All questions are shown.

Correct Answer

Partially Correct

Incorrect Answer

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 1/16
7/9/2021 Assessment Review - Corporate Finance Institute

1 If you want to test how an increase in percentage of cost of good sold affects the contribution margin,
you would use ________.

Your Answer
Scenario analysis

Correct Answer
Sensitivity analysis

Explanation
Scenario analysis tests changes in multiple assumptions, like what will happen if another "story" happens,
while sensitivity analysis can only test one assumption at a time. Although scenario analysis can be used to
test one variable, sensitivity analysis is much easier and provides multiple ranges out output depending on
changes to the assumption.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 2/16
7/9/2021 Assessment Review - Corporate Finance Institute

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?

Your Answer
Data Validation

Correct Answer
Data Validation

Explanation
Data Validation is used to create a switch for the live case which the user wants to test to see how changes
in multiple inputs impact the output.

Data Table, Tornado Chart and the INDEX MATCH MATCH function are used to perform a sensitivity analysis.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 3/16
7/9/2021 Assessment Review - Corporate Finance Institute

3 You should perform sensitivity analysis when:

Your Answer
You need to determine which assumptions matter the most

Correct Answer
You need to determine which assumptions matter the most

Explanation
None.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 4/16
7/9/2021 Assessment Review - Corporate Finance Institute

The formula contained in the yellow highlighted cell (I39) is =   1.   (   2.   , 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.)

Your Answer
1. CHOOSE

2. $I$4
https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 5/16
7/9/2021 Assessment Review - Corporate Finance Institute

Correct Answer
1. CHOOSE

2. $I$4

Explanation
The formula is =CHOOSE($I$4,I9,I24)

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 6/16
7/9/2021 Assessment Review - Corporate Finance Institute

5 What is the share price when the discount rate is 13% and revenue growth is 15%?

Your Answer
36.70

Correct Answer
36.70

Explanation
Please review the correct answers in the attached file:

Scenario and Sensitivity Full Model Answers.xlsx

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 7/16
7/9/2021 Assessment Review - Corporate Finance Institute

6 What is the share price when COGS increases by 5%?

Your Answer
29.29

Correct Answer
29.29

Explanation
Please see Question 5 for the completed model.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 8/16
7/9/2021 Assessment Review - Corporate Finance Institute

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. 

Your Answer
=SMALL($F$177:$F$180,E179)

Correct Answer
=SMALL($F$177:$F$180,E179)

Explanation
None.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 9/16
7/9/2021 Assessment Review - Corporate Finance Institute

The formula contained in the ORANGE cell (H178) is =   1.   ($A$177:$G$180,   2.   (   3.  
,$F$177:$F$180,0),MATCH($A$177,$A$177: $F$177,0))

Hint: do not forget the $ when using absolute references.

Your Answer
1. INDEX

2. MATCH

3. G178

Correct Answer
1. INDEX

2. MATCH

3. G178

Explanation
Please see Question 5 for completed model.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 10/16
7/9/2021 Assessment Review - Corporate Finance Institute

9 What type of chart did we use to build a tornado chart?

Your Answer
Stacked bar chart

Correct Answer
Stacked bar chart

Explanation
The tornado chart is built with a stacked bar chart. You can experiment with the Excel to see what each type
of chart looks like with the input data.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 11/16
7/9/2021 Assessment Review - Corporate Finance Institute

10 Rank the assumptions (drivers) according to sensitivity from lowest to highest.

COGS +/-5%

Your Answer
3

Correct Answer
3

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 12/16
7/9/2021 Assessment Review - Corporate Finance Institute

Revenue growth +/-5%

Your Answer
4

Correct Answer
4

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 13/16
7/9/2021 Assessment Review - Corporate Finance Institute

EV/EBITDA Exit +/-5%

Your Answer
2

Correct Answer
2

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 14/16
7/9/2021 Assessment Review - Corporate Finance Institute

Discount rate +/-5%

Your Answer
1

Correct Answer
1

Explanation
Please see Question 5 for completed model.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 15/16
7/9/2021 Assessment Review - Corporate Finance Institute

Page 1 of 1

Summary

Return to Course

Software by

Version 10.2

Privacy Policy. Assessment content is copyright 2021, Corporate Finance Institute.

https://cfi.onlinetests.app/Assess.aspx?guid=7E7E385A69464519BE0D348D9F667072&a=R1 16/16