Assistance with Budget expenditure forecasting in excel

[camacint]

Hi, I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).


I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2
>D2),(B2-C2)*F6+F2,IF((B2<D2),(D2-C2)*F6+F2,IF((F2=0),D2*F6)))


<D2),(D2-C2)*F6+F2,IF((F2=0),D2*F6))) p <>A B C D E F G
<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=510 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 36.75pt; mso-height-source: userset" height=49><TD class=xl22 id=tdContent style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 36.75pt; BACKGROUND-COLOR: transparent" width=83 height=49>Month</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71>Number of applications</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=69>Actual Number of participants</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Projected monthly starts</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=68>Short course participants, <12 weeks</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Actual Budget Commitment YTD</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71>Forecasted 08/09 Budget Commitment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>April</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>12</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num>25</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="144235">144,235</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="144235">326,325</TD></TR></TBODY></TABLE>

F6 = 13,000 (average)

What I want this to do, is automatically use F2 at month's end. Right now I have to manually enter a value >0 in cell G70 on 'worksheet A'. I've tried to use dates in the argument for month's end - "if today is greater than the last day of last month, use F2", but can't figure it out. Thanks in advance for your help!

Craig

 

[camacint]

Hi, formula is cut-off in the field above - here is the remainder.


((B2<D12),(D12-C12)*F6+F12,IF((F12=0),D12*F6)))) p <> formula continues to be cut-off in this field - I don't know what to do...I can PM the entire formula (I hope...)

I am looking for a formula using dates in place of: ('Worksheet A'!G70>0) - or if you can offer an entire formula more concise than what I've developed - please do. I really don't know much about excel and am entirely self-taught.

Thanks,

Craig

 

[bButler]

"forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months)"

So, if you want to forecast the future months based on actuals from the month's past, are you looking at the past full 12 months? or just the months relevant to that fiscal year?

 

[camacint]

Hi,

Months relevant to this fiscal year.

We will use our best 'guess' for forecasting future months based on suspected activity for those months. "projected monthly starts" is the only column that requires manual input - the remaining cells are populated from other worksheets as we go through the current fiscal year.

The average (F6) is floating and changes based on actuals from previous months in this fiscal year.

We don't want to use actuals from the previous fiscal year as our costs and activity continue to increase each fiscal year.

We will start with a 'blank' forecast each fiscal year, without actuals from the previous year (except carry-over committment).

Our forecast for the entire fiscal year is done at the conclusion of each month.

Thanks,

Craig

 

[bButler]

And does WorksheetA!G70 represent the month of the fiscal year? Shouldn't it be >1, because the first month would be actual?

If WorksheetA!G70 does represent the month of the fiscal year, why don't you assign a value based on =Now(), so if your fiscal year begins in March, =month(now())-2 would give you a 4 for June, which is the 4th month of the fiscal year. That would eliminate your having to enter it manually.

Having established that number, you could subtract the 4 from 12, multiply that number (8) times your average in F6 (which I think is what you're doing now) and then add it to your actuals.

 

[camacint]

Hi, thanks - so right now that we're in June, and I want the formula in April (and May) to recognize automatically that April (and May) has ended, regardless that it's June, July, August, or January 2009 - I don't need something indicating that 'today's date' > 'yesterday's date' ?

New fiscal year begins every April.

Cheers,

Craig

 

[bButler]

No . . . I would only focus on today's date to determine how far along you are on the 1 through 12 scale of the fiscal year. Today will ALWAYS be greater than yesterday.

This would give you the current month of your fiscal year, even if it's January, February or March:

=IF(MONTH(NOW())>4,MONTH(NOW())-3,MONTH(NOW())+9)

 

[dcamos]

=TEXT(NOW(),"m")-1

That will give you the last month that has ended as 5 if today is in June. Maybe a little more info would help make the formula what youare looking for.

 

[bButler]

That's close, except the fiscal year began in April . . . so he wants Month 1 to begin in April. Even the formula I posted isn't quite right, because he needs to know how many months of the fiscal year have transpired. I think this is closer, because it will give him 2 (April and May) when today's date is June, or 10 if today's date is January.

=IF(MONTH(NOW())>4,MONTH(NOW())-4,MONTH(NOW())+9)

Also, I'm not sure why you are averaging, since it is probably skewing the forecast. This may be over-simplified, since I'm not considering the monthly starts or participation, but a more accurate forecast would be:

=sum([actual expecditures]/[number generated above])*12

 

[camacint]

Hi, I believe I have you both going down a different path then mine. This formula that I need assistance with is in column G for each month - it's used to filter-in actuals for each month - from unknown - unknown/actual - actual only (month's end). Each month is forecasted and all of the values for each month are added together to give us our forecast.

<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=510 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 36.75pt; mso-height-source: userset" height=49><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 36.75pt; BACKGROUND-COLOR: transparent" width=83 height=49>Month</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71>Number of applications</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=69>Actual Number of participants</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Projected monthly starts</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=68>Short course participants, <12 weeks</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Actual Budget Commitment YTD</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71>Forecasted 08/09 Budget Commitment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>April</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>12</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num>25</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num>25</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="144235">144,235</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="144235">144,235</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>May</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>22</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>20</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num>25</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff"></TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="227397">227,397</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="227397">227,397</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>June</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>14</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>8</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num>25</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff"></TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="83582">83,582</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="282008.61538461538">282,009</TD></TR></TBODY></TABLE>

I need this to automatically recognize with the formula used in Column G for April and May that those months have ended based on todays date and to filter out the guesswork and use only the actuals in column F.

I am expecting to modify the formula for each month so G2 will identify when April has ended, G3 will identify when May has ended, and so on... and filter in the actuals (F) for the value in G - you'll notice that F and G are the same value for April and May, but not for June. June is still using the combination of 'best guess' and current actual for the value in G

The entire formula that I have for each month in column G was cut off for some reason in this forum - sorry.

Thanks!