Exp19_Excel_Ch08_ML1_Portfolio_Analysis

Project Description:

You are a financial advisor, and a client would like you to complete an analysis of his portfolio. As part of the analysis you will calculate basic descriptive statistics with the Analysis ToolPak, calculate standard devi-ation and variance in value, calculate correlation between asset age and value, and create a stock forecast sheet.

Steps to Perform:

Step Instructions

1 Start Excel. Download and open the file named Exp19_Excel_Ch08_ML1_HW_PortfolioAnalysis.xlsx. Grader has automatically added your last name to the beginning of the filename.

2 Use the STDEV.S function to calculate the standard deviation between the current values of all commodities in cell G6.

3 Use the VAR.S function to calculate the variance between the current values of all commodities in cell G9.

4 Ensure the Analysis ToolPak is loaded. Create a descriptive statistics summary based on the current value of investments in column E. Display the output in cell G12.

5 Format the mean, median, mode, minimum, maximum, and sum in the report as Accounting Number Format. Resize the column as needed.

6 Use the FREQUENCY function to calculate the frequency distribution of commodity values based on the values located in the range I6:I10.

7 Click the Trend worksheet and use the CORREL function to calculate the correlation of purchase price and current value listed in the range C3:D9.

8 Create a Forecast Sheet displaying a forecast of purchase price through 1/1/2025. Name the worksheet Forecast_2025. Mac users, insert a new sheet named Forecast_2025. Copy the range B2:C9 on the Trend worksheet and paste it into cell A1 on the Forecast_2025 sheet. Type Forecast(Purchase Price) in cell C1 and 1/1/2025 in cell A9. In cell C9, enter the formula =FORECAST.ETS(A9,B2:B8,A2:A8).

9 Save and close EXP19_Excel_Ch08_ML1_HW_PortfolioAnalysis. Exit Excel. Submit the file as directed.