Solver, Goal Seek, Multi Goal seek or VBA

[diegomatador]

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:

'Inputs'!$AI$45:$AT$45
'Inputs'!$AI$53:$AT$53​

Target Cell

'Funding'!$AY$136​

Thanks for your assistance in advance.

Diego

 

[shg]

You might put a workbook on a file sharing site (box.net) and psot a link, and explain clearly what you want in that context.

 

[diegomatador]

Hi,

I have attached an example worksheet to highlight the problem I am having - should be available on the following box.net link - https://app.box.com/s/j4rpq80qvl53wxvsd6rd .

The Inputs page has all the inputs to the business. The cells highlighted in Green (C3:N4) represent the variable cells that I need adjusted by a constant factor to drive my desired result in the target cell. These are the price inputs and drive revenue.

The Operations page works out the P&L of the business, a summary of the Revenue, Costs, Tax and Interest Expense.

The Overdraft Account page works keeps an ongoing record of the overdraft facility. Cell N7, marked in red, is the Target Cell. In this example, N7 is the cell that I would like to be able to adjust my variable cells and return my desired result in this cell - ie target debt balance at the End of December of $40,000.

Basically, I would like to find a quick and simple solution to manipulate the Variable Cells by a constant factor to drive a target result in the Target Cell N7 rather than having to trial and error.

Thanks again

 

[shg]

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

1​

238​

316​

190​

358​

256​

124​

288​

190​

58​

366​

320​

238​

1.183​

O1 (Changing cell): Input​

2​

202​

204​

120​

240​

212​

90​

248​

170​

56​

352​

308​

190​

40,000​

Target value​

3​

​

​

​

​

​

​

​

​

​

​

​

​

40,000​

O3: ='Overdraft Account'!N7​

4​

Jan-14​

Feb-14​

Mar-14​

Apr-14​

May-14​

Jun-14​

Jul-14​

Aug-14​

Sep-14​

Oct-14​

Nov-14​

Dec-14​

0​

O4 (Solve to set this to 0): =O3-O2​

5​

281.50​

373.76​

224.73​

423.43​

302.79​

146.66​

340.64​

224.73​

68.60​

432.89​

378.49​

281.50​

​

C5 and across and down: =$O$1*C1​

6​

238.92​

241.29​

141.93​

283.87​

250.75​

106.45​

293.33​

201.07​

66.24​

416.34​

364.29​

224.73​

​

​

I added a few rows above your data and copied the input cells. The input cells (now in row 5 & 6) are the original inputs times the multiplier value in O1 that is set by Solver to make O4 (the difference between the target value in O2 and the value in O3) zero.

 

[shg]

...