Exp19_Excel_Ch08_ML1_Portfolio_Analysis
Solutions Aplus
Ahmed_Exp19_Excel_Ch08_ML1_Portfolio_Analysis.zipExp19_Excel_Ch08_ML1_Portfolio_Analysis_Instructions.docx
Grader - Instructions Excel 2019 Project
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 |
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:I10. |
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:D9. |
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 |
Created On: 03/03/2022 1 Exp19_Excel_Ch08_ML1 - Portfolio Analysis 1.2
Ahmed_EXP19_Excel_CH08_ML1_HW_PortfolioAnalysis.xlsx
Portfolio
| Account Number | Last Name | First Name | ||||||
| 367459 | Hyat | Kato | ||||||
| Purchase Date | Commodity | Purchase Price | Current Value | Standard Deviation | Frequency of Occurrence | |||
| 8/4/14 | CD | $ 20.00 | $ 65.00 | $ 20.00 | ||||
| 12/22/14 | Stock | $ 17.00 | $ 40.00 | $ 40.00 | ||||
| 4/3/15 | Bond | $ 10.00 | $ 35.00 | Variance | $ 60.00 | |||
| 8/13/15 | Bond | $ 7.00 | $ 45.00 | $ 80.00 | ||||
| 10/20/15 | Stock | $ 17.00 | $ 18.02 | $ 100.00 | ||||
| 12/11/15 | CD | $ 20.00 | $ 25.65 | |||||
| 9/1/16 | CD | $ 10.00 | $ 12.50 | |||||
| 11/28/16 | Bond | $ 24.00 | $ 28.00 | |||||
| 8/10/17 | CD | $ 25.00 | $ 27.50 | |||||
| 8/12/17 | Bond | $ 6.00 | $ 10.00 | |||||
| 11/12/17 | Bond | $ 9.00 | $ 10.54 | |||||
| 11/22/17 | CD | $ 24.00 | $ 30.00 | |||||
| 3/2/18 | Bond | $ 19.00 | $ 32.12 | |||||
| 5/21/18 | Stock | $ 14.00 | $ 75.00 | |||||
| 9/7/18 | Stock | $ 18.00 | $ 60.00 | |||||
| 7/22/19 | CD | $ 9.00 | $ 13.50 | |||||
| 3/6/20 | Stock | $ 15.00 | $ 110.00 | |||||
| 7/13/20 | Stock | $ 20.00 | $ 75.00 | |||||
| 10/25/20 | CD | $ 15.00 | $ 15.90 |
Trend
| Date | Purchase Price | Current Value | Correlation | ||
| 1/1/90 | $ 5.00 | $ 6.50 | |||
| 1/1/95 | $ 10.00 | $ 11.00 | |||
| 1/1/00 | $ 15.00 | $ 13.75 | |||
| 1/1/05 | $ 20.00 | $ 21.25 | |||
| 1/1/10 | $ 25.00 | $ 26.75 | |||
| 1/1/15 | $ 30.00 | $ 35.00 | |||
| 1/1/20 | $ 35.00 | $ 36.45 |
