FX risk model in Excel, requires FX knowledge / risk management

I work for and represent Lumon, a UK-based corporate foreign exchange specialist. We assist UK corporate businesses in managing their foreign exchange (FX) risk through straightforward hedging strategies. Our primary tool is the use of vanilla forward contracts, which allow clients to hedge a portion of their forecasted FX exposure for future periods, typically ranging from 0 to 36 months. In simpler terms, for UK businesses, we provide solutions that both hedge their FX risk and facilitate the underlying payment, effectively converting one currency into another.



I'm currently seeking someone with experience in both data modelling and foreign exchange/risk management. I will be able to provide five years' worth of historical market data to support the modelling task outlined below.


As part of our service, we create bespoke risk management strategies tailored to our clients' specific needs. These strategies are based on several key factors:



- **Currency pair**: Which currency pairs do they trade? For example, GBP/USD.

- **Transaction direction**: Does the client sell GBP and buy USD, or the other way around?

- **Annual exposure**: What is the total exposure per year? For instance, a client might need to purchase $12 million annually to settle invoices for goods sourced from China.

- **Risk exposure**: What is their risk exposure, both historically and in the future? From a historical standpoint, we assess the past performance of the relevant currency pair. For example, GBP/USD may have traded between 1.32 and 1.20 over the past 12 months, representing roughly a 10% variance in cost.

- **Hedge proportion**: What percentage of their annual exposure do they wish to hedge? For instance, some clients may opt to hedge 50% of their exposure for the upcoming 12 months, meaning they will forward-purchase $6 million at a fixed or variable rate. Others may only hedge 70% of the next six months' exposure, equating to forward-buying $4.2 million of their $6 million six-month exposure.



I require an Excel-based model that incorporates historical FX data and integrates the factors listed above to help determine the optimal hedge level for each individual client, including back-testing functionality.



**Example Scenario:**



We have a client who purchases $12 million annually in USD but currently does not hedge their FX exposure. They are interested in exploring different hedging scenarios, which need to be visually represented—perhaps through line graphs or similar charts—to show the varying levels of risk they would assume based on different hedge ratios (the amount hedged at any given time). The model should allow me to adjust for the currency pair, the amount of annual exposure, and the client’s preferred hedge period (e.g., whether they wish to hedge for 3 months, 12 months, or 2 years).



In this scenario, the model should answer questions such as:



- **Impact of different hedge ratios**: What would the client’s exposure look like if they hedged only 50% of their annual exposure, compared to hedging 75%? How much risk would they be mitigating compared to relying solely on spot rates (the average daily rate over the period)?

- **Shorter-term hedging**: What would the risk exposure be if they hedged for only six months, but at a higher hedge ratio?

- **Back-testing**: The model must have the capability to back-test historical data and show how the chosen strategy would have performed over the previous year.

- **Hedge rates**: The model should factor in the current hedge rate, which will serve as the starting exchange rate for any given hedging strategy.



I have attached several example spreadsheets and past proposals to provide additional context regarding our service offering. These examples should help illustrate what we are aiming to achieve with the model.