Change of Market Data: Linear Regression

Use Case

The reference rate decreases from 4.5% to 4.2%.

Calculation

You would now like to simulate the effects on the following balance sheet item:

Business figure	Account number	Amount
Business figure	Account number	31/12/2019	31/12/2020	31/12/2021	31/12/2022	31/12/2023
Receivables from customers	10003	37,000.00	41,000.00	44,000.00	46,000.00	47,600.00

In this context, the balance sheet item ‘Receivables from customers’ is to be adjusted using a linear regression with the reference rate as the independent variable.

The calculation can be called up using the Linear Regression function in the Control File as follows:

CODE

[10003] = Linear regression in relation to reference rate

Calculation Data

To perform the linear regression, the following data is provided:

in sheet Input Files | HISTORICAL_VALUES

Parameter	Date	Value
Reference rate	31/12/2019	3.5%
Reference rate	31/12/2020	3.7%
Reference rate	31/12/2021	3.8%
Reference rate	31/12/2022	4.1%
Reference rate	31/12/2023	4.5%

in sheet Input Files | FORECASTS

Parameter	Date	Value
Reference rate	31/12/2024	4.2%

Result

A simple linear regression is performed with the following values:

historical values of the balance sheet item ‘Receivables from customers’ as the dependent variable:
(37,000.00, 41,000.00, 44,000.00, 46,000.00, 47,600.00)
historical values of the reference rate as the independent variable:
(3.5%, 3.7%, 3.8%, 4.1%, 4.5%)

result of the regression:

intercept: 4,126.32
coefficient of the independent variable: 994,736.84

calculation for 31/12/2024:

official forecast of the reference rate: 4.2%
new value of the balance sheet item ‘Receivables from customers’:
4,126.32 + 994,736.84 * 4.2% = 45,905.26

Variant

In addition to the above change of the reference rate, the increase in the unemployment rate from 6.0% to 6.4% should also be taken into account.

In this case, the calculation can be called up using the Linear Regression function in the Control File as follows:

CODE

[10003] = Linear regression in relation to reference rate and unemployment rate

To perform the linear regression, additionally the following data is provided:

in sheet Input Files | HISTORICAL_VALUES

Parameter	Date	Value
Unemployment rate	31/12/2019	5.5%
Unemployment rate	31/12/2020	5.2%
Unemployment rate	31/12/2021	5.8%
Unemployment rate	31/12/2022	6.2%
Unemployment rate	31/12/2023	6.0%

in sheet Input Files | FORECASTS

Parameter	Date	Value
Unemployment rate	31/12/2024	6.4%

Now, a multiple linear regression is performed with the following values:

historical values of the balance sheet item ‘Receivables from customers’ as the dependent variable:
(37,000.00, 41,000.00, 44,000.00, 46,000.00, 47,600.00)
historical values of the reference rate as the first independent variable:
(3.5%, 3.7%, 3.8%, 4.1%, 4.5%)
historical values of the unemployment rate as the second independent variable:
(5.5%, 5.2%, 5.8%, 6.2%, 6.0%)

result of the regression:

intercept: -2,634.42
coefficient of the first independent variable: 821,603.54
coefficient of the second independent variable: 236,020.29

calculation for 31/12/2024:

official forecast of the reference rate: 4.2%
official forecast of the unemployment rate: 6.4%
new values of the balance sheet item ‘Receivables from customers’:
-2,634.42 + 821,603.54 * 4.2% + 236,020.29 * 6.4% = 46,978.22