Sensitivity analysis for the Northern Museum The model Museum. xlsx is a prototype planning model for a small regional museum. The model projects revenues, costs, operating surplus/deficit and the endowment out to 2014, based on 2003 and 2004 actuals and assumed growth rates. The most important outcome measure is the level of the endowment in 2014, cell N39.
Copy this model to your computer and use it to answer the following questions. You may assume this model is logically correct. Write in the answers here and also document the answer to each of the questions on a separate worksheet.
Note: each question should be answered independently of the others, starting with the base case.
a. The model is based on 16 parameters in column D. Which four have the biggest impact on the outcome? (To answer this question, you should assume that each parameter can vary by plus or minus 10% of its base case value.) List them here in order of importance.
Parameter
1
2
3
4
b. Assume that all 10 growth rates for revenues can vary by +/- 20%, the five cost parameters by +/- 10%, and the interest rate parameter by 15%. Which four have the biggest impact on the outcome? List them here in order of importance.
Parameter
1
2
3
4
c. What interest rate (cell D38) would the Museum have to earn to ensure that the endowment in 2014 was equal to its value at the end of 2003?
d. One faction on the Museum Board is concerned about the vulnerability of the endowment to the interest rate. Sketch a graph that will show how the endowment value in 2014 changes as the interest rate varies from a low of 0% to a high of 15%. For grading purposes, clearly label the numerical values at 0% and 15%.
e. Another faction of the Board is interested in ensuring that the endowment in 2014 exceeds $3 million. Create (and record below) a table which shows the combinations of the growth rate in municipal grants (cell D11) and the interest rate (cell D38 that will bring this result about? (Each parameter can vary between 0 and 15%).