For this project, you will learn how to enter information into a spreadsheet, do calculations based on your data, how to chart your results using the tools within Excel, and how to use PDF files.

What you will do in this section of Project 6

          Create a Spreadsheet              Part A

          Research stocks on Internet    Part B

          Insert a graph                           Part C

          Show formulas                         Part D

          Convert Print files to PDF        Part E

Part A: Setting up your spreadsheet

1) Open Microsoft Excel

1. From Start, select (Applications – Microsoft Office – Microsoft Excel).

1. Type in the Spreadsheet as shown below.

1. Select (File – Save ) be sure to save to the assn6 folder on your USB device.  Name your spreadsheet file lastname_portfolio.xlsx  where lastname is your last name.

2) Formatting your Spreadsheet

1. In the upperleft corner cell type your:  Your Name – Stock Portfolio CIS 120 Campus CRN #:(Set Font Size to 12 and Bold)

1. In the cells below that, set font size to 10, type the data in the example.  You will need to resize the columns so that the headers can be read properly.

1. Format the cells that contain the ‘headings’with: Bold, Highlight in Yellow, Underline, and Center Justify.

1. Center Justify all of the other data cells with the exception of the Stock’s Namefield.

1. Bold and Right Justify the following cells: ‘Original Value of Portfolio,’ Current Value of Portfolio,’ and ‘Winnings in the Market.’

1. For the Number of Shares column, format it to have 2 decimal points. Highlight that column and select (Format – Cells – Number – Decimal to two places)

1. Formatting for Currency< Highlight cells that will have a dollar amount in it.  Those are the ‘Buying Price,’ ‘Current Selling Price,’ ‘Original Value,’ ‘Current Value,’ ‘Gain/Loss,‘Original/Current Value,’ and ‘Winnings in the Market’ cells.  Select (Format – Cells – Number).  Select Currency and make sure that it will have 2 decimal places as shown next.

   

2. Highlight the cell that will contain the Winnings in the Market. Select (Format – Cells).  Currency should already be selected – leave it alone.  Go to the box containing ways to have negative numbers represented. Select the one that will show negative #’s with a minus sign.

3) Enter the Formulas.

1. The cells lacking numbers need to be filled with formulas that use the data you have typed. Formulas will have the cell names (Column number Row number; B5 or C7 for examples) and math symbols to manipulate the data. We will use three forms:
   1. Difference Formulas
      

      This formula is used to find the change from an original value to its current day counterpart.  An example we might use in the Gain/Loss column =G4-F4 which could represent Gain/Loss  = Current Value (G4) – Original Value (F4)

   2. Multiplication Formulas
      

      This formula is used to find the total amount of an object we have.  Use this type to calculate the Original and Current values. Total Quantity =Number * Price. Try to figure out which cells you need to multiply together in your formula. (Hint: D4*C4 and so on)

   3. Summation Formulas
      

      This formula is used to calculate the sum of a larger list of numbers. Use these to find column totals.    Summation = SUM(First in List : Last in List)  Again, determine which cells will be added together. (Hint: Original Value of Portfolio =SUM(F4:F8). Find the SUM key by clicking on Formulas/AutoSum (Σ).

2. Type in the formulas for the remaining cells of the worksheet; let Excel do all the calculations.

4) Saving your spreadsheet

1. Go to the‘Page Layout’ tab in the tool bar.
   1. Find the ‘PageSetup’ box and click on the tiny arrow in the right corner. The Pagesetup dialog box will appear:

      

   2. Make sure you are on the ‘Page’ tab.
   1. Click on the‘Orientation’button and choose ‘Landscape’.

1. Set the Scaling to ‘Fit to: 1 page wide by 1 tall. This will fit your entire spreadsheet, no matter how big it is; on one page (at least it is supposed to do it).

1. Save to your USB disk under Project 6. File name should be: lastname_portfolio.xls. Where lastname is your lastname.

Part B:  Research your stocks buying price on the Internet

1. Do a search for Stock Price Quotes using a search engine. On the Internet there are dozens of web sites that are either devoted to or provide stock quotes that are up to date, in most cases within 15 minutes. You are to go to one of these online broker pages and get their stock quotes for our stocks. Some examples of these online broker web pages include:
   1. http://finance.yahoo.com
   1. http://Money.MSN.com/Stocks

1. When you get to any one of these pages there should be a place for you to enter the symbol for the stock you want to find.  Enter in the company symbols and write down their Current Market Value.  Try to do all quotes at the same time and day.

1. Enter this information into the Buying Price column in your spreadsheet. Enter the date you do this on your spreadsheet.

1. Save your spreadsheet.

Part C:  Research your stocks selling price on the Internet

1. Wait at least 3 Stock Market working days and then repeat the process you used in part B.

1. Go to one of the online broker pages, enter your symbols again and write down their Current Market Value.  Note the date and time you do this.  Try to do all quotes at the same time and day.

1. Enter this information into the Current Selling Price column in your spreadsheet. Your spreadsheet should look similar to this:

   

1. Save your spreadsheet.

1. Create a PDF file of your spreadsheet and save.
   1. Select (File – Print). Choose the CutePDF Writer as your Printer Name.  Then click okay.

      

   2. Your page will NOT go to the printer. Instead it will be redirected to the PDF driver and a ‘Save As’ dialog box will appear.

      

   3. Save to your Disk under Project 6. File name should be: lastname_portfolio-a.pdf. Where lastname is your lastname.

Part D:  Inserting a Graph into your spreadsheet.

1. Use ChartWizard to create a chart.

1. Highlight the cells from the first Original Value down to the last Current Value (including the headers). This is the range of cells that will be represented on the graph.

1. Click the mouse on the “Insert” tab near the upper-left corner of the screen.

1. Select a chart type from those shown. It is recommended that you use a bar chart that gives the comparisons next to each other.

1. Next, click on the “Chart Tools/Layout” tab and find the Chart Title button. Click on it and then choose ‘Above Chart’.  Add a Title for your chart like ‘Russ’ Portfolio’. Then press Enter to lock the Title in.

1. You may need to reposition the chart by clicking in the MIDDLE of the chart and dragging it to a new place on the page where it is not hiding any of the spreadsheet data.

1. You may re-size the chart by clicking on one of the handles in the corners of the chart and dragging the mouse accordingly.

1. After re-sizing and repositioning, click the mouse in any other cell to “de-select the chart”, make sure the handles around the chart disappear.
1. Save spreadsheet.

1. Create a PDF file of your spreadsheet and save.
   1. Select (File – Print). Choose the CutePDF Writer as your Printer Name. 

      

   1. Follow the steps above to create a second PDF file. File name should be: lastname_portfolio-b.pdf. Where lastname is your lastname.

Part E: Showing your formulas

1. Show the formulas you entered.
   1. Press CTRL + ~ (tilde ~ found on the same key as the reverse ‘) (Top left of number row on keyboard)
   1. Your spreadsheet will now appear with the formulas showing.  The formatting that you did at the beginning of this project will be changed.  THIS IS OKAY!  YOUR SPREADSHEET WILL LOOK DIFFERENT WITH THE FORMULAS SHOWING.

      

2. Create a third PDF file of your spreadsheet and save.
   1. Select (File – Print). Choose the CutePDF Writer as your Printer Name.
   1. Follow the steps above to create a third PDF file. File name should be: lastname_portfolio-c.pdf. Where lastname is your lastname.

1. Reverse the formula screen you have made by pressing CTRL + ` (reverse ‘ found on the same key as the tilde ~) again.