ism project excel 1

Part 1: The Data and Spreadsheet

Add labels and make formatting changes:

• Download and the DisneyPrices.txt file from Blackboard. In a new Excel workbook, find and open the Disney Prices file. Recognize that the Disney file is a text file not an Excel file. You will need to make sure you have configured Excel’s “open” dialogue to see and open .txt type files.
• Once you have the txt file in your Excel spreadsheet, do the following:
  • Add a row at the top of the sheet for column labels. Your labels should read Date, Opening Price, High Price, Low Price, Closing Price, Volume Traded. (Make sure your labels correspond to the correct columns of data.) Make all the labels bold.
  • Format the cells in the date column to be a date format and to show dates in the form of xx/xx/xx.
  • Format the Opening Price, High Price, Low Price, and Closing Price data to the number format with two decimal places.
  • Format the Volume Traded data to the number format with no decimal places.
• Create a new column of data in column G. Put a label in the top row of G that reads “Motion Above”. The data in this column will show the maximum amount the stock price was above the close price for each day. Use the absolute value function (ABS) that shows the absolute value difference between the Close Price and the High Price.
  • Copy this function and formula for all the rows of data.
  • Change the format of the data in column G to a number value with two decimal places.
• Create a new column of data in column H. Put a label in the top row of H that reads “Motion Below”. The data in this column will show the maximum amount the stock price was below the close price for each day. Use the absolute value function (ABS) that shows the absolute value difference between the Close Price and the Low Price.
  • Copy this function and formula for all the rows of data.
  • Change the format of the data in column H to a number value with two decimal places.
• Create a third column of data in column I. Label this column “Price Change”. The purpose of this column is to show the percent change in stock price from one day to the next. You will use a formula to calculate the percentage change for each day. (You need to start in row 3 rather than row 2 because we don’t know the close price for the day that precedes our first day of data.) In I3, enter a formula the following formula: =(E3-E2)/E2. (This is difference between the close price of the current day (E3) and the close price of the previous day (E2) divided by the close price of the previous day.)
  • Copy the formula to show the Price Change for all other days.
  • Change the format of the data in column I to a percentage format with two decimal places.
• Use conditional formatting to highlight positive price changes and negative price changes in our Price Change column. You should highlight any values that are greater than 0 with light green fill with dark green text. Highlight any values that are less than 0 with light red fill with dark red text.
• Create a scatter chart to show Disney closing stock prices between 11/22/19 and 01/21/20. Make your chart look exactly like mine below. Here are a few tips:
  • The dotted line running diagonally down the chart is a trend line.
  • The vertical lines that stick out the tops and bottoms of most of the dots are error bars. You will need to specify custom values for the error amounts. The positive error values are the motion above data and the negative error values are the motion below data.
  • You will need to modify the minimum and maximum axis values to get your axis numbers to match my chart. (Ignore what I taught you in class about always showing 0 on the vertical axis. That only applies to bar charts, not line/scatter charts.)
  • You should place your chart on the same sheet as the Disney stock price data. Please place your chart just to the right of the last column of data, at the top of the spreadsheet.
• Interpretation: In cell K1, write down your general interpretations of the chart and Disney stock prices during the period shown on the chart. Point out any interesting features and provide evidence or support for your interpretations or conclusions.

Create new columns of data:

Use conditional formatting to highlight key information:

After completing all the steps, the top portion of your data and spreadsheet should look like this:

Part 2: The Chart

IMPORTANT: When you are finished, save your work in a single spreadsheet in the Excel file type (.xlsx), not a text (.txt) file type. (If you save it as a txt file, it will not save your chart after you close your file.)