diegomatador
New Member
- Joined
- Aug 27, 2013
- Messages
- 2
Hi All,
I have a financial model that I use to report and forecast past and future results. It is set up over multiple pages, starting with an inputs page where all model inputs are input and an actuals page for all actual results to be input as they occur. Other pages provide information calculated based on the inputs and actuals pages, ie funding, summary, operations, P&L.
What I am trying to do is to find what change is required to the monthly 2014 Price inputs (for each month there is 1 Peak and 1 Off Peak value for a total of 24 price input values through the year) on the Inputs worksheet, based on a desired outcome for a single specific cell in a different worksheet, ie to forecast a target Debt Level, Cash Balance or Net Income, calculated by the model and determined [through a number of steps] by the input price levels I am looking to adjust. In each scenario I am only looking to adjust the inputs so that 1 target cell shows the desired value (ie change inputs to hit desired Profit), not multiple target cells. I am happy for each of the variable cells to be adjusted by a single change factor [ie the change is constant across all 24 values].
The challenges are:
- The variable cells and the target cells are on different worksheets so I have not been able to use the Solver Function in Excel as it requires all cells to be on the same page
- There is more than one input cell so Goal Seek is not an option
- From what I have read the Multi-Goal Seek function may not be suitable as it performs the goal seek function by looping individual goal seeks, where-as this would need to calculate the adjustment to each variable cell at the same time to hit the targetted value
- The model I am using is a 35mb file that took 8 months to build professionally and ideally I would like to avoid rebuilding worksheets/workbooks so that I can have more flexibility
- It is possible to manually adjust the inputs cells and use trial and error to hit the desired level in the target cell, but this is crude and can be time consuming adn I would like to find an efficient formula, code or function based solution.
Happy to answer any questions that may have in case I haven't explained the situation completely or entertain solutions that go part of the way there.
If it will help you in writing VBA code or formulas or similar, here are the cells that I am currently trying to manipulate to reach a target value in the target cell:
Input Cells:
Target Cell
Thanks for your assistance in advance.
Diego
I have a financial model that I use to report and forecast past and future results. It is set up over multiple pages, starting with an inputs page where all model inputs are input and an actuals page for all actual results to be input as they occur. Other pages provide information calculated based on the inputs and actuals pages, ie funding, summary, operations, P&L.
What I am trying to do is to find what change is required to the monthly 2014 Price inputs (for each month there is 1 Peak and 1 Off Peak value for a total of 24 price input values through the year) on the Inputs worksheet, based on a desired outcome for a single specific cell in a different worksheet, ie to forecast a target Debt Level, Cash Balance or Net Income, calculated by the model and determined [through a number of steps] by the input price levels I am looking to adjust. In each scenario I am only looking to adjust the inputs so that 1 target cell shows the desired value (ie change inputs to hit desired Profit), not multiple target cells. I am happy for each of the variable cells to be adjusted by a single change factor [ie the change is constant across all 24 values].
The challenges are:
- The variable cells and the target cells are on different worksheets so I have not been able to use the Solver Function in Excel as it requires all cells to be on the same page
- There is more than one input cell so Goal Seek is not an option
- From what I have read the Multi-Goal Seek function may not be suitable as it performs the goal seek function by looping individual goal seeks, where-as this would need to calculate the adjustment to each variable cell at the same time to hit the targetted value
- The model I am using is a 35mb file that took 8 months to build professionally and ideally I would like to avoid rebuilding worksheets/workbooks so that I can have more flexibility
- It is possible to manually adjust the inputs cells and use trial and error to hit the desired level in the target cell, but this is crude and can be time consuming adn I would like to find an efficient formula, code or function based solution.
Happy to answer any questions that may have in case I haven't explained the situation completely or entertain solutions that go part of the way there.
If it will help you in writing VBA code or formulas or similar, here are the cells that I am currently trying to manipulate to reach a target value in the target cell:
Input Cells:
'Inputs'!$AI$45:$AT$45
'Inputs'!$AI$53:$AT$53
'Inputs'!$AI$53:$AT$53
Target Cell
'Funding'!$AY$136
Thanks for your assistance in advance.
Diego
