98% of companies do not use specialized software to track their exposures (like Letters of Credit, Buyers' Credits, Rupee Packing Credit etc) and hedges (like Forward Contracts and Options). All the work is, unfortunately, done on MS Excel. Truth be told, this is one of the biggest problems in forex risk management, possibly as big, or even bigger, than managing the market risk itself.

Maintaining forex data on MS Excel is tedious, time consuming and prone to errors. In 10% of companies, this work is undertaken by highly competent executives who are well versed with Excel functions, formulae, macros, pivot tables et al. In another 20-30% of companies, those maintaining the data are reasonably proficient in Excel. Unfortunately, in more than 50% of the companies, especially in SMEs, the work of maintaining the data is done by clerical staff that is, many a times, unaware of some of the basics of MS Excel.

This can prove to be a very big hurdle in getting correct and timely information on forex exposures, making it impossible to manage the market risk effectively.

Most companies do not use specialized forex software because affordable software suited to Indian conditions did not exist till very recently. There is good news on that front, though. There is an Indian company in Bangalore that has written a specialized software for tracking forex exposures and hedges. If you would like to know more about it, please write to us at info@kshitij.com.

Coming back to the topic, in this article, I will give you ONE tip on how Excel can be used better for the purposes of forex risk management. Many of you may already know this first tip that I am going to share below. In the next article I'll give you another tip and there are good chances that the second tip will be a new one for you. Either way, make sure you show this to your junior staff members, who prepare your forex exposure and hedging reports for you.

Calculate Due Date automatically in Excel

All companies have to track various due dates for transactions like LCs, buyers' credits, interest payments, forward contracts etc. Here is an example of how Excel can be used to make life easier.

Let us take the example of a Letter of Credit. Some people enter the Date in TEXT format. They then calculate the LC Maturity Date on a calculator and again enter it in TEXT format in the next column. And then, if the LC Period is extended, they calculate the Maturity Date manually all over again! This is like having a car, a driver, a tank full of petrol and still going walking to work. Instead, all you have to do is enter the Date in DATE format. You can then use the date in calculations to automatically arrive at the due date. This is not possible if you enter the Date in TEXT format.

Enter the LC Opening Date in DATE format and the LC Period in NUMBER format. Then the LC Due Date can be calculated by Excel using the formula "LC Opening Date + LC Period". Simple? Of course. Excel is meant to do that for you. Obvious? Yes.

Why enter Dates in DATE format?

When you enter a date in DATE format, instead of TEXT format, you can

Do addition and subtraction on Dates, as shown in the example above. This is useful for calculating various due dates, and for calculating things like Interest Period when given two dates. The ability to calculate automatically makes life easier when LC or BC are extended.
Sort data on Date. This is useful to sort things like LCs or Forward Contracts on their due dates and put them into monthly buckets. You can then use this date-sorted data to figure out the period for which your forex risk is most acute, so that you can take hedges accordingly.

TIP:
I prefer recording dates as to read as 10-Sep-12. This avoids confusion between 10-09-12 and 09-10-12, which has started to come from people using different conventions and data files coming from different places. All you have to do is go to the relevant cell and type in "10Sep12" and hit Enter. The data will automatically appear as "10-Sep-12", in DATE format.

DO, enter your dates in DATE format, preferably as "10-Sep-12"

DON'T enter your dates in TEXT format. Then they are useless to you in Excel. They might as well be in Word, where you can't use them in calculations!

In the next article I will talk about a more advanced Excel tip. Keep an eye out for that.

NEED A FOREX SOFTWARE?

In case you have been feeling the need for a dedicated forex exposure management software, you can write to us at info@kshitij.com

We can put you in touch with this Bangalore company that has developed exactly such a software for India.