Practical 1: Using MS Excel for financial computation

1
Practical 1 Using MS Excel for financial
computation

• Gopalan Vivek
• vivek_at_bic.nus.edu.sg

2
Objectives

• Identify major components of Excel window
• Navigate within and between worksheets
• Select and move worksheet cells
• Insert text, date and values
• Insert and delete worksheet row and columns
• Resize worksheet rows and columns
• Print a worksheet

3
Start using MS Excel

• Open a blank Excel windows
• Open an existing Excel file
• Terms ( What is ?)
• Workbook (Excel file)
• Worksheet or spread sheet (sheets in the file)
• Cell
• Cell Range

4
Start using MS Excel
5
CELL

• Cell is the fundamental unit of excel worksheet.
• Constant values (string,number)
• Formulae (sum, average)
• Logical (TRUE,FALSE)
• Date(02/08/2003)
• Format
• Manipulate color, background, border, protection,
  size, font, type.

Click in the menu Format-gtcell or right on a cell
and click on formatting cell for more details in
formatting the cell
6
Entering information into cell

• Enter text phone number and Matric No.s are
  treated as text, text is aligned left by default
  finance,A1250
• Enter constant values, value is aligned right by
  default 1, 25, 0.05 etc.
• Enter date 8/28/2002, 28-Aug-2002, August
  28, 2002

7
Handling Cell content

• Deleting
• Select the cell
• Delete the content using the Del key or type on
  the cell to overwrite it.
• Editing
• Double click on a cell or select the cell and
  press F2 function key.

To cancel any changes, press ESC.
8
Cell address

• A1 is the reference to cell content in column A
  and row 1.

9
Working with cell range

• Cell range-- adjacent range and nonadjacent range
• To refer to a range of cells, enter the reference
  for the cell in the upper-left corner of the
  range, a colon (), and then the reference to the
  cell in the lower-right corner of the range
• A3B4 adjacent range
• A1C4A7C11 - non adjacent range

Use function key F5 to navigate around the
worksheet
10
Formula

• A formula is an equation that performs operations
  on worksheet data. (SIN(0.25),22, etc )
• Perform mathematical operations, such as addition
  and multiplication
• Compare worksheet values or join text.
• Refer to other cells on the same worksheet, cells
  on other sheets in the same workbook, or cells on
  sheets in other workbooks.
• Entering a formula
• Click the cell in which you want to enter the
  formula.
• Type (an equal sign). Enter the formula.
• Press ENTER.

11
Examples (52)3 A1SIN(PI()/4)
12
Move or copy a formula

• When you MOVE a formula, the cell references
  within the formula do not change.
• When you COPY a formula, absolute cell references
  do not change relative cell references will
  change. (See the next 2 slides for more details)

13
Relative Referencing

• A cell reference used in a formula changes when
  the formula is COPIED to another cell or range.
• After the formula is copied and pasted, the
  relative reference in the new formula is changed
  to refer to a different cell that is the same
  number of rows and columns away from the formula
  as the original relative cell reference is to the
  original formula.
• For example, if cell A3 contains the formula
  A1A2 and you copy cell A3 to cell B3, the
  formula in cell B3 becomes B1B2.

Relative Referencing
14
Absolute referencing

• Unlike relative references, absolute references
  don't automatically adjust when you copy formulas
  across rows and down columns.
• Use the character before the column or row
  references to FREEZE.
• It takes the form A1, B1, and so on.
• An absolute row reference takes the form A1, B1
  and so on.(Row reference wont change when
  copying the formula)
• An absolute column reference takes the form A1,
  B1, and so on. .(Column reference wont change
  when copying the formula)

Type a formula in cell and press function key
F4 several times to change the FREEZE status.
15
Mouse Pointers
16
Working with rows and columns

• Insert or delete a row or column
• Insert or delete multiple rows or columns
• Insert or delete individual cells
• shift cell left, shift cells down, entire
  row, entire column
• Resize the width of the row or height of the
  column

Type insert row or delete row in the Excel
Help to learn more about insertiing and deleting
cells. Press function key F1 for help.
17
Select a range

• Select a adjacent range
• Select a nonadjacent range
• Select all the cell in the worksheet
• Select a large amount a cell
• Select a entire row or column

Type select text in the Excel Help to learn
more about selection features. Press function key
F1 for help.
18
Exit the Excel

• Remember to save before exit the application

19
Income statement

• Text constant value editing
• Formatting
• Cell border
• Cell font, background color
• Cell size
• Cell format (number of decimal character,etc.)
• Formula
• Referencing

20
(No Transcript)
21
Sample Income statement
http//www.orbital.com/Investor/CurrentIncomeState
ment/
22
http//biz.yahoo.com/fin/l/m/msft_ai.html
Sample Income statement
23
Practice Do this after finishing your Quiz (if
you have time)

• Create a multiplication table (30 x 30) using
  formula.
• Find the average, sum, minimum, maximum values of
  the function y 10x3 5x2 for x 0, 0.1, 0.2,
  , 20.

24
Some useful links .. .. ..

1. http//www.baycongroup.com/el0.htm
2. http//www.uic.edu/depts/accc/seminars/excel-intro
   /welcome.html
3. http//www.bris.ac.uk/is/selfhelp/documentation/ex
   l5-i1/exl5-i1.htm
4. http//www.wellesley.edu/Computing/Excel/excelbasi
   cs.html