Exp19_Excel_Ch08_ML1_Portfolio_Analysis

Step

Instructions

Points Possible

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.

0

2

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

14

3

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

14

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.

16

5

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

10

6

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

14

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:D10.

14

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).

18

9

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

0

Total Points

100