Case Study: Impact of the oil price and FX and interest rates on profit and loss
Live access to large international data sources with Risk Kit Data
By Uwe Wehrspohn and Sergey Zhilyakov
[Download]
Contents
Synchronization of time-series
Calibration of oil price and interest rate models
Many people in the global economy process and analyze information on a daily basis. The importance of quick and easy access to that information cannot be overestimated today. Quite a few different data providers grant access to big databases with financial data and statistics over the internet through special APIs. From one hand, this simplifies the access to information. On the other hand, some technical knowledge is required to read and parse this information. Moreover, every data provider implements his own API, which makes it difficult to accumulate and aggregate the data in one place.
Risk Kit Data1 solves these problems and provides a standardized interface to access and process financial data and statistics from different data sources. Over a user-friendly graphical interface, users can browse statistics, perform searches, export data directly to Excel sheets and even more.
Risk Kit Data supports five popular data sources such as the European Central Bank ECB, Eurostat, the Federal Reserve Economic Data FRED, the World Bank and Apilayer Marketstack.
Presentation of Risk Kit Data
Risk Kit Data is an add-in for Microsoft Excel. After the installation, the add-in is available as a new toolbar.
Figure 1. Risk Kit Data toolbar.
Risk Kit Data provides a graphical user interface to browse statistics and financial data. Use the Finance Task Pane by clicking ‘Browse Financial Data’ on the toolbar to get recent and historical stock and index prices and exchange rates. Access the Data Task Pane by clicking ‘Browse Historical Data’ to browse the full data bases of the European Central Bank ECB, Eurostat, the Federal Reserve Economic Data FRED, the World Bank and Apilayer Marketstack.
Use the examples under the ‘Example Workbooks’ drop-down menu to get started with Risk Kit Data. Under ‘Add-in Info’, you will find the online help as well as the information about the add-in.
To deactivate the add-in in Excel, go to the ‘Add-in Settings’ and click ‘Disable add-in’.
Note that you need a personal API token from Apilayer Marketstack to access their data feeds. To request a token go to ‘Add-in Settings’ on the toolbar and follow the displayed link. Here you also find a reference to their terms and conditions for the usage of their data.
Figure 2. Add-in Settings and API token
Profit and loss model
The oil price has changed strongly over the last years. The high volatility especially affects companies that import crude oil and other raw materials. In this section, we consider a Profit and Loss model where the oil price is a major source of randomness. We also include the USD/EUR-exchange rate and a 1-year EUR-interest rate as additional examples of typical market factors. To get the historical prices of these factors and to consolidate the time-series, we will use the Risk Kit Data add-in.
We will also take another add-in, Risk Kit, to estimate the parameters of process models of the data and to perform a Monte-Carlo simulation of the profit and loss model. For a detailed introduction to Risk Kit see Wehrspohn/Zhilyakov, Monte-Carlo Simulation with Risk Kit, 2021.
Determination of the model
As the first step, we define a simple purely illustrative P&L model and identify its sources of randomness (Figure 3). To calculate the gross profit and loss, we start with a turnover (D7) and deduct all expenses including the material costs (D8), raw materials (D9), personnel costs (D10) and write-offs (D11). That leads to the intermediate result ‘Earnings before interest and taxes’ (EBIT) (D12). Further, we calculate the ‘Earnings before Taxes’ (EBT) (D15) which is equal to the sum of the interest income (D13) and the extraordinary income (D14) added to EBIT.
Figure 3. Profit and Loss model.
Let’s assume that the turnover (C7), material costs (C8), raw materials (C9), personnel costs (C10) and the interest (C13) and extraordinary income (C14) are subject to risk.
We assume the turnover (C7) to have a triangular distribution with parameters a=900, b=1000 and c=1050 which means that the turnover fluctuates between 900 and 1050 and the likeliest value is 1000.
Figure 4. Identifying the sources of randomness.
You may also notice the ‘OutputName’ function in the formula bar appended to the main distribution. This function specifies the label for this particular risk, which we will see on the charts afterwards.
For the material costs, we assume a normal distribution with expectation = 4% and standard deviation = 2%.
Figure 5. Identifying the distribution for the material costs.
Since the actual material costs are variable and depend on the level of turnover, we model them as a relative quantity ‘in % of turnover’. Thus, the total material costs in D8 are equal to the relative costs (C8) multiplied by the total turnover (D7).
Figure 6. Calculating the total material costs.
One of the biggest expenses in our example is raw materials, which is the product of the total volume of the imported oil (barrels) and its price. We assume that the volume of the imported oil is uniformly distributed in interval from 7.5 to 9.5 thousand barrels. To estimate the oil price, let’s analyze the historical market data.
Selection of data
To get the historical prices, activate the ‘Risk Kit Data’ toolbar (Figure 1) and click ‘Browse Historical Data’. The add-in then opens the data task pane.
Figure 7. Data task pane.
The data task pane allows to browse historical statistics from different data sources, perform searches, filter and select data and load the data to Excel.
To perform a search, activate the Search tab (Figure 8), enter the search query in the text field and press ENTER. In our example, we look for ‘crude oil’. Note that depending on the search query, the operation may take a few minutes.
Figure 8. Searching the data.
It is also possible to narrow down the search by excluding the results for some data sources. To do so, click ‘Filter Data Sources’ and clear the checkboxes against those data sources, that you want to exclude.
Figure 9. Filtering data sources.
We will use the historical prices of Brent crude oil for the estimations. Double click the ‘Crude Oil Prices: Brent – Europe’ table in the list to load the historical data. You may notice that there are four similar tables in the search results. The difference between these tables is in the frequency of the data. We use the first table with the daily frequency which provides prices for business days.
Once double clicked, the add-in activates the ‘Data’ tab.
Figure 10. Loaded data series.
In this example, we use the Federal Reserve Economic Data that has only one data series per table. The other data sources may have thousands of data series per table. Use the filters to reduce the number of data series and to specify the time window of the observations (Figure 12).
Figure 11. Oil price between 2011 and 2021
Oil prices were quite volatile in the last decade covering a range between 9.12 USD per barrel on 21 April 2020 to 128.14 on 13 March 2012. Therefore, to get a more homogenous sample that is representative for the current market situation we set the time window from 21 February 2015 to 21 February 2021.
Figure 12. Filtering data series.
Click ‘Apply Filter’ to filter the data series.
If you place the mouse cursor over the table name, you will see the detailed description in the tooltip. Notice the units of the data.
Figure 13. Table description.
To export the data to the worksheet, click ‘Export Data Series’. The tool gives two options here. The data can be exported either as a cell-function or as raw values. The cell-function is preferable because it gives more control on the data. In particular, it is possible to order the observations by date, get multiple data series at once, change the time window afterwards etc.
To output the series ID in the first row and the dates in the first column, activate the respective checkboxes.
Figure 14. Exporting the data.
The add-in automatically fills the necessary number of rows and columns.
Figure 15. Example of the DData cell-function.
To update the parameters of the function, either edit the function in Excel’s formula bar or use the Function dialog of Risk Kit Data. To do so, activate a cell where you want to place the function and click ‘Insert Function’ on the Risk Kit Data toolbar (Figure 1). In this particular example, we order the observations descending by date to have the most recent date at a stable position in the worksheet. This is often helpful since forecasts later on are rooted in the most recent known value of a series.
To do that, we set the ‘DatesInIncreasingOrder’ argument of the function to FALSE.
Figure 16. Updating the DData function using the Excel Function dialog.
Since the oil prices are in U.S. dollars, but we are interested in euros, the next step is to get the historical exchange rates and convert the prices.
To do so we stay with Federal Reserve Economic Data (FRED) to have one data provider for all time-series.
In the table of contents of the Data task pane, we choose FRED as the data source and then go to ‘Money, Banking & Finance’ – ‘Exchange Rates’ and select ‘Daily Rates’ to have a consistent periodicity with the oil price data. There we select ‘US / EUR Foreign Exchange Rate’ by double-click on the entry.
Figure 17. Browsing data tables and sources
To match the time-window for oil prices, we set the filter accordingly and import the data into our worksheet as an array function. Note that the time window stated on one of the previous steps is automatically applied to the newly selected data series.
Figure 18. Filtering the target time-window
Finally, we select a 1-year-EUR-interest rate accordingly. From the Data task pane, we choose ‘FRED’ – ‘Money, Banking & Finance’ – ‘Interest Rates’ – ‘Interest Rate Swaps’ and by double-click select the 1-year tenor based on EUR2. We filter the target time-window as for the previous series and insert the data as an array formula into the worksheet.
Figure 19. Selecting swap rates from FRED
Note that the ‘From’-date and ‘To’-date as borders of the time-window for the data selection can be defined by reference. This provides an easy way to create dynamic models that can be updated easily. Also, the end date of the series is optional and therefore can be omitted. In this case the function returns the most recent data available.
In the example below, reference dates are defined on a central worksheet ‘PARAMS’ and then quoted in the formulas.
Figure 20. Dynamic link to dates
Synchronization of time-series
To synchronize the oil prices and exchange rates as well as the interest rates by date, we use the DDataSync function that returns a union of dates in the first column and the data series in subsequent columns. To employ the function, click ‘Insert Function’ on the toolbar (Figure 1). In the opened dialog select the DDataSync function and click OK.
Figure 21. Risk Kit Data Functions dialog.
The function takes the ranges in the first argument. Use the Excel argument separator to delimit the ranges.