Goal Seek Macro for Multiple Cells

Question

I have forecast, sales and forecast accuracy columns.  I want to calculate the revised forecast if I increase forecast accuracy by 20%.  Is there a way to use Goal Seek to calculate the revised forecast without running it for each line of data?  There are over 900 lines of data.  My excel formula works when forecast is greater than sales but when the bias is negative (sales higher than fcst) I need to use Goal Seek to calculate the revised forecast.

Answer 1

We would need to see the layout of the work to give a meaningful answer.

Have you thought of using Solver?

best wishes

Answer 2

I'm open to any suggestions.  Yes, I tried solver but can't figure out how to use multiple goals.  

Below is a picture of the layout.  Fcst and Sales are given.  Accuracy formula is below.  If we improve accuracy by 20%, what would the forecast be?  I used a formula to calculate Revised Fcst but when I use Goal Seek the first line correctly changes to 52.4. 

What do you recommend?  Is there a way to use Goal Seek for all the products (this is an abridged list) or can you suggest a better process or formula to calculate Revised Fcst?

  

Oct Accuracy formula  =IF(Oct Fcst=Oct Sales,1,IF(Oct Fcst=0,0,(Oct Fcst-ABS(Oct Fcst-Oct Sales))/Oct Fcst))

Revised Fcst =IF(Oct Accuracy + 20pct=Oct Accuracy, Oct Accuracy, ABS(Oct Sales/Oct Accuracy + 20pct))

Answer 3

Please visit my website (look at link at bottom of this message http://people....) to get my email address

best wishes

Answer 4

Unfortunately, Bernard Liengme advised me that he is no longer available to help me with my issue so if anyone has a solution I would appreciate the help.

Answer 5

> ...  I want to calculate the revised forecast if I increase forecast accuracy by 20%. 

I'm a little confused because it seems you already did that calculation above.

Goal Seek requires one to change an input cell to have a Target cell reach a desired value.

You do not mention what cell you want to change, and what the target value should be.

However, it seems you have a function, so that would probably be the better choice.

IF(Oct Accuracy + 20pct=Oct Accuracy, ...

I may be missing something, but the above looks like it will never be true.

Did you really mean this:

> ... ABS(Oct Sales/Oct Accuracy + 20pct)

or this based on the description:

ABS(Oct Sales/ (Oct Accuracy + 20pct)   )

> ... when I use Goal Seek the first line correctly changes to 52.4. 

We don't know what you changed, or what the target was.

I don't see 52.4 in the first line.

> ... Oct Accuracy + 20pct

I was a little confused at first because I thought you were adding 20%.  I "assume" this is a range name constant with a value of just 0.2.

Since it appears to be a value, and not a percentage, I might suggest just using:

Oct Accuracy + 0.2