Sep, 24, 2012 By Vikram Murarka 2 comments

Here's another very important MS Excel tip, one that I got from one of our clients. It has made my life so much simpler. Thank you, Krishnan!

Let us say there is a company that exported goods worth $ 5 million and received payment at a Dollar-Rupee rate of 45.45 on 17-Aug-11. It then exported goods worth $ 1 million and received payment at a rate of 56.00 on 23-May-12.

What is the average rate at which the exports have taken place? You will be surprised at the number of people who reply saying 50.7250, the simple average of 45.60 and 56.00. However, as you know, the correct answer is actually 47.21, since the larger amount of $ 5 million was exported at the lower rate of 45.45 in August 2011, as compared to the smaller amount of $ 1 million which was exported at 56.00 in May 2012. The rate of 47.21, the weighted average rate calculated as (45.45 x 5 mln + 56.00 x 1 mln)/ 6 mln.

Not only do the export/ imports/ forward contract/ option transactions take place at different exchange rates, the transaction amounts are also always different. ** Therefore, there is always a need to calculate the weighted average rate at which forex transactions have taken place.** Since the transaction amounts are different, calculating simple average is simply wrong.

Thankfully, while a few people do erroneously make do with a simple average, most people calculate the Weighted Average Exchange Rate. Unfortunately, the way most people (I was one of them) do the calculation is quite cumbersome.

The way I used to calculate weighted average earlier in Excel is as follows:

Calculate the Rupee equivalent of the Dollar amount

Sum the Dollar amounts

Sum the Rupee amounts

Divide the sum of Rupee amounts by the sum of Dollar amounts

Note the SUMPRODUCT function in the formula bar. The weighted average is now calculated as SUMPRODUCT(range with Dollar amounts, range with Export rates)/sum of Dollar amounts.

Much simpler, isn't it? Thanks are due to Krishnan, my client at Marico Ltd, and to MS Excel. I hope this function will be as useful to you as it is to me!

Dollar Rupee Forecast. Get a Free Trial

15Jun17 USDJPY 111.05: Broken out of triangle. Target 113 now. Could reach in June itself https://t.co/ztg40LTXlH https://t.co/ztg40LTXlH

13Jun17, EURUSD 1.1195: First big break above 21-mth MA in last two mths, since 1.38. https://t.co/pqVUuEElwk https://t.co/pqVUuEElwk

#Gold 1284... Immediate support being 1280, Gold could head towards major resistance near... https://t.co/GKl5IBWuPG https://t.co/GKl5IBWuPG

Caution time for Euro as it shows great upside momentum but the resistance cluster overhe... https://t.co/h22tX9jG5I https://t.co/h22tX9jG5I

Forex reports by KSHITIJ.COM are based on dedicated and in depth analysis of various economic and financial parameters. Hence the judgement, quality, probability and reliability of these forex risk management views are quite high.

Lupin Ltd.

Mumbai

*We are privileged to be associated with Kshitij as our Forex Advisor. Their valuable advice has helped Marico to redefine its forex management policies. We look forward to a long and rewarding association with them.*

Marico Ltd.

Mumbai

Kshitij is the only advisor that has taken a firm stand on the market at various times and at the same time they have always been willing to accept their mistakes gracefully.

Shyam Group of Companies

Bangalore

WHAT'S NEW?

Euro Longterm Forecast

Need Euro, Yen and Aussie forecasts for business costing and planning your hedges? You may also be interested in other __long term forecasts__

WHAT'S NEW?

June Calendar wallpaper

As mentioned in the print calendar in January that we would be at crossroads by mid-2017, we are now standing at a crucial inflection point. Now, it is to be seen if the bounce extends past 72.50 or the major downtrend from 86.50 (C ) resumes. This is important to watch, as it could impact Dollar-Rupee as well. __here__

WHAT'S NEW?

June Monthly Forecast

Our Jun-17 Monthly forecast is now available. To order a ** PAID **copy, please ** click here** and take a trial of our service.

Copyright © 2016 Kshitij, All Rights Reserved

- Kshitij Consultancy Services
- Email: info@kshitij.com
- Ph: 00-91-33-24892010 / 24892012

Good post, do you have any others on this topic?

Thank you, Steve.

You may also want to look at

http://colourofmoney.kshitij.com/working-with-date-in-excel/