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

Neavalmi

New Member
Joined
Jun 14, 2009
Messages
43
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Here you go:


Excel 2010
ABC
1Financial Year2015
2
3Annual SavingsDateAdjusted Savings
4$120,00031/12/2014$110,000
Sheet1
Cell Formulas
RangeFormula
C4=A4/12*(DATEDIF($B$4,DATE($B$1,10,31),"m")+1)
 
Upvote 0
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.
 
Upvote 0
So you'll probably want something like this:


Excel 2010
ABCDE
1Financial Year2015
2FY Start11/01/14
3FY End10/31/15
4
5Project StartProject EndAnnual SavingsSavings this Financial Year
612/01/1312/31/13120000$0
701/01/1401/01/15120000$30,000
802/01/1404/05/14120000$0
903/01/1412/05/14120000$20,000
1010/30/1410/01/15120000$120,000
1110/30/1411/01/15120000$120,000
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top