AFIN270: Finance Assignment - Risk, Statistics, and Data Analysis

This document presents a comprehensive solution to an AFIN270 finance assignment. The solution addresses a range of financial concepts including probability calculations related to guard duties in Camelot, where probabilities are assigned to different roles within the city. It also provides an analysis of financial returns using a provided dataset, calculating correlation coefficients and interpreting their significance. The solution further incorporates Monte Carlo simulation techniques to generate random numbers within Excel. Additionally, the assignment examines linear regression models, including the interpretation of coefficients and the analysis of residuals, using advertising spending and sales data. Finally, the document explores the application of the Generalized Extreme Value (GEV) distribution to calculate probabilities associated with financial risk, and it includes all the excel outputs and calculations.

Question 1
Q1a)
Royal Battlement Peace
Royal family 0.7 0.3 0
Battlement 0.4 0 0.6
Peace 0.1 0.4 0.5
Q1b)
P(Not reassigned to patrol battlement) = probability patrol royal family + probability patrol peace
= 1/3*0.1 + 1/3* 0.5 = 0.2
Q1c)
Probability Royal family 0.7 0.3 0
Bet $5 $2 $1
Expected payoff = 0.7 *$5 + 0.3*$2 + 0*$1 = $4.1

Question 2
Q2a)
Q2b)
From the excel
Return A
Return
B
Return
A 1
Return
B 0.451917 1
Q2c)
The positive coefficient of 0.451917 indicates a moderate direct connection between the Return A
and Return B, therefore, the more the moderate shares are return in back bank A, the more the
moderate shares are also return in bank B.
Q2d)
The features of the current data to be preserve include the dates
The approach used is called Monte Carlo simulation
To generate the random numbers in excel we use the two functions
1. RAND ( ): generating random numbers from 0 and 1
2. RANDBETWEEN(a, b): generating random numbers from the integers between a and b
-0.08
-0.06
-0.04
-0.02
0
0.02
0.04
-0.08 -0.06 -0.04 -0.02 0 0.02 0.04 0.06
Return B
Return A

To avoid the random numbers generated from changing on excel we enter RAND ( ) on the bar
formula and press the key function F9. Similarly, copy the random numbers generated using Ctrl-C
and paste on the same location.
RANDBETWEEN would only generate integer values.

Question 3
Q3a)
Q3b)
From the excel
Business Advertising Sales
Business 1
Advertising 0.997503 1
Sales 0.884906 0.882872 1
Q3c)
𝛼 is the Si-intercept, this is the value predicted when the value of Ai = 0
We expect the annual sales Si for business i, to be 1.5765 with no annual spending for
business i.
Since Ai is a continuous variable,and 𝛽 would represent the difference in the value of Si
predicted for a different of one unit of Ai, this would mean that if Ai would differ by a one
unit then Si would differ by an average unit of Ai. Therefore, in this case an annual
advertising spending for business i, would on average be equivalent to 2.5963 annual sal
for the prior an annual advertising spending.
Q3d)
The sum of the residuals is always zero for the line of best fit
The mean residual value is equivalent to zero = sum/n = 0/n = 0
0
5
10
15
20
25
30
35
0 2 4 6 8 10 12
Sales
Advertising

The variance for error term =
From the graph
The dots are tightly adhered to the zero baselines; therefore the regression is accurately reasonable
-15
-10
-5
0
5
10
0 2 4 6 8 10 12
Residuals errors

Question 4
Q4)
Solution
The cumulative distribution function of a GEV is
F(R; [ ( )]
F(R; 0.03, 0.02, 0.015) = [ ( )]
= [ ( )]
b) Pr(R > 10%)
= 1 – p(
= 1 - [ ( )]
= 1 – 0.3679 = 0.6321