Dear Sir Comfy ,
Thank you very much for your quick reply. Your formula is working, how i may have missed some other important factors.
I am pasting the range of dates in which the adjusted savings is supposed to have.
[TABLE="width: 377"]
<tbody>[TR]
[TD]Range[/TD]
[TD][/TD]
[TD]# of Months[/TD]
[TD]Annual[/TD]
[TD]P&L[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD="align: right"]12/31/2013[/TD]
[TD] 1[/TD]
[TD] 120,000[/TD]
[TD] 10,000[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1/31/2014[/TD]
[TD] 2[/TD]
[TD] 120,000[/TD]
[TD] 20,000[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]2/28/2014[/TD]
[TD] 3[/TD]
[TD] 120,000[/TD]
[TD] 30,000[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]3/31/2014[/TD]
[TD] 4[/TD]
[TD] 120,000[/TD]
[TD] 40,000[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2014[/TD]
[TD="align: right"]4/30/2014[/TD]
[TD] 5[/TD]
[TD] 120,000[/TD]
[TD] 50,000[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2014[/TD]
[TD="align: right"]5/31/2014[/TD]
[TD] 6[/TD]
[TD] 120,000[/TD]
[TD] 60,000[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014[/TD]
[TD="align: right"]6/30/2014[/TD]
[TD] 7[/TD]
[TD] 120,000[/TD]
[TD] 70,000[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2014[/TD]
[TD="align: right"]7/31/2014[/TD]
[TD] 8[/TD]
[TD] 120,000[/TD]
[TD] 80,000[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]8/31/2014[/TD]
[TD] 9[/TD]
[TD] 120,000[/TD]
[TD] 90,000[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2014[/TD]
[TD="align: right"]9/30/2014[/TD]
[TD] 10[/TD]
[TD] 120,000[/TD]
[TD] 100,000[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2014[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD] 11[/TD]
[TD] 120,000[/TD]
[TD] 110,000[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]11/30/2014[/TD]
[TD] 12[/TD]
[TD] 120,000[/TD]
[TD] 120,000[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2014[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD] 11[/TD]
[TD] 120,000[/TD]
[TD] 110,000[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1/31/2015[/TD]
[TD] 10[/TD]
[TD] 120,000[/TD]
[TD] 100,000[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2/28/2015[/TD]
[TD] 9[/TD]
[TD] 120,000[/TD]
[TD] 90,000[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]3/31/2015[/TD]
[TD] 8[/TD]
[TD] 120,000[/TD]
[TD] 80,000[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]4/30/2015[/TD]
[TD] 7[/TD]
[TD] 120,000[/TD]
[TD] 70,000[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]5/31/2015[/TD]
[TD] 6[/TD]
[TD] 120,000[/TD]
[TD] 60,000[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2015[/TD]
[TD="align: right"]6/30/2015[/TD]
[TD] 5[/TD]
[TD] 120,000[/TD]
[TD] 50,000[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]7/31/2015[/TD]
[TD] 4[/TD]
[TD] 120,000[/TD]
[TD] 40,000[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]8/31/2015[/TD]
[TD] 3[/TD]
[TD] 120,000[/TD]
[TD] 30,000[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]9/30/2015[/TD]
[TD] 2[/TD]
[TD] 120,000[/TD]
[TD] 20,000[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2015[/TD]
[TD="align: right"]10/31/2015[/TD]
[TD] 1[/TD]
[TD] 120,000[/TD]
[TD] 10,000[/TD]
[/TR]
</tbody>[/TABLE]
Just to explain why our fiscal year like 2 months offset.
11/1/2014 - 11/30/2014 => is supposed to be the Jan-2015 month in P&L , so if a project starts to be implemented in Nov-2014 then it will have the whole year as savings. We captured 1Yr of VA/VE savings.
Then if the project is implemented 1 month late, Dec 2014 month then savings for 2015 will be from Feb-2015 to Dec-2015 which is only 11 months for 2015 but will have a carry over of 1 month in 2016.
Projects that were implemented before 11-2014, example Oct-2014 will have only 1 month of savings in 2014 and will have a carry over of 11 months in 2015 hence the 11 month in the above table.
I am really greatful to everyone here as i am trying to automate my sheet rather than manually adjusting the "savings" manually. I hope to have the savings be automatically adjusted based on the date of implementation we put in each project.
Many Thanks in advance.