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

Hi,

Try This:

A B C D E
1 Mounth FC
2 jan 100
3 feb 80
4 mar 120
5 apr 100
6 may 100
7 jun 70
8 jul 130
9 aug 100
10 sep 100
11 oct 100
12 nov 150
13 dec 50
14 Total 1200

in the cell D2 enter: =C2

in the cell D3 enter:
=IF(C3<0,C3,IF(SUM(INDIRECT("$B$2:B"&E3))=SUM(IN DIRECT("$C$2:C"&E3)),B3,ROUND(B3+(SUM(INDIRECT("$B $2:B"&E3))-SUM(INDIRECT("$C$2:C"&E3)))/(12-$C$15),0)))

in the cell E3 enter: =IF(C2<0,ROW()-1,E2)

in the cell C15 enter: =COUNT(C2:C13)

copy and drag formula in cell D3 to D13

copy and drag formula in cell E3 to E13

if you enter actual amounts in column C it adjust the remaining next months.

hope this will work for you.

Thanks,


--
Farhad Hodjat


"diaare" wrote:

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

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

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

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

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

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

Diane.

It can be done.

I can give you more solutions but it would be helpfull if you can send me a
copy of last years file, so that i can see where the numbers come from and
whether you have constants to explain the difference in June 70 and July 130
and Nov 150 Dec 50
while most of the other months are 100.

Bill
billdotkuundersatxtradotcodotnz


"diaare" wrote in message
...
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

Diane
So I had some more time to ponder.....

Go back to the trial sheet
delete rows 2,3, 4, 5 and 6

in row 2 enter the workdays per month i.e. 20, 22, 21, 24, etc
so that each month will get a proportion of the workload depending on
workdays available.

enter a value in B3 (the actual production for Jan) B4, B5, B6,etc
enter yearly totals in cells N3,N4, etc

enter the following formula in cell C3 ( for Feb in the forecast line)

=(($N3-SUM($B3:B3))*(C$2/SUM(C$2:$M$2)))
extend it across
end extend the whole row down for other products


N column only has values, no sums just the total for the year by product
Row 2 with the work days per month stays as a constant reference for all
products.
format all cells as numbers, 0 decimals
Let me know how you got on.

Greetings from New Zealand
Bill


"diaare" wrote
in message ...
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

Bill

Thank you so much for your help on this. I knew I was close...but just
couldn't get away from those darn circular references.

Thanks again,

Diane

"diaare" wrote:

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

You're welcom,
Thanks for the feed back.
Bill
"diaare" wrote in message
...
Bill

Thank you so much for your help on this. I knew I was close...but just
couldn't get away from those darn circular references.

Thanks again,

Diane

"diaare" wrote:

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