Average Weighted Cost/Rate

[Hamzah]

Hi All,

As my pursuit to invent a template for computing foreign exchange gains on Financial Instruments using FIFO continues, i have made a bit of progress.
However, there is one challenge, i am trying to allocate a weighted average rate for the sales during the year for unique instruments (derived from column P & Q) to the matched sales in column M.
I have tested this with a similar scenario but for one instrument and it works just fine.

Please assist me with a formula to replicate whatever was done for one instrument but this time with different instruments thus requiring a condition.

Both excel samples and details have been attached.

 

[Hui]

In the GBP file, you show M23 as 3,233, but if it is calculated as : =SUM(Q12,Q14,Q15)/SUM(P12,P14,P15)
=3,236 not 3,233

Is that logic correct ?

Is M24 the same or different and why ?

 

[Hamzah]

In the GBP file, you show M23 as 3,233, but if it is calculated as : =SUM(Q12,Q14,Q15)/SUM(P12,P14,P15)
=3,236 not 3,233

Is that logic correct ?

Is M24 the same or different and why ?

Hi Hui - You are right. Weighted average in M23 is 3,236 (I had missed out a figure).

M24 will have to be different since L23 (2.5 Mn of instrument M) allocated 1 Mn to P12 and the remaining 1.5 Mn to P14 thus leaving P14 with 12.5 Mn to which L24 will be fully allocated returning average weighted rate of 3240.62. Thats the logic i am trying to achieve. A FIFO of sorts then returns weighted average of the rates.