Thank you for the help.

I have set up a trial sheet, and fully understand your method. Unforunately
I don't think it will work for me. I have over 400 rows (different products)
with monthly forcasts, that are each calculated off four years previous data.
It would be too time consuming for the end user to have to manually adjust
row three after inputting each actual production number, for all 400
products, each month.

What is really frustrating is that the sheet worked last year. Somehow the
creator figured out how to get it to automatically recalculate and adjust the
forecasts in the same row that the actuals were being inputted.

If anyone has an idea of how to do that, please let me know... I am at my
wits end.

"Bill Kuunders" wrote:

So we do it in rows.....
Again, just to help explain, please set up a trial sheet

starting in A1............. month.. B1 etc .....Jan.. Feb.. Mar....Apr
A2 original forecast. B2 etc. 100. 80.....120....100
A3 adjust by month.C3... -20 20
A4 new forecast..............enter formula in C4
=IF(C50,"",C3+($N$2-$N$5)/(12-COUNT($B$5:$M$5))) and extend it accross to
M4
A5 actual.............enter value in B5,,C5 etc as time
passes

enter formulas to do the totals accross so in N2 it would be = sum(B2:M2)
N3 ....=sum(B3:M3)
N4.....=sum(B4:M4)
N5....=sum(B5:M5)
N6..........................=sum(N4:N5)
N6 should be the same N2
Now the real trick with this set up is to always keep N3 to zero
So when one month passes that had an adjustment in it, you would need to
delete the next adjustment as well
Or you enter a opposite adjustment into a different month.
This will give you the flexibility to adjust individual months.
You can with this also increase or decrease the original forecast in row 1.

Hope this works for you.

--
Greetings from New Zealand


"diaare" wrote in message
...
Okay...

so I have tried both of your suggestions. I am still struggling. I can
get
them to calculate correctly after I put in Jan. actual, but once I put in
Feb
Act the numbers that recalculate are wrong. Is there a way to have it
continue to recalulate based on the new actuals and forecasts each month?


Here is some background on my spreadsheet.

It was created by the person that held my job last year, he is no longer
with the company. It worked all last year...when they copied the file
over
for 2007 it quit working...here is why.

B D E F G N O
1 JAN FEB MAR APRIL MAY.....DEC TOTAL 2007
2 100 80 120 100 50 150 1200


The original creater had forumlas in row2 that recalculated the forecasts
as
the actuals changed. AND...the actuals were typed right into row2 as the
months progressed. SO....at the end of jan B2 would become an acual
production numberand the rest of the numbers in row2 would adjust (to keep
total production at 1200) based on that Jan actual.

SO...because the user was typing in acuals over the formulas that
recalculated the cells, at the end of 2006 when we copied it to make a
template for 2007, all of the formulas that were in row2 were gone
(replaced
by raw actual data).

It is my job to get it working again.

I would be okay with expanding the fields to 3 rows (one for FC, one for
Actuals, and one for formulas) but at this point I can't figure out how to
do
that.

Any ideas?

"Bill Kuunders" wrote:

My answer didn't show yesterday so here goes again

Diane

one way.........

to explain the formula you will need to set up a trial sheet as follows

cells A2 to A13 have the months Jan, Feb, Mar etc
cells B2 to B13 have the forecast numbers 100, 80 ,120 etc
cells C2 to C13 have the actual production numbers
cells D3 to D13 have a formula

=IF(C30,"",($B$15-$C$15)/(12-COUNT(C$2:C$13)))

where B15 has =sum(B2:B13) ...........1200
and C15 has =sum(C2:C13) ..........the total produced so far for the
year

enter the formula in D3 and extend down

you can still vary the forecast numbers per month by changing the
formula's
for those months with
the same amount

for instance June would get
=IF(C70,"",(($B$15-$C$15)/(12-COUNT(C$2:C$13))-30))
and July would then have to become
=IF(C70,"",(($B$15-$C$15)/(12-COUNT(C$2:C$13))+30))

or to be smarter you could create another column with expected variances
+30, -30, +50 ,-50 (as long they add up to zero)
and change the main formula to include any numbers in that column (E
column)
=IF(C30,"",($B$15-$C$15)/(12-COUNT(C$2:C$13)+E3))

so now you do not have to change formula's to do some manual adjustments
between months

have fun

--
Greetings from New Zealand

"diaare" wrote in message
...
I have a speadsheet that forecasts the number of parts built each month
for
2007. I would like to replace each months forecasts with actual
production
numbers, and then have the speadsheet adjust the remaining months
forecasted
numbers accordingly in order to keep the total annual forecast the
same.

EX:

FCJan = 100
FCFeb = 80
FCMarch = 120
FCApril = 100
FCMay = 100
FCJune = 70
FCJuly = 130
FCAug = 100
FCSept = 100
FCNov = 150
FCDec = 50
FC2007 = 1200

If actual Jan is 320 then each month thereafter would have to decrease
by
20
parts in order to compensate the overproduction in Jan.

Can I create formulas that will still work as I place actual data in
each
of
the months throughout the year.

Thanks
-Diane