EXCELL TUTORIAL
Prof.SmithsonThis assignment consists of two (2) parts – Part I: an Excelbased assignment and Part II: a report to your client. You are an investment advisor to your client. The companies for the assignment are:
 Oracle Corp traded on NASDAQ; ticker symbol ORCL; Software: JD Edwards EnterpriseOne and PeopleSoft
 Microsoft Corp traded on NASDAQ ticker symbol MFST; Software: Microsoft Dynamics GP (formerly Great Plains and Dynamics)
 Intuit Corp traded on NASDAQ ticker symbol INTU Software: QuickBooks
Part I: Excel Spreadsheet Assignment
1. Create one (1) Excel workbook that contains the results for each of the four (4) portfolios (detailed below). The data is provided. Using the data and spreadsheet provided, create a worksheet in the workbook that of a summary table and descriptive charts or graphs. The charts should include, but are not limited to, the performance of each stock in each portfolio, the performance of each portfolio, and a comparison of the results.
Use 3D References to create the summary worksheet, WhatIf Analysis (or Solver) to create the different portfolios, and Scenario Summary to obtain results for each. Use the appropriate Excel formulas and functions to produce your summations and use MAX and MIN function to identify the highest price and lowest price in the 45 days. Note: Follow the completion and submission instructions provided in the table below.
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, WhatIf 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, WhatIf 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, WhatIf 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
Start your portfolio using the openingday 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.

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. 
 6 years ago
Purchase the answer to view it
 acc.assignment.excell_masterpiece.xlsx
 acc.assignment.excell.xlsx
Excel Project part 1 Help
This assignment consists of two (2) parts – Part I: an Excelbased assignment and Part II: a report to your client. You are an investment advisor to your client. The companies for the assignment are:
 …
6 years ago