Submit a spreadsheet to answer the 3 questions:

5. If the price of a car is $31,000, the interest rate is 4.9%, and the payment period is 5 years, how much is the monthly payment?

If the interest rate goes down to 3.5%, how much is the new monthly payment?

 

6. A new Web-based order entry system for the ABC Company has the following estimated cash flows for the next 7 years. How much is the NPV of this project at 6% interest rate?

Will the NPV go up or down if the interest rate goes up to 8%? −5000, −3000, −2000, 2900, 4400, 5900, 4800 

7. The ABC Company pays a commission to its employees based on the following conditions: Under $10,000 total sales ($0–$9999): No commission $10,000 to $20,000 total sales ($10000–$20000): $1000 Over $20,000 total sales ($20,001+): $1000+12% of sales over $20,000 Using the Nested IF function, calculate the total commission for the following 10 employees:

  

Employee Name    Total Sales    Total Commission 

  

Bob                          $12,000

Alan                         $22,000 

Moury                      $17,000 

Mary                        $32,000 

Ron                           $8,000 

Dave                        $14,000

Brian                        $25,000 

Jean                         $29,000 

Lori                            $9,800 

Jasmine                   $31,800

follow directions asap

1A p p e n d i x A

APPENDIX A

MODELING ANALYSIS WITH EXCEL

A-1 INTRODUCTION

A-2 RELATIVE ADDRESSING

A-3 ABSOLUTE ADDRESSING

A-4 MIXED ADDRESSING

A-5 FINANCIAL FUNCTIONS

A-5-1 FV(Rate,Nper,Pmt,Pv,Type)

A-5-2 NPV(Rate,Value1,Value2,…)

A-5-3 PMT(Rate,Nper,Pv,Fv,Type)

A-5-4 SLN(Cost,Salvage Value,Life)

A-5-5 SYD(Cost,Salvage Value,Life,Period)

A-6 STATISTICAL FUNCTIONS

A-7 LOGICAL FUNCTIONS

A-7-1 AND

A-7-2 OR

A-7-3 NOT

A-7-4 IF(Condition, A, B)

A-8 LOOKUP FUNCTIONS

A-8-1 HLOOKUP(Lookup_Value,Table_Array, Row_Index_Num)
A-8-2 VLOOKUP(Lookup_Value,Table_Array, Col_Index_Num)

A-9 USING EXCEL FOR WHAT-IF ANALYSIS

A-9-1 WHAT-IF ANALYSIS USING DATA TABLE: ONE VARIABLE
A-9-2 WHAT-IF ANALYSIS USING DATA TABLE: TWO VARIABLES

A-10 USING EXCEL FOR GOAL-SEEKING ANALYSIS

A-1 INTRODUCTION

This appendix explores some of the Excel features that
can be used to create simple mathematical and statistical
models

.

It starts with a discussion of relative, absolute,
and mixed addressing, then presents several � nancial
and statistical functions. The IF function is explained as
a decision-making tool, then the Lookup functions are
reviewed. Finally, a couple of what-if analysis features
offered by Excel are presented.

A-2 RELATIVE ADDRESSING

Every cell in Excel has four different addresses: one
relative, one absolute, and two mixed addresses. These
addresses are explained in the next three sections.
When you use cell addresses in Excel formulas, you
need to be aware of the fact that Excel remembers a cell
by its position in the spreadsheet. In relationship to cell
E10, for example, cell G4 is two columns to the right
and six  rows above. An example of a relative address
procedure is shown in Exhibit A.1.

2 A p p e n d i x A

Exhibit A.1
Relative addressing

In cell B11, the formula is as follows: =B9+B8+B7+B6

If you copy this formula to cell C11, Excel changes the
formula to read as follows:

=C9+C8+C7+C6

The new cell addresses in the formula maintain the
same relationship to cell C11 as the old addresses did
to cell B11.

This powerful feature is called relative addressing. You
can use relative addressing with the copy command to
facilitate calculations. Suppose you have sales data related
to 100 different businesses in the � rst 100 columns of a
worksheet. To calculate the sum of each column, all you
need to do is type a formula for one column and then
copy the same formula to the other 99 columns. Excel
automatically changes the cell addresses for you.

A-3 ABSOLUTE ADDRESSING

Relative addressing is a powerful feature. However,
there will be many times when you will want to refer

to an exact location with an exact value. You may even
want to use prede� ned numbers or ratios. In these
instances, you must use absolute addressing.

In Exhibit A.2, � ve divisions of the XYZ Company
have sold different numbers of a particular product.
Your task is to calculate each division’s percentage
of  total sales, and you have used the formula
=B5/B11 in cell C5. However, if you copy this
formula to cells C6:C9, you will receive an error mes-
sage because in each case the division’s units must be
divided by the total units in cell B11. When you copy
the formula, cell B11’s address changes to re� ect the
formula’s new location, which produces the error.
You must make the reference to cell B11 absolute
(� xed). You do this by placing a dollar sign in
front of the column letter and another dollar sign
in front of the row number ($B$11). In Exhibit A.2,
we � rst entered =B5/$B$11 in cell C5, then copied
this formula into cells C6:C9.

C
re

a
te

d
u

si
n

g
M

ic
ro

so
ft

E
xc

e
l®

,
u

se
d

w
it
h

p

e
rm

is
si

o
n

f
ro

m
M

ic
ro
so
ft
.

3A p p e n d i x A

Exhibit A.2
Absolute addressing

A-4 MIXED ADDRESSING

Sometimes, you will want to use relative and absolute
addressing at the same time. You might make the row
reference absolute and leave the column reference free
to change as you copy the formula, or vice versa. For
example, $A10 means the column remains the same
but the row changes. Likewise, B$10 means the column
changes but the row is � xed.

Exhibit A.3 illustrates two examples of mixed addressing.
The � rst example shows the discounted prices under
different discount rates for two products with original
prices of $12 and $30. The second example in the exhibit
shows the same information in a different format. Can
you tell what the difference is between the two?

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h

p
e
rm

is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

4 A p p e n d i x A

Exhibit A.3
Mixed addressing

A-5 FINANCIAL FUNCTIONS

Financial functions are concerned with the time value
of money. They also calculate different depreciation
methods. In this section, we introduce FV, NPV, PMT,
SLN, and SYD.

A-5-1 FV(Rate,Nper,Pmt,Pv,Type)

The Future Value function returns the future value of
an investment based on periodic, constant payments,
and a � xed interest rate. The parameters used in the
function are explained below:

Rate is the yearly interest rate.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period.

Pv is the present value or the lump-sum amount that
a series of future payments is worth right now. If Pv is
omitted, it is assumed to be 0.

Type is either 0 or 1 and indicates when payments are
due. If Type is omitted, it is assumed to be 0. If
payments are due at the end of the period, Type=0.
If payments are due at the beginning of the period,
Type=1.

You must always be consistent when using Nper and
Rate. If you make monthly payments on a 6-year loan
at 8% annual interest, use 8%/12 for Rate and 6*12 for
Nper. If you make annual payments on the same loan,
use 8% for Rate and 6 for Nper. Exhibit A.4 shows two
examples of this function.

When working with Excel functions, you should
always use cell addresses instead of actual values. By
doing this, you will be able to perform what-if analyses
by changing the contents of cell addresses without
changing the formulas.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

5A p p e n d i x A

Exhibit A.4
The Future Value function

A-5-2 NPV(Rate,Value1,Value2,…)

The Net Present Value function calculates the net
present value of an investment by using a discount rate
and a series of future cash � ows. Cash out� ows are
negative values, and cash in� ows are positive values.

The cash in� ows and out� ows do not need to be equal.
This function is very helpful for calculating the today
worth of an investment that may generate different
future cash in� ows and out� ows. Exhibit A.5 illustrates
two examples of this function.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

6 A p p e n d i x A

Exhibit A.5
The Net Present Value function

A-5-3 PMT(Rate,Nper,Pv,Fv,Type)

The Payment function calculates the payment for a loan
based on constant payments and a � xed interest rate.
The parameters used in the function are explained below:

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value or the total amount that a series
of future payments is worth now.

Fv is the future value or a cash balance you want to
attain after the last payment is made. If Fv is omitted, it
is assumed to be 0.

Type is either 0 or 1 and indicates when payments are
due. If type is omitted, it is assumed to be 0. If payments
are due at the end of the period, Type=0. If payments
are due at the beginning of the period, Type=1. Exhibit
A.6 shows two examples of this function.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

7A p p e n d i x A

Exhibit A.6
The Payment function

A-5-4 SLN(Cost,Salvage Value,Life)

The Straight-Line Depreciation function calculates the
straight-line depreciation of a piece of equipment for
one period. This method assumes the same amount of
depreciation for every period. The parameters used in
the function are explained below:

Cost is the initial cost of the equipment.

Salvage Value is the value of the equipment at the end
of the depreciation period.

Life (useful life) is the number of periods over which the
equipment is depreciated.

Exhibit A.7 shows two examples of this function.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

8 A p p e n d i x A

Exhibit A.7
The Straight-Line Depreciation function

A-5-5 SYD(Cost,Salvage Value,Life,Period)

The Sum-of-the-Years’ Depreciation function calculates
the sum of the years’ depreciation for the selected period.
The parameters used in the function are explained below:

Cost is the initial cost of the equipment.
Salvage Value is the value of the equipment at the end
of the depreciation period.
Life (useful life) is the number of periods over which the
equipment is depreciated.

Period is a given period in useful life.

This method of depreciation accelerates the rate of
depreciation; therefore, more depreciation expenses
occur in earlier periods than in later periods. Because
maintenance costs are minimal in the � rst few years,
this method balances out the total cost of a piece of
equipment. In later years, there are fewer depreciation
costs and more maintenance costs. Exhibit A.8 shows
two examples of this function.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

9A p p e n d i x A

Exhibit A.8
The Sum-of-the-Years’ Depreciation function

A-6 STATISTICAL FUNCTIONS

Excel offers several statistical functions, including
AVERAGE, MAX, MIN, and so forth. With the
exception of COUNT, these functions accept numeric
values and list values for arguments. The COUNT
function accepts numeric as well as nonnumeric values
as arguments. In all of these functions, an argument
can be a single address or a group of addresses. The
following are brief descriptions of Excel’s most popular
statistical functions:

AVERAGE calculates the average of all values included
in a list or group.

COUNT counts the number of occupied (nonblank)
cells in a list.

MAX returns the maximum value in a list.

MIN returns the minimum value in a list.

STDEVP calculates the standard deviation based on the
entire population.

VARP calculates variance based on the entire population.

SUM calculates the sum of all values in a list.

Exhibit A.9 shows an example that uses these functions.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

10 A p p e n d i x A

Exhibit A.9
Statistical functions

A-7 LOGICAL FUNCTIONS

These functions generate values based on the results of
conditional statements. When you use logical functions,
remember that a blank cell has the value of 0. If you use
a list that represents a range of cells, Excel examines the
upper-left cell.

A-7-1 AND

The AND function returns true if all of its arguments
are true.

A-7-2 OR

The OR function returns true if any of its arguments
is true.

A-7-3 NOT

The NOT function reverses the logic of its argument.

A-7-4 IF(Condition, A, B)

The IF function returns the value A if the condition is
true and returns the value B if the condition is false. The
condition must be either a numeric value or a calcula-
tion that results in a numeric value. For example:

Function Returns

=IF(3<5, 10,-5) 10

=IF((10+5)/2<10,”T”,”F”) T

Exhibit A.10 shows a few examples of the IF function.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

11A p p e n d i x A

Exhibit A.10
The IF function

Another example of the IF function is calculating the
total pay of an employee who has worked overtime.
The formula is as follows:

=IF(H1>40,R1*40+(H1-40)*1.5*R1,H1*R1)

The total pay is rate (R1) multiplied by hours (H1).
However, if the employee works more than 40 hours,
overtime pay is calculated at the rate of 1.5 times regu-
lar pay. Overtime pay is only for the hours over 40
(see the lower part of Exhibit A.10).

There are many applications of the IF function. For
example, you can use this function to check a customer’s
credit limit. If the customer’s credit is good, you send
one message; if it is bad, you send a different message.
You can also use the IF function for inventory manage-
ment; if the inventory on hand is below 500 units, you
need to reorder. If it is not, you do not need to reorder.

Using the IF function with the logical operators
NOT, AND, and OR adds a strong decision-making
component to Excel. As introduced earlier, when you
use AND, all the conditions must be true; using OR,
only one of the conditions must be met; and NOT
screens out everything that doesn’t meet the condition.

Consider the information shown in Exhibit A.11.
In Row 4, the student is accepted because she has met
both conditions: Her GMAT is greater than 600, and
her GPA is greater than 3.3. In Row 6, the student is
accepted because her GMAT score is greater than 600,
and the GPA condition does not matter. In Row 9,
the student is rejected because both conditions must
be true and, in this case, they are not. In the exhibit’s
column A, the actual formula is displayed instead of the
results, so you can see exactly what was entered in cells
D4 through D9.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

12 A p p e n d i x A

Exhibit A.11
The IF function with logical AND, OR, and NOT

Exhibit A.12 shows another example of the IF function.
Northwest Lumber Company pays a commission to its
employees based on the following conditions:

Under $15,000 total
sales ($0–$14999) No commission

$15,000 to $20,000 total
sales ($15000–$20000) $800

Over $20,000 total sales $800+15% of sales
($20,001+) over $20,000

This example illustrates a Nested IF function. In column C,
we show the actual formulas used in column D.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

13A p p e n d i x A

Exhibit A.12
A Nested IF function

A-8 LOOKUP FUNCTIONS

Lookup functions are used for searching a value in a
table. The two commonly used ones are HLOOKUP
and VLOOKUP, which are explained below.

A-8-1 HLOOKUP(Lookup_Value,Table_Array,Row_
Index_Num)

The HLOOKUP function performs a horizontal table
search (see Exhibit A.13). This function compares the
value of Lookup_Value (test variable) to each cell in

the top row of the speci� ed table. The top row of the
table used by the HLOOKUP function must be sorted
in ascending order.

When Excel � nds a number larger than the test vari-
able, it stops and backs up one cell. Then, Excel moves
down the speci� ed number of rows (Row_Index_Num)
and returns the content of that cell. If there is an exact
match to the test variable, the search stops at this cell,
and no backtracking takes place.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

14 A p p e n d i x A

Exhibit A.13
The Hlookup function

If the test variable is smaller than the � rst value, the
function returns an error. If the test variable is larger
than all of the values, the search stops at the last cell
in the top row of the table, then Excel moves down the
speci� ed number of rows and returns the contents of
that cell.

Using the information shown in Exhibit A.13, the
following HLOOKUP functions return the values shown:

=HLOOKUP(25000,B4:H9,2) 2500

=HLOOKUP(40000,B4:H9,3) 2900

=HLOOKUP(24000,B4:H9,5) 1900

=HLOOKUP(22500,B4:H9,2) 2300

=HLOOKUP(15000,B4:H9,3) #N/A

A-8-2 VLOOKUP(Lookup_Value,Table_Array,Col_
Index_Num)

The VLOOKUP function performs a vertical search
much like the HLOOKUP function’s horizontal
search. VLOOKUP compares the value of the test
variable to  each cell in the � rst column of a speci� ed
table (see  Exhibit A.14). Again, in order to work
correctly, the contents of these cells must be sorted in
ascending order.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

15A p p e n d i x A

Exhibit A.14
The Vlookup function

When Excel � nds the � rst number that is either not
greater than the test variable or an exact match, it
moves across the search table in the speci� ed number
of columns and returns the contents of that cell.

If the test variable is smaller than the � rst value in the
search column, the function returns an error. Using
the information shown in Exhibit A.14, the following
VLOOKUP function returns the values shown:

EXAMPLES OF VLOOKUP:

=VLOOKUP(26500,A4:G10,2) 3000

=VLOOKUP(45000,A4:G10,3) 7100

=VLOOKUP(37300,A4:G10,4) 5200

=VLOOKUP(41000,A4:G10,5) 6300

=VLOOKUP(47000,A4:G10,6) 7400

=VLOOKUP(50000,A4:G10,7) 8500

=VLOOKUP(19000,A4:G10,1) #VALUE!

Exhibit A.15 shows an interesting application of the
VLOOKUP function. In this example, Professor Smith
uses the function to assign letter grades to his students
based on the results of three test scores. We’ve entered
a LOOKUP table in cells H10:I14. Excel assigns letter
grades to the average of these tests using the LOOKUP
table. Exhibit A.15 shows the actual formulas. We
selected Formulas (from the menu bar), then clicked
Show Formulas. Exhibit A.16 shows the results without
using the Formulas option.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

16 A p p e n d i x A

Exhibit A.15
The Lookup table with Show Formulas option

Exhibit A.16
The Lookup table with actual values

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft

.
C

re
a
te

d
u

si
n
g

M
ic

ro
so

ft
E

xc
e
l®

,
u
se

d
w

it
h
p

e
rm
is
si

o
n
f

ro
m

M
ic
ro
so

ft
.

17A p p e n d i x A

A-9 USING EXCEL FOR WHAT-IF ANALYSIS

What-if analysis calculates the effects of a change in
one variable on other variables or an entire worksheet.
A simple example is break-even analysis. The break-even
point is the number of units generated in which total cost
is equal to total revenue. For example, if the � xed cost of
an operation is $500, the variable cost of a unit is $10,
and the selling price is $15, the break-even point would
be 100 units. At this point, the company will neither lose
nor gain anything. Above this point, the company will
gain, and below this point the company will lose. Excel
can help you discover what will happen to the break-
even point if, say, the selling price is increased to $17 or
the variable costs are decreased by $3.

This feature can be used in a much more complex
environment. Let’s say you have projected the budgets of
your company for the next 5 years. Suddenly, you learn
that the projected income for 2018 will be reduced by
5%. What is the impact of this income reduction on the
entire budget? Thousands of accurate calculations must
be done in order to answer this question. But if the budget
is on an Excel spreadsheet, the software can perform all

of the calculations almost instantly, and with no errors.
Just change the old value to the new value and press Enter.

What-if analysis can be done with charts as well
(see  Appendix B). Change any data item, and Excel
generates a new chart instantly. The next section
presents a couple of Excel’s what-if analysis features
that use the Data Table command.

A-9-1 WHAT-IF ANALYSIS USING DATA TABLE:
ONE VARIABLE

The Data Table (with one variable) command can
be used to determine the effect of one variable on a
formula or an entire worksheet. There are many areas
where this command can be useful—for example,
the effect of different interest rates on an IRA plan, the
effect of different interest rates on a loan, or the effect
of different commission percentages on the total
commission generated by a salesperson.

To use the Data Table command with one variable,
you must � rst establish a table range. In Exhibit A.17,
C4:E15 is the table range. The table range can be
anywhere in the worksheet.

Exhibit A.17
The Data Table command with one variable

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

18 A p p e n d i x A

Next, you must choose an empty cell outside the table
range as the input cell. The address of this cell will be
used to change values in a formula; it is also used in
Excel internal calculations. Do not be concerned about
its role. In this example, the input cell is A5.

Next, � ll in the changing values in a column—in this
case, cells C5:C15. Above and to the right of these values,
in cell D4, is the formula, in this case the future value:
=FV(2000,A5,20). This function calculates the future
value of an IRA plan to which you contribute $2000
each year for 20 years with a variable interest rate.

We copied the =FV(2000,A5,20) to cell E4, but we
changed the number of years to 30. The intersection
of these values (interest rates and formulas) is empty;
this empty cell will be used by the Data Table (with two
variables) command introduced in the next section.

When you have de� ned the parameters, the future value
of the IRA plan will be calculated for different interest
rates and numbers of years. Exhibit A.17 shows an
application of this command. After building the table,
the following keystrokes were used to � ll in the table:

1. Select cells C4:E15.

2. Click Data.

3. Click What-If Analysis.

4. Click Data Table.

5. Click column input cell.

6. In the dialog box, enter A5, then click OK.

A-9-2 WHAT-IF ANALYSIS USING DATA TABLE:
TWO VARIABLES

You can use the Data Table command with two
variables to calculate the effects of two variables over

the entire worksheet or a speci� ed range. Suppose
Sunrise Electronics has designed a formula for calculat-
ing the total salary of each employee. The total salary is
calculated based on the years of education and years of
experience. In every case, $2000 is the base salary. The
formula is as follows:

=2000+100*A1+175*B1, where A1 is the number of
years of experience and B1 is the number of years of
education (these are the input cells).

In Exhibit A.18, we’ve used the Data Table command to
calculate the entire table for Sunrise Electronics. The table
range is D3:H18, the row input cell is A5, and the column
input cell is B5. The formula =2000+100*A5+175*B5
was copied in cell D3, which is the intersection of row
3 (years of education) and column D (years of experi-
ence). We entered the numbers 1 to 15 in cells D4:D18
and numbers 12, 16, 18, and 21 in cells E3:H3. After the
table was built, the following keystrokes were used to � ll
the table:

1. Select cells D3:H18.

2. Click Data.
3. Click What-If Analysis.
4. Click Data Table.

5. Click Row input cell.

6. Enter B5.

7. Click Column input cell.

8. In the dialog box, enter A5.

9. Click OK.

19A p p e n d i x A

Exhibit A.18
The Data Table command with two variables

A-10 USING EXCEL FOR GOAL-SEEKING
ANALYSIS

Goal-seeking analysis is the reverse of what-if analysis.
Here, you ask a question such as the following: “To
generate $5,000,000 in total sales, how much money
should I spend on advertising?” If you build an adver-
tising model (Excel provides you with the tools to do
this), performing this kind of analysis is easy. When
performing a goal-seeking analysis, you manipulate one
variable in order to achieve a goal. Excel’s Goal Seek
feature assists you with these kinds of analyses.

As an example of using goal-seeking analysis, recall that
earlier in this appendix you were shown how to use the
PMT function to calculate a loan payment amount. In
that case, the amount of the loan, the interest rate, and
the payback period were known in advance. Suppose
instead that you want to buy a house worth $200,000,
you want to pay the loan off in 15 years, and you are
only able to pay $1500 a month. Therefore, you want
to know the interest rate at which you should secure the

loan in order to be able to buy this house. Excel’s Goal
Seek feature can answer this question for you. Follow
the steps below:

1. Start a blank worksheet.

2. In cell A1, enter Loan amount.

3. In cell A2, enter Payment period.

4. In cell A3, enter Yearly interest rate.

5. In cell A4, enter PMT formula.

6. In cell B1, enter 200,000.

7. In cell B2, enter 15.

8. In cell B4, enter =PMT(B3/12,B2*12,B1).
We  left cell B3 empty for the interest rate that
will be calculated by the Goal Seek feature.
Please notice that we divided B3 by 12 and
multiplied B2 by 12 in order to make them con-
sistent; payments are monthly, and the interest
rate is also monthly now.

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

20 A p p e n d i x A

This is all the input data you need. As soon as you enter
the PMT formula in cell B4, Excel displays (1,111.11)
(see Exhibit A.19). That is because, at this point, the

interest rate is 0. Also, it is displayed as a negative
number because this is a cash out� ow.

Exhibit A.19
A sample worksheet for the Goal Seek feature

Follow the steps below to calculate the interest rate
using the Goal Seek feature:

1. Click cell H1 (or any other cell outside the
worksheet data).

2. Click Data, What-If Analysis, and Goal Seek. The
Goal Seek dialog box opens (see Exhibit A.20).

3. In Set cell, enter B4.

4. In To value, enter -1500.

5. In By changing cell, enter B3. As soon as you
click OK, 4.2% is displayed in cell B3. We for-
matted the B3 cell with the Percentage option to
one decimal (see Exhibit A.21).

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

21A p p e n d i x A

Exhibit A.20
The Goal Seek dialog box

Exhibit A.21
The interest rate calculated by the Goal Seek feature

C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.
C
re
a
te
d
u
si
n
g
M
ic
ro
so
ft
E
xc
e
l®
,
u
se
d
w
it
h
p
e
rm
is
si
o
n
f
ro
m
M
ic
ro
so
ft
.

22 A p p e n d i x A

This simple analysis tells you that if you secure a loan
with a 4.2% interest rate, you will be able to buy a
house worth $200,000 and your monthly payment will

be $1500. Above this rate, you will not be able to afford
the house. If you � nd a loan with an interest rate below
4.2%, your monthly payment will be less than $1500.

22 A p p e n d i x A

1. How many different addresses could a given cell
have in Excel?

2. What are two examples of � nancial functions?
Mention one business application of each.

3. What are two applications of the IF function?

4. What are two what-if features of Excel?

5. If the price of a car is $31,000, the interest rate is
4.9%, and the payment period is 5 years, how much
is the monthly payment? If the interest rate goes down
to 3.5%, how much is the new monthly payment?

6. A new Web-based order entry system for the ABC
Company has the following estimated cash � ows
for the next 7 years. How much is the NPV of this
project at 6% interest rate? Will the NPV go up or
down if the interest rate goes up to 8%?

−5000, −3000, −2000, 2900, 4400, 5900, 4800

7. The ABC Company pays a commission to its employees
based on the following conditions:

Under $10,000 total sales ($0–$9999): No commission

$10,000 to $20,000 total sales ($10000–$20000): $1000

Over $20,000 total sales ($20,001+): $1000+12% of
sales over $20,000

Using the Nested IF function, calculate the total com-
mission for the following 10 employees:

Employee Name Total Sales Total Commission

Bob $12,000

Alan $22,000

Moury $17,000

Mary $32,000

Ron $8000

Dave $14,000

Brian $25,000

Jean $29,000

Lori $9,800

Jasmine $31,800

8. Modify the Lookup table in Exhibit A.15 as follows:

LOOKUP TABLE

50 F

60 D−

65 D

68 D+

70 C−

75 C

78 C+

80 B−

85 B

88 B+

90 A−

95 A

98 A+

Now generate the new grade for each student. You
may add additional students to the list with their test
scores and see how accurately Excel assigns a letter
grade to each student.

9. Modify the interest rates in Exhibit A.17
as follows:

2.00%

2.50%

3.00%

3.50%

4.00%

4.50%

5.00%

5.50%

6.00%

6.50%

7.00%

Problems, Activities, and Discussions

23A p p e n d i x A 23A p p e n d i x A

In cell G4, enter the function =FV(A5,40,−2000).
Now, using Data Table command with one variable,
recalculate this new table.

10. In Exhibit A.18, enter the new formula listed below
in cell D3 and calculate the salary of each employee:

=2100+A5*150+B5*225

How much is the salary of an employee with
10 years of experience and 18 years of education?

11. Using the Goal Seek feature of Excel, calculate the
interest rate of a house worth $300,000 if you want
to pay the loan off in 30 years and you are only able
to pay $2000 a month.

12. The COUNT function accepts numeric as well as
nonnumeric values as arguments. True or False?

13. Any cell in Excel has two absolute addresses and
two mixed addresses. True or False?

14. All of the following are examples of valid Excel cell
addresses except:

a. $A$1

b. 1A

c. A1

d. A$1

15. All of the following are examples of � nancial
functions except:

a. FV(Rate,Nper,Pmt,Pv,Type)

b. NPV(Rate,Value1,Value2,…)

c. IF(Condition, A, B)

d. PMT(Rate,Nper,Pv,Fv,Type)