Rolling average with bank balance

[AlexMcNee]

Date DR CR Exchange Rate


1/07/2011 € 100,000.00 $0.7400

2/07/2011 € 50,000.00 $0.7550

3/07/2011 € 7,500.00 $0.7800

4/07/2011 € 5,000.00 $0.7700

5/07/2011 € 160,000.00

6/07/2011 € 100,000.00 $0.7900

7/07/2011 € 80,000.00


I want to calculate what the average exchange rate was for each credit amount. E.g.$160,000 was purchased using a first-in-first-out method, so of this, €100,000 @ 0.74 then another €50,000 @ 0.755 and then €7,500 @ 0.78 and the last €2,500 (of the €5,000) was purchased at 0.77. What was the average of all these exchange rates? Then I want to be able to move down to the next credit, and use the remaining $2,500 first and then the residual to come from the $100,000.


Thoughts??

 

[Faseeh]

Hi AlexMcNeee,


Consider this layout:

[pre]

Column1	         Column2	Column3	  Column4
1/7/2011	100,000.00	$0.74 	100,000.00
2/7/2011	50,000.00	$0.76 	150,000.00
3/7/2011	7,500.00	$0.78 	157,500.00
4/7/2011	5,000.00	$0.77 	162,500.00
5/7/2011	160,000.00	$0.00 	322,500.00
6/7/2011	100,000.00	$0.79 	422,500.00
7/7/2011	80,000.00	$0.00 	502,500.00

In Column 4 you simply multiply Col2 to col3. This data is in A1 to D8, In G2 write total qtty;

In F5 write: =IF($G$2-D2<1,IF($G$2+B2-SUM($B$2:B2)<1,0,$G$2+B2-SUM($B$2:B2)),B2)
in G5 Write: =IF(ISNUMBER(F5)=TRUE,F5*C2,0)

Now Sum of Col G / Sum of Col F is you avg. rate.

Regards

[/pre]

 

[SirJB7]

Hi, AlexMcNee!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Rolling%20average%20with%20bank%20balance%20%28for%20AlexMcNee%20at%20chandoo.org%29.xlsm


It's still unfinished, the only thing is that it lacks the control of previous sold stock (check cell H9, it should be 0 and displays full value of 80K).


I'd try to go on with this later. In the meanwhile I wouldn't feel offended at all if you find the last part of the solution


Regards!

 

[Faseeh]

Hi AlexMcNee,


You can use the last sheet of this file named MyOpinion: for the above calcualation. Replace data with that of yours;


http://dl.dropbox.com/u/60644346/Copy%20of%20datafile.xls


Regards,

 

[SirJB7]

@Faseeh

Hi!

I win you by seconds, hahaha! But mine's still not finished. All ideas are welcome. Last sentence of my previous post applicable too for you.

Regards!

 

[Faseeh]

@SirJb7,


Yup!! You won!! Here is your Prize





Faseeh

 

[SirJB7]

@Faseeh

Hi!

I really appreciate, you well know how to touch my heart.

Regards!