Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
I have the following Data Inputs
Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
I'd recommend doing this as a table with the column headings as normal +10%
-10% or whatever else and then just duplicate the columns, alternatively you could have each of the scenarios as a different tab at the bottom of the page if you didn't want to see the whole table "David" wrote: I have the following Data Inputs Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
Would this not be far too much of work as I would have to repeat the whole
excercise again, it has about 15 inputs with about 30 outputs, I would like to use the what if analysis in the data tab, click on the scenario manger, tell it which cells I want to change and link that to a formulae and then choose the result cells which would be the performance indicators. This spread sheet is going to be used as a finacial analysis tool, so the users will only need to enter their data in the data sheet and everything else will be automated including the scenario analysis, is it possible to automate scenario anlysis "Monkey-See, Monkey-Do" wrote: I'd recommend doing this as a table with the column headings as normal +10% -10% or whatever else and then just duplicate the columns, alternatively you could have each of the scenarios as a different tab at the bottom of the page if you didn't want to see the whole table "David" wrote: I have the following Data Inputs Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
It would take about 2 mintues of work!?
The user would be able to just insert the root data and the other data would be calculated and populated... You might be able to do it with "what if" but I've never used that. As far as I'm aware you can't enter cell identifiers into the changing values fields so it's not much use for formulas I suggest getting your main sheet working and then copying the whole sheet for the number of times you have scenarios. Then change the values you've identified in column C (link them to the front sheet e.g. =Sheet1!C1*1.1 for plus 10%) Rename your tabs to match the scenario type and viola. All you have to do is change the values on the front sheet and all the other scenarios will automatically calculate. You might even copy those values back to the first sheet in a summary table and then you don't even have to change tabs to see the info... "David" wrote: Would this not be far too much of work as I would have to repeat the whole excercise again, it has about 15 inputs with about 30 outputs, I would like to use the what if analysis in the data tab, click on the scenario manger, tell it which cells I want to change and link that to a formulae and then choose the result cells which would be the performance indicators. This spread sheet is going to be used as a finacial analysis tool, so the users will only need to enter their data in the data sheet and everything else will be automated including the scenario analysis, is it possible to automate scenario anlysis "Monkey-See, Monkey-Do" wrote: I'd recommend doing this as a table with the column headings as normal +10% -10% or whatever else and then just duplicate the columns, alternatively you could have each of the scenarios as a different tab at the bottom of the page if you didn't want to see the whole table "David" wrote: I have the following Data Inputs Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
Here is a link that will help you create scenarios that will automatically
show. http://www.contextures.com/xlScenario03.html "David" wrote: Would this not be far too much of work as I would have to repeat the whole excercise again, it has about 15 inputs with about 30 outputs, I would like to use the what if analysis in the data tab, click on the scenario manger, tell it which cells I want to change and link that to a formulae and then choose the result cells which would be the performance indicators. This spread sheet is going to be used as a finacial analysis tool, so the users will only need to enter their data in the data sheet and everything else will be automated including the scenario analysis, is it possible to automate scenario anlysis "Monkey-See, Monkey-Do" wrote: I'd recommend doing this as a table with the column headings as normal +10% -10% or whatever else and then just duplicate the columns, alternatively you could have each of the scenarios as a different tab at the bottom of the page if you didn't want to see the whole table "David" wrote: I have the following Data Inputs Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
David -
Another popular way to investigate sensitivity is to perform single-factor sensitivity analysis and show the results in a tornado chart or spider chart. Examples of Excel add-ins for automating this process are available from www.treeplan.com (SensIt) and www.palisade.com (TopRank). These add-ins handle sensitivity analysis of a spreadsheet model with multiple inputs and a single output. Of course, you can run the add-in several times, once for each output of interest. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "David" wrote in message ... I have the following Data Inputs Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
None of the suggestions have helped unfortumately, is it possible for me to
email my spreadsheet so you could see exactly what is required "Mike Middleton" wrote: David - Another popular way to investigate sensitivity is to perform single-factor sensitivity analysis and show the results in a tornado chart or spider chart. Examples of Excel add-ins for automating this process are available from www.treeplan.com (SensIt) and www.palisade.com (TopRank). These add-ins handle sensitivity analysis of a spreadsheet model with multiple inputs and a single output. Of course, you can run the add-in several times, once for each output of interest. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "David" wrote in message ... I have the following Data Inputs Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
What If Analysis in Excel 2007
David -
The automation you seem to be seeking will likely require a custom macro, e.g., VBA. Based on the feedback you have received so far, I suggest you rethink carefully what you want, redescribe your situation, and then post in the microsoft.public.excel.programming newsgroup (assuming you want to consider a VBA solution). - Mike "David" wrote in message ... None of the suggestions have helped unfortumately, is it possible for me to email my spreadsheet so you could see exactly what is required "Mike Middleton" wrote: David - Another popular way to investigate sensitivity is to perform single-factor sensitivity analysis and show the results in a tornado chart or spider chart. Examples of Excel add-ins for automating this process are available from www.treeplan.com (SensIt) and www.palisade.com (TopRank). These add-ins handle sensitivity analysis of a spreadsheet model with multiple inputs and a single output. Of course, you can run the add-in several times, once for each output of interest. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "David" wrote in message ... I have the following Data Inputs Capital Expenditure C1 Cost of Borrowing C2 Income C3 Expenses C4 Discount Rate C5 These inputs are used to generate a project discounted cash flow and a amortization table I then calculate perormance indicators such as NPV Profitability Index Breakeven ratio I the want to create three scenarios 1) Capital Expenditure increase by 10% 2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would like to see the effect it has on all the performance indicators. Q1) When you click on the changing cells option in the what if analysis can you use a formulae to say C2*-0.1 2) Is it possible for your scenario results to change automatically should you change the data inputs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I do sensitivity analysis in excel 2007? | Excel Worksheet Functions | |||
Data Analysis with Excel 2007 | New Users to Excel | |||
regression analysis in excel 2007? | Excel Discussion (Misc queries) | |||
data analysis addin in Excel 2007 | Excel Worksheet Functions | |||
Excel 2007 Analysis Toolpak | Excel Discussion (Misc queries) |