Need help with finance class for a final project milestone. Looking for help solving the formula's not the written part.

FIN 340 Milestone Two Guidelines and Rubric Overview: In the first milestone, you prepared a client analysis. In this milestone, you will create the stock analysis and portfolio development sections of your final project. First, you must understand what you are investing in. You have to know the underlying characteristics of the investment. What type of asset is it? What type of security? How is it priced? What are the expected cash flows? Who are the typical investors and what are their typical motives? If you do not understand the answers to those questions, then the initial expectations you develop about the value and risk of the asset will be fundamentally flawed. This sets you up for missteps that can lead to underperforming your investment objectives. Second, you must be able to estimate the value of the asset. Valuation is about assessing the estimated cash flows of the asset. This is a key component of discerning absolute return potential and the differences between competing assets. It has a significant influence on the third step in the process as well. The third step is developing a thesis about an asset's expected return and the associated risk. This is accomplished by assessing your valuation estimates against the current market price and any developing economic or market dynamics that may impact your expected valuation or its pricing. The market is constantly changing, and these expectations need to be monitored on a regular basis to ensure they continue to correspond to the objectives you are trying to achieve. Finally, you must understand how the assets in a portfolio interact with one another. It is likely that you will not have just one investment, so any additional assets will impact the overall performance of the portfolio. You want to formulate a plan to add assets that, when combined together, will have the potential to meet your objectives. Putting all of these steps together into a consistent, thorough process will position you to better meet the investment objectives laid out at the beginning. Prompt: This milestone involves creating a draft of the stock analysis and portfolio development sections of the final project. Use the provided spreadsheet to calculate your portfolio's standard deviation. Specifically, the following critical elements must be addressed: II. Stock Analysis: In this section, you will select five stocks from the provided list and determine their values by applying an appropriate valuation model from the following options: price to multiple model (earning or sales), dividend valuation model, or free cash flow model. A. Determine the value of each stock by using an appropriate model based on the characteristics provided for each stock; use each model at least once. B. Provide a rationale for the stock valuation method you chose for each stock. Cite specific information to support your decisions. C. Using the calculated valuation, the current market price, and historical performance, determine the expected return for each stock. III. Portfolio Development: In this section, you will develop a portfolio for both clients based on their risk tolerance, return objectives, and liquidity objectives. You will select appropriate assets from the provided list. A. For each client, develop a portfolio from the list of assets provided that is informed by your analysis of each client's objectives and (if applicable) the stock valuation you determined. B. For each portfolio, calculate the expected portfolio return using the CAPM (beta) model. C. For each portfolio, calculate the expected portfolio standard deviation. Rubric Guidelines for Submission: Your client analysis should a 4- to 6-page Microsoft Word document, double spaced, with 12-pt. Times New Roman font, one-inch margins, and citations cited in APA format. Note that your submission may be longer than 6 pages if you need to show numerous calculations and work. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Critical Elements Exemplary (100%) Stock Analysis: Determine Accurately determines the value of the Value each stock using an appropriate model based on the characteristics provided for each stock Stock Analysis: Stock Provides a rationale for the stock Valuation Method valuation method chosen for each stock, citing specific information to support decisions Stock Analysis: Expected Accurately determines the expected Return return for each stock based on the calculated valuation, current market price, and historical performance Portfolio Development: Develops portfolios from the lists of Develop a Portfolio assets provided that are informed by an analysis of clients' objectives Needs Improvement (75%) Not Evident (100%) Determines the value of each stock, but Does not determine the value of each determination contains inaccuracies, or model stock applied is not appropriate Provides a rationale for the stock valuation method chosen for each stock, but rationale is missing components or misaligned, or information cited is not relevant or nonexistent Determines the expected return for each stock based on the calculated valuation, current market price, and historical performance, but determination is missing components or contains inaccuracies Develops portfolios from the lists of assets provided that are informed by an analysis of clients' objectives, but portfolios are missing components or are illogical Value 17 Does not provide a rationale for the stock valuation method chosen for each stock 17 Does not determine the expected return of each stock 17 Does not develop portfolios for each client 17 Portfolio Development: Accurately calculates the expected Calculates the expected portfolio return using Expected Portfolio Return portfolio return for each portfolio using the CAPM model but does not calculate the CAPM model expected returns for both portfolios, or calculations contain inaccuracies Portfolio Development: Accurately calculates the expected Calculates the expected portfolio standard Expected Standard portfolio standard deviations for each deviations but does not calculate for both Deviation portfolio portfolios, or calculations contain inaccuracies Articulation of Submission has no major errors Submission has major errors related to Response related to citations, grammar, citations, grammar, spelling, syntax, or spelling, syntax, or organization organization that negatively impact readability and articulation of main ideas Does not calculate the expected portfolio returns using the CAPM model 13.5 Does not calculate the expected portfolio standard deviations 13.5 Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas Total 5 100% Instructions: 1. Insert portfolio weights in the yellow highlighted area. 2. Portfolio Standard Deviation is calculated below in grey. Standard Portfolio Deviation Weight x1 x2 8% 20.0% 0% 0 6% 13.0% 0% 0 13% 28.0% 0% 0 2% 16.0% 0% 0 6% 14.0% 0% 0 -6% 16.0% 0% 0 13% 32.0% 0% 0 18% 45.0% 0% 0 21% 37.0% 0% 0 10% 16.0% 0% 0 9% 11.0% 40% 0.001936 14% 18.0% 20% 0.001296 9% 15.0% 20% 0.0009 -1% 19.0% 0% 0 1% 1.0% 0% 0 4% 6.0% 0% 0 4% 13.5% 0% 0 7% 6.0% 20% 0.000144 8% 8.0% 0% 0 100% 0.004276 0.012975 Return IBM KO BMY ORCL MMM BAX BIG NFLX AKAM GE SPY IWM EFA EEM SHY IEF TLT LQD HYG Portfolio Standard Deviation 13.13% Correlation Matrix Security IBM KO IBM 1 KO 0.289 BMY 0.185 ORCL 0.589 MMM 0.524 BAX 0.368 BIG 0.215 NFLX 0.179 AKAM 0.255 GE 0.539 SPY 0.622 IWM 0.539 EFA 0.568 EEM 0.514 SHY -0.127 IEF -0.252 TLT -0.246 LQD 0.023 HYG 0.429 x2 IBM KO BMY ORCL MMM BAX BIG NFLX AKAM GE SPY IWM EFA EEM SHY IEF TLT LQD HYG 0.0130 IBM KO 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 BMY 0.289 1 0.162 0.316 0.447 0.283 0.184 0.187 0.194 0.393 0.52 0.359 0.444 0.491 0.002 0.012 0.021 0.135 0.417 0.185 0.162 1 0.262 0.242 0.247 0.155 0.143 0.117 0.283 0.348 0.313 0.264 0.198 -0.053 -0.147 -0.155 -0.08 0.197 ORCL MMM BAX BIG NFLX AKAM 0.589 0.524 0.368 0.215 0.179 0.255 0.316 0.447 0.283 0.184 0.187 0.194 0.262 0.242 0.247 0.155 0.143 0.117 1 0.535 0.432 0.249 0.311 0.404 0.535 1 0.425 0.231 0.135 0.228 0.432 0.425 1 0.086 0.265 0.257 0.249 0.231 0.086 1 0.092 0.187 0.311 0.135 0.265 0.092 1 0.225 0.404 0.228 0.257 0.187 0.225 1 0.5 0.672 0.415 0.259 0.254 0.372 0.739 0.703 0.569 0.376 0.372 0.479 0.636 0.585 0.476 0.483 0.317 0.417 0.664 0.617 0.516 0.275 0.284 0.437 0.628 0.586 0.487 0.268 0.314 0.412 -0.178 -0.136 -0.141 -0.054 -0.11 -0.193 -0.29 -0.218 -0.175 -0.092 -0.106 -0.21 -0.31 -0.249 -0.159 -0.074 -0.082 -0.203 -0.048 0.012 0.019 0.048 -0.052 -0.053 0.558 0.51 0.453 0.231 0.25 0.333 BMY ORCL MMM BAX BIG NFLX AKAM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 GE SPY 0.539 0.393 0.283 0.5 0.672 0.415 0.259 0.254 0.372 1 0.744 0.651 0.635 0.603 -0.229 -0.319 -0.32 -0.089 0.504 GE 0.622 0.52 0.348 0.739 0.703 0.569 0.376 0.372 0.479 0.744 1 0.877 0.857 0.807 -0.276 -0.392 -0.375 -0.054 0.742 IWM EFA EEM SHY IEF 0.539 0.568 0.514 -0.127 0.359 0.444 0.491 0.002 0.313 0.264 0.198 -0.053 0.636 0.664 0.628 -0.178 0.585 0.617 0.586 -0.136 0.476 0.516 0.487 -0.141 0.483 0.275 0.268 -0.054 0.317 0.284 0.314 -0.11 0.417 0.437 0.412 -0.193 0.651 0.635 0.603 -0.229 0.877 0.857 0.807 -0.276 1 0.74 0.688 -0.271 0.74 1 0.856 -0.245 0.688 0.856 1 -0.118 -0.271 -0.245 -0.118 1 -0.386 -0.391 -0.208 0.821 -0.365 -0.396 -0.205 0.668 -0.072 -0.045 0.11 0.668 0.66 0.722 0.776 -0.106 TLT -0.252 0.012 -0.147 -0.29 -0.218 -0.175 -0.092 -0.106 -0.21 -0.319 -0.392 -0.386 -0.391 -0.208 0.821 1 0.929 0.81 -0.194 LQD -0.246 0.021 -0.155 -0.31 -0.249 -0.159 -0.074 -0.082 -0.203 -0.32 -0.375 -0.365 -0.396 -0.205 0.668 0.929 1 0.814 -0.198 0.023 0.135 -0.08 -0.048 0.012 0.019 0.048 -0.052 -0.053 -0.089 -0.054 -0.072 -0.045 0.11 0.668 0.81 0.814 1 0.179 HYG 0.429 0.417 0.197 0.558 0.51 0.453 0.231 0.25 0.333 0.504 0.742 0.66 0.722 0.776 -0.106 -0.194 -0.198 0.179 1 SPY IWM EFA EEM SHY IEF TLT LQD HYG 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0028 0.0023 0.0000 0.0000 0.0000 0.0000 -0.0001 0.0000 0.0000 0.0028 0.0000 0.0016 0.0000 0.0000 0.0000 0.0000 -0.0001 0.0000 0.0000 0.0023 0.0016 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 -0.0001 -0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 FIN 340: Final Project Scenarios & Tables Overview: These scenarios and tables will be necessary to complete the final project. Client 1: 1. 2. 3. 4. 5. Ezra, aged 26, is single. However, he is dating and is preparing to get engaged. He will need roughly $5,000 for an engagement ring almost immediately, and expects he will need $10-15,000 for the wedding in the next 12-24 months. He is currently employed and earns about $70,000 per year in salary. This salary is enough to cover all his taxes and normal living expenses of approximately $4,800. This leaves him with about $1,000 in savings each month ($350 to 401K, $650 to savings). He has been able to save roughly $15,000 to date in a 401K plan from work and about $20,000 in cash savings. His 401K plan has been invested in 100% in the stock market, including some sector specific funds. His other savings have been in interest bearing savings and cash substitutes such as money market funds. He recently received a windfall $60,000 and this prompted him to come to you for some advice. The following are few of Ezra's comments to help guide your thoughts: \"I understand I am young, so I need to take on as much risk as I can.\" \"I am willing to lose 30-40% on my invested capital if the return is commensurate.\" \"I do like to have a decent sized cushion in the \"bank\" in case something happens at my job.\" \"I don't foresee my risk tolerance changing after I get married.\" \"Do you have any good stock tips?\" Client 2: Jacob and Rachel, 53 and 52 respectively, are married with 4 children. Two of the children are currently in college and two in high school. They expect the other 2 children to attend college. The couple has done relatively well for themselves and earn roughly $275,000 before tax between the two of them, which equates to $190,000 after taxes. They live well below their means and this should allow them to cover all of their children's college expense out of pocket, but won't leave much for them to save over the next 6-8 years. Through savings and portfolio growth, they have managed to accumulate $900,000. To this point, they have been moderately aggressive (70 - 75% equities) with their portfolio, but feel they need to begin preparing the portfolio for partial retirement in 8 years and full retirement in 13 years. 1. 2. 3. 4. 5. \"I know we still need to be somewhat aggressive, we could live until we're 90, so we need to plan for some growth even in retirement.\" \"We definitely can't afford to take a big hit in our portfolio. We don't have enough time to recover.\" \"Our jobs allow us to work part-time in retirement and we will probably do so as long as we are able.\" \"What do bond yields look like today?\" \"I think we'll need to draw om 3-5% of our portfolio in retirement. We'd like to earn enough income from the portfolio to cover that.\" CAPM Inputs (Element III & IV): Use the values as appropriate for the CAPM formula and the Portfolio Measures. Market Return Risk-free Rate 9% 0.75% Stock Analysis Table (Element II & III): Use the information in this table to help determine the value of 5 of these stocks using the stock valuation models in Chapter 8 of the textbook (pgs. 308-324) Symbol Estimated Beta Dividends per Share Earnings per Share Sales per Share Free Cash Flow per Share 5 Year Dividend Growth Average Industry P/E Ratio Average Industry P/S Ratio Free Cash Flow Growth IBM 0.86 Use Last Year Use Last Year Use Last Year Use Last Year 4.6% 23.7 1.12 2.60% KO 0.66 Use Last Year Use Last Year Use Last Year Use Last Year 3.2% 22.6 2.2 3.50% BMY 0.78 Use Last Year Use Last Year Use Last Year Use Last Year 0% 24.4 3.37 N/A ORCL 1.1 Use Last Year Use Last Year Use Last Year Use Last Year 21.1% 20.5 4.45 10% MMM 0.98 Use Last Year Use Last Year Use Last Year Use Last Year 7.0% 23.8 2.59 7% BAX 0.75 Use Last Year Use Last Year Use Last Year Use Last Year -16.9% 36.09 3.68 N/A BIG 1.04 Use Last Year Use Last Year Use Last Year Use Last Year N/A 23 1.12 N/A NFLX 1.57 None Use Last Year Use Last Year Use Last Year N/A 52.5 6 N/A AKAM 1.34 None Use Last Year Use Last Year Use Last Year N/A 41.8 3.58 17% GE 1.12 Use Last Year Use Last Year Use Last Year Use Last Year 6.3% 23.8 2.59 N/A Portfolio Development (Element III) - Available Assets Table: Use the Stocks listed in Analysis Table along with these additional ETFs to build a portfolio that will meet the risk, return and liquidity objectives of the clients. Symbol Estimated Beta Standard Deviation SPY 1 13% IWM 1.15 16.50% EFA 1.03 15% EEM 1.09 20% SHY 0 1% IEF -0.2 6% TLT -0.48 13% LQD -0.02 5.25% HYG 0.38 7.50% Ex-post Returns (Element IV): Imagine that One year has passed since you developed the client portfolios in Element III. Now you will use the following table which provides the actual returns of each individual asset over the past year. Use these to calculate the Portfolio Return for both clients. Symbol Return Standard Deviation Benchmarks Return Standard Deviation IBM 8% 20.00% Growth 9.6% 13.1% KO 6% 13.00% Income 8.1% 10.2% BMY 13% 28.00% Capital Preservation 5.8% 7.2% ORCL 2% 16.00% MMM 6% 14.00% BAX -6% 16.00% BIG 13% 32.00% NFLX 18% 45.00% AKAM 21% 37.00% GE 10% 16.00% SPY 9% 11.00% IWM 14% 18.00% EFA 9% 15.00% EEM -1% 19.00% SHY 1% 1.00% IEF 4% 6.00% TLT 4% 13.50% LQD 7% 6.00% HYG 8% 8.00%