1 / 34

CS 202 and ICS 202 - Class 8

CS 202 and ICS 202 - Class 8. by A.Penjira (Mony) Kanthawongs. Class 8 - Agenda. Give out keys for 10 MC and 10 SA Proj . R einforce. Excel Project 1 Lecture on Excel Project 2 (E 2.5 - E 2.23 - Half Chapter)

ammief
Download Presentation

CS 202 and ICS 202 - Class 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS 202 and ICS 202 - Class 8 by A.Penjira (Mony) Kanthawongs A.Penjira Kanthawongs

  2. Class 8 - Agenda • Give out keys for 10 MC and 10 SA Proj. Reinforce. Excel Project 1 • Lecture on Excel Project 2 (E 2.5 - E 2.23 - Half Chapter) • G. HW: Use G. HW from Class 7 and add AVERAGE, MAX, MIN functions for your company (Look at requirements like Page E 2.68 and from the lecture) turn in next class. • Ind. HW: Do 20 MC and 10 SA questions for Excel P. 2 and turn in next class. A.Penjira Kanthawongs

  3. Notice for next class. • Continue Lecture Excel Project 2 (the rest of the chapter). • Prepare for Excel – Lab Test (E.Project 1 & E.Project 2) • Change in Syllabus • Class 9, Move “Present Group Work # 2 on Excel” to Class 10 • All Access lecture and homework will be extra credits...not requirements to do them. A.Penjira Kanthawongs

  4. Project 2: BetNet Stock Club • Michael Santos and 6 classmates began playing the stock game with Yahoo!’ s web site (quote.yahoo.com). • Michael and his friends started with $100,000 each person to buy and sell stocks for 1 month. A.Penjira Kanthawongs

  5. Yahoo! Web site awarded the top finisher a $5,000 cash prize. • Michael and his friends won the contest. They used the prize money to start the BetNet Stock Club and decided to invest in real stocks. A.Penjira Kanthawongs

  6. Each month, Michael summarizes the month-end financial status. As the club members approach graduation from college, the value of the club’s portfolio has grown to nearly $900,000. • As a result, the members voted to buy a new computer and Microsoft Office 2000 for Michael. With Office 2000, he plans to create a worksheet summarizing the club’s stock activities that he can e-mail to the members. A.Penjira Kanthawongs

  7. Michael has asked you to show him how to create the workbook and access real-time stock quotes over the Internet using Excel 2000. A.Penjira Kanthawongs

  8. Project 2: BetNet Stock Club • Need: An easy-to-read worksheet that summarizes the club’s investment (Figure 2-1a). It includes each stock’s … name, symbol, date acquired, # of shares, initial price, i-cost, current price, c-value, gain/loss, % gain/loss. It also includes totals, average, highest value, lowest value for each column of #. A.Penjira Kanthawongs

  9. Michael wants to use Excel to access real-time stock quotes using Web queries (Figure 2-1b). A.Penjira Kanthawongs

  10. Source of Data: The data supplied by Michael includes the stock names, symbols, dates acquired, number of shares, initial prices, and current prices. A.Penjira Kanthawongs

  11. Calculations: 1.Initial Cost= Shares x Initial Price 2. Current Value=Shares x Current Price 3. Gain/Loss=Current Value - Initial Cost A.Penjira Kanthawongs

  12. / 4.Percentage Gain-Loss= Gain-Loss / Initial Cost 5. Compute the Totals for Initial Cost, Current Value, and Gain-Loss. • Percent Gain-Loss comes from Total Gain-Loss/ Total Initial Cost. A.Penjira Kanthawongs

  13. 6. Use the AVERAGE functionto determine the average for the # of Shares, Initial Price per share, Initial Stock Cost, Current Stock Price, Current Stock Value, and Gain/Loss for each stock. A.Penjira Kanthawongs

  14. 7. Use the MAX and MIN functionsto determine the highest and the lowest values for the # of Shares, Initial Price per share, Initial Stock Cost, Current Stock Price, Current Stock Value, and Gain/Loss for each stock, and Percent Gain/Loss. A.Penjira Kanthawongs

  15. Web Requirements: Use the Web query feature of Excel to get real-time stock quotes for the stocks owned by BetNet Stock Club (Figure 2-1b) A.Penjira Kanthawongs

  16. To start a new line in a cell, press ALT + ENTER after each line. To start typing in a new cell, press ENTER key or one of the arrow keys. Look in Orange Box on Page E 2.7 about “Wrapping Text.” You can instruct Excel to wrap the text in a cell by Choose the cell you want to change the format | Right-Click | Choose “Format Cells” | Choose “Alignment” tab | Check the box “Wrap Text”. Entering the Titles and Numbers into the Worksheet. A.Penjira Kanthawongs

  17. Look in all Orange Boxes on Page E 2.8. You can increase and decrease height if rows to add white space between information. You may use 4-digit years to ensure that Excel interprets year values the way you intend. Best way to enter data into a range of cells…Select the range | Enter the data | Press “Enter” key | Enter the data. Formatting a Worksheet. A.Penjira Kanthawongs

  18. On Page E 2.7, Table 2-1, each Date Acquired is stated by MM/DD/YY. But you need to enter in this format “DD/MM/YYYY” ….always follow this format!Otherwise, you might get wrong calculations at the end. A date should be right-aligned in the cell because it is considered as a number. If you have trouble, try to fix it by Select the date cell | Right-Clicking | Choose “Format Cells” | Choose “Date” | Choose the format you want. Important notes when entering dates into Excel. A.Penjira Kanthawongs

  19. This is how the worksheet looks when the titles and numbers are entered before any calculation. A.Penjira Kanthawongs

  20. - * * • Calculations: 1.Initial Cost= Shares x Initial Price 2. Current Value=Shares x Current Price 3. Gain/Loss=Current Value - Initial Cost A.Penjira Kanthawongs

  21. A.Penjira Kanthawongs

  22. In Excel, you can start a formula with equal (=) , plus (+), and minus (-) signs and they will be considered as numbers and numbers can be calculated! Remember that! If you want to multiply in Excel, you need to press the asterisk (*) not the letter x (X). You can enter the cell references in formulas in uppercase or lowercase, and you can add space before and after arithmetic operators to make the formulas easier to read. “=d3*e3” is the same as “=D3 * e3” Entering Formulas A.Penjira Kanthawongs

  23. Order of Operations or Precedence: 1. First Negation (-3) 2. All percentages (%) 3. All exponentiations (^) 4. All multiplication (*) and division (/) from Left --> Right 5. All additions (+) and Subtractions (-) from Left --> Right Order of Operations in Excel P 2 Slash (/) is different from backslash (\) A.Penjira Kanthawongs

  24. For example: • =6+(-3)^2 | = 6+9 | = 15 • =D1 / X6 - A3 * A4 + A5 ^ A6 • ^  /  *  –  + A.Penjira Kanthawongs

  25. / 4.Percentage Gain-Loss= Gain-Loss / Initial Cost A.Penjira Kanthawongs

  26. A.Penjira Kanthawongs

  27. Using Point Mode Point mode allows you to create formulas using the mouse. (Page E 2.11 in Orange Box) Using the Fill Handle Page E 2.13 The Fill Handle is a small rectangle in the lower-right corner of the active cell. A.Penjira Kanthawongs

  28. A.Penjira Kanthawongs

  29. Relative References (Page E 2.14) • Copydownward, Excel adjusts the row portion of cell ref., copy across, Excel adjusts the column portion of cell ref. • To see the formulas in the Excel worksheet, click Tools | Options | Choose “View” tab | Check in the box in front of the “Formulas.” A.Penjira Kanthawongs

  30. / 5. Compute the Totals for Initial Cost, Current Value, and Gain-Loss. • Percent Gain-Loss comes from Total Gain-Loss/ Total Initial Cost. (Page E 2.15) A.Penjira Kanthawongs

  31. BetNet’ s Worksheet with all Totals. A.Penjira Kanthawongs

  32. BetNet’s worksheet with the Total % Gain-Loss. A.Penjira Kanthawongs

  33. Lab Test next class. • There will be lecture from Page E 2.23 - the end of Excel Project 2 before the Lab Test. • Prepare for the Excel Lab Test • Practice Excel Project 1 - Excel Project 2 ..Know it by heart :) .. • Questions may come from the textbook or from the instructor. A.Penjira Kanthawongs

  34. 30 points, Time: 1 hour, Help yourself…asking your friends or instructor is prohibited, No borrowing any materials esp. textbook and disks. A.Penjira Kanthawongs

More Related