a.penjira kanthawongs1 cs 202 and ics 202 - class 8 by a.penjira (mony) kanthawongs

A.Penjira Kanthawongs 1

- 202 202 8CS and ICS Class

by A.Penjira (Mony) Kanthawongs


Class 8 - Agenda• Give out keys for 10 MC and 10 SA Proj.

Reinforce. 1Excel Project•Lecture - - on Excel Project 2 (E 2.5 E 2.23 H

alf 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.


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


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.


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


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


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


Project 2: BetNet Stock Club

• Need: - - An easy to read worksheet that summari - zes the club’s investment (Figure 2 1 a). It incl

udeseachst ock’s … name , symbol , date acquired , # of shares , initial price , -i cost , current pric

e , -c value , gain/loss , % gain/loss . I t al so i n cl udes totals , average , highest value , lowe

st value foreachcol umn of #.


• - MichaelwantstouseExcelt o access r eal t i me st ock quot -es using Web queries (Figure 2 1

b).


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


• Calculations:1. I ni t i al Cost = Shar es x I ni t i al Pr i c

e 2. Current Value = Shares x Current Price 3. Gain/Loss = - Current Value Initial Cost


4. -Percentage Gain Loss = - GainLoss / I ni t i al Cost 5. Comput e t he Totals for Initial Cost , CurrentValue , and -Gain Loss.– - Percent Gain Loss comes from Total Gain

-Loss / Total Initial Cost.

/


6. Use the AVERAGE function to determine t he average for the # of Shares , Initial Pric

e per share , Initial Stock Cost , Current S tock Price , Current Stock Value , and Gai

n/Loss for each stock.


7. Use the MAX and MIN functions to determine the highest and the lowest values for the # of Shares , I

nnnnnn nnnnn nnn nnnnn , Initial Stock Cost , nnnnn nt Stock Price , C urrent Stock Value , and Gain/L

oss for each stock , and Percent Gain/Loss.


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


EEEEEEEE EEE EEEEEE EEE E EE E EEEE EEE E EEEEEEEEE.

• To start a new lin e in a cell, press

ALT + ENTERafter 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”.


E E EEEEEEEEE.

• Look in all Orange Boxes on Page E 2.8. – You can increas

e and decrease height if rows to

add white space between inform

ation.

– -4You may useEEEEE EEEEE EE EEEEE et hat Excel i nt erpretsyear val ues t h eway you i nt end.

– EEEE EEE EE EEEEE EEEE EEEE E EEEEE EE EE EEEEE EEE EEEE E EEEEE EE… ||“ nter”key| Ent er t he da

ta.


EE EEEEEEE EEEEE E EEE EEEEEEE g dates into Excel.• 27On Page E . , Tab - EEEE E EEE E2 1 ,

EEEEEEE EE EEEEEE E y MM/DD/YY EEE EEE EE.

edt o ent er i n t hi s f o rmat“ DD/MM/YYYY” ….always follow this format! Otherwise, you might get wrong calculations at the end.

• A dat e should be righ- t aligned in the cell b

ecause it is considere d as a number. If you

havet r oubl e, t r y t o fi x it by Select the date

cell | Right-Clicking | Choose “Format Cells” | Choose “Date” | Choose the format you want.


• This is how the worksheet looks wh en the titles and numbers are enter ed before any calculation.


• Calculations:1. I ni t i al Cost = Shar es x I ni t i al Pr i c

e 2. Current Value = Shares x Current Price 3. Gain/Loss = - Current Value Initial Cost

**-


Entering Formulas• In Excel, you can start

a formula with equal ( =) , plus (+), and min - us ( ) signs and they will be considered as

numbers and numbe rs can be calculate

d!• Remember that! If

you want to multiply i n Excel, you need to

press the asterisk (*) not the letter x (X).

• Youcanent er t he cel l r e ferences in formulas

in uppercase or low ercase, and you can

addspace bef or e and a fterar i t hmet i c oper at orst o make t he f or mul aseasi er t o r ead.– “ =d3 * e3 ” is th

3e same as “=D *3e”


Order of Operations i nExcel P2

• Order of Operatio ns or Precedence: 1. First Negation (

-3) 2. All percentage ( %)

EEE EEEEEEEEEEE3

ions (^)

4. All multiplication (*

EEEE ) ( / )

-- Left > Right

5. All additions (+) an

- d Subtractions ( ) fro

-- m Left > Right Slash (/) is different from

backslash (\)


– For example: • - =6+( 3) 2̂ | E E69 1

5

•=D1 / X6 - A3 * A4 + A5 ^ A6– ^ / * – +


4. -Percentage Gain Loss = - Gain Loss / Initial Cost

/


• Using Point Mode– Point mode al

lows you to cre ate formulas u sing the mouse

. (Page E 2.11 i n Orange Box)

• Using the Fill Handle– 213PageE .– The Fill Hand

le EE E EEEEE EEEEEEEEE EE EEE EEE EE-EEEEE EEE

ner of the acti EEEEE.


• Relative References (Page E 2.14)– Copy downward , Excel adjusts the row portio

n 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 ” t ab | Check in the box in front of the “Formulas.”


5. Compute the Totals for Initial Cost , Cur rent Value , and -Gain Loss.

– - Percent Gain Loss comes from Total-Gain Loss / Total Initial Cost. (Page E

2.15)

/


E E EEEEEEEE E EEE EEE ’ Eotals.


BetNet’s worksheet with the E otal % Gain-Loss.


Lab Test EEEEEE.

• There will be lecture from Page E - 2 .2 3 the end of Excel Projec

2t before the Lab Test.• EEEEEEE EEE EEE EEEEE EEE EEEE

– Pr act i ce ExcelPr oj ect 1 - Excel Project 2 .. EE EE:)..

– Questionsmay come f r omt he t ext book o rf r omt he i nst r uct or .


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

a.penjira kanthawongs1 cs 202 and ics 202 - class 8 by a.penjira (mony) kanthawongs

Documents

current stock value

initial stock cost

current stock price

stock game

stock names

current value initial

penjira kanthawongsproject

penjira kanthawongsmichael