Arbor City Community Foundation (B) Case Solution And Analysis, HBR Case Study Solution & Analysis of Harvard Case Studies

Arbor City Community Foundation (B) Case Study Solution

Introduction

This case revolves around the investment portfolio of the (Arbor City Foundation). This organization considers two portfolios the previous and the new portfolio with the addition of new assets(Karl Schmedders, 2011). All the calculations and analysis used the new portfolio returns and data. The organization worked on asset management with its team to identify and calculate the total annual return in the year 2006 in the overall portfolio. Now they look for the allocation of each asset to identify the best asset with high return and low risk. For this allocation, different calculations and analyses are done, which include (risk portfolio, rebalancing analysis, and marginal risk analysis) to get accurate results from assets having low risk and getting maximum return. However,the organization and its board members are worried about the new asset (CAT), which include in the portfolio and the overall case mainly focus on this asset allocation, risk, and returns.

Question 1

In the first part of the case calculate the daily and monthly standard deviation from the given historical data of each asset in the portfolio. The calculation for the standard deviation is analyzed for both the previous and new portfolios. Standard deviation helps to understand how far each value for the asset is from its mean(Shi, 2020). It indicates the ratio of risk related to each asset in the portfolio of the organization. In this analysis, various assets are included by including the index (SPY). All the returns for each asset are given daily for the whole year 2006. The standard deviation for the daily returns is simply calculated by using the formula of (St.Dev) in excel by selecting the overall range of returns for each asset. Now, the monthly standard deviation is simply calculated by dividing 12 by the value of the daily standard deviation.

The average daily return, monthly return, and variance for each asset are also calculated in part one to get the values for further analysis in other parts. These are calculated by using the average and variance formulas in excel.

Question 2

In this part, the values of daily return in the new portfolio for each asset are plotted by using (Column Chart). It allocates all the assets in plotting, which determines the return on each day with other assets' return comparison. In addition, there is also a chart plot for the standard deviation of daily return for each asset, which provides graphical data of the portfolio.

Question 3

In part 3, there are two analyses calculated (Skewness and Kurtosis) for new portfolio daily returns and monthly returns. To measure the performance of each asset reading the confidence level is (95%) by using the normal distribution of returns.

Part (a)

Firstly, perform the analysis for daily returns by using the overall returns of each asset given in the data. For the analysis using the function of (Data Analysis) that presents descriptive statistics. It ultimately creates an overall analysis after selecting the input and output range. This analysis includes various values for each asset (mean, median, mode, St.Dev). For the (CAT) the value of skewness is (-2.070 and kurtosis is (17.62). Now move towards the estimation for monthly returns and this firstly calculates the monthly returns by using the daily return data for each month. Monthly returns calculate by using the average formula in excel to get monthly returns for each asset. Now the estimation of skewness and kurtosis are calculated by using the same step used for daily returns. The value of skewness is (1.355) and kurtosis (1.908) for the (CAT) asset.

This estimation presents that the value of kurtosis in daily returns for (CAT) is not appropriate because a good range of kurtosis is (-10 to 10) but the values of skewness are acceptable in both daily and monthly returns for (CAT). All the other asset estimations present appropriate and acceptable values because they all are under the good range.

Question 4

Part (a)

In this part, there is an estimation for the daily returns in the new portfolio by using the confidence level at (95%) and (99%). The approach used for the estimation is (VARs) risk matrix value at risk is calculated by using two different approaches (historical and parametric approach)(Khindanova, 2019). Firstly, calculate the (VAR 95) and (VAR 99) by using the historical approach. For the analysis first sorted the overall daily returns from loss to gain and then calculate the total number of trading days by using the count function and the total days are (251). For calculating (VAR 95) multiply (0.05) bythe total count and the value is (12.55) and for (VAR 99) multiply (0.01) bythe total count and the value is (2.51). The VAR model is used to identify the loss and profit probability for the overall portfolio. (VAR 95) presents that there is no probability to generate a loss of more than 4% and the same for (VAR 99) no loss of more than 1% (See Excel sheet Q: 4).....................

Arbor City Community Foundation (B) Case Study Solution