Excel 1&2

Stock Shares Analysis (50 points)

You have a budget of $100,000 and you are considering investing your money by buying shares of stock. You are interested in purchasing stock from companies that you personally support.

1. Go online and find the following information about stock prices from five companies with which you do business:

a. Stock price from one year ago

b. Today’s stock price

[Important] Note. These five companies should be different from other students. Because the probability of the five companies selected by different students being equal to each other is close to 0%, if the list of the five companies are the same as the other students, your assignment grade will be zero for any reason.

2. Now enter this data into an Excel worksheet:

a. Be sure to include the title Stock Shares Analysis and include your name as a subtitle.

b. Enter the information for each company in different rows.

c. Enter the name of the company in the first column, the stock price from one year ago in the second column, and today’s stock price in the third column.

d. Enter the investment amount in the row below the companies.

e. Label the amount appropriately in a separate cell.

3. Calculate the difference of the stock prices between today and a year ago. Enter the current stock price minus the stock price a year ago in the fourth column.

4. Calculate the percent of growth (or decrease) from last year to this year for each company by entering the appropriate formula in the fifth column of information. The percent of growth is calculated by subtracting last year’s price from today’s price and dividing the result by last year’s price. (If the result is a negative number, there was a decrease rather than growth.)

5. Enter in the 6th column how many percent of each company's shares will be invested. All totals must be 100%. Units should be expressed in %.

6. Calculate the amount of investment in each stock in the 7th column based on the investment ratio you have determined in column 6. (budget * invest rate(%))

7. Calculate how many shares you can purchase with your investment amount based on today’s price for each company.

a. Enter the formula for this information in the 8th column.

b. Since stocks are not traded in decimal units, the number of stocks that can be purchased must be natural.

c. Add a comment in this column indicating how the shares were calculated. In other words, explain the formula.

8. In the 9th column, calculate your actual invest money for each company.

9. In the 10th column, enter a formula that will display “Increased” if the value of the stock increased or “Decreased” if the value of the stock decreased from last year to this year.

10. Identify all columns appropriately.

11. Create a line chart that shows the growth or decrease from last year to this year. Be sure to include a title and a legend.

12. Sort the companies, and their data, in alphabetical order.

13. Save the worksheet as Stock100K

14. You are curious to see how many shares you could purchase from any one company if you saved an extra $100,000.

a. Create a new worksheet as Stock200K

b. Increase the budget amount to $200,000, complete calculation.

15. Save your file as “Excel HW2 – your Last name.xlsx” (the worksheet with the updated totals).

Submit all worksheets on the blackboard via this assignment link.