Annual Savings Calculation based on P&L Date (Excel 2007)

[Neavalmi]

Hello Excel Experts,

I would like to know if there is a way i can automate my P&L savings (Profit and Loss) in excel since currently i am doing it manually.

Our P&L date savings calculation is not from Jan to Dec but from Nov 01, 2013 to Oct 31, 2014 for YR 2014 and from Nov 01, 2014 to Oct 31, 2015 for YR 2015.

Here's the scenario:

1) In column A, i have my annual savings of US$ 120,000. My column B is P&L Date. If i entered any date from Nov 01 to Nov 30, the calculated P&L Savigns in column "C" should be US$ 120,000 (12 months from Nov to Oct).

2) In reference to the above, if i have entered any date in column B from Dec 01 to Dec 31, the computed P&L savings in C should be US$ 110,000 (only 11 months from Dec to Oct)

Appreciate your help if this is possible without macros.

Thanks.

 

[Comfy]

Here you go:

Excel 2010
	A	B	C
1	Financial Year	2015
2
3	Annual Savings	Date	Adjusted Savings
4	$120,000	31/12/2014	$110,000
Sheet1

Cell Formulas
Range	Formula
C4	=A4/12*(DATEDIF($B$4,DATE($B$1,10,31),"m")+1)

 

[Neavalmi]

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.

Range		# of Months	Annual	P&L
12/1/2013	12/31/2013	1	120,000	10,000
1/1/2014	1/31/2014	2	120,000	20,000
2/1/2014	2/28/2014	3	120,000	30,000
3/1/2014	3/31/2014	4	120,000	40,000
4/1/2014	4/30/2014	5	120,000	50,000
5/1/2014	5/31/2014	6	120,000	60,000
6/1/2014	6/30/2014	7	120,000	70,000
7/1/2014	7/31/2014	8	120,000	80,000
8/1/2014	8/31/2014	9	120,000	90,000
9/1/2014	9/30/2014	10	120,000	100,000
10/1/2014	10/31/2014	11	120,000	110,000
11/1/2014	11/30/2014	12	120,000	120,000
12/1/2014	12/31/2014	11	120,000	110,000
1/1/2015	1/31/2015	10	120,000	100,000
2/1/2015	2/28/2015	9	120,000	90,000
3/1/2015	3/31/2015	8	120,000	80,000
4/1/2015	4/30/2015	7	120,000	70,000
5/1/2015	5/31/2015	6	120,000	60,000
6/1/2015	6/30/2015	5	120,000	50,000
7/1/2015	7/31/2015	4	120,000	40,000
8/1/2015	8/31/2015	3	120,000	30,000
9/1/2015	9/30/2015	2	120,000	20,000
10/1/2015	10/31/2015	1	120,000	10,000

<tbody>
</tbody>

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.

 

[Comfy]

So you'll probably want something like this:

Excel 2010
	A	B	C	D	E
1	Financial Year	2015
2	FY Start	11/01/14
3	FY End	10/31/15
4
5	Project Start	Project End		Annual Savings	Savings this Financial Year
6	12/01/13	12/31/13		120000	$0
7	01/01/14	01/01/15		120000	$30,000
8	02/01/14	04/05/14		120000	$0
9	03/01/14	12/05/14		120000	$20,000
10	10/30/14	10/01/15		120000	$120,000
11	10/30/14	11/01/15		120000	$120,000
Sheet2

Cell Formulas
Range	Formula
B2	=DATE($B$1-1,11,1)
B3	=DATE($B$1,10,31)
E6	=D6/12*IFERROR(DATEDIF(MAX($B$2,A6),MIN($B$3,B6),"m")+1,0)

If you provide the project duration and the financial year you wish to calculate the savings for this will tell you.

both currency and Date are in a US format.