Excel-Based Investment Portfolio Analysis And Reporting

An Excel Based Investment Portfolio Analysis and Reporting

This assignment consists of two parts: an Excel-based assignment and a report to your client. You are an investment advisor to your client, analyzing four different investment portfolios involving Oracle, Microsoft, and Intuit stocks over a 45-day period, with specific investment strategies and calculations. You will create a comprehensive Excel workbook that includes data analysis, summary tables, charts, and scenario comparisons based on stock prices and investment outcomes, without considering transaction costs or commissions.

Paper For Above instruction

The objective of this assignment is to develop a detailed investment portfolio analysis using Excel, focusing on four different investment scenarios involving three prominent technology companies: Oracle Corp (ORCL), Microsoft Corp (MSFT), and Intuit Inc (INTU). Each scenario employs different investment strategies, purchase dates, and stock combinations. A critical aspect of the assignment involves understanding stock data analysis, using Excel functions, and presenting findings through charts and scenario summaries.

Initially, the assignment demands building a comprehensive Excel workbook that consolidates stock data and calculations for each portfolio scenario. The key elements include calculating the number of shares purchasable with an initial investment, based on opening prices, while respecting the constraint of purchasing whole shares only. Since fractions of shares cannot be bought, total investments may not precisely match $5,000,000 but should be as close as possible. Using the opening and closing prices, profits or losses are calculated for each stock and for the entire portfolio over the 45-day trading period. The use of MAX and MIN functions helps identify the highest and lowest stock prices during the analysis period.

For each portfolio, a summary worksheet should be created utilizing 3D references to facilitate cross-referencing data across worksheets. Scenario management tools like What-If Analysis or Solver are to be employed to simulate different investment distributions. A Scenario Summary report will compile the outcomes for each portfolio, highlighting key financial metrics, including total gains or losses, and identifying the best and worst performing stocks in terms of price fluctuation and profit margin. These calculations enable comprehensive comparison across different portfolios.

Visualization of data plays a central role in conveying insights. At least two charts or graphs are to be developed per portfolio, illustrating stock performance and overall portfolio results. These visual aids should effectively showcase stock trends, profit/loss comparisons, and performance differences among the portfolios. The charts should help clients understand the variability and returns associated with the different investment strategies.

The assignment extends to a detailed comparative analysis of the portfolios. For three of them, the investments are based on an even split between selected stocks, with purchase dates at the beginning of the period. For the fourth portfolio, a longer-term analysis is undertaken, tracking stock performance over three months post-purchase, emphasizing daily gains and losses. Using daily stock data, the cumulative net profit or loss after completing the 45-day tracking period is calculated, complemented by visual representations of stock performance over time.

This comprehensive exercise involves using Excel formulas (like SUM, MAX, MIN, and AVERAGE), functions for referencing data across sheets, and tools like Scenario Manager and Solver. Proper structuring of the workbook ensures clarity, with separate sheets for raw data, calculations, summaries, and visualizations. The final deliverable must synthesize all findings into a professional, client-ready report, including detailed analysis, graphical representations, and a portfolio summary table.

References

  • Higgins, R. C. (2012). Analysis for Financial Management. McGraw-Hill Education.
  • Shim, J. K., & Siegel, J. G. (2012). Financial Management. Barron's Educational Series.
  • Levy, H., & Sarnat, M. (2004). How to Think About Money. HarperBusiness.
  • Brealey, R. A., Myers, S. C., & Allen, F. (2017). Principles of Corporate Finance. McGraw-Hill Education.
  • Damodaran, A. (2012). Investment Valuation: Tools and Techniques for Determining the Value of Any Asset. Wiley Finance.
  • Evans, J. R., & Lind, D. A. (2012). Financial Accounting and Reporting. Cengage Learning.
  • Arnott, R., & Bernstein, P. (2002). What Works on Wall Street. Financial Times Prentice Hall.
  • O'Brien, P. (2009). Financial Data Analysis. Springer.
  • Fama, E. F., & French, K. R. (1993). "Common risk factors in the returns on stocks and bonds". Journal of Financial Economics, 33(1), 3-56.
  • Sharpe, W. F. (1964). "Capital asset prices: A theory of market equilibrium under conditions of risk". Journal of Finance, 19(3), 425-442.

Related Assignments