


Robert H. Smith School of Business 

University of Maryland – College Park 





By the end of this tutorial, you will: 

Make decisions using spreadsheets 

Understand major excel capabilities 

Develop skills to effectively use software 

Manage worksheets and program commands 

Utilize mathematical formulas and functions 





This chapter shows you how to use: 

Loan analysis 



Goal Seek Command 



Mortgages analysis 





This chapter shows you how to use: 

Power of excel 



Grade book 



Large spreadsheets 







You will analyze loans by: 

Using PMT Function 

Using Goal Seek Command 



Reduce monthly payments 





3
Arguments 

Interest rate/period 

Number of periods 

Amount of loan 




Computes associated payment on loan 

Arguments placed in parentheses 

Arguments separated by commas 







Deo and Claude want to buy a freightliner truck
for livestock 

They need a loan to pay 

Terms of loan 

Price of truck = $12,500 

Interest = 8% per year 

Loan period = 4 years 






Payment = PMT (interest rate/period, # of
periods,  loan amount) 

Note: loan amount is always negative 

Excel enters values according to cells 

i.e. (B1, B3, etc.) 





What is the correct equation for Deo and Claude? 

Answer (1) = PMT (8/12, 3, 12500) 

Answer (2) = PMT (.08/12, 36, 12500) 





What is the correct equation for Deo and Claude? 

Answer (1) 

Answer (2) ( ß this is the correct answer) 




Maximum amount user can borrow 

Keep payments at specified amount 

Sets end result to determine input 

Input determines possible outputs 




One input varied at a given time 

Select TOOLS menu 

Select GOAL SEEK 

Enter address of dependent cell 

Enter desired value of cell 




Indicate changeable cell (variable) 

Click OK to execute command 

Tips and tricks:click here for quick tips! 





You will be able to choose: 

Longterm mortgage 

Shortterm mortgage 

Distinguish between: 

Relative addresses 

Absolute addresses 





Worksheets show a variable rate mortgage 

Monthly payments for different interest rates 

Compare payments for 15 and 30year loans 





What table incorporates principal and interest
amounts? 







What table incorporates principal and interest
amounts? 

Amortization Schedule 






What are the two basic types of mortgages? 









What are the two basic types of mortgages? 

Longterm and shortterm mortgages 





You will be able to distinguish: 

Relative reference 

Absolute reference 







Relative reference: 

Cell address that varies during operations 

Absolute reference: 

Cell address that does not change 





In a PMT Function equation: 

PMT(A7/12, 15*12, $D$4) 

Loan amount ($D$4) remains constant 

Interest rate (A7/12) changes 






All formulas based on amount borrowed 

Based on starting interest 

User can change either, shifting formula 

Cell reference should not change when formula is
copied 

Specified as an absolute address 





An absolute reference is defined as: 

Answer (1) Cell address that doesn’t change 

Answer (2) Cell address that changes 





An absolute reference is defined as: 

Answer (1) ( ß this is the correct answer) 

Answer (2) 







True or False: Interest rate is relative 

True 

False 







True or False: Interest rate is relative 

True ( ß this is the correct
answer) 

False 




The Fill Handle is a tiny black square that
appears in the lowerright corner of the selected cell 

The Fastest way to copy a cell to an adjacent
cell 






Select Cells to be copied 

Point to fill handle 

Click and drag the fill handle to desired range 

Release the mouse 




Select the cell to contain the formula 

Type an equal sign, user is now in enter mode 






Click on cell user wants referenced in formula; the user is now
in point mode 

Type any arithmetic operator to place cell
reference in formula and return to enter
mode 




Continue pointing to additional cells and
entering arithmetic operators until user completes the formula 

Press enter key to complete 







Financial 

Date and Time 

Math and Trig 

Statistical 




Select the category user wants 

Choose desired function within category 

Click OK to display the dialog box 






In the dialog box enter the value, cell
reference or formula for each argument in the text boxes (or by clicking
appropriate cells in the worksheet) 




Excel will display calculated answer to the
right of each argument and the computed value for the function as a whole
at the bottom of the dialog box 

Press the OK button 






What is the purpose of the Fill Handle? 















What is the purpose of the Fill Handle? 

To copy information across many cells. 






What does pointing prevent? 





What does pointing prevent? 

Mistakes caused by incorrectly typing the cell
address 




Where will the solution to a function appear? 





Where will the solution to a function appear? 

To the right of each argument 




Name three categories of Functions. 





Name three categories of Functions. 

Refer to first function slide 






What are the three ways to enter arguments into
a formula? 







What are the three ways to enter arguments into
a formula? 

By entering values, cell addresses or by
clicking the appropriate boxes 




MAX, MIN, AVG 

These functions give you the highest, 

lowest, and average numbers from your list. 



COUNT Function 

This function gives you the number 

of
cells with numeric entries. This
includes formulas that evaluate numeric results. 




COUNTA Function 

This function includes those cells with
numeric values and those with text. 





Worksheet calculations can be done in two ways: 

Can use
formula [ex.=(A1=A2=A3/3)] 

Or user
may use equivalent function (ex.=Average(A1:A3)) 







Arithmetic Expressions allow the user to perform
their own formula, if it is not included in excel functions. 



Functions are easier and quicker, and are
preferred 









Illegal Cell Reference: #VALUE! Error 

This occurs because the cell referred to is
invalid 





A Shortcut to enter certain series into adjacent
cells 

Step 1 is to enter the first value(s) of a
series (January) 





Step 2 is to drag fill handle over the adjacent
cells that the rest of the values should be (adj. 11 cells) 

Step 3 Excel creates the rest of 

the series based on the initial value 

(rest
of the months) 





Three arguments 

A condition that is evaluated as True or False 

The value to be returned if true 

The value to be returned if false 





IF (condition, valueiftrue, valueiffalse) 

The IF returns one of the values depending on
true or false 

Values may be: 

Numeric 

A specified cell 

A formula 

A function 

A text entry in “ ” 




This “vertical lookup” function assigns an entry
to a cell based on numeric value contained in another cell 



The HLOOKUP function is the same 

with
the exception that the table 

is
arranged horizontally 





Arguments 

The numeric value to look up 

The range of cells in which the value is to be
looked up 

The column number within the table that contains
the result 








Which
statistical functions give you the highest, lowest, and average values? 

a) MAX, MIN, AND AVERAGE 

b) MAX, MIN, COUNT 

c) COUNT, COUNTA, AVERAGE 










What happens when there is an illegal cell
reference? 

a) #VALUE! Error occurs 

b) Nothing, it is just the computer’s
mistake 

c) You need to use autofill to correct
something 




What happens when there is an illegal cell
reference? 

a) #VALUE! Error occurs 

b) Nothing, it is just the computer’s
mistake 

c) You need to use autofill to correct
something 





What is the quickest way to enter certain series
into adjacent cells? 

a) AutoFill 

b) VLookUP 

c) HLookUP 




Name the three arguments of the IF function 






Name the three arguments of the IF function 

A condition that is evaluated as true or false 

The value to be returned if the condition is
true 

The value to be returned if the condition is
false. 






What are the three arguments of the VLookUP
Function? 






What are the three arguments of the VLookUP
Function? 

The numeric value to look up 

The range of cells containing the table in which
the value is to be looked up 

The columnnumber within the table that contain
the result 




Some spreadsheets get so long or wide that they
cannot fit on the screen in their entirety. Scrolling allows a user to select and view any portion of a
spreadsheet. 




Freeze panning allows a user to see his desired
portion of the screen, and “freeze” the headings so that no matter where he
scrolls, the headings are visible. 






Freezing Panes command is in the Window menu 

A user can see and use the frozen cells 

Unfreeze Panes command counteracts the Freeze
Panes command 






Helps to arrange information 

Can group rows or columns 




Group and Outline command located in the Data
menu 

A plus sign indicates that the group has been
collapsed 

A minus sign indicates that the group is being
displayed 





What function is used to retain title cells when
a spreadsheet becomes too large to view all at once? 

Grouping and Outlines 

Freezing Panes 

Scenario Manager 





What function is used to retain title cells when
a spreadsheet becomes too large to view all at once? 

Grouping and Outlines 

Freezing Panes 

Scenario Manager 







When using the grouping and outlines function,
which sign indicates that the group is being displayed? 

Plus sign 

Minus sign 





When using the grouping and outlines function,
which sign indicates that the group is being displayed? 

Plus sign 

Minus sign 







What can the grouping and outlines command
organize into groups? 

Rows 

Columns 

Both rows and columns 





What can the grouping and outlines command
organize into groups? 

Rows 

Columns 

Both rows and columns 






What does scrolling accomplish? 





What does scrolling accomplish? 

It allows the user to select and view any
portion of the spreadsheet 




How are Freezing Panes useful? 





How are Freezing Panes useful? 

They help the user to see the title headings
over any portion of the spreadsheet, which allows for more efficient data
entry. 





Tracey Molloy 

Nuzaira Khan 

Eleanor Lam 

Laura Hart 

Christo Christoff 

Alexis Lynady 

Kevin McMahon 

Greg Rachins 

Kristine Riordan 


