Serious help needed for this project Can anyone help me with this

[Guest]

I have a w/s that compares forecasts and actuals.
Column A is Month: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov Dec.
Column B is the Planned Forecast in $ for each month.
Column C is the Actual Amt in $ that was spent for that month

I need 3 formulas that at the end of each column do the following:

1) YTD total for each - this formula would take the $ amount for each month
and * by the number of days in that month then divide by the actual number of
toal days YTD so far. We are a month behind so we would only use through July
not include August yet. In other words it is not to date (system date).

2) Forecast total - $ amt for each month * # of days in the month divided
by 365 days for the entire year.

3) Sum for the year (got this one figured out).

for Actual Totals:

Same as for forecast for YTD.

The second formula is my problem: for Actual $ amt MTD Forecast. Take the $
amt for each month *the # of days in that month PLUS include the $amt for
the forecast months that we have NOT gotten to yet this year. We are done
with July, so you would only include Planned/Forecast $ for Aug, Sept, Oct,
Nov, Dec taken from that column and sub for actual $ until we can replace it
with the info when it comes in. This total is under Actual/Forecast.

The last one for Actuals is sum and I got that one.

3) Total - got this one - it is the actual sum YTD.
EXAMPLE
Month Forecast Actual
Jan 91.21 51.75
Feb 82.39 109.31
Mar 91.21 159.24
Apr 88.27 69.86
May 91.21 51.92
Jun 89.69 53.76
Jul 92.73 51.20
Aug 92.73
Sep 89.73
Oct 93.25
Nov 90.26
Dec 104.22
_______________________________
YTD 89.64 77.86
Forecast 91.50 84.67
Total 1096.90 547.05

Jannie

 

[Guest]

Assuming your data is in columns A to C:

in Column D to calculate days in month:

in D2 and copy down:

=DAY(DATE(YEAR(A2),MONTH(A2)+1,0))

YTD Forecast:

=SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($B$2:$B$13)*($D$2:$D$13))/SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($D$2:$D$13))

YTD Actual:

=SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($C$2:$C$13)*($D$2:$D$13))/SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($D$2:$D$13))

Forecast/Plan

=SUMPRODUCT(($B$2:$B$13)*($D$2:$D$13))/SUM($D$2:$D$13)

Forecast Actual

=(SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($C$2:$C$13)*($D$2:$D$13))+SUMPRODUCT(--(MONTH($A$2:$A$13)>MONTH(TODAY())-1)*($B$2:$B$13)*($D$2:$D$13)))/SUM($D$2:$D$13)

HTH

 

[Guest]

I understand what you are saying here to do but I copy and paste the formulas
but I get a !Value error.

 

[Guest]

Is the data in column A actual dates, with cell format of "mmm": I have
assumed they are dates so "Jan" would be 01/01/2007, "Feb" 01/02/07 (UK
format!)

"Value" indicates a data mismatch and the dates most likely candidate. Did
the days of the month work OK in column D?

I tested my solution on your data and got identical results.

If you still have problems post w/book to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM

 

[Guest]

The dates are just like on the data I sent you. Jan, Feb, Mar and so on. Just
the month name and no actual date.
Jannie

 

[Guest]

I was able to getthe days of the month to work in Column D and the Forecast
formula =SUMPRODUCT(($B$2:$B$13)*($D$2:$D$13))/SUM(SUM($D$2:$D$13)) works
fine but all the others are giving me the #Value! error. I am assuming it has
something to do with the Month (Column A) and how the formula is referencing
it.
Jannie

 

[Guest]

I tried to send a copy of the w/s to your email but it won't go as the full
email address is not complete I guess, yes, I removed the NOSPM.
Jannie

 

[Guest]

e-mail address is correct

toppers<at>johntopley.fsnet.co.uk

change <at> to @