An Example of Sensitivity Analysis in Excel
- 13
- Add a Comment
I am including an Excel Workbook with 9 worksheets. These show step-by-step procedures on how to build a 1 and a 2 input “What If” analysis.
The main example is based on a very brief Income Statement. The requirement is to project the budgetary figures for 2007 using actual values from 2006 multiplied by one or more growth factors. Our objective is to see how the Net Profit Margin depends on the Sales Growth used. The formulation shows it for 4%. The What If analysis allows you to vary the Sales Growth from 2% to 12% in 0.5% increments and study the resulting Net Profit Margin.
The last worksheet shows how sensitivity analysis is applied to discounted cash flow whereby the NPV (Net Present Value) is varied against different discounting rates.
Sadly, Excel only allows us these limits: 1) One input variable but one or more output variables and 2) Two input variables but only one output variable. To define more variables, Excel provides us with the Scenario Manager. This is a powerful facility but needs a bit more work to complete. I will show you its intricacies in the next entry.
Download the What If Excel Workbook.
13 Comments
Craig
February 15th, 2007
at 7:11am
Cannot download the file and would like to. Thanks. Craig
M Lampell
February 16th, 2007
at 6:19am
The link to the Excel workbook does not work
Craig
February 16th, 2007
at 8:57am
Please email me the file. maximusdaytrader@yahoo.com. Thanks. Craig
enigma
February 21st, 2007
at 9:14am
The download link is not working. It is giving a permission rights error.
regards,
enigma
Jaffer Al Arab
June 2nd, 2007
at 3:13am
I want to learn from your site please
geetu
February 4th, 2008
at 11:59pm
good
Rama
April 15th, 2008
at 10:59pm
Thank you akram. I am using sensitivity analysis for my project and your excel spreadsheet was very helpful and made me understand how to apply sensitivity analysis using excel spread sheets. you are a very good teacher who must be very understanding and caring of others
Rama
April 15th, 2008
at 11:00pm
woops wrong email
Dickson
September 1st, 2008
at 2:04am
Very good and professional! :-)
Though there is an error on a graph (Net profit vs Sales Growth), the example is brilliant!
Akram
September 8th, 2008
at 9:48pm
The file is downloading well. The issue is that Excel macro security settings may be blocking the opening of the file (since there are macros in the file). You would need to reset the macro security settings in your Excel before opening the file. (High, will not let you open, Medium will give you a warning, Low will not question you!).
jared
October 23rd, 2008
at 6:43pm
Thank you so much this was a GREAT help!! I appreciate you taking the time to help others!
John
November 15th, 2008
at 12:35pm
Your example rocks! The MS excel help function was pathetic on this subject. Thanks very much!
Shafi
March 17th, 2009
at 2:20am
Its a great effort, so informative, keep it up Najjar!! Thanks