Geniuses, Masters and Gurus of Excel.
i have a very challenging situation with Excel and beating my brain against the wall. but still failed to accomplish.
I have small project, where i receive the fund from a client/company in EURO however my financial system and bank account is in USD and whatever fund i receive in EURO, it converts using that days foriegn exchange rate of the bank. so this part is easy, i can get the exchange rate from my bank statement. All of the project expenses are in USD but based on my agreement with my client, i have to report back the expenses in EURO currency, converting all USD expenses back to EURO using prevailing foreign currency exchange rate at the date of receipt of funds in my bank. Now the method to convert the expense USD to EURO based on the foreign currency exchange rate at the date of receipt of funds Must be in (FIFO) method "First In, First Out"
I have put a detail workout breakdown of the calculation in the uploaded workbook. if anything is unclear, please let me know, so that i provide further elaboration on the question.
I would really appreciate any help on this challenging situation and this will save hours of my time.
file uploaded https://app.box.com/s/zmkzsqo8eg4l684zwi1z
i have a very challenging situation with Excel and beating my brain against the wall. but still failed to accomplish.
I have small project, where i receive the fund from a client/company in EURO however my financial system and bank account is in USD and whatever fund i receive in EURO, it converts using that days foriegn exchange rate of the bank. so this part is easy, i can get the exchange rate from my bank statement. All of the project expenses are in USD but based on my agreement with my client, i have to report back the expenses in EURO currency, converting all USD expenses back to EURO using prevailing foreign currency exchange rate at the date of receipt of funds in my bank. Now the method to convert the expense USD to EURO based on the foreign currency exchange rate at the date of receipt of funds Must be in (FIFO) method "First In, First Out"
I have put a detail workout breakdown of the calculation in the uploaded workbook. if anything is unclear, please let me know, so that i provide further elaboration on the question.
I would really appreciate any help on this challenging situation and this will save hours of my time.
file uploaded https://app.box.com/s/zmkzsqo8eg4l684zwi1z
| Excel 2010 | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | Date | Deposits Income in EURO | Converted deposit income in USD | Exchange rate based on the rate as of fund receipt date | Manual Workings texts | ||||||||||||||||||||||
| 2 | 5/1/2010 | € 910,215.00 | $ 1,191,380.89 | 0.764 | |||||||||||||||||||||||
| 3 | 9/18/2012 | € 982,713.00 | $ 1,204,305.15 | 0.816 | Now since our first deposit converted USD was 1,191,380,89, we can convert our expenses to EURO using 0.764 rate up to this amount 1,191,380.89 | ||||||||||||||||||||||
| 4 | 1/12/2013 | € 287,674.10 | $ 368,288.38 | 0.781 | once we finish convertion of expense amount up to this limit then we have to move to the next exchange rate . See calculation below | ||||||||||||||||||||||
| 5 | Total | € 2,180,602.10 | $ 2,763,974.42 | ||||||||||||||||||||||||
| 6 | |||||||||||||||||||||||||||
| 7 | $ 575,001.55 | USD balance of first deposit after converstion of 576,140.01 & 40,239.33 ubB10 and B11 | |||||||||||||||||||||||||
| 8 | $ 694,525.51 | since the USD 1,269,527.06 is greater than available balance from first deposit , we need to convert the expense from the second deposit | |||||||||||||||||||||||||
| 9 | Date | Expense in USD | Convert to EURO based on the exchange rate of receipt of fund using FIFO method | € 439,301.18 | EURO converted using the first deposit exch rate | ||||||||||||||||||||||
| 10 | 2/2/2011 | $ 576,140.01 | € 440,170.97 | € 566,732.81 | EURO converted using the second deposit exch rate | ||||||||||||||||||||||
| 11 | 2/17/2011 | $ 40,239.33 | € 30,742.85 | $ 509,779.64 | USD Balance from the second deposit after converstion of 694,525.51 | ||||||||||||||||||||||
| 12 | 8/22/2012 | $ 1,269,527.06 | € 1,006,034.00 | Now since the B13 value of USD 88,866.93 is lower than the balance from the second deposit then we can use again the second deposit exch rate to convert | |||||||||||||||||||||||
| 13 | 10/1/2012 | $ 88,866.93 | € 72,515.41 | $ 420,912.71 | USD is the balance left after another convertion of expense 88,866.93 was deducted | ||||||||||||||||||||||
| 14 | 1/18/2013 | 730,345.13 | € 585,165.84 | Now, we want to convert USD 730,345.13 and our balance from the second deposit which is 420,912.71 cannot suffice, we will split the convertion between second deposit and third deposit exchange rates | |||||||||||||||||||||||
| 15 | 3/9/2013 | $ 50,145.00 | € 39,168.81 | € 343,464.77 | EURO converted using the second deposit exch rate | ||||||||||||||||||||||
| 16 | Total | $ 2,755,263.46 | € 2,173,797.88 | $ 309,432.42 | this is the remaining USD expense to conver using third deposit exchange rate | ||||||||||||||||||||||
| 17 | € 241,701.07 | EURO converted using the third deposit exch rate | |||||||||||||||||||||||||
| 18 | $ 58,855.96 | USD balance left from the third deposit | |||||||||||||||||||||||||
| 19 | now we want to convert the last item in B15 and since this is within the available balance from third deposit we simply convert it using the third deposit exchange rate | ||||||||||||||||||||||||||
| 20 | AS YOU CAN SEE, I AM DOING ALL THESE CONVERTIONS USING MANUAL HECTIC MANUAL MATH OPERATIONS | ||||||||||||||||||||||||||
| 21 | I NEED A FORMULA TO PUT IN RANGE C10:C15 SO THAT IT GIVES ME THE SAME RESULT WITH THE POWER OF FORMULA INSTEAD OF THIS MANUAL CALCULATION | ||||||||||||||||||||||||||
| 22 | I WOULD GREATLY APPRECIATE ANY HELP | ||||||||||||||||||||||||||
| 23 | |||||||||||||||||||||||||||
| 24 | |||||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2 | =B2/C2 | |
| D3 | =B3/C3 | |
| D4 | =B4/C4 | |
| B5 | =SUBTOTAL(109,Sheet1!$B$2:$B$4) | |
| B16 | =SUBTOTAL(109,Sheet1!$B$10:$B$15) | |
| C5 | =SUBTOTAL(109,Sheet1!$C$2:$C$4) | |
| C10 | =B10*D2 | |
| C11 | =B11*D2 | |
| C12 | =F9+F10 | |
| C13 | =B13*D3 | |
| C14 | =F17+F15 | |
| C15 | =B15*D4 | |
| C16 | =SUBTOTAL(109,Sheet1!$C$10:$C$15) | |
| F7 | =C2-(B10+B11) | |
| F8 | =B12-F7 | |
| F9 | =F7*D2 | |
| F10 | =F8*D3 | |
| F11 | =C3-F8 | |
| F13 | =F11-B13 | |
| F15 | =F13*D3 | |
| F16 | =B14-F13 | |
| F17 | =F16*D4 | |
| F18 | =C4-F16 | |
waiting to see you and to hear sound from the pop of the cork from this bottle of champagne.
