Assignment 1: Case Overview: You have been hired as a Junior Financial Analyst

Assignment 1: Case Overview: You have been hired as a Junior Financial Analyst at Cumberlands 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 10 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:2 Large-Cap Stocks: Select 2 stocks from well-established, large-cap companies.
3 Mid-Cap Stocks: Select 3 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.
3. Adding Live Data to the Tracker:Current Price Updates: Set up the spreadsheet to pull live stock prices using financial websites, APIs, or built-in functions available in Excel, Google Sheets, or Office 365, ensuring that the current price columns update automatically.
Data Accuracy: Make sure that the data source used is reliable and that the information is updated frequently to reflect market changes.
4. Final Presentation and Report:Spreadsheet Submission: Submit the completed portfolio tracker, ensuring all formulas, data connections, conditional formatting, and the pie charts are functioning properly.
Video Presentation: Create a 10-15 minute video presentation demonstrating the features of your portfolio tracker. In the video, you should:Explain the stock selection process and justify your choices.
Show the live tracking features, highlighting how the current prices and other key metrics are updated.
Walk through the key elements of the portfolio tracker, such as allocation, total value, gain/loss, conditional formatting, and the pie charts showing the portfolio allocation by stock, sector, and cap.
Provide an overview of the portfolio’s performance and your final recommendations.
Presentation: Present your findings and recommendations in a professional manner, simulating a client meeting scenario.
Justification: Provide a detailed written justification for each stock, including:
Company Overview: Describe what the company does, its market position, and competitive advantage.
Sector Analysis: Explain the growth prospects of the sector and how the company is positioned within that sector.
Market Capitalization: Discuss the company’s size and stability (Large Cap, Mid Cap, Small Cap).
Recent Performance: Include recent financial performance and stock price trends.
Growth Potential: Highlight the company’s future growth potential and any catalysts for growth.
Risk Factors: Identify any potential risks associated with investing in the company.
If you plan to use Office 365 for this assignment, please follow the link below to access the steps for gaining access.https://helpdesk.ucumberlands.edu/support/solutions/articles/7000045435
Video Guide for Assignment
Video Guide for AssignmentCreate a Live Stock Portfolio Tracker in Excel | No Microsoft 365 RequiredBuild a Stock Tracker in Google Sheets: The Ultimate GuideLIVE Stock Portfolio Dashboard in Excel
Assignment 2:
Group Projects:
Investing involves making informed decisions, which means researching companies and industries before plunking down your hard-earned money! An excellent source of information about a company is the company itself, particularly its annual report to stockholders. In this project, you’ll examine the annual stockholders’ report of a company in which you are interested.
The annual report is a document that provides financial and operating information about a company to its owners, the stockholders. Obtain a copy of the latest annual report of the company you are researching. Copies can be found in many public and college libraries, at local brokerage offices, or on the company’s Internet site. Carefully study the annual report and then prepare a corporate profile of the firm you selected. Your profile should include the following elements:
Name of the company, its ticker symbol, and the exchange on which it trades
Current market price of the stock and its percentage change from 1, 3, and 5 years ago (try to find a chart of its stock price)
Location of its corporate headquarters, names of its officers, and percentage of inside ownership
Brief description of the company, including its major products or services
Brief history of the company
Major competitors
Sales and profit summaries
Other relevant financial ratios and measures
Recent developments and future plans
Based on your findings, would you consider this company for a potential investment? Why or why not?
Group Project Presentation:
Your presentation should be a top-line summary of the key points from your findings
Each team will have 15 minutes to present
Every member of the team must present, and have a speaking part of the presentation.
Use headings and subheadings throughout your presentation.
Presentation should discuss all required parts of the research.