Need everything answered including the Excel spreadsheet completed with the 3 pie charts. Can send Excel and pie charts to my email: b..d@msn.com

It needs to be in English!

Case Overview: You have been hired as a Junior Financial Analyst at Zenith Financial Services, a boutique financial advisory firm that specializes in personal wealth management. Your first major assignment is to assist a senior financial advisor in creating a dynamic live stock portfolio tracker for a high-net-worth client who is keen on building a diversified stock portfolio. The client has given you the freedom to choose 15 stocks for the initial portfolio, and they are interested in tracking the performance of this portfolio in real-time.

The client expects a well-researched justification for each stock selected and requires a comprehensive tool that will allow them to monitor their investments closely. This tool will help them make informed decisions about their holdings, and it will also serve as a blueprint for future investment decisions.

Client Profile:

• Name: Mr. John McLeod
• Age: 55
• Investment Goal: Growth-oriented, with moderate risk tolerance.
• Investment Horizon: 10-15 years.
• Preferred Sectors:
  • Technology
  • Healthcare
  • Renewable Energy
  • Financial Services
  • Consumer Goods
  • Industrials
  • Telecommunications
  • Utilities

Mr. McLeod is particularly interested in companies that are leaders in their respective fields and have strong growth potential. He is open to investing in both large-cap, well-established companies and promising mid-cap companies that offer growth potential. He is also concerned about market volatility and wants to keep track of his portfolio's performance on a daily basis.

Objective: Your task is to create a dynamic live stock portfolio tracker using Excel in combination with Google Sheets, Google Sheets, or Office 365 that will help Mr. McLeod track his investment in real-time. Additionally, you are required to provide a detailed justification for each stock selection, explaining why it fits Mr. McLeod's investment goals and risk profile.

Steps to Complete the Assignment:

1. Stock Research and Selection:

• Research: Conduct thorough research on potential stock picks, focusing on sectors Mr. McLeod is interested in.
• Selection: Construct the portfolio with a balanced mix of market capitalizations:
  • 4 Large-Cap Stocks: Select 4 stocks from well-established, large-cap companies.
  • 6 Mid-Cap Stocks: Select 6 stocks from mid-sized companies with strong growth potential.
  • 5 Small-Cap Stocks: Select 5 stocks from small-cap companies with high growth potential but higher risk.

2. Live Portfolio Tracker Development:

• Spreadsheet Setup: Create a live portfolio tracker using Excel in combination with Google Sheets, Google Sheets, or Office 365. The spreadsheet should include the following columns:
  • Symbol: Ticker symbol of the stock.
  • Name: Full name of the company.
  • Sector: The industry or sector the company belongs to.
  • Cap Size: Market capitalization size (Large Cap, Mid Cap, Small Cap).
  • # of Shares: Create hypothetical units of shares for each stock in the portfolio.
  • Price Paid Per Share: Use the average price of the stock over the last 30 days as the purchase price for your hypothetical shares.
  • Amount Paid: Total amount paid for the shares (calculated as # of Shares * Price Paid Per Share).
  • Current Price: The current market price of the stock.
  • Total Value: Current value of the shares (calculated as # of Shares * Current Price).
  • Allocation: Percentage of the portfolio's total value that each stock represents.
  • Total Gain/(Loss) $: The dollar amount of gain or loss (calculated as Total Value - Amount Paid).
  • Total Gain/(Loss) %: The percentage gain or loss (calculated as (Total Gain/(Loss) $ / Amount Paid) * 100).
• Conditional Formatting: Apply conditional formatting in the spreadsheet to visually highlight the performance of each stock:
  • Green: For gains in the "Total Gain/(Loss) $" column.
  • Red: For losses in the "Total Gain/(Loss) $" column.
• Pie Chart for Stock Allocation: Create a pie chart to visually represent the allocation of the portfolio to each stock. This chart should update dynamically based on the total value of each stock in the portfolio.
• Pie Chart for Sector Allocation: Create another pie chart to show the allocation of the portfolio by sector. This chart should dynamically reflect the distribution of investments across different sectors based on the total value of the stocks in each sector.
• Pie Chart for Cap Allocation: Create a pie chart to visually represent the allocation of the portfolio by market capitalization (Large Cap, Mid Cap, Small Cap). This chart should update dynamically based on the total value of stocks in each cap category.

Need everything answered including the Excel spreadsheet completed with the 3 pie charts. Can send Excel and pie charts to my email: b..d@msn.com

It needs to be in English!