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!

Need for Weighted Average

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.

Old Way of Calculating Weighted Average

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

SUMPRODUCT Way of Calculating Weighted Avg

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!

Forecasts and Accountability

Everybody loves a forecast and wants to know where Dollar-Rupee is headed. This is natural because forex is the business of every importer/ exporter. Even the RBI conducts a Professional Forecasters’ Survey every quarter in which it seeks the views of professional forecasters on various aspects of the economy, including the Rupee.

Yet, people tend to have very low respect for forecasters. This is not so much because their forecasts tend to go wrong. What people do not like is the lack of sense of accountability displayed by many forecasters, whether in banks, in companies, or outside. Even Shri G Padmanabhan, Executive Director, RBI lamented this fact at his recent “Goa to Goa” speech on 23rd August. He said, “If we accept that market makers/ analysts who often are in a position to influence the market sentiment tend to be either euphoric or despondent (at crucial market turning points), what is the accountability if their forecasts turn out to be widely wrong…?” The words in the brackets are ours

The Forecaster has a Responsibility

Forecasters need not be defensive on hearing the above statement. Accountability does not mean the forecaster has to make good the losses suffered by a client on account of an incorrect forecast.

However, forecasters should bring in accountability by (a) admitting to mistakes and correcting subsequent forecasts and (b) publishing a track record of the reliability of their forecasts based upon the directional and numerical accuracy of the forecast when compared with the actual market rates that occur afterwards; and pre-informing a prospective client of it.

We invite you, Dear Reader, to take a look at our track record by clicking on http://www.72pct.com

Further, suggesting proper ways to use a forecast is another way of discharging responsibility towards a client. We’ll take that up just a little later.

Seek Reliability, not Accuracy

Before that, the client too has to acknowledge that forecasting is not a science. It is an art, a skill, practiced in the most uncertain conditions. As such, the client needs to look for broad reliability, rather than expect pin-point accuracy.

Also, profits or losses come from the market itself, and would accrue to the exporter/ importer whether or not he uses a forecast. The job of a skilled forecaster is not to guarantee, but to help increase the chances of a profit and decrease the chances of a loss. So, wanting compensation for a wrong forecast is incorrect.

Of course, an unskilled forecaster might increase the chances of a loss. Therefore a client would do well to seek out the services of a reliable forecaster.

Three types of forecasts

Further, it is important to know that besides moving up or down the market also moves sideways. Most people tend to ignore the last possibility even though it generally has a large probability.

Also, sometimes the outlook is clear and sometimes it simply is not. Such a situation usually arises after the market has been ranged for a long time, and usually means that a big trend movement is going to take place once some news emerges that can break the uneasy equilibrium that exists at the time.

The forecaster should spell out the conditions as clearly as he can. And, the client should see what best he can do in the circumstances.

How to Use a Forecast

The question before an exporter/ importer is not whether he should hedge or not. He should. The questions are how much to hedge and how to hedge.

How much to hedge? The client should not hedge everything based on any one single forecast. This is because (a) any one single forecast of even a reliable forecaster has no more than a 50% chance of being correct and (b) prudent risk management says that one should never hedge 0%, nor 100% and certainly not at one go.

Small amounts should be hedged over a period of time, slowly building up to a full hedge.

How to hedge? The forecast should be used to choose the hedging instrument. If the forecast is that the Dollar will move up, the Importer can buy a Forward and the Exporter should buy a protective Put. On the flip side, if the Dollar is expected to fall, the Exporter should sell Forward and the Importer should buy a protective Call.

And when the market has been ranged sideways for a long time, both should buy protective out of the money Calls and Puts.

The important point here is that when the Exporter and Importer buy a Put or a Call respectively, they protect themselves against the danger of a forecast going wrong while allowing themselves the opportunity of benefiting if the forecast is right.

People would do well to sanction a hedging cost budget, in order to be able to buy Options. Remember, sanctioning a hedging cost budget is the first step to hedging success.

While forecasters should definitely act with responsibility, clients too can use forecasts better. Here’s to more effective risk management!

The old financial year is over. The accounts are done and profit/ loss has been calculated. Hopefully you would have seen a good growth in turnover and profits. And, in the case of your Imports/ Exports, we certainly hope that you might have been able to avoid an exchange rate loss and show an exchange rate profit.

Looking at your forex risk management process, you would be keeping track of a number of rates. You normally keep a watch on the Spot Rate, the Forward Rate, maybe even option volatilities. In your weekly/ monthly exposure management reports you calculate your Import/ Export Book Rate (the rate at which your exposure is taken into your accounts at the time of its creation) and you calculate your hedge ratios. You also do mark-to-market calculations on your hedges. Besides all this, you also have to keep abreast of the global stock, commodity and money markets.

It is a lot of work, but it is necessary for prudent risk management.

Net Purchase/ Sale Rate

However, is it possible that despite all the hard work, you are totally unaware of that ONE vital variable that you really need to track? If you are an importer, do you know your Net Purchase Rate (NPR) for Dollars? If you are an exporter, do you know your Net Sale Rate (NSR)?

Simply, do you know how much you really paid for the Dollars you bought for your imports, or how much you really got for the Dollars you sold against your exports in the last month/ quarter/ year?

Use of the Net Purchase/ Sale Rate

Your actual net payment/ sale rate will give you the true picture of the effectiveness of your forex risk management policies and practices in clear and unambiguous terms. It is that one number which really matters because that is what impacts the CASH profit/ loss of your company.

There is a 99.99% chance that the rate at which your exposure (imports/ exports) is retired/ realized is going to be different from your “book rate” or the rate at the time you recognized your exposures in your accounts. If you do not hedge at all, your import payment/ export realization will happen at the Spot Rate on the date of import payment/ export realization. This is simple enough.

But, we all hedge in order to avoid the danger of a rise (or fall) in the Dollar as the case may be. We employ various techniques, tactics and tools to hedge. We rely on various forecasts. We spend a lot of time and effort on hedging. Some people believe in hedging 100% through Forwards on Day One. Some others do not hedge at all. Some people believe they can optimize their rate by trading in/ out of the market. Some people have a firm hedging policy that they stick to. Others keep experimenting with many different strategies. There are many approaches to risk management, with their own advantages and drawbacks. An earlier article in this series dealt with the drawbacks of common hedging methods

True Barometer of Effectiveness

Whatever be your own preferred approach to hedging, you can say your hedging has been effective/ profitable if, as an importer, your Net Purchase Rate (NPR) happens to be less than the Average Spot Rate (ASR). If you are an exporter, your Net Sale Rate (NSR) should be more than the Average Spot Rate (ASR).

In formulae terms:
Gain for an Importer = ASR – NPR
Gain for an Exporter = NSR – ASR

We suggest the above measure of effectiveness, because if you had not hedged, you would have got the Spot Rate on payment/ receipt date in any case. By hedging you should either pay less or receive more than that Spot Rate.

How to calculate the NPR/ NSR?

Say you are an Importer. Over a period of time, you might have hedged as follows:

1. You would have hedged some part of your exposures using Forwards. Simple enough. And you might be having the wise policy of utilizing your hedges for actual payments, to the extent possible.
2. Some part of your imports might have been hedged using Call Options. At maturity, some of them would have been exercised if the Spot on maturity was greater than the Strike. The others would not have been exercised and you would have paid for your imports at the Spot rate.
3. A part of your exposures might have remained intentionally unhedged, because you should neither hedge 0% nor 100%, and you would have paid off those imports also at the Spot rate.
4. During this period, you might have also engaged in some in-out-in-out forex trading on the Major currencies. Not necessary, but possible.

Given the above, you would calculate your Net Purchase Rate as shown in Example 1 below. As an Exporter, you can calculate your Net Sale Rate as shown in Example 2 below.

Example 1: Calculating Net Purchase Rate, for Importers

The NPR or Net Purchase Rate during September 2012 has been 55.32. As the Weighted Average Spot Rate during the period has been 54.6404, the hedges have not done well.

Example 2: Calculating Net Sale Rate, for Exporters

The NSR or Net Sale Rate during September 2012 has been 55.42. As the Weighted Average Spot Rate during the period has been 54.6404, the hedges have done quite well.

Comparing Imports (NPR) and Exports (NSR)

If the NPR (55.32) is compared with the NSR (55.42) we see that Importers have paid less than Exporters and vice versa (of course). If some company has both Imports and Exports, it has earned a NET Profit of 10 paise per Dollar between the two.

Importantly, the above example also shows that there is scope even for a company with a Perfect Natural Hedge to earn a net positive net differential between the NPR and NSR. This is worth striving for.

For your own good

The Accounting Standards do not required to calculate your Net Payment/ Sale Rate. But, do so for your own benefit. You might be surprised by the results. As the Hindi saying goes, “Doodh ka doodh, pani ka pani ho jayega.”

Those who believe forex is their business and are really keen to do well in forex hedging, will take up the challenge.