Recalculating Monthly Production Forecasts

[Guest]

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

 

[Guest]

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(INDIRECT("$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,

 

[Bill Kuunders]

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(C3>0,"",($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(C7>0,"",(($B$15-$C$15)/(12-COUNT(C$2:C$13))-30))
and July would then have to become
=IF(C7>0,"",(($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(C3>0,"",($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

 

[Bill Kuunders]

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(C3>0,"",($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(C7>0,"",(($B$15-$C$15)/(12-COUNT(C$2:C$13))-30))
and July would then have to become
=IF(C7>0,"",(($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(C3>0,"",($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

 

[Guest]

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]

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(C5>0,"",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.

 

[Guest]

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]

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

 

[Bill Kuunders]

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

 

[Guest]

Bill

Thanks for your offer to stick with me. I know it is possible, I am just
missing something. I think you are on the right track with the days in the
month...they are already in the original sheet.

To quickly answer one of your questions, the forecasts numbers I gave you
were bogus...I was just trying to make it easy by using round numbers. The
true numbers are actually calculated using the the past 4 years actual
production numbers (turned into a percent then multiplied by the years total
production goal). I am fairly certain that all of that is working
correctly...and also fairly certain that I understand the formulas being used
to correct it if it wasn't.

I am going to mess with your latest suggestion this morning, but if you
really have the time, and don't mind helping me, I will send you a copy of
the original sheet. Just let me know where to send it.

Thanks for all your help,

Diane

 

[Guest]

So I think you've figured it out!!! I have to play with it a little more so
that I understand what you've done, but when I put your forumula in my
original spreadsheet the original forecast numbers were only off by one in
each month (yet the total annual production numbers were the same (a
difference that I can live with).

I am going to play with it for a while, and once I understand your process,
I will give you an update.

Thank you!

Diane

 

[Guest]

Okay so now I have had time to play with it. Here is a summary of what I
know...

In the original spreadsheet B2, B3, B4... etc were calculated fields that
determained the monthly forecast for that product, based off of 4 years past
data and the FC annual production for that part. I have been trying to make
my new formula for that line include those original formulas...so that, for
example, if historically we know that in Dec our production is always low
(even though there are 20 working days) we could compensate.

Your formula does not take into acount those forecasts based on historical
data...but in the product lines I tried it in there was so little variation
that it was okay...actually, much better than having nothing that worked at
all. For now it will do...

But, it will continue to nag me if I don't figure out how it was done
originally. So, if you would like to continue to think about it, your ideas
are much appreciated.

Thanks again,

Diane

 

[Bill Kuunders]

Diane,

My email address if you still want to send a file.
billdotkuundersatxtradotcodotnz

just change the dots to a . and the at to an @

Bill

 

[Guest]

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

 

[Bill Kuunders]

You're welcom,
Thanks for the feed back.
Bill

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