EXCEL training workshop on Financial Modeling
Welcome to 2 days
Workshop on
Financial Modeling by using Spreadsheets
Shivaraj Bhor BE, PG (NITIE), CWA
Deputy Manager (Mgmt Acct Cell)
GAIL (India) Limited
1
Objective of the Workshop
• Enabling Business Managers:
– Actively learn : To Design, Develop / Implement spreadsheet real-world models
– Put theory into Practice: By understanding Model Formulation
– Increase Analytical Skills: By Exploiting
Mathematical techniques
Exploring Office 2003 - Grauer and Barber
Introduction to
Excel
3
Spreadsheet
• Spreadsheet is the PC application that is used most frequently by managers and executives.
– It is the computerized equivalent of an accountant's ledger.
– Enables us to organize data in a readily understandable format.
Exploring Office 2003 - Grauer and Barber
Introduction to Spreadsheets
• Workbook – contains one or more worksheets
• Spreadsheet – a computerized ledger
• Rows and Columns
– Columns identified with alphabetic headings
– Rows identified with numeric headings
• Intersection of row and column forms a cell.
Definitions
• General format – it is the default format for numeric entries and displays a number according to the way it was originally entered.
• Number format – displays a number with or without the 1000 separator and with any number of decimal places. Negative numbers can be displayed with parentheses and/ or can be shown in red.
• Scientific format – which displays a number as a decimal fraction followed by a whole number exponent of 10; for example the number 12345 would appear as 1.2345E+04. The exponent, +04 in the example, is the number of places the decimal point is moved to the left. Very small numbers have negative exponents.
An Excel Workbook
Rows, Columns, and Cells
Types of Cell Entries Constants & Functions
• Constant – an entry that does not change
– Can be a numeric value or descriptive text
| Student | Adam | 100 |
• Functions – a predefined computational task
– Users cannot change them
– =AVERAGE(B3:B7)
– =SUM(A1:B2)
Formulas and Constants
• Formulas and Constants
– Constant is entries that does not change. It may be a number, such as a grade on an exam, or it may be descriptive text (name).
– Formula is combination of numeric constants, cell references, arithmetic operators, and/or functions.
– Always begins with an equal sign
– =(B3+B4+B5/5)
– =AVERAGE(B3:B7)
Toolbars
• Appear beneath the menu bar
• Contain buttons that perform commonly-used commands
• Standard toolbar – buttons correspond to most basic commands in Excel
– Examples include opening, closing, and saving a workbook
• Formatting toolbar – buttons correspond to common formatting operations
– Examples include boldface and cell alignment
12
Worksheet Basics
13
14
• Excel’s main screen is called a “worksheet”.
• Each worksheet is comprised of many boxes, called “cells”.
Organize Information
• You can organize information by typing a single piece of data into each cell.
How to Enter Information
16
Selecting a Cell
• “Select” a cell by clicking on it once (don’t double click).
• You can move
from cell to cell with the arrowkeys or by pressing the “Enter” key.
18
Entering Information / The Formula Bar
Double Click to Modify a Cell
• To modify the
contents of a cell double click on the cell.
• Then use the right, left arrow keys and the Insert and Delete keys to modify the data.
• When you are done:
– Press the Enter key or
– Click on the check box.
Names of Rows, Columns and
Cells
20
Column Names (letters) & Row Names
(numbers)
Cell Names (ex. B4)
• The name of a cell is a combination of the Letter Of The Column that the cell is in followed by the Number Of The Row that the cell is in.
• Example: the selected cell in the picture is named B4 (NOT 4B)
• Excel automatically shows the the name of the currently selectedcell in the “name box” (located above the worksheet).
• The letter must come first (i.e. B4, NOT 4B) and there may NOT be any spaces between the letter and the number.
• We will learn later why it is important to understand how to name cells.
Information that is “too wide” for a cell
• The word “Name” is in cell A5
• The words “Hours Worked” are in cell B5 (NOT in cell C5). However, since the information is too wide for cell B5, it looks like it extends into cell C5.
• You can determine that the information is really onlyINcell B5 by selecting cellB5 and looking at theformula bar and then selecting cell C5 andlooking at the formula bar.
Information that is “Chopped Off”
• If there is • You can see the
information in the complete data by
cell to the right, then selecting the cell and the original cell still looking in the formula
Change the Width of a Column or the Height of a Row
25
Make a column wider Drag column
separator to the
• To make Column B wider, point the cursor to the column separator between columns B and column C.
• The cursor changes to a “Double headed arrow”.
• Now, click the left mouse button and without letting go of the button, drag the separator to the right to make the column
wider (or to the left to make the Column is now wider column narrower).
Getting the Exact Width
• To get the “exact” width, Double click here double click on the separator instead of dragging it.
Column is now EXACTLY the correct width
Resizing a Row
• Make a
row taller or shorter to resize row 5.
Putting an “Enter” inside a cell
Basic Formatting
(e.g. bold, colors, fonts, etc)
30
Formatting Cells
• Select one or more cells and then click on any of the formatting buttons (see below) to change the formatting of the selected cells.
• Formatting buttons:
Example – unformatted worksheet
• Unformatted worksheet – see next slide for formatting.
Example –making cells bold
• Click on cell A1 and drag to cell A3.
• Then press the Bold button to make cells A1,A2,A3 bold.
• You could also press the font or background color buttons to change the color or apply any other formatting you like (this is not shown below).
Other Ways of Selecting More Than
One Cell
• To select a large range of cells, click on the upper left cell in the range. Then hold the shift key and click on the lower right cell in the range.
• You can select different “non-contiguous” areas of cells by holding down the Ctrl key while clicking and dragging.
Selecting Non-Contiguous Ranges
• Click and drag to
l ranges
Selecting entire Rows, entire Columns or all cells on the worksheet.
• To select an entire column, click on the letter for the column header. To select several columns, click on the header for the first column and drag to the right.
• To select an entire row, click on the number for the row header. To select several rows, click on the header for the first row and drag down.
• To select all of the cells on the spreadsheet, click on the upper left hand corner of the spreadsheet (where the column headers meet the row headers)
Select Entire Columns/Rows/Worksheet
Example - continued
• Step 1: Click on row header for row 5
• Step 2:
Ctrl-click on row-header for row 11
• Step 3: Press Bold button or type ctrl-b
• Note: After
being “bolded”, the word “Employee” is now too wide for the column, so make the column wider if necessary (this step is not shown).
Format Cells
• Using the formatting buttons only give you a limited amount of formatting ability.
• For more formatting ability, select one or more cells and right click on the selection. Then choose “format cells” from the popup menu.
• Choose options from the Number, Alignment, Font, Border and Patterns tabs and press OK to change the way your information looks on the screen.
• The Protection tab is used to lock cells so that their contents can’t be modified.
• We will not go into the details of using the format cells dialog box at this time but you should be able to figure out most of it by yourself.
Formatting changes how things LOOK, not how they WORK.
• When you change the format of a cell, Excel still “remembers” the original value.
• Excel will use the un-formatted value when calculating formula values.
• Example: if you change numbers to appear with fewer decimal points the original number with all of its decimal points are used in calculations.
Formulas
41
Excel Formulas
• You must have an equals sign ( = ) as the first character in a cell that contains a formula.
• The = sign tells excel that the contents of the cell is a formula
• Without the = sign, the formula will not calculate anything. It will simply display the text of the formula.
Formulas - correct
Missing = sign
Types of operations
• You can use any of the following operations in a formula:
| operation | symbol | example |
| addition: | + | =a1+3 |
| subtraction: | - | =100-b3 |
| multiplication: | * | =a1*b1 |
| division: | / | =d1/100 |
| exponentiation | ^ | =a2^2 |
| negation | - | =-a2+3 |
(same symbol as subraction)
Explicit (literal) values and cell references
• You can use both explicit values and cell references in a formula
• An explicit value is also called a literal value
– Formula with only cell references: =a1*b1
– Formula with only literal values:
=100/27
– Formula with both cell references and literal values:
=a1/100
Errors in Formulas
47
Common Errors
• The following are some errors that may appear in a spreadsheet (there are others too).
– #######
• Cell is too narrow to display the results of the formula. To fix this simply make the column wider and the “real” value will be displayed instead of the ###### signs. Note that even when the ###### signs are being displayed, Excel still uses the “real” value to calculate formulas that reference this cell.
– #NAME?
• You used a cell reference in the formula that is not formed correctly (e.g. =BB+10 instead of
=B3+10)
– #VALUE!
• Usually the result of trying to do math with a textual value. Example: =A1*3 where A1 contains the word “hello”
– #DIV/0!
• Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero)
– Circular Reference
• Using a formula that contains a reference to the cell that the formula “lives in”. Example: putting the formula =A1+1 in cell A1 or putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2
Order of Operations
49
Complex formulas
• You can use several operations in one function
• You can group those operations with parentheses
• Examples
=3*2+1
=c1*(a1+b1)
=(100*a2-10)+(200*b3-20)+30
=(3+2*(50/b3+3)/7)*(3+b7)
Order of operations
• When using several operations in one formula, Excel follows the order of operations for math.
| – first: | all parentheses - innermost first |
| – second: | exponents (^) |
| – third: Do | all multiplication (*) and division (/). |
| these starting with the leftmost * or / | |
| and work to the right. | |
| – fourth: | all addition (+) and subtraction (-). Do |
| these starting with the leftmost + or - | |
| and work to the right. |
Order of operations
• The value of
3 + 2 * 5
is
13
NOT 25!
Order of operations
3 + (100 - 20) / 10 - 6 * 2 / 4 + 9
3 + 80 / 10 - 6 * 2 / 4 + 9
3 + 8 - 6 * 2 / 4 + 9
3 + 8 - 12 / 4 + 9
3 + 8 - 3 + 9
11 - 3 + 9
8 + 9
answer: 17
Functions
What is a function?
• A function is a "named operation"
• Functions have
– a name
– parentheses
– parameters/arguments inside the parentheses
• The words parameter and argument mean the same thing
• you can have many parameters for one function separated with commas (,)
• The number of parameters is one more than the number of commas.
The SUM function
• Examples
| Function | Result |
| =SUM(1,2,3,4,5) | 15 |
| =SUM(a1,b1,c1) | a1+b1+c1 |
| =SUM(9,a1,b2,5,c1) | 9+a1+b2+5+c1 |
Terminology
SUM(1,2,3,4,5)
– The name of the function is "SUM"
– The parameters or argumentsto this function are
1,2,3,4 and 5
– The entire thing, i.e. SUM(1,2,3,4,5), is a function call
– The value of this function call is 15.
Another way to say this is that this function call returns15.
Ranges (e.g. a1:c3)
58
Ranges
• A rectangular box of cells is called a “range”.
• The name of a range is
– the name of the upper left cell of the range – Followed by a colon :
– Followed by the lower right cell of the range
• Example: A1:B2 is shorthand for A1,A2,B1,B2
– See next slide for more examples
Examples of Range Names
B3:E3
Using a range as a parameter
• Ranges can be specified as a parameters to a function call.
• Both of the following function calls produce the same result as =a1+b1+c1+a2+b2+c2+a3+b3+c3+a4+b4+c4 however the 2nd version uses a range and is much shorter.
without a range
=SUM(a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4)
with a range
=SUM(a1:c4)
Function calls with multiple parameters
• You can include multiple ranges and cells as parameters
• Example: the following function call has 3 parameters.
There are two ranges (a1:b2 and c4:c7), one number
(100) and one cell reference (d3)
=SUM(a1:b2,100,c4:c7,d3)
Is the same as:
=SUM(a1,a2,b1,b2,100,c4,c5,c6,c7,d3)
Other Functions
63
Other functions
• Click the function button to see the available
Example
• AVERAGE
formula that contains a function value
=AVERAGE(2,4,10,4) 5
=AVERAGE(a1,f32) (a1+f32) / 2
=AVERAGE(a1:c1) (a1+b1+c1) / 3
=AVERAGE(a1:c1,10) (a1+b1+c1+10) / 4
Combining Functions and other values in a single formula
Functions and other values
• You can combine functions, cell references and literal values to make a complex Excel formula
• Examples
=3 + b23 * SUM(d20:g20)
=SUM(a1,100) * AVERAGE(d10:j10)
=100 / ( AVERAGE(b2,c2,d30) + AVERAGE(f1:f20) )
Other Types of Cell References
References to entire ROWs
References to entire COLUMNs
References to cells or ranges on other worksheets (i.e. tabs)
Entire Rows (e.g. 2:2 or 2:4)
• A cell reference of the form <rowName>:<rowName> refers to the range of all the cells for those rows.
• Example:
– The reference, 2:2, refers to all of the cells on the 2nd row.
– The following formula adds up all of the values on the 2nd and 4th rows of the spreadsheet:
=sum(2:2,4:4)
• Another Example:
– The reference, 2:4, refers to all of the cells on the 2nd , 3rd and 4th rows,.
– The following formula adds up all of the values on the 2nd, 3rd, 4th , 10th, 11th , 12th, 13th, 14th and 15th rows of the spreadsheet:
=sum(2:4,10:15)
Entire Columns (e.g. B:B or B:D)
• A cell reference of the form <colName>:<colName> refers to the range of all the cells for those columns.
• Example:
– The reference, B:B, refers to all of the cells in the 2nd column.
– The following formula adds up all of the values in the 2nd and 4th columns of the spreadsheet:
=sum(B:B,D:D)
• Another Example:
– The reference, B:D, refers to all of the cells in the 2nd, 3rd and 4th columns.
– The following formula adds up all of the values in the 2nd, 3rd, 4th, 6th and 7th columns of the spreadsheet:
=sum(B:D,F:G)
References to cells on other worksheets
• Cell on another sheet: sheetName!cellReference
• Range on another sheet: sheetName!range
• Row on another sheet: sheetName!row:row
• Column on another sheet: sheetName!column:column
• If a sheet name has a space in it, you must surround the sheet name with apostrophes (i.e. single quotes)
• Examples
sheet2!a1 sheet2!b4:c8
'2002 Forecasts'!f3:f10
=sum('2002 Forecasts'!f3:f10)
=sum('2202 Forecasts'!f:f)
More examples
• Add up values from 2 different sheets
=sum ( 'great stocks'!b2:c4, 'so so stocks'!b2:c4)
• This next one is a little confusing
=sum (a1,a!a1,b1:b4,b1!b4,c!c:c)
Explanationa1 this is a cell reference on the current sheet a!a1 "a" is the name of sheet. "a1" is a cell on the "a" sheet b1:b4 this is a range on the current sheet
b1!b4 "b1" is the name of a sheet. "b4" is a cell on the "b1" sheet
c!c:c “c" is the name of a sheet. “c:c" is all of the cells in the c column
on the “c” sheet
Absolute and Relative Cell References Absolute and Relative Cell
References
• By default, when you copy a formula that contains a cell reference, excel will automatically adjust the cell reference.
• You can stop Excel from automatically adjusting the cell reference by using one or more dollar signs ($) in the cell reference. These are called absolute cell references.
• A cell reference without a dollar sign is a relative cell reference.
Examples
• The following all refer to the same cell
d9 $d$9 $d9 d$9
• The only difference between these cell references relates to what happens when you copy a formula that contains the cell reference.
Relative Cell Reference
• d9 This is a "relative cell reference".
– Changing the column: If I copy this cell reference to another cell:
• the "d" will increment one letter for every cell that I move over to the right.
• The "d" will decrement one letter for every cell that I move over to the left
– Changing the row: If I copy this cell reference to another cell:
• the "9" will increment by one for every cell that I move down.
• The "9" will decrement by one for every cell that I move up
Absolute cell reference
• $d$9 This is an absolute cell reference.
– If I copy a formula with this cell reference, the cell reference will NOT change AT ALL.
Mixed References
• $d9 and d$9 These are "Mixed" cell references:
• $d9
– The "d" will stay the same when you copy the cell, but the "9" will change.
• d$9
– The "d" will change when you copy the cell, but the "9" will stay the same.
Data Types
Data Types
• Numeric
– values: any number
– operators: + - * / ^ %
– sample functions: sum( ), average( ), max( ), min( ) etc.
• Text (AKA Character or String)
| – values: | Any group of letters or numbers or special characters. | ||
| Prefix value in cell with an apostrophe ( ' ) to force a text value | |||
| – operators: | & (concatenation) | ||
| – sample functions: | right( ), left(), mid(), lower(), upper(), len(), etc | ||
| • | Dates | ||
| – values: | dates and times | ||
| operators: | N/A | ||
| – sample functions: | now( ), today( ), hour(), minute(), etc. | ||
| • | Logical (AKA boolean) | ||
| – values: | true false | ||
| – Operators: | < > = <> <= | >= | |
| – sample functions: | if( ), and( ), or( ), not( ), isblank() | ||
Data Types for Values in Cells
• By default:
– a cell that contains a number is treated as numeric data
– a cell that contains a date is treated as date data (we'll see more about this later)
– a cell that contains data which is not numeric and not a date is treated as "text"
Text Data
82
Text / String / Character
• The following three terms all used to refer to "text" data. All three terms mean the same thing.
– text data
– string data
– character data
• This presentation will generally use the term "text data" but you should be familiar with the terms "string data" and "character data"
84
Text data
• Text data is used to store general purpose text
(e.g. names, places, descriptions, etc)
• You can't do "math" with text values
(obviously)
Text isn't part of numerical calculations
(obviously)
Text Functions
86
Text Functions
• Many functions are used to manipulate text values.
• The following are only some of them right( ) left( ) mid( ) concatenate( ) lower( ) upper( ) len( )
RIGHT, LEFT and MID functions
88
RIGHT function
• The RIGHT function is used to isolate a specific number of “characters” from the right hand side of a text value.
• (example on next slide)
RIGHT ( <text>, <numCharacters>)
Formula View
Values View
RIGHT – numCharacters is optional
• The <numCharacters> parameter in the RIGHT function is optional. If you don’t specify it the default is 1 (one).
Formula View
These produce the same results.
Values View
LEFT
• The LEFT function is the same as the RIGHT function, but it returns characters from the LEFT side of the value.
MID ( <text>, <startPosition> , <numCharacters>)
• MID is used to get values from the middle of some text.
• MID takes 3 parameters:
– The original text
– The position to start taking the new value from
– The number of characters to take for the new value
• Example on next slide
Example: MID ( <text>, <startPosition> ,
<numCharacters>)
• This example extracts the second through the fourth characters from the original text value:
Formula View
Values View
Concatenation
( & ) and
CONCATENATE function
95
Concatenation (&)
• Use & to combine (or concatenate) two different text values
Concatenate many values
• You may concatenate many values together
Concatenation with "literal" values
• You can also concatenate "literal" values.
• You must include the literal values inside quotes
• For example to display spaces in the "full name" in the previous example you could use the following formula. Each space that you want to display must be included in quotes.
=A2&" "&B2&" "&C2
(Don't forget any of the &'s )
Concatenating spaces - Example
• You can concatenate spaces into a formula
LEFT( ) with & in same formula
• You can combine the results of different function calls with concatenation.
Formula View
Values View
Putting it all together
• In this example we concatenate periods into the initials.
Separate first name & Last Name
- Use of Search & Left function
Formula View
Values View
CONCATENATE Function
• You can use the CONCATENATE function instead of the ampersand (&).
• The following formulas are equivalent:
=A1&B1&C1
=CONCATENATE(A1,B1,C1)
• The CONCATENATE function can take as many parameters as you like.
More Text Functions:
PROPER
LOWER
UPPER
LEN
104
LOWER ( <textValue> )
UPPER ( <textValue> )
• LOWER converts text to lower case.
• UPPER converts text to upper case.
• PROPER Coverts first letter in upper Case • Example:
Formula View
Values View
LEN ( <textValue> )
• LEN returns a numeric value equal to the number of character in a text value (i.e. the “length” of the text value).
• Spaces ARE included in the length.
• Example
Formula View
Values View
TRIM ( <textValue> )
Formula View
Values View
REPT ( “|”,Cell Ref )
Formula View Values View
Dates and Times
109
How Excel Stores Dates
• Dates are stored in Excel as the number of days since Dec 31, 1899 for that date. (ex. Jan 1, 1900 is stored as the number 1).
• To see this, type a date in a cell and then press Ctrl-` to see the “formulas view”.
• Example
– Values View
Times and Dates in the same Cell
• A cell can contain both a date and a time.
• The value of both the date and the time is stored internally as a single decimal number.
• The whole number portion represents the DATE and is the number of days since Dec. 31, 1899
• The decimal part represents the TIME and is the fraction of the day that has elapsed.
• Examples:
– Jan 1, 1900 at 12AM is 1.0 (i.e. 1 day since Dec 31, 1899 and 0 percent of the day elapsed so far)
– Jan 1, 1900 at 12PM is 1.5 (i.e. 0.5 of the day elapsed)
– Jan 2, 1900 at 12PM is 2.5 (i.e. 2 days since Dec. 31, 1899)
– Feb 1, 1900 at 1:05 PM is 32.5451388888889 (i.e. 32 days since Dec 31, 1899 and 0.5451388888889 of the day elapsed by 1:05 PM. This makes sense as it is a little past noon so a little more than half of the day elapsed.
Times and Dates - Example
• Values View
• Formulas View
Date Arithmetic
• You can do arithmetic with dates.
• Add and subtract days by adding and subtracting whole numbers.
• Add and subtract times by adding and subtracting fractional values.
• Examples
=A1+7 (one week after the date in A1)
=A1-5*7 (5 weeks before the date in A1)
=A1- (1/24) (one hour before the time specified in A1)
=A1+ (3/24) (three hours after the time specified in A1)
=A1+2.5 (two and a half days after the time specified in A1)
=A1-A2+1 (the # of days between the date in A1 and the date in A2)
Formatting cells with Dates and
Times
• Right click on the cell and choose “Format Cells”
• From the “Category” list in the “Number” tab either
– Choose “Date”, “Time” or “Custom” and choose an
appropriate looking format
OR
– If you choose “General” or “Number”, the internal number for the Date/Time will be displayed in the spreadsheet even in the “values” view.
Date & Time Functions
• Current Date - TODAY( )
• Current Time – Now ( )
• Year ( )
• Month ( )
• Day ( )
Date & Time Functions
• Add months to start dates by : EDATE (start date, months)
• Extract age of person by – DATEDIF (Start date, end date, format)
– Format :
• ‘Y’ for difference in years
• ‘M’ for difference in months
• ‘D’ for difference in Days
• ‘YM’ for difference in Months ignoring years
• ‘YD’ for difference in Days ignoring years
• ‘MD’ for difference in Days ignoring Month & years
Date & Time Functions
• Formula View
• Values View
Logical values
118
Logical operators
• In Excel the following "operators" are used
Operator Meaning
| > | greater than |
| < | less than |
| >= | greater than or equal to |
| <= | less than or equal to |
| = | equal to |
| <> • Examples | not equal to |
| 3 > 2 | true |
| 3 < 2 | false |
Logical Formulas
Same formulas, different values
IF function Formula View Values View
IF with a numerical result
AND
OR
NOT
AND
• The following is TRUE
Fish live in water AND deer live on land.
• The following are all FALSE
Fish live in water AND deer live in water.
Fish live on land AND deer live on land.
Fish live on land AND deer live in water.
AND function
AND
IF with AND - nested function calls
• You can use an AND inside of an IF.
• This is called a NESTED FUNCTION CALL
• Example
=IF( AND (A2>A3,B2<>B3) , 500, 1000)
These parentheses "belong to" the if
IF with AND - parameters
Parameters for IF function:
IF with AND - spreadsheet views
AND function
• Takes any number of parameters
• Returns TRUE if ALL of the parameters evaluate to TRUE otherwise returns FALSE.
OR and NOT functions
OR
• Takes any number of parameters
• Returns TRUE if ANY of the parameters evaluate to TRUE otherwise returns FALSE
NOT
• Takes ONLY ONE parameter
• Returns the "opposite" of the value of the parameter
– returns FALSE if the parameter value is TRUE – returns TRUE if the parameter value is FALSE
Examples of Complex Nested Function Calls
• =IF(AND(A2>A3, OR(B2=B3,C2<C3)), 500, 1000)
• =IF(NOT(AND(A2>A3, OR(B2=B3,C2<C3))), 500, 1000)
• =IF(AND(A2>A3, NOT(OR(B2=B3,C2<C3))), 500, 1000)
Other Logical Functions:
ISBLANK
136
ISBLANK( <value> )
• ISBLANK returns TRUE if the value is blank and
false otherwise. (see example below) Total will be wrong if
quantity isblank (sincea blank is
Total will be correct even if quantity normallyis blank (quantity is assumed to be 1treated as
in that case) zero)
FORMATTING A CELL AS TEXT
138
Numbers with leading zeros
• Sometimes you desire to have to have zeroes displayed at the beginning of a number.
• For example, US social security numbers are made up of 9 digits. The first few digits may be zeroes.
• This causes in a problem in Excel. When you type in a number with leading zeroes into a cell, Excel removes the leading zeroes when you press Enter.
• EXAMPLE:
If you type the following into a cell (before you press Enter)
When you press Enter you get this:
Leading
zeroes aremissing
• See next slides for how to fix this …
Formatting a cell to display as text
• To fix this problem you can “format” the cell to display as “text” instead of as a “number”.
• The value will still be able to be used in calculations but it will be displayed on the screen using the rules for text values instead of the rules to display numbers
• One of the rules Excel uses to displaying numbers is to remove leading zeroes.
• However, if a number displayed as "text" data then Excel WILL display leading zeros.
• See next slide for instructions on how to do this
Opening the "Format cells" dialog box
• Select the cell or cells that you want to format as text.
• Right click on the selected cell(s) and choose the following from the popup menu
format cells
or click on a cell and choose the following menu choice
format | cells
• Then you will see the "Format Cells" dialog box.
(See the next slide )
"Format Cells" dialog box
• Choose
"Text" from the
"Numbe
r" tab and press the OK button.
Not a Perfect Solution …
• When you format the cell as “text” it will display the leading zeroes (you must type them in again).
• However, Excel will warn you that a number is formatted as text.
Result of Formatting a Number as
• Excel indicates this issue with a
green triangle in the upper left hand corner of the cell:
Create validation rules for data entry
• You will want to prevent errors in your workbooks as much as possible.
– You can specify the type of data that is allowed and/or a range of acceptable values
– If a value is entered that does not meet the requirements, an error message is displayed
– Setting a rule like this is a preventative measure that allows you to validate data upon entry
• There are several different options in the Data Validation dialog box allowing you to provide various rules related to data entry.
• You can also provide an input message that will aid the user in entering the data.
The Data Validation dialog box
Menu Bar: Data \ Data Validation
The Allow list box options
The Input Message tab of the Data Validation dialog box
A worksheet with an input message displayed
The Error Alert tab of the Data Validation dialog box
Functions within functions
• You can use functions within functions. Consider the expression
=ROUND(AVERAGE(A1:A100),1).
– This expression would first compute the average of all the values from cell A1 through A100 and then round that result to 1 digit to the right of the decimal point
Circular reference
Situation when some parameter in the formula refers to the formula itself
For example, in cell C5 the following formula is entered:
VLOOKUP(C5, F2:G15, 2, TRUE)
The address C5 refers to the formula itself.
In this case it is a wrong way of writing the formula.
In some cases circular references may be used for creating recursive (recurrent) functions.
Tables, Graphs and Excel
Solving Problems Using
Graphical Analysis
Tables
• Tables should always have:
– Title
– Column headings with brief descriptive name, symbol and appropriate units.
• Numerical data in the table should be written to the proper number of significant digits.
– The decimal points in a column should be aligned.
• Tables should always be referenced and discussed (at least briefly) in the body of the text of the document containing the table.
Graphs
• Proper graphing of data involves several steps:
– Select appropriate graph type
– Select scale and gradation of axes, and completely label axes
– Plot data points, then plot or fit curves
– Add titles, notes, and or legend
Graphs - Types
1. Pie Chart2. Bar Graph
Travel Expenses
Travel Expenses
Graphs - Types3. 3-D Graph4. Line Graph
Graphs
• Each graph must include:
– A descriptive title which provides a clear and concise statement of the information being presented
– A legend defining point symbols or line types used for curves needs to be included
– Labeled axes
• Graphs should always be referenced/discussed in the body of the text of the document containing the table.
Titles and Legends
• Each graph must be identified with a descriptive title
• The title should include clear and concise statement of the information being presented
• A legend defining point symbols or line types used for curves needs to be included
Axis Labels
• Each axis must be labeled
• The axis label should contain the name of the variable and its units.
• The units can be enclosed in parentheses, or separated from the label by a comma.
Gradation
• Scale gradations should be selected so that the smallest division of the axis is an integer power of 10 times 1, 2, or 5.
• Exception is units of time.
Scale Graduations, Smallest Division=1
0 10 20 30
Acceptable
Scale Graduations, Smallest Division=3.33
0 10 20 30
Not Acceptable
Data Points and Curves
• Data Points are plotted using symbols
– The symbol size must be large enough to easily distinguish them
– A different symbol is used for each data set
• Data Points are often connected with lines
– A different line style is often used for each data set
Example
Velocity of Three Runners
During a 5 km Race
Distance (Km)
Building a Graph In Excel
• Select the data that you want to include in the chart by dragging through it with the mouse.
Building the Graph
• Choose XY
(Scatter), with data connected by lines if desired.
• Click “Next”
Building the Graph
• Make sure that the series is listed in columns, since your data is presented in columns.
• Click the Series tab to enter a name for the data set, if desired.
• Choose “Next”
Building the Graph
• Fill in Title and Axis information
• “Next”
Building a Chart
• Select “As new sheet” to create the chart on it’s own sheet in your Excel file, or “As object in” to create the chart on an existing sheet
• “Finish”
Creating a Secondary Axis
• This is useful when the data sets cover very different ranges.
• Right click on the line (data series) on the chart that you want to associate with a secondary axis.
• Select “format data series”
• Select the Axis tab, then “Plot series on secondary axis” as shown.
• “OK”
Editing/Adding Labels
• Now you can go back to the “chart options” to add labels
– Click the chart in a blank area, then either right click and select chart options or choose chart options from the
Result
Goal Seek
• Goal Seek is an analytical function that allows a value in a formula to be adjusted to reach a desired result or answer.
• Goal Seek can eliminate unnecessary calculations that can be used to determine a single variable value in a formula.
• For example, a salesperson might participate in a bonus program that pays 3 percent of all sales dollars.
The salesperson wants to receive a bonus of at least $2,500 and needs to know the target sales dollar amount needed.
• Create a worksheet with the following information
• When the Goal Seek command starts to run, it repeatedly tries new values in the variable cell to find a solution to the problem.
• This process is called iteration, and it continues until Excel has run the problem 100 times or has found an answer within .001 of the target value specified.
• The iteration settings can be adjusted by choosing Tools, Options, and adjusting the Iteration options in the Calculations tab.
• It calculates so fast, the Goal Seek command can save significant time and effort over the brute force method of trying one number after another in a formula.
Goal Seek
USING THE GOAL SEEK COMMAND
• The Goal Seek feature is used to fill in the target value of the cell containing the Sales Dollar amount.
– The Goal Seek values read “Set cell = B3, To value = 2500, By changing cell = $B$1.”
• To use the Goal Seek command:
1. Select Tools from the main menu, then select Goal Seek.
2. Specify the cell that contains the desired value in the Set cell text box. Type in or select B3.
3. Enter the desired value or answer in the To value text box. Type in 2500.
4. Enter the cell whose value will be changed in the By changing cell text box. Type in or select $B$1.
5. The Goal Seek dialog box should look like Figure – next slide 6. Choose OK.
a. If a solution is found, the Goal Seek Status dialog box appears.
b. The results are shown in Figure T4.6.
7. Select OK.
• Goal Seek is used to adjust a single variable in a formula. Use the Solver feature to adjust multiple variables in a formula, as described in the next section.
Goal Seek Worksheet
Goal Seek Results
Solver
Solver
• Solver is part of a suite of functions sometimes called what-if analysis tools used for optimizing problems thatcontain more than one variable.
• The Solver add-in utility is needed to analyze the scenarios in decision-making situations that involve consideration of values and constraints for several variables simultaneously.
• This powerful function uses multiple changing variables and constraints to find the optimal solution to solve a problem.
• For example, consider a coffee shop that currently sells three beverages: (1) regular fresh-brewed coffee, (2) premium caffe latte, and (3) premium caffe mocha.
• The current price for regular coffee is set at $1.25, caffe latte at $2.00, and caffe mocha at $2.25, but the revenue potential is uncertain. What special emphasis (or marketing) should be given to each of the beverages to maximize revenue?
• Although the premium coffees bring in more money, their ingredients are more expensive and they take more time to make than regular coffee. Making some basic calculations by hand is easy, but there needs to be some structure to the sales data in a worksheet so that periodic changes can be made and analyzed.
Coffee Sales Data Sheet for Solver T4.7
• The first step in using the Solver command is to build a “Solver-friendly” worksheet. This involves creating a target cell to be the goal of your problem
– For example, a formula that calculates total revenue—and assigning one or more variable cells that the Solver can change to reach the goal.
• To use Solver, complete the following:
1. Set up a worksheet similar to Figure T4.7.
2. The three variable cells in the worksheet are cells D5, D9, and D13. These are the cells whose values the Solver needs to determine to maximize the weekly revenue.
3. In the bottom-right corner of the table is a list of constraints to use for forecasting.
4. The worksheet must contain cells (G6 through G8) that include the formulas used as constraints. The limiting values for the constraints are listed in cells G11 through G13.
• No more than 500 total cups of coffee (both regular and premium).
• No more than 350 cups of premium coffee (both caffe latte and caffe mocha).
• No more than 125 caffe mochas.
5. The subtotals for cells D6, D10, D14 need to be calculated, as well as the Total Revenue (sum of D6, D10, and D14) in G4.
6. The value for cell G6 should equal the value that will be calculated for D5 and the value for cell G7 will be the sum of the values from D9 and D13. The calculation of G8 = SUM of D5, D9, and D13.
7. Click the target cell G4—the one containing the formula that is based on the
8. variable cells you want the Solver to determine.
9. Select Tools from the main menu, then select Solver. The Solver Parameters dialog box opens, as shown in Figure T4.8.
a. Select the Set Target Cell text box (unless it already contains the correct reference), and then click cell G4 to insert $G$4 as the target cell.
b. The Equal To option button (Max) is already selected. Do not change this since the problem requests the maximum value for the target cell.
10. Select the By Changing Cells text box. Click the button in the text box to collapse the dialog box. Select each of the variable cells by holding down the Ctrl key and clicking D5, D9, and D13. This places commas between the three cell entries in the text box: $D$5, $D$9, $D$13 (refer to Figure T4.9).
11.Click Solve to calculate the result.
12.Solver displays a dialog box describing the results of the analysis. If the Solver runs into a problem, an error message will be displayed. If the Solver finds a solution, a Solver Results dialog box like Figure T4.11 will appear.
display the new solution in the worksheet, click the Keep Solver Solution option button, and then click OK. The Solver places an optimum value in the target cell and fills the variable cells with the solutions that satisfy the constraints specified and provide the optimal result, as shown in Figure T4.12.
Solver T4.8-T4.11
Optimum Revenue for Solver Results T4.12
Pivot Tables
PIVOT TABLES
• A powerful built-in data-analysis feature in Excel is the PivotTable
• A PivotTable analyzes, summarizes, and manipulates data in large lists, databases, worksheets, or other collections
• It is called a PivotTable because fields can be moved within the table to create different types of summary lists, providing a “pivot”
What is an Excel Pivot Table?
• An interactive worksheet table
– Provides a powerful tool for summarizing large amounts of tabular data
• Similar to a cross-tabulation table
– A pivot table classifies numeric data in a list based on other fields in the list
• General purpose:
– Quickly summarize data from a worksheet or from an external source
– Calculate totals, averages, counts, etc. based on any numeric fields in your table
– Generate charts from your pivot tables
Pivot Table Advantages
• Interactive: easily rearrange them by moving, adding, or deleting fields
• Dynamic: results are automatically recalculated whenever fields are added or dropped, or whenever categories are hidden or displayed
• Easy to update: “refreshable” if the original worksheet data changes
Appropriate Data
• Data arranged in a list:
– Columns represent fields
– Rows represent a record of related data
• First row = column label
• Columns contain one sort of data
– For example, text in one column and numeric values in a separate column
• Remove subtotals
– You CAN work with subtotals, but use caution
• De-normalized database extracts are great for pivoting!
• Incomplete records:
• Mixed use columns :
• Column label issues:
PivotTable Terminology
• Some notable PivotTable terms are:
– Row field - Row fields have a row orientation in a
PivotTable report and are displayed as row labels
– Column field - Column fields have a column orientation in a PivotTable report and are displayed as column labels
– Data field - Data fields from a list or table contain summary data in a PivotTable, such as numeric data (e.g., statistics, sales amounts)
– Page field - Page fields filter out the data for other items and display one page at a time in a PivotTable report
PivotTable Terminology
Using the PivotTable Feature
1. Select the worksheet PivotTableData
2. Click any cell in the list
3. Select Data on the menu bar, then choose Pivot-Table and PivotChart Report
4. In the Where is the data that you want to analyze? area, choose Microsoft Excel list or database if it is not already selected
5. In the What kind of report do you want to create? area, choose PivotTable
6. Click the Next button
• In the Range box, the range should be
$A$1:$D$352
7. Click the Next button
8. Click Finish
9. Select New Worksheet
• Drag the Business Segment button to the Page Field area
• Drag the Unit button to the COLUMN area
• Drag the Particulars button to the ROW area
• Drag the Amount button to the DATA area
Modifying A PivotTable View
PivotTable Tools
• PivotTable- Contains commands for working with a PivotTable
• Format Report - Enables the user to format the PivotTable report
• Chart Wizard - Enables the user to create a chart using the data in the PivotTable
• Hide Detail - Hides the detail information in a PivotTable and shows only the totals
• Show Detail - Shows the detail information in a PivotTable
PivotTable Tools
• Refresh External Data- Allows the user to refresh the data in the PivotTable after changes to data are made in the data source
• Include Hidden Items in Totals - Lets the user show the hidden items in the totals
• Always Display Items - Always shows the field item buttons with drop-down arrows in the PivotTable
• Field Settings - Displays the PivotTable Field dialog box so that the user can change computations and their number format
• Hide Field List - Hides and shows the PivotTable Field List window
Building A PivotChart
• A PivotChart is a column chart (by default) that is based on the data in a PivotTable
• To build a PivotChart:
1. Click the Chart Wizard on the PivotTable toolbar
– Excel will automatically create a new worksheet, labeled Chart 1, and display the current PivotTable information in chart form
2. Modifications to the PivotChart can be done by selecting the drop-down lists to the right of the field names
Building A PivotChart
Protect Sheet/ Workbook
Protect the contents of worksheets and workbooks
• Once you have a worksheet that you know is correct, you may want to protect the worksheet so that users cannot make changes.
– Setting the locked property will disallow any changes to a particular cell
– The worksheet will have to be protected in order for the locked property to have any affect
• You can also specify a password that must be entered in order to remove worksheet protection.
• Finally, you can protect an entire workbook, which would disallow changes to the workbook, such as adding or deleting worksheets.
Unlocking Selected Cells
The Protect Sheet dialog box
The Protect Workbook dialog box
Financial Functions
Financial Function descriptions
This chart shows some commonly used financial functions and a description of what they do.
Excel's financial functions
• Financial functions are very useful to calculate information about loans.
• Common functions are FV, IPMT, PMT, PPMT and PV.
• All these financial functions will use similar arguments that differ based upon which function you are using.
– Think of the arguments as members of an equation
– The arguments represent the values of the equation that are known and the function provides the solution for a single variable, or unknown, value
Use the financial functions
• The FV function calculates the future value of an investment based on periodic, constant payments and a constant interest rate per period.
• The IPMT function provides the interest payment portion of the overall periodic loan payment.
• The PMT function calculates the entire periodic payment of the loan.
• The PPMT function calculates just the principal payment portion of the overall periodic payment.
• The PV function calculates the present value of an investment.
Use the financial functions (cont’d)
• NPER Determines the number of payments needed for an Investment to grow or pay back a loan
• RATE Determines the effective interest rate
Standard arguments
• rate: Interest rate (in decimal) per period
• nper: Number of periods
• pmt: Regular payment
More standard arguments
• pv: Present value: The amount the series of future payments is worth now. The beginning value.
• fv: Future value: The amount the series of future payments will be worth in the future. The final value.
• type 0 = payment at end of period (default) 1 = payment at beginning
PMT( ) Payments
• Returns the periodic payment for an annuity or loan
• PMT(rate, nper, pv, fv, type)
• The first 3 (red) arguments are required
PMT( ) Example
• Ms Just Retired has $200,000 to invest at 10% annual interest. Her goal is to have $100,000 left after 5 years. If she makes equal withdrawals each year for 5 years, how much can she withdraw each year?
• PMT(rate, nper, pv, fv, type)
= PMT(0.10, 5, -200000, +100000)
= $36,380
PMT( ) Example
PV( ) Present Value
• Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. That is, it is the beginning value of the investment or loan.
• PV(rate, nper, pmt, fv, type)
PV( ) Example
• A person promises to pay you $200 per month for 3 years. If you assume 12% interest compounded monthly, what is this annuity worth today?
• How much can you borrow at 12% annual interest compounded monthly and repay in 3 years paying $200 per month?
• = -PV(0.12/12, 3*12, 200) = 6021.50
PV( ) Example
FV( ) Future Value
• Returns the future (final) value of an investment. The future value is the total amount including interest that series of payments will be worth.
• How much money will there be in your account if you make regular payments for a period of time?
• FV(rate, nper, pmt, pv, type)
FV( ) Examples
• You will make $200 a month payments into a 12% annual interest payable monthly account. How much will you have after 3 years?
• = -FV(0.12/12, 3*12, 200) = 8615.38
• You will put $1000 in your account that pays 5% annually compounded monthly. You will add $100 to the account every month. How much will you have after 10 years?
• = -FV(0.05/12, 10*12, 100, 1000) = 17175.24
FV( ) Examples
IRR( ) Examples
IRR( ) Examples
NPV( ) Examples
SYD( ) / SLN () Examples
Formula View
Value View
Statistical Functions
Computing the Mean
• Sum xi divide by n (or N for population mean)
• Excel
–=AVERAGE(cellrange)
Computing the Mode
• Value that occurs most often in discretized data
• Excel
– =MODE(cellrange)
– Reports first value seen if tie
Computing the Median
• The middle value in sorted data
• Excel
– =MEDIAN(cellrange)
Computing the Range
• Range is min to max values
• Excel
–=MIN(cellrange)
– =MAX(cellrange)
Computing the Standard Deviation
• Std. Dev. is Square-Root of Variance
• Excel
–=STDEV(cellrange) - sample
– =STDEVP(cellrange) - population
– =VAR(cellrange) - sample
– =VARP(cellrange) - population
Others Statistical Functions
• Countif ( )
• Countblank ( )
• Subtotal ( )
• Rank ( )
Mathematical Functions
Mathematical Functions
• SUMIF ( )
• SUMPRODUCT ( ) • ROUND ( )
– Roundup ( )
– Rounddown ( )
– MROUND ( )
• FLOOR ( )
• POWER ( )
Correlations
Correlations
A quick review:
• Every correlation has a direction (positive or negative):
– + correlation: high scores on one variable are associated with high scores on another variable.
– - correlation: high scores on one variable are associated with low scores on the other variable.
• Every correlation has magnitude or strength:
– The closer the correlation coefficient is to +1.00 or -1.00, the stronger it is.
– The closer the correlation coefficient is to 0.00, the weaker it is.
Correlation Practice
l Put the following r values in order from weakest to strongest:
-.96 +.05 +.68 -.14 +.70 -.33
What did you get?
+.05 -.14 -.33 +.68 +.70 -.96
* Remember to use absolute values (ignore + or – signs) when comparing the strengths of one or more correlation coefficients. Negative correlation does not mean “less” than positive correlation.
Correlation Example
Correlation Example
Lookup Functions
Lookup / Reference Functions
• VLOOKUP ( )
• HLOOKUP ( )
• MATCH ( )
• INDEX ( )
Lookup / Reference Functions
Conditional Formatting
Highlight Debt Equity Lower than 0.25 by using
Conditional Formatting
Case Study I
Prioritization of Projects
• This is a real life case
• You need to prioritize the investment in projects by working out composite scores
• Parameters for evaluation of Scores for each project are:
– Project IRR
– Debt Equity
– Gestation Period
– Strategic Importance
• Work out the Composite Score for the given projects
Case Study II
• This is the Most Important Case Study for the students who want to do their career in:
– Finance
– Business Analyst
– Business Development Dept.
– Project Evaluation
– Risk Estimation
• Prepare following Financial Statement from given case study:
– Project Cash Flow and IRR
– Equity Cash Flow and IRR
– Payback Period (Nominal & Exact)
– Profit & Loss Statement
– Balance sheet
– Interest & Debt Repayment Schedules – Major Financial Ratios
• DSCR
• ROCE
• Profit Margin etc
• Real Life Challenges:
– Assumptions are changing:
• Model should be sensitive to all the assumptions – Easy to Understand:
• Model should not be un-necessarily complex
• Appreciation comes to you if others understands your work
– Assumptions sheet should be easily identifiable
– Result should be identified easily
– Model should be checked for accuracy
Preparation of Financial Model - Assumptions
Capacity of Petrochemical 100000 Tons
| Initial Price of Polymers | 10000 Rs/MT | |
| Price Escalation | 10% | |
| Variable Cost | 30% of Price | |
| Fixed Cost | 30% of Price | |
| Capex of Plant | 10,000 Lakhs | |
| Debt Equity | 0.50 | |
| Equity Portion | 0.50 | |
| Interest cost | 10% | Percentage |
| Corporate Tax Rate | 33% | Percentage |
| Dividend Payout | 20% | Percentage |
| Dividend Distribution Tax | 15% | Percentage |
| Gestation Period | 2.00 | Years |
| Marotorium Period | 2.00 | Years |
| Repayment | 3.00 | Years |
| Loan Tenor | 5.00 | Years |
| Economic Life of Project | 4.00 | Years |
| Project Tenor | 6.00 | Years |
Discounting Rate 12% Percentage
Preparation of Financial Statements
|
|
|
|
Preparation of Financial Statements
|
|
|
Preparation of Financial Model
Now its your time
Prepare the Financial Model
Real World is Complex, Does your model takes care of ….
| Complexities | YesCor No D |
| Funding Strategy Can change | C |
| Interest Rate Can Change with time | C |
| Change in Capacity Utilization | C |
| Capital Expenditure Can vary with inflation | D |
| Economic life of Project may reduce / Expand | D |
| Project may complete Early or may also delay | D |
| Addition of Risks | D |
| Complex Tax calculations | D |
THANK YOU