Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I have attatched the document for my finance assignment and will greatly appreciate it, if someone can help me out. I have attatched how the
I have attatched the document for my finance assignment and will greatly appreciate it, if someone can help me out. I have attatched how the assignment is suppose to look like with my professors example.
STOCK PORTFOLIO ANALYSIS Transaction Sources & Uses Sources (Initial Capital - Nov 2, 2015) Margin Loan 50,000 Equity 50,000 Total 100,000 50.0% 50.0% 100.0% Interest Rate 7.00% Uses Stocks Cash Total Anount $ 88,500 $ 11,500 $ 100,000 % Interest Rate 88.5% 11.5% 1.50% 100.0% SUMMARY Portfolio Return Analysis Summary Equity Cash Flow Summary Portfolio Value (incl. Cash) Portflio Appreciation/(Depreciation) - P Cummulative Appreciaiton S&P 500 (Market - M) Portfolio Returns/Analysis HPR (Unlevered) - Risky Portfolio HPR (Levered) Average Monthly Returns (N-1) Standard Deviation Sharpe Ratio 2-Nov-15 1-Dec-15 (50,000) $ (27) $ $ 99,473 $ -0.527% -0.527% 0.091% $ 4-Jan-16 1-Feb-16 1,593 $ 2,815 $ 105,065 $ 108,080 $ 5.622% 2.869% 5.095% 7.964% 2.180% 3.111% Average Monthly Returns (n-1) Standard Deviation Sharpe Ratio Monthly Change % M (X - Avg X) PxM M^2 -0.03252 0.02897 0.00144 -0.00507 0.00718 -0.01993 0.00096 0.01027 -0.00791 0.01661 0.00065 0.00003 0.00001 0.00004 0.00012 0.00040 0.00000 0.00011 0.00006 0.00028 0.00085 0.00084 Beta (Slope) = 1.0096 S&P 500 2 3 4 2.08% 1.45% 1.35x P (Y - Avg Y) Portfolio 1 1-Apr-15 2-May-15 (97) $ 64,608 114,280 3.443% 13.625% 3.745% S&P Return/Analysis 13.34% 37.78% 2.72% 2.22% 1.17x Portfolio Vs S&P 500 0.06 0.05 0.04 0.03 0.02 0.01 0 -0.01 1-Mar-16 (3) $ 110,477 $ 2.218% 10.182% 1.293% 5 Months Regression Statistics Regression Statistics Multiple R 0.6589 R Square 0.43420968 Adjusted R Square -1.6667 Standard Error 0.0193 Observations 1 ANOVA df 5 3 8 SS 0.0009 0.0011 0.0020 MS 0.0002 0.0004 F 2.3023 Coefficients Standard Error t Stat P-value Lower 95% 0.0062 1.0096 0.0163 0.6654 0.3802 1.5173 0.7291 0.2265 -0.0458 -1.1079 Regression Residual Total Intercept X Variable 4 X Variable 5 MONTHLY CASH FLOW ANALYSIS IRR per month Beginning Cash Plus Dividends Buy / Sell Stock Less Debt (Principal) Less Debt (Interest Payments) Interest Income Plus Cash Total Cash Flow Cash Deposit Ending Cash Cash % of Total Value 0 2-Nov-15 50,000 $ $ $ 5.71% $ $ 0 $ (88,500) $ 50,000 $ $ (11,500) (50,000) $ 11,500 $ 11,500 11.5% 1 1-Dec-15 11,500 250 - Upper 95% Lower 95.0% Upper 95.0% -1.3756 1.3881 0.0582 -0.0458 0.0582 3.1270 -1.1079 3.1270 2 4-Jan-16 11,473 $ $ (292) $ 14 $ (27) $ $ 11,473 11.5% 1 120 1,750 3 1-Feb-16 13,065 $ $ (292) $ 14 $ 1,593 $ $ 13,065 12.4% 390 2,700 4 1-Mar-16 15,880 $ $ (292) $ 16 $ 2,815 269 - 5 1-Apr-15 15,877 $ $ (292) $ 20 $ $ (3) $ $ 15,880 14.7% $ 15,877 14.4% 175 - $ $ $ (292) $ 20 $ $ (97) $ 6 2-May-15 15,780 99,100 (50,000) (292) 20 15,780 64,608 $ (15,780) 15,780 64,608 13.8% 100.0% Stock Prices 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Companies ABC BCD CDE DEF EFG FGH GHI HIK IKL KLM LMN MNO NOP OPQ PQR S&P Industry Chemicals Industrial Publishing Hospitality TV/Cable Techonlogy Service Retail Pharmaceutical Healthcare Hospitality TV/Cable Techonlogy Retail Chemicals Index Number of Shares Own Companies 1 ABC 2 BCD 3 CDE 4 DEF 5 EFG 6 FGH 7 GHI 8 HIK 9 IKL 10 KLM 11 LMN 12 MNO 13 NOP 14 OPQ 15 PQR Industry Chemicals Industrial Publishing Hospitality TV/Cable Techonlogy Service Retail Pharmaceutical Healthcare Hospitality TV/Cable Techonlogy Retail Chemicals Bought ./ Sold - Shares Companies 1 ABC 2 BCD 3 CDE 4 DEF 5 EFG 6 FGH 7 GHI 8 HIK 9 IKL 10 KLM 11 LMN 12 MNO 13 NOP 14 OPQ 15 PQR Industry Chemicals Industrial Publishing Hospitality TV/Cable Techonlogy Service Retail Pharmaceutical Healthcare Hospitality TV/Cable Techonlogy Retail Chemicals 0 2-Nov-15 23.00 12.00 18.00 40.00 52.00 31.00 15.00 8.00 15.00 25.00 1 1-Dec-15 24.00 10.00 19.00 42.00 60.00 20.00 16.00 9.50 13.00 26.00 30.00 20.00 52.00 2 4-Jan-16 22.50 12.00 20.00 48.00 60.00 25.00 17.00 10.50 12.00 26.00 32.00 19.00 55.00 11.00 3 1-Feb-16 25.00 12.00 19.00 49.00 60.00 26.00 18.00 11.00 14.00 26.00 35.00 18.00 56.00 11.00 4 1-Mar-16 26.00 15.00 22.00 51.00 62.00 20.00 19.00 11.50 15.00 26.00 42.00 18.00 58.00 11.00 20.00 5 1-Apr-15 28.00 18.00 20.00 55.00 64.00 22.00 19.00 12.00 18.00 26.00 44.00 16.00 59.00 11.00 22.00 1,100.00 1,101.00 1,125.00 1,160.00 1,175.00 1,219.00 0 2-Nov-15 500 500 400 300 200 400 600 1000 300 300 1 1-Dec-15 500 500 400 300 200 400 600 1000 300 300 0 0 0 0 0 2 4-Jan-16 200 200 300 300 200 400 600 1000 300 300 100 100 100 0 0 3 1-Feb-16 200 200 300 200 200 400 600 1000 300 300 100 100 100 200 0 4 1-Mar-16 200 200 300 200 200 100 600 1000 300 300 100 100 100 200 300 5 1-Apr-15 200 200 300 200 200 100 600 1000 300 300 100 100 100 200 300 6 2-May-15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2-Nov-15 1 1-Dec-15 2 4-Jan-16 -300 -300 -100 3 1-Feb-16 4 1-Mar-16 5 1-Apr-15 6 2-May-15 -200 -200 -300 -200 -200 -100 -600 -1000 -300 -300 -100 -100 -100 -200 -300 -100 -300 100 100 100 200 300 2 6 2-May-15 32.00 18.00 18.00 54.00 65.00 25.00 18.00 12.50 20.00 20.00 45.00 18.00 65.00 12.00 23.00 Bought ./ Sold - Shares Companies 1 ABC 2 BCD 3 CDE 4 DEF 5 EFG 6 FGH 7 GHI 8 HIK 9 IKL 10 KLM 11 LMN 12 MNO 13 NOP 14 OPQ 15 PQR Total Industry Chemicals Industrial Publishing Hospitality TV/Cable Techonlogy Service Retail Pharmaceutical Healthcare Hospitality TV/Cable Techonlogy Retail Chemicals $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0 2-Nov-15 - $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0 2-Nov-15 11,500 6,000 7,200 12,000 10,400 12,400 9,000 8,000 4,500 7,500 88,500 11,500 100,000 Total Value 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Companies ABC BCD CDE DEF EFG FGH GHI HIK IKL KLM LMN MNO NOP OPQ PQR Total Value Cash Total Value Dividends Paid per Share Companies 1 ABC 2 BCD 3 CDE 4 DEF 5 EFG 6 FGH 7 GHI 8 HIK 9 IKL 10 KLM 11 LMN 12 MNO 13 NOP 14 OPQ 15 PQR Industry Chemicals Industrial Publishing Hospitality TV/Cable Techonlogy Service Retail Pharmaceutical Healthcare Hospitality TV/Cable Techonlogy Retail Chemicals Industry Chemicals Industrial Publishing Hospitality TV/Cable Techonlogy Service Retail Pharmaceutical Healthcare Hospitality TV/Cable Techonlogy Retail Chemicals 0 2-Nov-15 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1 1-Dec-15 - $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1 1-Dec-15 12,000 5,000 7,600 12,600 12,000 8,000 9,600 9,500 3,900 7,800 88,000 11,473 99,473 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2 4-Jan-16 6,750 3,600 2,000 (3,200) (1,900) (5,500) 1,750 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2 4-Jan-16 4,500 2,400 6,000 14,400 12,000 10,000 10,200 10,500 3,600 7,800 3,200 1,900 5,500 92,000 13,065 105,065 1 1-Dec-15 $ 0.20 $ 0.30 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 3 1-Feb-16 4,900 (2,200) 2,700 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 3 1-Feb-16 5,000 2,400 5,700 9,800 12,000 10,400 10,800 11,000 4,200 7,800 3,500 1,800 5,600 2,200 92,200 15,880 108,080 2 4-Jan-16 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 4 1-Mar-16 6,000 (6,000) - $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 4 1-Mar-16 5,200 3,000 6,600 10,200 12,400 2,000 11,400 11,500 4,500 7,800 4,200 1,800 5,800 2,200 6,000 94,600 15,877 110,477 3 1-Feb-16 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 5 1-Apr-15 - 6 2-May-15 $ 6,400 $ 3,600 $ 5,400 $ 10,800 $ 13,000 $ 2,500 $ 10,800 $ 12,500 $ 6,000 $ 6,000 $ 4,500 $ 1,800 $ 6,500 $ 2,400 $ 6,900 $ 99,100 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 5 1-Apr-15 5,600 3,600 6,000 11,000 12,800 2,200 11,400 12,000 5,400 7,800 4,400 1,600 5,900 2,200 6,600 98,500 15,780 114,280 6 2-May-15 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 64,608 $ 64,608 6 2-May-15 $ $ 5 1-Apr-15 0.20 0.30 4 1-Mar-16 0.40 $ 0.75 $ 0.40 $ $ 0.20 0.23 $ 3 0.25 Total Cash Dividends Companies 1 ABC 2 BCD 3 CDE 4 DEF 5 EFG 6 FGH 7 GHI 8 HIK 9 IKL 10 KLM 11 LMN 12 MNO 13 NOP 14 OPQ 15 PQR 0 2-Nov-15 Industry Chemicals Industrial Publishing Hospitality TV/Cable Techonlogy Service Retail Pharmaceutical Healthcare Hospitality TV/Cable Techonlogy Retail Chemicals Total $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1 1-Dec-15 100 150 250 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2 4-Jan-16 120 120 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 3 1-Feb-16 150 240 390 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 4 1-Mar-16 200 69 269 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 5 1-Apr-15 40 60 75 175 6 2-May-15 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ - Diversification Factors 2-Nov-15 1-Dec-15 4-Jan-16 1-Feb-16 1-Mar-16 1-Apr-15 13.0% 8.5% 13.6% 6.8% 5.1% 8.1% 9.0% 10.2% 14.0% 11.8% 100.0% 13.6% 8.9% 14.3% 5.7% 4.4% 8.6% 10.8% 10.9% 9.1% 13.6% 100.0% 4.9% 8.5% 19.1% 2.6% 3.9% 6.5% 11.4% 11.1% 16.8% 15.1% 100.0% 5.4% 8.5% 14.4% 2.6% 4.6% 6.2% 14.3% 11.7% 17.4% 15.0% 100.0% 11.8% 8.2% 15.2% 3.2% 4.8% 7.0% 14.5% 12.1% 8.2% 15.0% 100.0% 12.4% 7.9% 15.6% 3.7% 5.5% 6.1% 14.4% 11.6% 8.2% 14.6% 100.0% Industry Concetration Chemicals Healthcare Hospitality Industrial Pharmaceutical Publishing Retail Service Techonlogy TV/Cable Total Total Company Concetration ABC BCD CDE DEF EFG FGH GHI HIK IKL KLM LMN MNO NOP OPQ PQR $ 88,500 13.0% 6.8% 8.1% 13.6% 11.8% 14.0% 10.2% 9.0% 5.1% 8.5% 0.0% 0.0% 0.0% 0.0% 0.0% 100.0% $ 88,000 $ 13.6% 5.7% 8.6% 14.3% 13.6% 9.1% 10.9% 10.8% 4.4% 8.9% 0.0% 0.0% 0.0% 0.0% 0.0% 100.0% 92,000 4.9% 2.6% 6.5% 15.7% 13.0% 10.9% 11.1% 11.4% 3.9% 8.5% 3.5% 2.1% 6.0% 0.0% 0.0% 100.0% 4 $ 92,200 5.4% 2.6% 6.2% 10.6% 13.0% 11.3% 11.7% 11.9% 4.6% 8.5% 3.8% 2.0% 6.1% 2.4% 0.0% 100.0% $ 94,600 5.5% 3.2% 7.0% 10.8% 13.1% 2.1% 12.1% 12.2% 4.8% 8.2% 4.4% 1.9% 6.1% 2.3% 6.3% 100.0% $ 98,500 5.7% 3.7% 6.1% 11.2% 13.0% 2.2% 11.6% 12.2% 5.5% 7.9% 4.5% 1.6% 6.0% 2.2% 6.7% 100.0% $ - Project Description Construct a portfolio of Stocks and Cash (Excel) using the following information: 1. 2. 3. 4. 5. 6. Initial Capital $50,000 (Equity) Obtain a loan (up to 50% Margin) for 7.0% interest per annum. Starting Date (Nov 2, 2015) Value Date (May 2, 2016) Maintain Diversification discipline* Always maintain at least 10% Cash at all times. Cash interest income at 1.5% per annum. 7. Trading stocks at least 5 times during this period (5 initial stock positions need to be replaced during this period) 8. Assume no trading costs or any additional expenses (except interest on the margin loan) You Spreadsheet should include the following: 1. Initial Transaction Sources and Uses (Nov 2, 2015) 2. List of stocks (Symbols) 3. Business Description and Industry categorization for each stock 4. Monthly Cash Flow which will include any dividends, gains and losses on trades, interest payments, interest income of cash balance. 5. Overall monthly performance (including a graph) 6. S&P 500 Index on Nov 2, Dec 1, 2015, Jan 4, Feb 1, Mar 1, Apr 1 and May 2, 2016 At Value Date calculate the following: 1. Total Portfolio HPR 2. Total Portfolio Standard Deviation of average returns. 3. Portfolio performance as compared (including a graph) to S&P500 index during this period - (Beta Coefficient calculation, Regression Analysis between portfolio and S&P500). Suggested website to use: http://finance.yahoo.com *Diversification Discipline: No less than 10 stocks in the portfolio at all times Each stock value cannot represent more than 20% of the total portfolio. Each industry value cannot represent more than 25% of the total portfolio 1 Across 8 different industry sectors and one of the industry sector should have at least 2 companies. 2Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started