Using Choose function, Sensitivity Analysis, and Goal Seek for Financial Modeling
Financial modeling
Choose Function
Choose function lets you have an index cell where you put in 1,2,3 and allows you to choose a scenario after which all the numbers change according the number of the scenario.
So you if you put under Revenue "=choose(index cell, optimistic case, base case, worst case)"
If you write 1 in the index cell, then the revenue is going to choose the optimistic case.
How to do a sensitivy analysis
First on the top left, you link it to the "enterprise value".
On the top row, you write the possible values for the WACC.
On the left column, you write the possible values for the growth rate.
After that, you click on the whole table, and go to "data" and do a "data table"
Then you do an input on the row "WACC" and the column "g"
After you click "OK," it does a sensitivity analysis for you.
GoalSeek Function
In this scenario, what is the variable cost that you need to have in order to have 17 as the gross margin? You would need to use the function "Goal seek" under data analysis.
Choose Function
Choose function lets you have an index cell where you put in 1,2,3 and allows you to choose a scenario after which all the numbers change according the number of the scenario.
So you if you put under Revenue "=choose(index cell, optimistic case, base case, worst case)"
If you write 1 in the index cell, then the revenue is going to choose the optimistic case.
How to do a sensitivy analysis
First on the top left, you link it to the "enterprise value".
On the top row, you write the possible values for the WACC.
On the left column, you write the possible values for the growth rate.
After that, you click on the whole table, and go to "data" and do a "data table"
Then you do an input on the row "WACC" and the column "g"
After you click "OK," it does a sensitivity analysis for you.
GoalSeek Function
In this scenario, what is the variable cost that you need to have in order to have 17 as the gross margin? You would need to use the function "Goal seek" under data analysis.
Comments
Post a Comment