Last Twelve Months (LTM) financial calculation in excel

[tim nicebutdim]

Hi All,

I wonder if anyone can help me as I am a complete novice when it comes to Excel. I need help with a rolling LTM calculation.

I have a basic sheet with the following headers;
Year - what year we sold our service
Type - New business or renewal
Month signed - format of MMM-YY
Company Name
Salesperson Name
Value

From this table I am able to show;
New Business year on year in a pivot showing month by month sales per salesperson
Renewals as above
Total 2009 sales as above

I have now been asked to calculate a rolling LTM value and I am stumped. I guess I need a field showing todays date and then a formula that searches my data for todays date going back 12 months to give me my number. I need this formula to be dynamic and change every month and so does not need manual amendments.

Does this make sense and can anyone help? Any help or suggestions are greatly appreciated.

Many thanks,
Tim

 

[Domski]

Hi,

Is your Month Signed a date formatted as MMM-YY or text?

Dom

 

[tim nicebutdim]

Hi Dom,

It is in date format.

Cheers,
Tim

 

[Domski]

Try this:

Book1
	A	B	C	D	E	F
1	Year	Type	Month Signed	Company Name	Salesperson Name	Value
2	2005	New Business	Jul-05	Company A	Bob	1552
3	2005	New Business	Aug-05	Company B	Paul	1885
4	2006	New Business	Jan-06	Company D	Bob	1313
5	2006	Renewal	Jul-06	Company A	Bob	1441
6	2006	Renewal	Aug-06	Company B	Paul	1877
7	2006	New Business	Nov-06	Company F	Steve	1651
8	2006	New Business	Dec-06	Company C	Steve	1969
9	2006	Renewal	Dec-06	Company C	Steve	1513
10	2006	New Business	Dec-06	Company E	Paul	1653
11	2007	Renewal	Jan-07	Company D	Steve	1989
12	2007	Renewal	Jul-07	Company A	Bob	1865
13	2007	Renewal	Aug-07	Company B	Paul	1339
14	2008	New Business	Mar-08	Company G	Steve	1073
15	2008	Renewal	Jul-08	Company A	Bob	1506
16	2008	Renewal	Aug-08	Company B	Paul	1904
17	2008	New Business	Oct-08	Company I	Paul	1878
18	2008	New Business	Nov-08	Company H	Bob	1346
19	2008	Renewal	Jan-09	Company D	Bob	1628
20	2009	Renewal	Mar-09	Company G	Paul	1969
21	2009	New Business	Mar-09	Company K	Paul	1724
22	2009	Renewal	Mar-09	Company B	Steve	1405
23	2009	New Business	Apr-09	Company J	Bob	1102
24	2009	Renewal	Jun-09	Company H	Steve	1311
25
26
27					Rolling Year	15773
Sheet1

Dom

 

[Domski]

Might want to change formula to:

=SUMPRODUCT(($C$2:$C$24>=DATE(YEAR(TODAY())-1,MONTH(TODAY())+1,1))*($C$2:$C$24<=DATE(YEAR(TODAY()),MONTH(TODAY()),1))*($F$2:$F$24))

Or it will include Jul-08 in the total.

Dom

 

[tim nicebutdim]

Dom I cant thank you enough. Thanks for this and spending the time to help me with it.

The second formula works perfectly and you were right the first one included Jul 08 or 13 months.

Thanks again,
Tim