CS 202 and ICS 202 Class 8 by

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

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 2

• 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 3

Project 2: Bet. Net 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 4

• 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 Bet. Net Stock Club and decided to invest in real stocks. A. Penjira Kanthawongs 5

• 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 6

• 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 7

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

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

• 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 10

• 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 11

/. 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 12

. 6 Use the AVERAGE function to 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 13

. 7 Use the MAX and MIN functions to 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 14

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

Entering the Titles and Numbers into the Worksheet. • 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”. A. Penjira Kanthawongs 16

Formatting a Worksheet. • Look in all Orange Boxes on Page E 2. 8. – You can increase and decrease height if rows to add white space between information. A. Penjira Kanthawongs – 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. 17

Important notes when entering dates into Excel. • 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. A. Penjira Kanthawongs 18

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

* * - • 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 20

A. Penjira Kanthawongs 21

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

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

– For example: • 6+(-3)^2 =| = 6+9 | = 15 • =D 1 / X 6 - A 3 * A 4 + A 5 ^ A 6 – ^ / * – + A. Penjira Kanthawongs 24

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

A. Penjira Kanthawongs 26

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

A. Penjira Kanthawongs 28

• Relative References (Page E 2. 14( – Copy downward, 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 29

/. 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 30

Bet. Net’ s Worksheet with all Totals. A. Penjira Kanthawongs 31

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

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 33

– 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 34