ExcelAssignment-Portfolio-Fall2023

docx

School

Baruch College, CUNY *

*We aren’t endorsed by this school

Course

CIS3400

Subject

Finance

Date

Jan 9, 2024

Type

docx

Pages

Uploaded by AmbassadorAlpacaMaster706

Stock Portfolio Analysis Due Date: Wednesday Dec 6th 11:59pm Group assignment: 3-4 students per team [Strictly NO 5 th student in any event!]  If your teammates have not contributed to the assignment, do not include their names in the submitted files. Please inform me of team issues right away. Points: 100 Objective You are working for a mutual fund manager at a boutique investment firm in downtown. The fund manager has been looking for new investment opportunities. She is particularly interested in the IT industry. She has asked you to identify five firms in IT industry listed on one of the U.S. stock exchange such as New York Stock Exchange –NYSE -- or NASDAQ. Using these five firms she wants you to create an IT portfolio. You are required to create a fictitious stock portfolio by investing a total of $100,000 (paper money, of course!) in five firms in the Information Technology Industry. Since she is new to the IT industry she first wants you to do some basic research using historical pricing. You can use Yahoo! Finance , Google Finance or any other resource to get information on stock prices 1 . Your primary tasks are: i. Complete the stock research as per the instructions in Parts A through D and then, ii. Prepare a business memo as per the instructions that makes a recommendation based on the findings of your research. Important… i. Put meaningful row/column labels and format all worksheets appropriately i.e. wherever appropriate use $, %, etc. ii. Make sure column title row is clearly visible at all times even if someone scrolls down and are formatted differently from the data. (i.e. freeze top row) iii. If your worksheets are not formatted appropriately, you will lose points. … HINT: macros 😊 Stock Research Instructions PART-A Instructions: Stock Price Analysis 1. Create a separate worksheet for each selected firm and enter the name of the Firm in the index tab. i. Obtain the stock prices of your selected firm since the Nov 1st 2018 (including Nov 1st 2018) until Nov 1st 2023 (including Nov 1st 2023). For each day, retrieve the open, high, low, close prices, and volumes traded, for each of your selected stocks. ii. From your data remove any rows (if any) that have dividend/split information. 2. For each stock, calculate % change in that day’s closing price from the previous day's closing price and store it in a new column called ‘ % Change from Previous Day’s Closing ’. 3. Using conditional formatting (CF), highlight the entire rows for top five highest positive and top five highest negative % change values a. You will need to use LARGE and SMALL functions to identify these numbers. 1 The assignment will be graded based on the completion of the required parts according to the instructions and NOT on the financial performance of your portfolio. In other words, if you lose money you don’t lose points! 1

b. Then use these functions in the formulas in ‘Manage Rules’ option in CF to create new rules for highlight the rows 2 . See the YouTube video link in the footnote in case you need additional help with conditional formatting. PART-B Instructions: Visual Stock Data Summary 1. Create a new worksheet named All Closing . Copy the daily closing prices of the 5 stocks in your portfolio, for the duration you held the stocks. 2. Also copy the closing value of the NASDAQ Composite market index (and divide it by 100) to get the "price" of the index for the same time period. The top of the All Closing worksheet would look like following: Date Firm 1 Firm 2 Firm 3 Firm 4 Firm 5 NASDAQ Composite (divide by 100) 11/1/2018 $xxx.xx $xxx.xx $xxx.xx $xxx.xx $xxx.xx $xxx.xx 11/2/2018 … 3. Create a pivot table and pivot chart in a new worksheet that shows the monthly average closing prices for all the five stocks and NASDAQ for the past five years. 4. Make sure that the chart has a title, the appropriate legends, and is properly formatted. i. Make sure lines are clearly visible (use proper colors and increase the thickness of the lines if needed) and PART-C Instructions: Portfolio Analysis 1. Create a new worksheet in your Excel workbook. Name it as MyTechPortfolio and would look like below. To fill this worksheet, follow the steps 2-5 below: Firm name Purchase price Sell price # of shares Initial Amt Invested Profit/Loss ROI (%) Firm 1 $xxx.xx Firm 2 Firm 3 Firm 4 Firm 5 Portfolio Analysis  Total Amt Invested Total Portfolio Profit/Loss Total Porfolio ROI (%) $xxx.xx $xxx.xx NASDAQ Purchase price Sell price # of shares Initial $ Invested Profit/Loss ROI (%) $xxx.xx 2. Create your own (hypothetical) portfolio by distributing $100,000 across your five selected firms. Determine the number of shares you want to buy from each firm. One way to do this is to decide the amount to invest, and then divide it by the stock price. Do not buy fractions of shares. i. You may allocate different amounts of money to each stock, but you must invest some money in each of the five firms (at least $5,000 and at most $50,000), and you must invest around $100,000 (without going over or under by more than $1000). 2 YouTube video on using LARGE function with conditional formatting: http://www.youtube.com/watch?v=cgYhdspUVQE 2

ii. You will invest in your portfolio by purchasing the shares at the opening prices on the first day i.e. Nov 1st 2018 [this will be purchase price ]. You will liquidate it by selling all the shares at the closing prices on the last day i.e. Nov 1st 2023 [this will be sell price ]. 3. For each firm: i. Develop and enter the appropriate Excel formulas to calculate: the initial amount invested in each stock, the profit (or loss) when you sold the shares of that firm, and Return on Investment (ROI). The formula to calculate ROI is : (sale price – buy price) / (buy price). Label and format the columns appropriately. 4. For the entire portfolio: i. Develop and enter Excel formulas to calculate Total Amt Invested, Total Gain Amt (or Total Loss Amt) and Total ROI of the entire portfolio. ii. Hint: Total ROI is not sum of individual ROIs. To compute the Total ROI on the entire portfolio, use the total profit (or total loss) on the portfolio and the total amount invested. 5. NASDAQ market comparison: i. Similar to how you invested in the stocks of firms, invest another $100,000 in the NASDAQ Composite Index. In order to do that, assume you can “buy” the index at a price equal to its value divided by 100 (e.g. if the index is 2100, then the price is $21.00). You will buy the index at its value at the opening price on the first trading day (i.e. Nov 1st 2018), and sell it at its value at the closing price of the last trading day (i.e. Nov 1st 2023). ii. Using the bottom part of the spreadsheet ‘MyTechPortfolio’ that you have already designed, note down the number of units of the NASDAQ purchased, the opening price, the closing price, the total purchase price, and compute your profit (loss) and the ROI. PART-D Instructions: Solver setup A. Identify your goal (It could be to maximize the Portfolio ROI or Portfolio Profit or minimize losses). B. Identify which cells should vary to set up the solver model. C. Use the following constraints: i. Initial amount invested in each firm must be at least $5,000 and should not exceed $50,000. ii. Total amount invested across all the five firms should be no less than $99,000 and no greater than $101,000. After you setup the solver parameters and run it, you will see that solver has identified a solution that meets your objective. EXTREMELY IMPORTANT : At this stage, solver will ask you which solution to keep. DO NOT select “Keep Solver Solution”. SELECT 2 nd option of “Retain Original Values’. Memo instructions Your memo should be between 300-400 words and should be typed up using a word processing software. It should be single-spaced, 1-inch margin, and with 12 point Times New Roman font. Memo header must include:  Date: The date on which the memo is (to be) distributed (This will be the assignment due date)  To: The person(s) to who it is primarily addressed – in this case addressed to the instructor 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

 From: Name of the writer(s) – in this case names of all the team members  Subject: Concise statement of the memo’s topic Memo body should include:  Your recommendation on which firms would be the best candidates for future investment, and  Your reasoning behind the recommendation, and  A brief response to each of the items in the following list: 1. She wants to first find out when and by how much stock prices for the selected firms fluctuate drastically. i. In your memo, include only the days on which top-most positive and top-most negative % change fluctuations occur for each firm. See Part-A for instructions to complete this. 2. The fund manager plans to discuss these results with her colleagues to get their feedback on investment strategy. She wants a visual summary of the stock pricing data and NASDAQ Composite index (Ticker symbol: IXIC). Create a pivot-chart that will help her in these discussions. i. In your memo include a copy of pivot chart and a couple of sentences about your observations from this chart. See Part-B for additional instructions to complete this. 3. The fund manager also wants to know how the ROI of your IT portfolio compares with that of NASDAQ Composite index, which is the market index for IT firms. She wants to know whether it was better than that of NASDAQ (i.e. whether you beat the market). i. In your memo, include a couple of sentences explaining this comparison with NASDAQ. See Part-C for instructions to complete portfolio analysis. 4. Your manager has recently learned about a powerful feature called, Solver, in Excel. She is interested in finding out how it may be used to optimize the performance of your portfolio (in other words, to improve either ROI or profit of your portfolio). She has asked you to use the solver and compare the ROI returned by solver with the ROI of your original portfolio . i. In your memo, include a couple of sentences explaining this comparison of your ROI and the solution returned by solver. (e.g. how were investments split across multiple firms, whether you were happy with Solver’s solution). See Part-D for instructions to conduction portfolio optimization. You must also consider the following when writing your memo: i. Is the memo concise? Does it come to the point? ii. Maintain a professional tone iii. Use headings, bullets, and/or numbered lists so key points stand out and the document is easy to read iv. Short paragraphs of 3-4 lines/sentences. v. Proofread! vi. Identification of any attachments vii. Suitable signoff (e.g. “from”, “sincerely”, etc.) Presentation of the EXCEL AND MEMO document matters and will be graded. Format your files to look clean and professional. Deliverables  Deliverables on the above due date: a. Memo word document 4

b. Excel spreadsheet  Name Excel and Word file properly: Files that do not follow this naming convention will have a penalty of 10%. Reminder: Do not include the names of team members who did not contribute. a. Excel_FirstNameLastName_FirstNameLastName_FirstNameLastName. xlsx or .xlsm and b. Memo_FirstNameLastName_FirstNameLastName_FirstNameLastName. docx c. TeamContributionDistribution_FirstNameLastName.docx (only name each submitting student)  Submit the files through Blackboard’s Assignment submission system by the above due date. See syllabus for penalties on late submission.  Each student submits the same files per team. i.e. everyone in the team submits but submits the same files except for the team contribution form. 5

Related Documents

Quiz_3_-_Ch._5__6.docx

Quiz_5_-_Ch._10.docx

Quiz_4_-_Ch._9.docx

Quiz_2_-_Ch._3.docx

Quiz_1_-_Ch._1_and__2.docx

Practice Quiz Module 5.docx

Practice Quiz Module 1.docx

Week 12 Sample Exam Solutions .pdf

A2.docx

A3.docx

FINA350 Extra Credit 3.docx

Chapter 11.docx

Related Questions

Question A A hedge fund manager pursuing a high-risk portfolio construction strategy is least likely to invest in: A. Fixed income security issued by a municipality. B. 52-week Treasury bill. C. Shares of stock in a financial institution. D. An apartment building. Full explain this question and text typing work only We should answer our question within 2 hours takes more time then we will reduce Rating Dont ignore this line. . Fast

quiz 8-19

Note:- Do not provide handwritten solution. Maintain accuracy and quality in your answer. Take care of plagiarism. Answer completely. You will get up vote for sure.

Case Problem 1. Investment StrategyJ. D. Williams, Inc. is an investment advisory firm that manages more than $120 million in funds for its numerous clients. The company uses an asset allocation model that recommends the portion of each client’s portfolio to be invested in a growth stock fund, an income fund, and a money market fund. To maintain diversity in each client’s portfolio, the firm places limits on the percentage of each portfolio that may be invested in each of the three funds. General guidelines indicate that the amount invested in the growth fund must be between 20% and 40% of the total portfolio value. Similar percentages for the other two funds stipulate that between 20% and 50% of the total portfolio value must be in the income fund and that at least 30% of the total portfolio value must be in the money market fund. In addition, the company attempts to assess the risk tolerance of each client and adjust the portfolio to meet the needs of the individual investor. For…

A quick response will be appreciated

sign Layout References Mailings Review Help el me what View RCM Acrobat Foxit Reader PDF Foxit 2. You are an advisor reviewing fund managers performance over the last year. Your records indicate that government bonds have returned 5% over the period. You have also obtained the following information: Return Standard deviation Beta Market portfolio 0.148 0.52 Fund manager W 0.148 0.36 0.18 Fund manager X 0.160 0.34 0.16 Fund manager Y 0.114 0.34 2.60 Fund manager Z 0.158 0.56 1.80 Given the information above, which fund manager's performance shows it lies on the Capital Market Line? a. Fund manager W. b. Fund manager X. c. Fund manager Y. d. Fund manager Z.

ABC has a substantial number of clients who wish to own a mutual fund portfolio that closely matches the performance of the S&P 500 stock index. A manager at ABC has selected five mutual funds (shown in the table below) that will be considered for inclusion in the portfolio. The manager must decide what percentage of the portfolio should be invested in each mutual fund. Based on the given information, please create your spreadsheet model and find the optimal portfolio. Please submit your business model and solution. Table: Five Mutual Funds and Annual Returns Mutual Fund Year 1 Year 2 Year 3 Year 4 International Stock $15.64 $17.62 $5.80 $3.13 Large-Cap Blend $15.31 $18.77 -$5.06 $1.75 Mid-Cap Blend $18.74 $18.43 $6.28 -$1.04 Small-Cap Blend $14.19 $12.37 $1.92 $7.32 Intermediate Bond $7.88 $9.45 -$1.56 $3.31 S&P 500 $13.00 $12.00 $7.00 $1.50

Suppose that at the beginning of Year 1 you invested $10,000 in the Stivers mutual fund and $5000 in the Trippi mutual fund. The value of each investment at the end of each subsequent year is provided in the table below. Which mutual fund performed better? Please show steps in Excel Year Stivers Trippi 1 11,000 5,600 2 12,000 6,300 3 13,000 6,900 4 14,000 7,600 5 15,000 8,500 6 16,000 9,200 7 17,000 9,900 8 18,000 10,600

● Following results were obtained for the period of six months ending September 2022. Birla Sundaram Sun Nifty Rf Rp 25.38 36.28 45.56 36.74 9.00 Sigma 4.0 6.86 4.31 3.69 Beta 0.23 0.52 0.63 1.00 Using inputs, rank the funds according to the predictive ability of the fund's manager.

Two tales of behavioral biases: You are a portfolio manager of a prestigious Investment Management company, and two of your clients have reached you to share their thoughts about their portfolio performances: • Mathew: “Hello, I have lost a lot of money in the last two quarters! I know that I asked you to invest my money into a very aggressive mutual fund even though you said that it did not fit my investor profile. I know, I know, but I now need to recover. I want you to move my money from this aggressive mutual fund to a technology fund that has done excellent in the last year. Thus, the potential return from this fund is higher, and I think that I can recover from my losses.” • Carol: “I wanted to thank you for the extraordinary performance of my portfolio in the last few quarters. I think that this situation will not continue in the future, however. Six consecutive quarters of gain? Come on, a loss is overdue, you know? Should I move my money elsewhere? Or, if we keep the money in…

Question 4 and 5 Help ( PLEASE DON'T USE EXCEL)

Understanding the Costs Involved in Mutual Fund Investments Bob is debating purchasing a mutual fund, but he has some questions for his financial advisor, Cho. The following table presents information on four different open-end mutual funds that Bob and Cho are discussing. Use the information in the table to answer the questions that follow. FUND NAV NET CHG YTD % RET MinT p 12.32 -0.04 2.6 TmRE r 15.77 0.06 5.5 PFIN 39.81 0.20 8.0 BRRT 43.21 0.29 9.1 BOB: After some research, I've narrowed it down to these four funds, but can you explain to me the relationship between 12b-1 fees and fund performance? CHO: Generally, funds that charge 12b-1 fees, such as MinT p, do not necessarily outperform funds that do not charge these fees. BOB: What about front-end load fees? For example, suppose BRRT has a front-end load fee of 6%, when do I pay this fee? CHO: If you purchase 100 shares of this fund at the NAV, you will pay a commission of S pay $ in front-end fees. BOB: It sounds like these fees…

None

5. Comparing loads and fees Claire wants to buy shares in a mutual fund and has narrowed her selection to the following four funds. Answer the questions below to help assess each fund in terms of the fees each charges. Fund NAV Net Chg. YTD % Ret. MinT p $15.32 -0.05 2.5 IntRA r $18.77 0.05 5.4 MRGG r $27.81 0.19 7.9 MajTrkⁿ $45.21 0.28 9.0 Suppose MajTrkⁿ charges a commission of 8%. This_________an example of a relatively low-load fund. If Claire purchases $4,000 worth of MajTrkⁿ shares, only $___________will actually be invested. This means that the commission—as a percentage of the amount invested—is equal to . The mutual fund MinT p is a no-load fund that imposes a_________on reinvested dividends as well as on the initial investment. Funds that charge this type of fee _________ generally be expected to outperform funds that do not charge these fees. Claire is planning on holding her shares for at least a year and wants a fund that…

What advantages/disadvantages do the mutual funds offer compared to company stock for your retirement investing?

help please answer in text form with proper workings and explanation for each and every part and steps with concept and introduction no AI no copy paste remember answer must be in proper format with all working

SEE MORE QUESTIONS

Recommended textbooks for you

Essentials Of Investments

Finance

ISBN:9781260013924

Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:Mcgraw-hill Education,

FUNDAMENTALS OF CORPORATE FINANCE

Finance

ISBN:9781260013962

Author:BREALEY

Publisher:RENT MCG

Financial Management: Theory & Practice

Finance

ISBN:9781337909730

Author:Brigham

Publisher:Cengage

Foundations Of Finance

Finance

ISBN:9780134897264

Author:KEOWN, Arthur J., Martin, John D., PETTY, J. William

Publisher:Pearson,

Fundamentals of Financial Management (MindTap Cou...

Finance

ISBN:9781337395250

Author:Eugene F. Brigham, Joel F. Houston

Publisher:Cengage Learning

Corporate Finance (The Mcgraw-hill/Irwin Series i...

Finance

ISBN:9780077861759

Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor

Publisher:McGraw-Hill Education

SEE MORE TEXTBOOKS