Top 5 Excel Mishaps in Business FX Management

3-minute read

I love Microsoft Excel as much as (if not way more than) the next guy but, I admit there are major drawbacks and obstacles that can cause painful, embarrassing, and frustrating problems. 

There are so many ways that even experienced Excel users can trip up – too many to list, so here are my top five excel mishaps and errors to be mindful of in the context of business currency management.

1. Incorrect Formulas

This sounds like an obvious one but, it's fairly easy to make a mistake when entering or editing a formula. Here are a few common issues to think about:

• Cell references (have you selected the correct cell(s)?)
• Use of parenthesis*: 3+3*3 = 12, but (3+3)*3 = 18
• Using SUM instead of SUBTOTAL for filtered ranges is very common
• The wrong function: for example COUNTA vs COUNT vs SUM

💡Always check as you go, especially the output of complex formulas

*Excel's order of precedence can be remembered by PEMDAS: Parentheses, Exponents, Multiplication, Division, Addition, Subtraction

2. Overwriting Cells / Formulas

You've spent hours creating a masterpiece workbook of multiple sheets and complex formulas only for someone else to accidentally overwrite several important cells. Or worse still, you carelessly delete them only to realise at some later time when something doesn't quite look right. 

💡Avoid headaches by locking cells and protecting worksheets 

3. Misapplied Absolute & Relative Cell References

You know those little dollar signs ("$") you see sometimes? Those determine the type of cell reference, which is handy for copy-pasting or dragging formulas across a range. Getting this right means you can create one formula then apply it to many cells and ensure accurate output.

All cell references are Column:Row, so "A1" or "B10", for example.

If I wanted to reference one fixed ("Absolute") cell within a formula then I might use $D$3 meaning if I copy or drag the formula, it will always point to cell D3. Like in the image below where I'm multiplying the number of sales by the price to give a total. If I used "D3" (without the $ signs) then Sarah and Jason's total values would be wrong if I dragged the formula in E6 down.

In the next image, I've fixed the row reference but I want the column reference to change as the formula is copied across the various products.

💡Always test and check to make sure copied formulas do what you want

4. Links to Other Workbooks / Data Sources

There's nothing wrong with referencing data from other workbooks, but care must be taken to ensure the quality, accuracy, security, and up-to-dateness of the source data.

Many business functions run on Excel – make sure to keep backups, limit the number of external references, and when distributing workbooks to others (e.g. other departments or external colleagues) sever links and replace them with static data.

Tracking down, removing, and updating links can be tedious. It's best avoided if possible but sometimes you need to have workbooks talk to each other.

5. Exchange Rate Errors

I had to include a specifically currency-focussed example here and, it's a really common one – using the wrong rate or worse, converting amounts incorrectly (dividing by the rate instead of multiplying or vice versa).

There are market conventions for how exchange rates are quoted, take the pound vs the dollar: it's always £1 = X dollars.

This means converting between GBP and USD amounts looks like this:

• GBP x Rate = USD
• USD ÷ Rate = GBP

Be careful with inverse rates, say GBPUSD 1.2050 = USDGBP 0.8298, and be extra careful with rounding errors and the number of decimal places you use. I always use a minimum of 4 but have a preference for 6.

If you mix up multiplying and dividing, you'll obviously have dodgy data. You can invert a rate by doing 1/Rate.

Need our help?

Thankfully we do all the heavy lifting for our clients using our proprietary risk management software.

If you're struggling with clumsy excel sheets and find it difficult to make decisions then please contact us for a review of your processes!

We're proud to work transparently with our clients, and we work hard to break the asymmetry of knowledge and information in the FX market. 

Reach us at [email protected] or 0203 838 0250.

Thanks for reading 👋