Excel basics
Download
1 / 54

Excel Basics - PowerPoint PPT Presentation


  • 307 Views
  • Updated On :

Excel Basics Fundamentals, Formula Techniques, and Tricks of the Trade Fundamentals The basics of spreadsheet models Fundamentals 1] Never enter the same information more than once. =B4 Fundamentals

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Excel Basics' - adamdaniel


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Excel basics l.jpg

Excel Basics

Fundamentals,

Formula Techniques, and

Tricks of the Trade


Fundamentals l.jpg

Fundamentals

The basics of spreadsheet models


Fundamentals3 l.jpg
Fundamentals

1] Never enter the same information more than once.

=B4


Fundamentals4 l.jpg
Fundamentals

2] Code inputs blue. When a blue input variable is changed ALL DEPENDENT values change accordingly.

=B4

=B4 ?

=E4 ?


Fundamentals5 l.jpg
Fundamentals

3] Name constants.

=CPN


Go to b13 l.jpg
Go to B13

Assign names in the name box (under the toolbar). This replaces the name B13 with PRI

Use [Insert] [Names…] [Define] to see and manage names used in the spreadsheet

In 2007 use [Formulas] [NameManager]


Fundamentals7 l.jpg
Fundamentals

3] Name constants.

Principal

=CPN

=RDT

=PRI

=SDT


Fundamentals8 l.jpg
Fundamentals

3] Name constants and arrays

  • Highlight

  • Enter the name

  • Shift+Ctrl+Enter

=Codes


Fundamentals9 l.jpg
Fundamentals

4] Format for clarity and ease of use.

vs


Fundamentals10 l.jpg

$A$1

A1

B1

$A1

$A1

A$1

B$1

$A$1

$A$1

A2

$A2

A$1

Fundamentals

5] Always line up repetitive formulas so they can be dragged across rows and down columns.

*Use [F4] to toggle the lock code


Fundamentals11 l.jpg
Fundamentals

6] Train yourself to use shortcuts

  • Customize your toolbar

  • Alt+ codes

    • TRY Alt + I N D


In session exercise l.jpg

In-Session Exercise

Bond Calculator


Dates l.jpg
Dates

Nov 8 is a date

Ctrl+Shift+1 and it changes to 39,758.00


Formats l.jpg
Formats

In B13

Ctrl+Shift+1 and it changes to 98.75

Ctrl+Shift+2 and it changes to 6:00PM

(.75 through the day)

Ctrl+Shift+3 and it changes to 7-Apr-00(Apr 7, 1900)

Ctrl+Shift+4 and it changes to $98.75

Ctrl+Shift+5 and it changes to 9875%

Ctrl+Shift+6 and it changes to 9.88E+01



Bond calculator16 l.jpg
Bond Calculator

  • Compute Base Price

  • Note that when you use = and point to the Principal Excel uses the Name Principal rather than B3

  • when you use = and point to Price Excel uses the Name PRI rather than B13


Functions l.jpg

Functions

Using Excel Functions


Functions18 l.jpg
Functions

  • There are hundreds of built-in functions

  • Find them by clicking on fx

  • Most of the ones we use are under Financial


Function coupdaysbs l.jpg
Function: COUPDAYSBS

Use names

See each step

Description of function

Use $A$7 which will default to 0


Bond calculator20 l.jpg
Bond Calculator

Note that everything is calculatedand coded black


Functions21 l.jpg
Functions

  • On any function click on the [fx] to open the applet.



Data validation l.jpg

Data Validation

Drop Down Lists


Data validation24 l.jpg
Data Validation

B7

Set to accept inputs from B19:B21only


Drop down lists in excel l.jpg
Drop Down Lists in Excel

  • From B7 use Data/ Validation

    • Choose List and specify $B$19:$B$21

List

Dropdown

$B$19:$B$21


Drop down lists l.jpg
Drop Down Lists

The drop down forces the user to choose from a list of inputs


Lookup l.jpg

Lookup

Finding specific information


Lookup28 l.jpg
Lookup

Basis for COUP functions

  • In A7=VLOOKUP(B7,Codes,2,FALSE)

Exact match only

Find the value in B7 in the 1st column of…

The array we named Codes

Get the corresponding value in column 2

=Codes


Lookup29 l.jpg
Lookup

  • Choose a different Bond type and watch the code change.

  • vLookup

    • Looks up in the first column of an array and matches the row

  • hLookup

    • Looks up in the first row of an array and matches the column


Slide30 l.jpg

If

Excel conditions


If condition true false l.jpg
=IF(Condition, True, False)

=IF(A7=1,"Treasury","Not a Treasury")

=IF(TEXT(RDT,"dd")="15",

"matures on the 15th",

"doesn't mature on the 15th")

Day part of SDT


If condition true false32 l.jpg
=IF(Condition, True, False)

=IF(A7=1,

IF(TEXT(B5,"dd")="15",

"",

"Treasuries mature on the 15th"),

"")

You can embed up to 7 if statements in one cell


Tables l.jpg

Tables

Sensitivity Analysis


Data tables l.jpg
Data Tables

  • Change one input in a model and calculate what various outputs would be

  • Change two inputs in a model and calculate what one output would be


Table l.jpg
{=Table()}

=E16

Cell where the Nominal Yield is calculated

Change the price quote (from 60 to 135)

=E17

Cell where income Yield is calculated

=E18

Cell where YTM is calculated


Table36 l.jpg
{=Table()}

  • Highlight the table area

    • The first column is the list of prices

    • The top row is the list of outputs


Table37 l.jpg

$B$13

{=Table()}

2.Use [Data][Table]

3.Indicate that the first column in the area is to replace cell B13

4.[OK]


Table38 l.jpg
{=Table()}

Test the table array by changing the input n and comparing the results with the table


Graphs l.jpg

Graphs

When a picture is worth a thousand words


Graphs40 l.jpg
Graphs

  • What are you trying to communicate?

  • How do you best communicate it?


Combined chart l.jpg
Combined Chart

Monarch

Price of a quarter of wheat

Wage of a good mechanic


Florence nightingale l.jpg
Florence Nightingale

Deaths from preventable disease

Deaths from wounds

Deaths from all other causes



Graphs44 l.jpg
Graphs

  • Chart as an xy scatter chart

  • exchange the x and y axes

    • Yield shouldbe the x axis

    • Price shouldbe the y axis

    • Use right click/source data


Chart area l.jpg
Chart Area

The Chart Area is the frame in which the chart is held. Set the chart font to the same as the spreadsheet and de-click autoscale


Plot area l.jpg
Plot Area

The Plot Area is the chart space. Highlight the grey and delete; highlight the gridlines and delete or reformat to make them less obnoxious.Expand the Plot Area to enlarge the graph within the frame


Slide47 l.jpg
Axes

Edit Chart Options to add Titles

Format Axes


Format data series l.jpg
Format Data Series

Format Data point to add label

Color nominal yield white to hide it


Format data series49 l.jpg
Format Data Series

Change to Income Alt+Enter Yield

The label Changes as well


Conditional formatting l.jpg

Conditional Formatting

Font color, highlights, etc


Conditional formatting51 l.jpg

Prices to increment by 10

Format/ Conditional Formatting

Set negative yields to red

Now set the Coupon to 4%

Conditional Formatting


Tricks of the trade52 l.jpg

Tricks of the Trade

One last trick




ad