GEK2507

1
Compound Prosper!

• GEK2507

Frederick H. Willeboordse willeboordse_at_yahoo.com
2
Stocks

• Lecture 7

3
Todays Lecture

• What are Stocks
• How can we find information about Stocks
• How can we use Excel to keep track of our Stocks

4
Excel Basics MIN MAX
Syntax
Min gives the minimum of a set of numbers.
Between the brackets, one can either list the
numbers (cells) separated by commas, list a
range, or combine the two Eg. Min(B2B6,C2)
Syntax
Max gives the maximum of a set of numbers.
Between the brackets, one can either list the
numbers (cells) separated by commas, list a
range, or combine the two Eg. Max(B2B6,C2)
5
Excel Basics AVERAGE MODE
Syntax
AVERAGE calculates the arithmetic mean, i.e. it
sums all the values and divides by the number of
values. Between the brackets, one can either list
the values (cells) separated by commas, list a
range, or combine the two Eg. AVERAGE(B2B6,C2)
Syntax
MODE returns the most frequently occurring value.
Between the brackets, one can either list the
numbers (cells) separated by commas, list a
range, or combine the two Eg. MODE(B2B6,C2)
6
Excel Basics MEDIAN
Syntax
MEDIAN determines the median value, i.e. it is
the value such that half the numbers listed is
larger and half is smaller. Between the brackets,
one can either list the values (cells) separated
by commas, list a range, or combine the two Eg.
MEDIAN(B2B6,C2)
Note The median is a very important indicator
and often more useful than the average as the
meaning of an average can be strongly distorted
by a single very different number. E.g. If Bill
Gates were to live in Kent Vale (NUS staff
housing), you could say that on average NUS
lecturers are multi-millionaires and hence very
rich!
7
Excel Basics Macros
A Macro is a series of commands that one can
execute any time. While it is possible to do
fancy programming, the good news is that macros
can actually be recorded and even be assigned to
graphics objects/buttons.
For Example, let us make a macro for changing the
font etc, of a cell
8
Excel Basics Macros
Step 1 Put Excel into the Macro recording mode
9
Excel Basics Macros
Step 1 Put Excel into the Macro recording mode
Give the Macro a name
You can also define a convenient shortcut.
After clicking OK, you will see a little stop box
appearing.
10
Excel Basics Macros
Step 2 Do the things you want to do, e.g.
1) Format Cells
3) Change Alignment to Align Right
4) Click Stop button
2) Change font etc.
11
Excel Basics Macros
Step 3 Run/Apply the Macro
1) Select a cell
2) From the tools menu, choose Macro-Macros
Result
3) When this box pops us choose which Macro you
want to run
12
Excel Basics Macros
Step 4 Make a simple button
1) Click the rectangle button and draw it
somewhere
2) Double-click on the newly-drawn rectangle to
get the format box. Choose a new color and click
OK.
13
Excel Basics Macros
Step 5 Assign Macro
1) Select the square, right-click on the mouse to
obtain this menu and choose Assign Macro.
2) This box will pop up and allow you to choose
the macro which you want to assign to the square.
14
Excel Basics Macros
Step 6 Run Macro
And this is the result.
1) Select a Cell and click on the newly-made
button.
15
Why Stocks
In order to setup a business or invest into new
business one needs money! Dont we all . The
natural question is of course Where can we get
that money for our earth-shattering idea. One
option would be to go to a bank
16
Why Stocks

• but two problems are immediate.
• Banks do not really like promises, they like
  solidity
• Banks charge interest, and that can be very
  significant.
• Would there be any better ways to get money?

17
Why Stocks
If you would want to set up a small company, you
could ask some friends or family members to take
a stake in return for partial ownership of your
company. Hey. Thats quite a good idea! You get
money on a promise and you will not have to pay
any interest. In return, if you are successful,
your stakeholders will get a share of the
profits. Great! A somewhat more formal
implementation of this is the issuance of stock.
18
Why Stocks
When a company is setup, one can issue a certain
number of Stock certificates. These certificates
represent the partial ownership of the
company. The certificates then can be sold to
investors who hope that the company (and thus the
stock certificate) will increase in
value. Naturally, the detailed process is quite
tricky and there are many laws governing this but
the principle is very simple.
19
Why Stocks
Often the founder of a company buys all or a big
part of the issued stock certificates. The
founder then owns the certificates, but the
company has the money. Theres an important
issue here. Companies that issue shares are
considered legally independent entities. They
therefore act and are treated like they were a
thinking person. We are quite used to this. We
e.g. say I bought this from Creative (Of
course you didnt, you bought it from the sales
person).
20
Why Stocks
Just being able to buy the stock certificates
when they are issued is not very
practical. Hence the Stock market was invented
so that investors can trade (or sell/buy) stock
certificates. To do so a stock needs to be
listed and fulfill certain conditions (hence
this is only done for not-so-small
companies). Trading on the stock market can be
difficult though which is why there are brokers
who help you do the trading for a commission.
21
Why Stocks
Its interesting to note that stock certificates
are actual pieces of paper. In principle, when
you buy a stock certificate you are entitled to
get this piece of paper. When the company whose
stock you are buying is not listed on any stock
market, you will get the certificate. For listed
companies, however, the moving around of the
physical certificates is a big hassle so most
investors will never ask for them and leave them
stored in a central place.
22
Why Stocks

• As always, a great place to find info is on the
  web
• Exchanges
• http//www.nyse.com
• http//www.nasdaq.com
• http//www.simex.com.sg/
• Brokers
• http//www.etrade.com
• http//www.poems.com.sg/

23
Stocks and Excel
Now let us assume that somehow you can buy and
sell stocks and that you want to keep track of
your investments. Lets start simply.
24
Stocks and Excel
IF(C11 "Buy",E11F11,-E11F11)
SUM(G8G11)
25
Stocks and Excel
This is nice, but what if we make a typo in
column C? Fortunately the formula can be improved.
IF(OR(C8 "Buy",C8 "Sell"),IF(C8
"Buy",E8F8,-E8F8),"error")
What a Monster! No, actually its quite
straightforward.
26
Stocks and Excel
I guess buying Yahoo during the internet bubble
was not such a good idea. This reminds me of A
reporter asks Richard Branson (the flamboyant
founder of Virgin) How do you become a
Millionaire? What do you think? And he
replies .
27
Stocks and Excel
Start as a Billionaire and buy an Airline!
Perhaps I should paraphrase Start as a
Billionaire and become an Investor
28
Stocks and Excel
The spreadsheet we just made can neatly keep
track of our transactions but of course it
doesnt reflect at all the current value of our
portfolio. In that sense its a rather useless
spreadsheet. So, lets add a second sheet to
keep track of this.
29
Stocks and Excel
On this sheet we can enter the valuation of the
portfolio.
This hurts!!!
But how much?
30
Stocks and Excel
On the one hand we can of course say it hurts by
778 200 578 dollars (balance on 10/10/00 on
slide 25 minus the remaining value on slide
29). True but since weve been buying and
selling stocks, the absolute amount doesnt
necessarily mean much. Somehow, it would be good
if we could keep track of our performance more
accurately so that we can compare ourselves to
others. May be we didnt do so badly but thats
outside the scope of this lecture (a useful Excel
function in this context is XIRR ).
31
Stocks and Books
There are all kinds of books be aware of their
quality! On a related note. Financial Planners
are all the rage now. It is surprising to see how
many (intelligent) people entrust their money to
often poorly qualified individuals. In case of
doubt, always go with simple, big,
well-known, trustworthy like e.g. NTUC, DBS,
Schröders. The following three books represent
certain types of books. That doesnt mean I
recommend them.
32
Stocks and Books
This is quite a good book I think.
33
Stocks and Books
Another good book. Though, it looks quite flashy.
How do you know that this one is OK? Make your
own judgment!
34
Stocks and Books
Well, this book looks similar to the one by Lynch.
The topics appear sound enough!
35
Stocks and Books
The book reads reasonable enough until you
consider what he doesnt tell you. But this gives
him away!
36
Stocks and Books
But heres a book I fully recommend!
37
Stocks and Books
The best and worst performance of stock, bond,
T-bills for various holding periods.
Note T-bills are short term (generally 3,6 or 12
months) government securities.
38
Stocks and Books
Be aware! This is the nominal chart. I.e.
inflation and taxes are not accounted for.
39
Stocks and Books
While less than the nominal numbers, the
difference between the various classes is clear.
40
Stocks (recent charts)
Dow last 80 years.
Dow last 5 days.
Nikkei last 25 years.
41
Stocks (trading)
Trading is NOT investing!
42
Key Points of the Day

• Stocks play a key role in business
• Excel is a great tool to keep track of financial
  data
• Be critical of data as presented know what they
  mean
• Youd better not let me manage your money!

Think for yourself!