Cash flow calculation

[BPM Aditya]

I am working on a cost reduction project in an automobile company.
Pl go through the below table as an example
Vehicle A Vehicle B Vehicle C
Savings throu Idea 1........ 200 250 300
Savings throu Idea 2........ 250 300 250
Savings throu Idea 3........ 100 150 150

Volumes in April - 10 50 50
Volumes in May - 20 50 50
Volumes in Jun - 30 100 100

Now, I want to calculate cash flow. Suppose Idea1 implemented in April, the benifits of the fin. year will be accountable on the vehicles from next month onwards (i.e., May, jun, jul... upto Mar).

If idea 3 implemented in Jun, benifits would be from July, aug and sep...upto Mar).

So i want to create a formula that if we key in the implementable month then excel should calculate the savings from the next month.

The formula for calculating cashflow is (Ex. if Idea1 impl in Apr)
=savings in Veh A * vol of Veh A (sum from May to Mar no.s) + savings in Veh B * vol of Veh B + savings in Veh C * vol of Veh C... and so on.

Can anybody suggest me what could be the formula because the work book size is very huge hence It is difficult to do this manually, that too every month.

 

[venkat1926]

sheet is given below

see formula in J4 which is copied to K4 and L4

see the formula in B11 which is copied down

check whether the figures agree with your thinking.
if not give clear calculation steps for these three

the calculations may be ok but the strings in A11,A12 ANS a13 MAY BE WRONG.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1		Vehicle A	Vehicle B	Vehicle C					Volumes in April	10	50	50
2	Savings throu Idea 1........	200	250	300					Volumes in May	20	50	50
3	Savings throu Idea 2........	250	300	250					Volumes in Jun	30	100	100
4	Savings throu Idea 3........	100	150	150					TOTAL VOLUMNE	60	200	200
5
6
7
8
9
10
11	CASHFOLOW APRIL	33000
12	CASHFLOW MAY	30000
13	CASHFLOW JUNE	34500
14
15
Sheet1

 

[BPM Aditya]

Thanks Venkat for the efforts taken. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am giving below the details work out.<o></o>
<o></o>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 535.5pt; HEIGHT: 301.5pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_1 type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="" src="file:///C:\Users\214086\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape><o></o>
<o> </o>
<o> </o>
Now my query is : In the above example I have shown that Idea 1 is being implemented in May'11 hence cash flow is shown from Jun'11 onwards. In case If I come to know in May that idea1 is not implentable in May but can be done in Jun'11, so I have to change again all the formulas. My original worksheet contains a huge size hence manually doing is very difficult.<o></o>
<o></o>
So i need a formula that whenever we change the cell F16, F17 or F18, excel should calculate automatically the cashflow accordingly.