Answered step by step
Verified Expert Solution
Link Copied!

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.

image text in transcribed 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. 2

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Microeconomics

Authors: Glenn Hubbard, Anthony O'Brien

7th Edition

0134737504, 978-0134737508

More Books

Students also viewed these Finance questions

Question

1. What will happen in the future

Answered: 1 week ago

Question

3. Avoid making mistakes when reaching our goals

Answered: 1 week ago