Excel Project part 1 Help

Portfolio 1

You have $5,000,000 to invest in the three (3) stocks, ORCL, MFST, and INTU. At first you believe in all 3 companies, and you invest the $5,000,000 evenly across the stocks, or approximately $1,666,667 in each stock. Note you will not be able to invest exactly $5,000,000 due to the share price. You cannot buy a fraction of a share.  Use the opening price at the beginning of the 45 days as the purchase/ buy price of the stock. Use the close price at the end of the 45 days as the sale/ sell price. Calculate your gain or loss for each stock within each portfolio and for each portfolio based on the different values at the beginning and the end. Identify the highest and lowest price during the period using MAX and MIN functions.  Present a comparison of outcomes in a separate worksheet in the workbook using 3D References, What-If Analysis (or Solver). Create a table that summarizes the portfolio’s outcomes using the functions that connect 2 workbooks.  Develop appropriate charts/ graphs that highlight the performance of each stock in each portfolio and each portfolio. Present a minimum of two (2) charts or graphs.

Note: Your purchase must be in whole shares. For example, you cannot purchase 100.5 shares. You must purchase either 100 or 101 shares. For this reason, you may not total to exactly $5,000,000, but you will come close.

Portfolio 2

Now pretend you are a believer in Oracle and Microsoft. With that, you now invest your $5,000,000 evenly between the two (2) stocks, or approximately $2,500,000 in both. Note you will not be able to invest exactly $5,000,000 due to the share price. You cannot buy a fraction of a share.  Use the opening price at the beginning of the 45 days as the purchase/ buy price of the stock. Use the close price at the end of the 45 days as the sale/ sell price. Calculate your gain or loss for each stock within each portfolio and for each portfolio based on the different values at the beginning and the end. Identify the highest and lowest price during the period using MAX and MIN functions.  Present a comparison of outcomes in a separate worksheet in the workbook using 3D References, What-If Analysis (or Solver). Create a table that summarizes the portfolio’s outcomes using the functions that connect 2 workbooks.  Develop appropriate charts/ graphs that highlight the performance of each stock in each portfolio and each portfolio. Present a minimum of two (2) charts or graphs.

Note: Your purchase must be in whole shares. For example, you cannot purchase 100.5 shares. You must purchase either 100 or 101 shares. For this reason, you may not total to exactly $5,000,000, but you will come close.

Portfolio 3

Now pretend you are a believer in Intuit and Microsoft. With that, you now invest your $5,000,000 evenly between the two (2) stocks, or approximately $2,500,000 in both. Note you will not be able to invest exactly $5,000,000 due to the share price. You cannot buy a fraction of a share.  Use the opening price at the beginning of the 45 days as the purchase/ buy price of the stock. Use the close price at the end of the 45 days as the sale/ sell price. Calculate your gain or loss for each stock within each portfolio and for each portfolio based on the different values at the beginning and the end. Identify the highest and lowest price during the period using MAX and MIN functions.  Present a comparison of outcomes in a separate worksheet in the workbook using 3D References, What-If Analysis (or Solver). Create a table that summarizes the portfolio’s outcomes using the functions that connect 2 workbooks.  Develop appropriate charts/ graphs that highlight the performance of each stock in each portfolio and each portfolio. Present a minimum of two (2) charts/ graphs.

Note: Your purchase must be in whole shares. For example, you cannot purchase 100.5 shares. You must purchase either 100 or 101 shares. For this reason, you may not total to exactly $5,000,000, but you will come close.

Portfolio 4

Now pretend you are a believer in Oracle and Intuit. With that, you will now invest your $5,000,000 evenly between the two (2) stocks. The purchase date of the stock will be three (3) months ago. Track your stocks’ daily performance for the 45 trading days following the purchase date. During this time, you will note the gains and losses each day. At the end of the 45 days’ tracking period, calculate your net gain (or loss) for each stock and your total investment at the end of the 45 days. Develop the appropriate charts that highlight your performance. You will create a minimum of two (2) charts.

Note: Your purchase must be in whole shares. For example, you cannot purchase 100.5 shares. You must purchase either 100 or 101 shares. For this reason, you may not total to exactly $5,000,000, but you will come close.

Note

Do not worry about any commission fees or miscellaneous charges.

Assignment

Determine the outcome of each stock for each portfolio. Also identify the highest price, lowest price, and trend.

Instructions on how to complete

The stock data is provided in an Excel spreadsheet.  Use this workbook and data to create an additional worksheet in the workbook that provides the summary data including:

•&νβσπ;&νβσπ;&νβσπ;&νβσπ;&νβσπ;&νβσπ; Stock name

• Stock symbol
• Number of shares purchased at beginning
• Stock price at beginning and end
• Dates of investment period
• Highest and lowest price in the 45 days of trading for each stock in each portfolio
• Gain or loss on each stock with a portfolio and each portfolio
• Any other pertinent observations

Start your portfolio using the opening-day price for each stock as your purchase price and use the closing stock price to calculate how much money you earned or lost at the end of the period

At the end of your scenario, calculate the following for each stock in question.

• The highest stock price for the period for the stock(s) selected
• The lowest stock price for the period for the stock(s) selected
• The average stock price for the period for the stock(s) selected
• The largest amount of profit (loss) earned for the period for the stock(s) selected
• The lowest amount of profit (loss) earned for the period for the stock(s) selected 

Submit

One (1) Excel workbook that contains the worksheets of the data and the summary of each portfolio. Use the appropriate Excel formulas and functions to report the outcomes/ summary/ comparison/contrast.