Your email should include “Intermediate 3 Assignment #1” in the subject line and should include an Excel file as an attachment. 

The goals of this assignment are: 

• Locate and download data for use in Excel. 

• Refine the dataset and graphically represent the results. 

• Create journal entries for investment in stock and adjusting entries. To begin, you’ll need to choose three companies with publicly traded stock. Companies with publicly traded stock are listed on a stock exchange and their prices are available to the general public. You are free to choose any company that interests you. While it’s okay to discuss this assignment with others, please try to avoid using the same three companies. Assume that your company made an investment in 10,000 shares of each stock on December 30, 2016. (1) The first step is download closing prices for each stock since that time. There are many sites where this data is available, but I like to use Yahoo Finance – finance.yahoo.com. To find stock prices, search for the company’s name or ticker symbol and click on Historical data. Once at this page, you’ll need to download prices from December 30, 2016 through today. Select the appropriate dates in Time Period and click Apply. Just below the blue Apply button where you select the date range, you’ll see an option to Download the data. Please do this. Open the file and make sure that it is sorted with the earliest date first. You can also eliminate all the columns except Date and Adj Close. Save as an Excel file, not a csv file. I would recommend naming it Intermediate 3 Assignment 1. (2) Complete this step two more times and you’ll have three spreadsheets. We now want to get all the data into one spreadsheet. There are many ways to achieve this, but I’m going to recommend the following (should out to Nardos who showed me this technique): a. After you have eliminated all the data except Date and Adj Close, right click on the worksheet tab. Select “Move or Copy” and then select your original file under “To book”. b. You will now see two worksheets in your original file – one for each of the first two stocks. c. Now repeat this process for the third company and you’re ready to go to the next step. (3) You will now create two graphs. Please place these graphs on separate worksheets in your Excel workbook. First, create one graph in Excel to show the daily prices of each of the three stocks over this period of time. Be sure to choose a graph that is appropriate to the type of data and add a title to the graph. You should also rename the worksheet. Again, there are lots of ways to do this, but I did the following: 

a. On each of your three worksheets, change the column header Adj Close to the name of the company. 

b. Add a new workbook called “Combined Data”. 

c. In cell A1, type = and then click back to cell A1 on the worksheet for the first company and hit Enter. Copy this formula to the cell to the right. You’ll now see Date at the top of the first column and the company name at the top of the second. 

d. To copy this formula down for all the rows, just click on the column heading (such as cell A1 in the Combined Data workbook) and double click. It copies it down for all the rows with data. Do the same with the other column. 

e. Your dates may look weird now. Click on the whole column (by clicking on the letter at the top) and then select Short Date in the drop-down box in the Number section of the home tab. 

f. Repeat this process for the other two companies – putting their data in columns C and D and E and F. 

g. All the dates should be the same, so you can delete columns C and E once you confirm this. 

h. Now, you’re all ready to create a graph that shows the stock prices over time. (Click on the columns with data, click on the Insert page on the ribbon at the top, and then select an appropriate graph.) (4) Now, using only the data for the last trading day of each year (2016, 2017, 2018, 2019, 2020) and create a new table that shows the total value of your investment of 10,000 shares of each stock on each of these dates. Create a column or bar chart with this table and insert it in a new worksheet. (5) Finally, in a worksheet that you label Journal Entries in your Excel file, create the following journal entries: 

• What is the journal entry that your company would use to record investment in these companies’ stocks on December 31, 2016? 

• What adjusting entries would you need to make on December 31, 2017, 2018, 2019, and 2020 to record the change in the value of this investment? I know that students have different levels of Excel skills, so please feel free to let me know if you need some help. I think that you’ll also find that there are tons of helpful resources online, as well as through UB’s tutoring services. Graduate students only: In the workplace, it’s not uncommon that we have month- or year-end procedures that are repeated. While you may complete a task many times, if you move up in your organization or change roles, you will have to turn over reporting responsibilities to someone else. Create a two-page instruction sheet that shows how you completed the four steps of this assignment. It is helpful to include both a narrative and screen shots (with arrows pointing to important items) that would help guide someone through the process of completing each step. You may use any tool that you like to create this, but Word may be the easiest for composition. Your final product should be a PDF file – you can export to this format from Word. It is important that your instructions cover each step in the process, but you are limited to two pages. You may want to provide guidance on where someone could get more help on a task, or helpful hints on mistakes to avoid. Please attach this PDF file to your email, along with the Excel file.