Lab 5 Assignment - Microsoft Excel (II)

Here is the excel file you will work with:https://docs.google.com/spreadsheets/d/1ei1y_3ApmUt1g-kh3zOt6XbNykzMqWht/edit?usp=sharing&ouid=118316878015473130198&rtpof=true&sd=true

PLEASE EXPLAIN WHAT YOU DID AND SHOW ALL THE STEPS.

INCLUDE THE FINAL EXCEL FILE AS WELL WITH THE FINAL RESULTS!

----------------------------------------------------------------------------------------------------------------------------------------------------

Overview

you will continue your portfolio analysis from Lab 4. This assignment is designed to familiarize yourself with advanced Excel features and functions, perform what-if analysis, goal-seek and scenario summary, and make appropriate charts to present your results effectively.

DO THE FOLLOWING:

Web Queries 1) make a new worksheet named "Web Queries" in your Excel file and move it to behind the "Charts" worksheet. Go to http://finance.yahoo.com and search for any four companies that you would like to invest in. Type their names in the search bar and obtain the "Summary" information, which is shown underneath their current market quote. Copy and Paste it into your Web Queries worksheet and adjust the formats and font size to make them clear and organized. 2) For each company, you should be able to look up its historical stock price by clicking "Historical Data" link. Find its opening price on January 1st, 2023 (or the first available date in 2023) and closing price of December 31st, 2023 (or the last available date in 2023) and calculate the Annual Rate of Return (ARR) of 2020. Highlight the company which has the highest ARR. Hint: the formula to calculate ARR is: (closing_price - opening_price) / opening_price

Advanced Analysis 1) Make a copy of "Base Case" worksheet and rename it to "Monthly Deposit". Move it to behind the Web Query worksheet and analyze the following situation: Suppose you want to keep the amount of your yearly investment the same but instead of making one deposit in each year, you would like to make 12 monthly deposits at the end of each month, each being a twelfth of the yearly amount. Assuming the interest rates are the same as before, is the ending value the same as before too? Why or why not? Use the proper chart in Excel to show your explanation. 2) make a new worksheet in Excel named "Comparison" and put it behind the worksheet "Monthly Deposit". Use tables similar to what you did in Lab 4 to answer the following question. To make your answer clear, you should use two separate tables (one for deposits, one for stocks). If you had invested in the four companies by buying their stocks instead of making deposits into four banks in your assignment 3, how much money would you have made (or lost)? You may assume the annual rates of return stay unchanged for the entire period of your investment and so then do the other factors, DO ALL OF THEM! (such as number of years, and amount per period, etc.).