About 98 per cent of companies do not use specialised software to track their exposures (such as letters of credit, buyers’ credits and rupee packing credit) and hedges (such as 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 per cent 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 per cent of companies, those maintaining the data are reasonably proficient in Excel. Unfortunately, in more than 50 per cent 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 big hurdle in getting correct and timely information on forex exposures, making it impossible to manage the market risk effectively.

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.

Calculate Due Date automatically in Excel

All companies have to track various due dates for transactions such as LCs, buyers’ credits, interest payments, and forward contracts. 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? Not to some people.

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!

(The author is Chief Currency Strategist at a kshitij.com. The views are personal. He can be reached at >vikram@kshitij.com )

Related Topics
comment COMMENT NOW