# DB Functions

Document Sample

```					     Click on Topics to Move to That Worksheet
All Regions              Math Statistical Functions
Consolidation            Pivot Table
Data Table               Scenarios
Database                 Scenarios Summary
Date-Time Functions      Solver
Financial Functions      SubTotals
Graph Data               Text Functions
Graphs                   Text to Columns
Group-UnGroup            Transpose Function
Index-Match Function     User Functions
Logical Functions        Visual Basic Info
LookUp Goal Seeking      Web Queries
Macros
MATH-STATISTICAL FUNCTIONS

Income                                     Week 1       Week 2       Week 3    Week 4
Burgers        3,000        3,250        2,980     3,475
French Fries       2,000        2,000        2,000     2,000
Shakes           900        1,050        1,300     2,400
=SUM(Range)                                 5,900       \$6,300       \$6,280    \$7,875

=AVERAGE(Range)                            \$1,967       \$2,100       \$2,093    \$2,625
=MIN(Range)                                  \$900       \$1,050       \$1,300    \$2,000

Expenses
Hamburger         1,000        1,050          950     1,070
Buns          200          210          190       214
Potatoes         300          315          285       321
Ice Cream          300          315          285       321
Milk         100          105           95       107
Condiments            50           53           48        54
Wages         1,400        1,470        1,330     1,498
Franchise Fee          500          525          475       535
Total Expenses                  \$3,850       \$4,043       \$3,658    \$4,120

=MAX(Range)                                  1,400       1,470        1,330     1,498
=SUM(Range) - MULTIPLE RANGE            \$31,339.00

Income Before Taxes                        \$2,050       \$2,258       \$2,623    \$3,756

Income Taxes (20%)                           \$410         \$452         \$525     \$751

Net Income/(Loss)                          \$1,640       \$1,806       \$2,098    \$3,004

=PRODUCT(val1,val2)                      Quanity      Cost       Product
Find the Product of Two Numbers           345        \$34.56       \$11,923.20
Use Function Wizard and select            434        \$21.87        \$9,491.58
Product                                   567        \$42.96       \$24,358.32
333        \$11.99        \$3,992.67
564        \$12.34        \$6,959.76
765        \$56.82       \$43,467.30
234          \$45.98            \$10,759.32
567           \$4.34             \$2,460.78
876          \$11.88            \$10,406.88
456          \$34.67            \$15,809.52

Donations to the Fund
ABC Sales, Inc.                        \$1,000             100
Odd Pets with Short Names, Inc.        \$2,000             100
Gusher, Inc.                           \$2,500
Toys R Mine                       none                    500
Flowers For You                        \$1,500             500
Wireless Cable                         \$1,300
Private Investigators, Inc.       none
Restaurant Supplies               none                    200
Whatchamacallit, Inc.                  \$1,000
Timepieces to Go                       \$1,200             100

=COUNTA(Range)                              11 Number of contacts
=COUNT(Range)                                6 Number of additional donations

=CountBlank(Range)                           3              34
Counts the number of                                        56
blank cells in a range
78
=CountIF(Range,Criteria)                     4
Counts the number of
cells which meet a                                         89
specific criteria, I.e. >50                                45
67
32

=Forecast(value to forecast,        Sales          Profit
known y's, known x's)            Known x's      Knows y's
65,765               8%
78,579              11%
99,322              13%
150,456              15%
210,678              19%
245,692           21.60%

=ROUNDUP(number,decimal places to round to) with AVERAGE
=ROUNDDOWN(number,decimal places to round to) with AVERAGE

Invoice Totals by Quarter
RoundUp                             1st Qtr         2nd Qtr              3rd Qtr        4th Qtr
Joe Tucker                         9,800.50        7,500.05             9,100.45       9,100.36
Sue Jones                          9,500.12        8,000.25             9,400.56       9,200.24
Mary Walters                       6,700.40        9,400.68             8,700.00       8,700.98
Fred Roberts                       9,100.03        8,700.00             8,600.25       8,800.56

RoundDown                          First Qtr       Mid-term            Third Qtr          Final
Ryan Nelson                               98             94                   94             76
Tom Watson                                65             79                   91             99
Nancy Carr                                89             85                   95             80
Barbara Orr                               75             88                   84             77

=SUMIF(range, vaule, range)     Need a range of number or data to search, a search criteria, and a range to
total if the data meets the criteria. The criteria can be a text match
like ="Apples" or a number match using "=. >, <, >=, or <=".
Answer in summing figures with a match on "Apples" is \$149.58

SearchRangeSumRange
Apples             34.96
Apples             23.98
Pears              76.54
Plums              34.55
Berries            66.43
Melons             34.56
Apples             14.21
Berries            67.81 Apples Total              \$    149.58
Grapes             23.45
Bananas            18.97
Grapes             67.18
Apples             76.43
Pears              41.23
Plums              34.58
Plums              23.46
Melons             78.98
Grapes             34.86
=QUARTILE(Range,quartile)   This is a formula to determine where the quartiles of a range of numbers.
The formula is =QUARTILE(range of numbers, quartile to find)
The quartile to find is based upon the keys from 0 to 4 indicated below:

Salaries
\$37,560
\$48,976
\$22,345
\$18,765
\$78,437
\$44,567
\$32,563
\$11,908
\$10,897                   0   Minimum               \$10,897
\$14,567                   1   First Quartile        \$20,699
\$45,678                   2   Second Quartile       \$32,510
\$54,678                   3   Third Quartile        \$44,845
\$21,343                   4   Maximum               \$87,654
\$22,346
\$22,438
\$34,234
\$87,654
\$34,278
\$32,456
\$11,781

=RAND()*x+1                 Generates a random number between 0 and 1. To generate random numbers
between a group of numbers like 1 and 48 or 1 and 80.
To generate random values in different numeric intervals, multiply RAND() by the
largest number in the range and add "1" to the answer to get a whole number.
Use =INT with the result to create random whole numbers.
The formula for generate random numbers between 1 and 48 is
as follows: =INT(RAND()*48)+1

=TREND                      Calculates the trend of a known range of numbers or the trend of future periods
based upon the known numbers. In calculating the trend for future
periods, the knows period are numbered beginning with 1 and the future
periods are the numbers beyond the last knows period into the future.
If you have six knows periods, you can calculate the trend for any periods
beyond six by indicated the future period numbers as the new periods in
the trend formula.

To calculate a trend for a known group of numbers without any future periods,
select a range of cells equal to the number of known periods before you
begin the formula with the function wizard. Then, just select the known
numbers and choose finish. REMEMBER TO place the text cursor in the
formula bar at the end of the formula and hit Control/Shift/Enter to complete
the trend for the remaining months.

Jan              Feb              Mar              Apr
\$33,456          \$35,465          \$34,323          \$37,654
=TREND(knows y's)                          \$33,761          \$34,649          \$35,537          \$36,424

To calculate a trend for future periods, enter the know values and count the number of
values you have beginning with 1. Then enter the future period numbers beginning a
the last known period number (If you have six knows periods, begin with 7 or any
number above 6). When you run the function wizard, select blank cells below or
to the right of the new period numbers where the future trend will be calculated.
Then begin the function wizard and select the know values for the first step and
then select the new period numbers for the new periods. Complete the formula
and then place the cursor in the formula bar behind the formula and make sure
you hit Control/Shift/Enter to complete the trend for the remaing new periods.

Jan              Feb              Mar              Apr
\$33,456          \$35,465          \$34,323          \$37,654
Jul              Aug              Sep              Oct
7                8                9               10
=TREND(knows y's,known x's, new x)         \$39,088          \$39,976          \$40,863          \$41,751

=Pi - Returns value of Pi            3.14159265

=Power(number,power)
(I.e. 6 raised to 5th power)                15625

=Large(Range,position) 15th                     83               89                77              74
=Median(Range)                                47.5              100                48              14
=Mode(Range)                                    61               61                36              92
=Average(Range)                                 49               44                63              25
=Percentile(Range,number) .2                    21               35                66              91
=Percentile Rank(Range,number) 66            0.722               36                21               7
=Small(Range) 5th                                4               28                34              90
=Stdev(Range)                                28.81               35                56              44
95                29              35
53                22              97
1                59              65
29   97   37
23   79    7
31   21   27
INDEX

M.T.D.
\$12,705
\$8,000
\$5,650
\$26,355

\$4,070
\$814
\$1,221
\$1,221
\$407
\$204
\$5,698
\$2,035
\$15,670

5,698

\$10,686

\$2,137

\$8,548
Rounded Average
8,876
9,026
8,376
8,801

Rounded Average
90
83
87
81

ch criteria, and a range to
an be a text match

es" is \$149.58
of a range of numbers.
quartile to find)
0 to 4 indicated below:

o generate random numbers         10   32    6
46   30   27
ervals, multiply RAND() by the   45   24   40
nswer to get a whole number.     28   18   22
48   19   36
en 1 and 48 is                    37   18   45
8   15   35

or the trend of future periods
the trend for future
ing with 1 and the future
period into the future.
the trend for any periods
rs as the new periods in

without any future periods,
nown periods before you
n, just select the known
lace the text cursor in the
ntrol/Shift/Enter to complete

May                     Jun
\$36,754           \$38,231
\$37,312           \$38,200

now values and count the number of
e future period numbers beginning above
ows periods, begin with 7 or any
zard, select blank cells below or
future trend will be calculated.
ow values for the first step and
periods. Complete the formula
nd the formula and make sure
for the remaing new periods.

May                     Jun
\$36,754     \$38,231
Nov                  Dec
11          12
\$42,639     \$43,527

75              82   94
13               4   59
90              74    7
93              44   19
16              62   61
26              86   20
5              96   50
61              47   83
25               3   12
72              46    4
60              61   53
60   85   49
17    2   57
39   29   67
FINANCIAL FUNCTIONS                                                                           INDEX

Annual                                              Monthly
Interest            Term           Principal        Mortgage
=PMT(Int,term,prin)                          7.75%                 360         \$45,000         \$322.39
Fill out table with 12                       7.75%                 360         \$50,000         \$358.21
entries increasing the                       7.75%                 360         \$55,000         \$394.03
principal by \$3,000 and                      7.75%                 360         \$60,000         \$429.85
keeping interest and                         7.75%                 360         \$65,000         \$465.67
term the same                                7.75%                 360         \$70,000         \$501.49
7.75%                 360         \$75,000         \$537.31
7.75%                 360         \$80,000         \$573.13
7.75%                 360         \$85,000         \$608.95
7.75%                 360         \$90,000         \$644.77
7.75%                 360         \$95,000         \$680.59
7.75%                 360       \$100,000          \$716.41
7.75%                 360       \$105,000          \$752.23

=NPER(Int,Payment,Prin)              Number of Periods = Need principal, interest rate, and payment
to calculate how long to pay off a loan. Remember to convert
rate to a monthly rate and place a (-) in front of payment

Principal           Annual Rate     Payment          NPeriods(Mon)
120000            7.75%               1000           231.71

=RATE(Term,Payment,Prin)             Interest Rate - Need principal, number of periods, and payment
per period. Remember to convert periods to months if necessary.
Place a (-) in front of the payment figure and REMEMBER to multiply
the interest rate answer by 12 to convert to an annual rate if months
used for number of periods.
Answer is .063% for monthly rate and 7.761% for annual rate

Principal           NPeriods(Yrs.)Payment            Monthly Rate
75000             25              560            0.63%

=FV(Int,Term,Prin)
Compound Interest on An Investment      Interest            Term           Principal   Future Value
Uses FV Formula and sets the                 7.18%                 10            25000   \$50,012.38
value of PMT to 0 and the PV
as the money you now have
Future Value of an annuity with             Interest         Term          Payment       CurrentPrin.
fixed paymetns over time and with                7.18%               20         2000            50000
a lump sum in the account now
uses FV with interest rate,
periods, payment each period
and present value of money now

Future Values - Used to calculate compound interest if you know
the interest rate, the amount you have saved, and the term, or
used to calculate the future value of an annuity if you know the
interest rate, the term, the initial deposit and the payment per term.
If you use FV to caculate compoung interest, place a "0" in the
Payment per periond box and place your present amount in the
PV or present value box. Place a "-" in front of the PV box
to calculate positive numbers for compound interest, and a
"-" in front of the payment per period to calculate positive numbers
for an annuity.

Answer for compound interest of \$5000 at 7.18% for ten years is 10,002.48
Answer for compound interest of \$5000 at 7.9% for twenty-five years with a
monthly deposit of \$150.00 is \$177,081.90

=FV(Int,Term,Payment,Prin)              PV-Present   Interest       Term                Payment
5000          7.18%                10                0
5000          7.90%                25              150

=SLN(cost of asset, salvage value after depreciation, life of asset)

Cost of Delivery Truck                          \$8,000
Salvage Value                                     \$500
Life of Delivery Truck in years                      10
Each year's SNL                                \$750.00

Cost of Machine                                \$10,000
Salvage Value                                     \$100
Life of Machine in years                             15
Each year's SNL                                \$660.00
ths if necessary.
MEMBER to multiply
nual rate if months

Annual Rate
7.62%
Future Value
\$289,723.68

r ten years is 10,002.48
twenty-five years with a

Future Value
\$10,002.48
\$177,081.90
DB Functions

DATABASE FUNCTIONS                                                     INDEX

Examples: The following illustration shows a database for a small orchard. Each record contains
information about one tree. The range A13:E19 is named TreeInfo, and the range A9:F11 is named Criteria.
Because the database (a14:e20) has a blank row above and below it, the formulas will automatically
recognize the field names in row 14. The database functions are unique formulas for finding and
evaluating data based upon the criteria of the formulas.

Tree       Height        Age        Yield     Profit     Height
Apple      >10                                            <16                       Criteria Range
Pear

Tree        Height       Age        Yield     Profit
Apple               15           20        14     \$105
Pear                12           12        10       \$96                             TreeInfo Range
Cherry              13           14         9     \$105
Apple               14           15        10       \$75
Pear                 9            8         8       \$76
Apple                6            9         6       \$45

DCOUNT(Database,"Age",A9:F10) equals 2. This function looks at the records of apple trees between a
height of 10 and 16 and counts how many of the Age fields in those records contain numbers.
Formula               2

DCOUNTA(Database,"Profit",A9:F10) equals 2. This function looks at the records of apple trees between
a height of 10 and 16 and counts how many of the Profit fields in those records are not blank.
Formula               2

DMAX(Database,"Profit",A9:A11) equals \$105.00, the maximum profit of apple and pear trees.
Formula        \$105

DMIN(Database,"Profit",A9:B10) equals \$75.00, the minimum profit of apple trees over 10.
Formula          \$75

DSUM(Database,"Profit",A9:A10) equals \$225.00, the total profit from apple trees.
Formula        \$225

DSUM(Database,"Profit",A9:F10) equals \$180.00, the total profit from apple trees with a height between 10 and 16.
Formula        \$180

DPRODUCT(Database,"Yield",A9:F10) equals 140, the product of the yields from apple trees with a height between 10 and 16.
Formula      \$140

DAVERAGE(Database,"Yield",A9:B10) equals 12, the average yield of apple trees over 10 feet in height.
Formula       \$12

DAVERAGE(Database,"Age",Database) equals 13, the average age of all trees in the database.
Formula       \$13

DSTDEV(Database,"Yield",A9:A11 equals 2.97, the estimated standard deviation in the yield of apple and

Page 18
DB Functions

pear trees if the data in the database is only a sample of the total orchard population.
Formula             2.97

DSTDEVP(Database,"Yield",A9:A11) equals 2.65, the true standard deviation in the yield of apple and pear
trees if the data in the database is the entire population.
Formula              2.65

DVAR(Database,"Yield",A9:A11) equals 8.8, the estimated variance in the yield of apple and pear
trees if the data in the database is only a sample of the total orchard population.
Formula              8.80

DVARP(Database,"Yield",A9:A11) equals 7.04, the true variance in the yield of apple and pear
trees if the data in the database is the entire orchard population.
Formula              7.04

DGET(Database,"Yield",Criteria) returns the #NUM! error value because more than one record meets the criteria.
Formula    #NUM!

Page 19
DB Functions

is named Criteria.

es between a

rees between

ght between 10 and 16.

es with a height between 10 and 16.

eet in height.

d of apple and

Page 20
DB Functions

apple and pear

ord meets the criteria.

Page 21
TextFunctins

TEXT FUNCTIONS                                                                            INDEX

Exact(TextString,TextString)          TRUE               John                     John
Returns true if text strings          FALSE              john                     John
are exact and faluse if they         TRUE               Joe                      Joe
are not (case sensitive)             FALSE              Jo                       Joe
FALSE              joe                      Joe

Concatenate(Text1,Text2)        John Vrana               John                     Vrana
Joins several text strings
into one. Use " " for
a space between words.

Dollar(Number)                               \$346.00                        346
Converts a number to text
in currency format

Even(Number)                                       344                342.73
Round a number up to the
nearest even integer

Fixed(Number,NoDecimals,               3,454.654700                3454.6547
no_commas) I.e. 6 and 2 dec.               3,454.65               3454.6547

=PROPER(text)                                            Proper                   Upper
=UPPER(text)                    deNver                   Denver                   DENVER
DallaS                   Dallas                   DALLAS
boston                   Boston                   BOSTON
San francisco            San Francisco            SAN FRANCISCO
albuquerque              Albuquerque              ALBUQUERQUE
salt Lake city           Salt Lake City           SALT LAKE CITY
new York                 New York                 NEW YORK
atlanTa                  Atlanta                  ATLANTA
conCord                  Concord                  CONCORD
seaTTle                  Seattle                  SEATTLE
las vegAs                Las Vegas                LAS VEGAS
los angeles              Los Angeles              LOS ANGELES
wichita                  Wichita                  WICHITA
spOkane                  Spokane                  SPOKANE
San diego                San Diego                SAN DIEGO
harrisBurg               Harrisburg               HARRISBURG
houston                  Houston                  HOUSTON
st. Louis                St. Louis                ST. LOUIS
tulsa                    Tulsa                    TULSA
Washington, D.C.         Washington, D.C.         WASHINGTON, D.C.
boisE                    Boise                    BOISE
des Moines               Des Moines               DES MOINES
albany                   Albany                   ALBANY

Page 22
TextFunctins

trenTon                Trenton            TRENTON
SpringField            Springfield        SPRINGFIELD
charleston             Charleston         CHARLESTON
honolulu               Honolulu           HONOLULU
anchorAge              Anchorage          ANCHORAGE

Student Name
Jan Schmidt
Donna Nunamaker
Rebecca Nowakowski
Patty Gilkey
Robert Kobak
Joanne Zindren
Robert Baumgartner
Karen Sandora
Debra Wilkerson
Ronald Young
Arleen Lucci
Lisa Boyde
Richard Ratliff
Peter Loncar
Annette Kleber
Vivian Mahli
Joanne Zindren
Denise Detorakis
Connie Johnson
Donna Smykla
Donna Smykla
Donna Smykla
Jack McMurray
Andy Novotny
Jack McMurray
Ken Marlovits
Melissa Mandak
Vicki McDonald
Marily Fitzsimmons
Thomas Magill

Find(TextString,Character)               Student Name   =Len(Student Name) =Left(" ")
Left(TextString,NoOfChars)           Jan Schmidt                        11                   4
Right(TextString,NoOfChars)          Donna Nunamaker                    15                   6
Rebecca Nowakowski                 18                   8
This example converts a name         Patty Gilkey                       12                   6
into a first and last name field.   Robert Kobak                       12                   7
Joanne Zindren                     14                   7
Robert Baumgartner                 18                   7
Karen Sandora                      13                   6
Debra Wilkerson                    15                   6

Page 23
TextFunctins

Ronald Young                12   7
Arleen Lucci                12   7
Lisa Boyde                  10   5
Richard Ratliff             15   8
Peter Loncar                12   6
Annette Kleber              14   8
Vivian Mahli                12   7
Joanne Zindren              14   7
Denise Detorakis            16   7
Connie Johnson              14   7
Donna Smykla                12   6
Donna Smykla                12   6
Donna Smykla                12   6
Jack McMurray               13   5
Andy Novotny                12   5
Jack McMurray               13   5
Ken Marlovits               13   4
Melissa Mandak              14   8
Vicki McDonald              14   6
Marily Fitzsimmons          18   7
Thomas Magill               13   7

Page 24
TextFunctins

Page 25
TextFunctins

Page 26
TextFunctins

Page 27
TextFunctins

Page 28
TextFunctins

Page 29
TextFunctins

Page 30
TextFunctins

Page 31
TextFunctins

Page 32
TextFunctins

Page 33
TextFunctins

Page 34
TextFunctins

Page 35
TextFunctins

Page 36
TextFunctins

Page 37
TextFunctins

Page 38
TextFunctins

Page 39
TextFunctins

Page 40
TextFunctins

O SPRINGS

=Left(B93,D93-1)   =Right(B93,C93-D93)
Jan                Schmidt
Donna              Nunamaker
Rebecca            Nowakowski
Patty              Gilkey
Robert             Kobak
Joanne             Zindren
Robert             Baumgartner
Karen              Sandora
Debra              Wilkerson

Page 41
TextFunctins

Ronald    Young
Arleen    Lucci
Lisa      Boyde
Richard   Ratliff
Peter     Loncar
Annette   Kleber
Vivian    Mahli
Joanne    Zindren
Denise    Detorakis
Connie    Johnson
Donna     Smykla
Donna     Smykla
Donna     Smykla
Jack      McMurray
Andy      Novotny
Jack      McMurray
Ken       Marlovits
Melissa   Mandak
Vicki     McDonald
Marily    Fitzsimmons
Thomas    Magill

Page 42
Logical Functions

LOGICAL FUNCTIONS                                                            INDEX

IF(Condition,ValueIfTrue,ValueIfFalse)

Name            Sales              Bonus
Donna Nunamaker
Rebecca           \$45,678         See Me
Nowakowski        \$55,698            \$5,570
Patty Gilkey      \$65,999            \$6,600
Robert Kobak      \$33,456         See Me
Joanne Zindren
Robert            \$49,999         See Me
Baumgartner       \$50,000            \$5,000
Karen Sandora     \$50,001            \$5,000
Debra Wilkerson   \$45,981         See Me
Ronald Young      \$88,987            \$8,899
Arleen Lucci      \$23,590         See Me
Lisa Boyde        \$66,666            \$6,667

=IF(AND(test1, test2...test 30),true result, false result)
=IF(OR(test1, test2...test 30),true result, false result)

Invoice Totals by Quarter - Greater than 8,500.00 and 0 late reports
1st Qtr    2nd Qtr     3rd Qtr     4th Qtr   Rounded Average Late Reports
Joe Tucker                9,800.50 7,500.05 9,100.45 9,100.36                   8,876.00             0
Sue Jones                 9,500.12 8,000.25 9,400.56 9,200.24                   9,026.00             5
Mary Walters              6,700.40 9,400.68 8,700.00 8,700.98                   8,376.00             4
Fred Roberts              9,100.03 8,700.00 8,600.25 8,800.56                   8,801.00             2

Grades - Above 85 or 0 absenses
First Qtr   Mid-term    Third Qtr       Final   Rounded Average     Absences
Ryan Nelson                    98          94          94          76                 91            5
Tom Watson                     65          79          91          99                 84            4
Nancy Carr                     89          85          95          80                 81            0
Barbara Orr                    75          88          84          77                 81            3

Page 43
Logical Functions

Bonus
Yes
No
No
No

Pass/Fail
Pass
Fail
Pass
Fail

Page 44
Date Functions

DATE FUNCTIONS                                            INDEX

=TODAY() - Todays Date
=NOW() - Todays Date and Time
=WEEKDAY - Code for Weekday

Date and Time                    ###########                      1   Sunday
Your Birth Date                     4/12/1972                     2   Monday
Today's Date                       11/29/2011                     3   Tuesday
How many days you have lived           14,476                     4   Wednesday
Weekday                                     4                     5   Thursday
Weekday you were born            Wednesday                        6   Friday
7   Saturday
Date Hired                           6/1/1992
Today's Date                       11/29/2011
How many days you have worked           7,120
Weekday                                     2
Weekday you were hired           Monday

=Day - Number for Day of Month              29
=Hour - Number for Hour                     18
=Minute - Number for Minute                 18
=Second - Number for Second                 23
=Time - Current Time                  6:18 PM
=Year - The Current Year                 2011

Page 45
LOOKUP AND GOAL SEEKING FUNCTIONS                                                  INDEX

=Vlookup(value to look up, table to look in, column in table)
To use a lookup table, you first create the table with the grade range
in column one and the grade in column two. Then select the grade table
including the column headings and the range and name the range by
selecting Insert, Name and Define and give it a name. Then you can
use this range in a lookup table to automatically give grades to the
students based upon their test scores. The formular for using a lookup
function is =Vlookup(cell where score is, range name of lookup range,
and the number 2 for the column in the table where the letter is for the range.)

50                    F                     Archer              59       F
60                    D                     Delaney             84       B
70                    C                     Bellows             92       A
80                    B                     Vrana               75       C
90                    A                     Wilson              68       D
Nunn                93       A
Katz                85       B
Graham              83       B
Sorensky            54       F
Daniel              77       C
Wilbert             64       D
Henry               89       B
Carson              75       C
James               88       B
Holfner             69       D
Gibbons             74       C
Larson              92       A
Kennedy             69       D
Ito                 88       B
Simpson             51       F

=VLOOKUP(what to look up, where to look it up, column for the answer)
Remember to make table including headings be an absolute reference in formula

Taxable Salary              Tax Rate
8,000          12%
12,000          13%
18,000          14%
28,000               16%
55,000               20%
81,000               25%
100,000               32%

Tax Rate   Net Salary
John                              8,100             12%       7,128
Sue                             132,000             32%      89,760
Heny                             48,000             16%      40,320
Lewis                            86,000             25%      64,500
Gail                             61,000             20%      48,800
Fran                             16,000             13%      13,920
Dolly                           143,000             32%      97,240
Edna                              9,500             12%       8,360
Louise                           19,000             14%      16,340

=Hlookup                   Fruit             Apples        Oranges        Pears       Grapes          Plums
Price lb.             1.67        2.34             1.45             0.98           1.33

Fruits             Lbs. Sold    Price Lb.   Total Sale
Apples                24          1.67             40.08
Oranges               34          2.34             79.56
Pears                 56          1.45              81.2
Apples                76          1.67            126.92
Plums                 45          1.33             59.85
Plums                123          1.33            163.59
Grapes               432          1.67            721.44
Apples                89          1.67            148.63
Peaches               15          2.34              35.1
Pears                 67          1.45             97.15
Oranges               44          2.34            102.96
Peaches               99          2.34            231.66
Pears                 45          1.45             65.25

GOAL SEEKING
Goal Seeking - Use Tools/Goal Seeking to change value in a
cell to another value. Place cursor on cell with the formula and then
use Tools/Goal Seeking and set value in this cell to \$400 by changing
value in principal cell.

Goal Seeking
Interest rate          7.75%
Term                      360
Principal          50000
Goal Seeking     \$358.21

=Column(Cell ref)                   5
=Row(Cell ref)                     76
Peaches
2.29
MACROS                                                                               INDEX

Checkbook Register

Deposit
Check  Check Checkbook
PAYEE                                                Date    Number Amount   Balance
CHECKBOOK BALANCE CARRIED FORWARD                            ----              \$585.21
Deposit - Paycheck                                  1/2/1996        ######## \$2,110.21
PNC Mortgage - January                              1/2/1996    9126    \$502.53    \$1,607.68
ALA Deposit                                          1/5/1996           \$120.88    \$1,728.56
Kaufmann's - Jo's Tennis Bracelet                    1/9/1996   9127    \$193.99    \$1,534.57
Bell Atlantic in full                                1/9/1996   9128    \$125.83    \$1,408.74
Duquesne Light in full                               1/9/1996   9129    \$111.65    \$1,897.09
Time Warner in full                                  1/9/1996   9130     \$39.47    \$1,857.62
Time - 1 of 3                                        1/9/1996   9131     \$14.98    \$1,842.64
Dan Prevade - Paper for January                      1/9/1996   9132     \$12.90    \$1,829.74
Welfare Check                                                          ########    \$3,029.74
Cash Deposit                                                            \$300.00    \$3,329.74
Browning Ferris - Garbage through March 31, 1996 1/9/1996       9133     \$29.91    \$3,299.83
Sears for Shower Head and Kitchen Faucet            1/11/1996   9134    \$123.03    \$3,176.80
Citibank in full for Greensburg, car oil, software. 1/11/1996   9135    \$203.63    \$2,973.17
People's Gas in full                                1/11/1996   9136    \$100.95    \$2,872.22
M. Stahl Plumbing for work in house                 1/14/1996   9137     \$59.80    \$2,812.42
Allstate - 1/2 paid, \$290.00 due                    1/14/1996   9138    \$275.00    \$2,537.42
SeeSaw Book Club                                    1/20/1996   9139      \$3.45    \$2,533.97
Bank Americard in full - Greensburg Meal            1/20/1996   9140     \$12.98    \$2,520.99

To create a macro which will automatically perform a deposit calculation in
the check register above, do the following:
1. Place the cursor in cell E8 and use Tools/Macro Record which will open a dialog box.
2. Name the macro DepositBalance and say OK.
3. A new toolbar with the stop recording button appear for you to stop keystroke recording
when you are done.
4. Begin recording by pressing the "=" sign to begin a formula. Then hit the up arrow key;
then type the "+" sign; then hit the left arrow key; then hit enter to complete the
recording.
5. Hit the stop button.
6. The finished macro will be placed on a Module page
7. Goto the Module page and delete the last line in the macro which repositions the cursor
because it is not necessary.
To create a macro which will automatically perform a check payment calculation in
the check register above, do the following:
1. Place the cursor in cell E9 and use Tools/Macro Record which will open a dialog box.
2. Name the macro CheckBalance and say OK.
3. A new toolbar with the stop recording button appear for you to stop keystroke recording
when you are done.
4. Begin recording by pressing the "=" sign to begin a formula. Then hit the up arrow key;
then type the "-" sign; then hit the left arrow key; then hit enter to complete the
recording.
5. Hit the stop button.
6. The finished macro will be placed on a Module page
7. Goto the Module page and delete the last line in the macro which repositions the cursor
because it is not necessary.

To Assign the Check Payment and Deposit Macros to buttons
on the toolbars, do the following:
1. GoTo View/Toolbars and choose Customize
2. Select the Custom Category of buttons and drag the gray button to a toolbar
3. When the assign macro box opens, choose the deposit macro
4. Next, right click on the gray button and choose Edit/Button Image
5. Use the drawing palette to draw a large Green "D" in the image box
6. When done, say okay.
7. The macro has now been assigned to the button and the button image edited.
8. Repeat the above process for the Check Payment Macro
9. Test to see that the macros work in the check register to the left.
QTR1         QTR2          QTR3         QTR4         TOTALS
North            456,555      479,383       503,352      528,519      1,967,809
South            567,345      595,712       625,498      656,773      2,445,328
East             498,765      523,703       549,888      577,383      2,149,739
West             500,333      525,350       551,617      579,198      2,156,498
TOTALS         2,022,998     2,124,148    2,230,355     2,341,873     8,719,374

To create a macro to automatically format a totals row with an outline border, bold type,
Dollar accounting format, and green fill pattern, do the following:
1. Select the totals row above from G8 through L8
2. Select Tools/Macro/Record Macro
3. Name the macro FormatTotals and say OK
4. Next GoTo the Format/Cells Menu and choose Number Format and select Accounting
and accept the first choice; then goto the Border tab and choose Outline and select
a heavy line or the third one down; then goto the pattern tab and select green as
the color; Then goto the Font tab and choose bold, and click OK to end the formatting
5. Then click on the Stop Button to Stop Recording.
6. Do a test by selecting the Totals row and then use Tools/Macro and scroll down until your find
the FormatTotals Macro and choose run.
7. The Totals row should now have the formatting that you like.

To create a macro to launch another program from within Excel like the file manager
Goto the Module 1 or Module 2 Page and type the following at the bottom of the
worksheet leaving a blank line between the beginning of this macro and anything above

Sub RunWinFile()
ChDir "c:\windows"
MyAppID = Shell("c:\windows\explorer.exe", 1) ' Run Explorer
AppActivate MyAppID ' Activate Explorer
End Sub

This is using statements from the Visual Basic Programming Language Built into EXCEL
To create a new Menu Item on the Menu Bar and Assign the Format Totals and Run
WinFile Macros to this menu so that that are easily accessible, do the following:
1. Choose View/Toolbars and place an "x" in the Visual Basic Toolbar to bring it on screen
2. Select the Menu Editor Button which is the second button in from left on top row
3. Click on "End of Menu Bar" at the bottom of the box on the left and choose INSERT
4. In the Caption Box, type "S&pecial" for the name of the menu; the "&" indicates
that you can select this menu with the Alternate P key.
5. Next click on the "End of Menu" text in the Menu Item Box and click on INSERT
6. In the Caption Box, type the test for the menu item as "Format Totals"
7. Next click on the drop down arrow in the macro box and select the "Format Totals" macro
8. Click on OK and notice that you have a new menu called "S&pecial" on your menu bar
9. It has a down down menu command to run the macro to format totals.
10. Use this same process to add the menu item "Run File Manager" to the new ""S&pecial" menu

To create a new Macro which will automatically fill all blank cells with a zero currency format
so that you can use fill blank cells in a selection and then use AutoSum to total the column
or row. The following is the macro code for the macro which should be hand entered on a
macro sheet. After the macro is entered, select a range of cells which includes blank cells
and then run the macro to fill the cells with zeros. Then use AutoSum to get a correct formula.
MACRO CODE
Sub FillBlanks()
For Each Cell In Selection
If Cell.Value = Empty Then
Cell.Value = 0
Cell.NumberFormat = "\$#,##0_);[Red](\$#,##0)"
End If
Next Cell
End Sub
TRANSPOSE FUNCTION                                                         INDEX
Change from vertical to horizontal or horizontal to vertical orientation

Month           Sales          Sales
Ending         (Units)       (Dollars)
Jan-98             576          \$8,876
Feb-98             619          \$9,539
Mar-98             898         \$13,838
Apr-98             900         \$13,869
May-98             338          \$5,209
Jun-98             670         \$10,325

Step 1        Select the area to transpose (I.e. A5:C12)
Step 2        Edit/Copy
Step 3        Position cursor in cell where you want information transposed - A25
Step 4        Edit/Paste Special, then check box for "Transpose"

Instructions for User Form and Visual Basic Code to Transpose Data
Step 1       GoTo Visual Basic Editor from Tools/Macro
Step 2       In VB Editor, use Insert User Form
Step 3       Use toolbar to draw labels, reference blocks, command buttons,
and check box
Step 4       Format controls appropriately and space them to make it look nice
Step 5       In VB Editor, Insert Visual Basic Module with Insert/VB Module
Step 6       Type following code in VB Module

Option Explicit
Sub TransposeData()
Dim InputRange As Range
Dim OutputRange As Range
Dim OutputRows As Integer
Dim OutputColumns As Integer
Dim OutputColumns As Integer
Dim TransposeFormula As Variant
Dim i As Integer
Dim j As Integer
With UserForm1
.RefEdit1.Text = " "
.RefEdit2.Text = " "
.CheckBox1 = False
.Show
Set InputRange = Range(.RefEdit1.Text)
Set OutputRange = Range(.RefEdit2.Text)
End With
OutputColumns = InputRange.Rows.Count
OutputRows = InputRange.Columns.Count
Set OutputRange = OutputRange.Offset(0, 0).Resize(OutputRows,
OutputColumns)
TransposeFormula = "=TRANSPOSE(" & UserForm1.RefEdit1.Text & ")"
TransposeFormula = Application.ConvertFormula _
(Formula:=TransposeFormula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlR1C1)
OutputRange.FormulaArray = TransposeFormula
If UserForm1.CheckBox1 = True Then
For i = 1 To OutputRows
For j = 1 To OutputColumns
OutputRange(i, j).NumberFormat = InputRange(j, i).NumberFormat
Next j
Next i
End If
End Sub

Step 8    Change text on button
Step 9    Assign the Transpose Function to the Macro
Step 10   Test and run the macro by clicking on the button and transposing numbers
.Text & ")"

berFormat
INDEX-MATCH FUNCTION                                INDEX

Commissions
Units
Prices                 \$1    \$50             \$100       \$150             \$200             \$250
\$1         \$1     \$2               \$2         \$3               \$3               \$4
\$5         \$5     \$8              \$10        \$13              \$15              \$18
\$10        \$10    \$15              \$20        \$25              \$30              \$35
\$15        \$15    \$23              \$30        \$38              \$45              \$53
\$20        \$20    \$30              \$40        \$50              \$60              \$70
\$25        \$25    \$38              \$50        \$63              \$75              \$88
\$30        \$29    \$45              \$60        \$75              \$90             \$105
\$35        \$34    \$53              \$70        \$88             \$105             \$123
\$40        \$39    \$60              \$80       \$100             \$120             \$140
\$45        \$44    \$68              \$90       \$113             \$135             \$158
\$50        \$49    \$75             \$100       \$125             \$150             \$175
\$55        \$54    \$83             \$110       \$138             \$165             \$193
\$60        \$59    \$90             \$120       \$150             \$180             \$210

Range Name "Prices"                                               Range Name "Commissions"

Sales Commission Report

Price per unit        \$45          Price per unit           \$57                  Price per unit
Units Sold              50         Units Sold               241                 Units Sold

Commission            \$68          Commission           \$165                    Commission

The Formula is
=INDEX(Commissions,MATCH(B23,Prices,1),MATCH(B24,Units,1))

Page 59
Page 60
Range Name "Units"

\$300   \$350   \$400
\$4     \$5     \$5
\$20    \$23    \$25
\$40    \$45    \$50
\$60    \$68    \$75
\$80    \$90   \$100
\$100   \$113   \$125
\$120   \$135   \$150
\$140   \$158   \$175
\$160   \$180   \$200
\$180   \$203   \$225
\$200   \$225   \$250
\$220   \$248   \$275
\$240   \$270   \$300

\$28
333

\$100

Page 61
DATABASE OPERATIONS                                                 INDEX

Company
Invoice      Date       Number        Company         Amount        Tax       Total
11560   9/22/1997        101   Blue Sky Airlines    \$3,299.00    \$197.94    \$3,496.94
11561   9/25/1997         85   National Museum        \$601.00     \$36.06      \$637.06
11562   9/30/1997        106   NAPCO                \$3,233.00    \$193.98    \$3,426.98
11563   10/3/1997         72   Diskriter              \$455.00     \$27.30      \$482.30
11564   10/4/1997         98   NewComm                \$689.00     \$41.34      \$730.34
11565   10/7/1997         85   National Museum        \$987.00     \$59.22    \$1,046.22
11566   10/7/1997        106   NAPCO                \$1,234.00     \$74.04    \$1,308.04
11567   10/7/1997        106   NAPCO                \$2,346.00    \$140.76    \$2,486.76
11568   11/3/1997        101   Blue Sky Airlines    \$4,170.00    \$250.20    \$4,420.20
11569   11/4/1997         72   Diskriter              \$498.00     \$29.88      \$527.88
11570   11/4/1997         85   National Museum        \$875.00     \$52.50      \$927.50
11571   11/5/1997        106   NAPCO                  \$987.00     \$59.22    \$1,046.22
11572   11/8/1997         66   Vrana Inc.             \$432.00     \$25.92      \$457.92
11573   ########          72   Diskriter              \$658.00     \$39.48      \$697.48
11574   ########          85   National Museum      \$2,233.00    \$133.98    \$2,366.98
11575   ########          66   Vrana Inc.           \$4,321.00    \$259.26    \$4,580.26
11576   ########          72   Diskriter            \$5,432.00    \$325.92    \$5,757.92
11577   ########          66   Vrana Inc.           \$2,345.00    \$140.70    \$2,485.70
11578   ########         101   Blue Sky Airlines      \$654.00     \$39.24      \$693.24
11579   ########          85   National Museum        \$729.00     \$43.74      \$772.74
11580   ########          85   National Museum        \$811.00     \$48.66      \$859.66
11581   12/3/1997         66   Vrana Inc.             \$959.00     \$57.54    \$1,016.54
11582   12/3/1997         72   Diskriter            \$1,265.00     \$75.90    \$1,340.90
11583   12/4/1997         66   Vrana Inc.           \$3,245.00    \$194.70    \$3,439.70
11584   12/5/1997        106   NAPCO                \$4,234.00    \$254.04    \$4,488.04
11585   12/6/1997        106   NAPCO                  \$711.00     \$42.66      \$753.66
11586   12/7/1997        101   Blue Sky Airlines    \$1,121.00     \$67.26    \$1,188.26
11587   12/8/1997         85   National Museum      \$1,918.00    \$115.08    \$2,033.08
11588   12/9/1997        101   Blue Sky Airlines    \$1,546.00     \$92.76    \$1,638.76
A. This is a simple database with column headings as fields and records as rows
B. To show form view, select top row & choose Data Form
C. To sort records, put curosr in a cell & choose Data/Sort and then pick field and order
D. To get subtotals, first sort on company and then place cursor
in any cell except top row and choose subtotals. Then pick Total as field and sum.
E. To filter records, choose Data/AutoFilter and choose criteria like Amount > 2000.

Sort, Find, Filter, Enter New Records, and Manipulate and Print Data from this Sheet
GROUP AND UNGROUPING DATA                                         INDEX

1993
North NorthEast     South SouthEast Central SouthWest West       To goup and ungroup data
January   \$34,567 \$36,295     \$38,110  \$40,016 \$42,016   \$44,117 \$46,323    Remember to select row n
February \$35,846 \$37,638      \$39,520  \$41,496 \$43,571   \$45,750 \$48,037    The Data is grouped and b
March     \$34,568 \$36,296     \$38,111  \$40,017 \$42,018   \$44,119 \$46,324    row or column headings to
April     \$35,847 \$37,639     \$39,521  \$41,497 \$43,572   \$45,751 \$48,038    group.
May       \$34,569 \$36,297     \$38,112  \$40,018 \$42,019   \$44,120 \$46,326    Select rows 4 through 15 th
June      \$35,848 \$37,640     \$39,522  \$41,499 \$43,574   \$45,752 \$48,040    Choose Data/Group and ch
July      \$34,570 \$36,299     \$38,113  \$40,019 \$42,020   \$44,121 \$46,327    Select rows 20 through 31
August    \$35,849 \$37,642     \$39,524  \$41,500 \$43,575   \$45,754 \$48,041    Select rows 37 through 48
September \$34,571 \$36,300     \$38,115  \$40,020 \$42,021   \$44,122 \$46,328    (You cannot group multiple
October   \$35,850 \$37,643     \$39,525  \$41,501 \$43,576   \$45,755 \$48,043    Notice than there is a "-" si
November \$34,572 \$36,301      \$38,116  \$40,021 \$42,022   \$44,124 \$46,330    Click on the minus sign to c
December \$35,851 \$37,644      \$39,526  \$41,502 \$43,577   \$45,756 \$48,044    Click on the "+" sign to exp
Totals   ######## \$443,634   ######## \$489,106 ######## \$539,240 ########   To remove the grouping, se
choose Data/Group and
1994
North NorthEast     South SouthEast Central SouthWest West
January   \$76,890 \$79,735     \$82,685  \$85,744 \$88,917   \$92,207 \$95,619
February \$34,572 \$35,851      \$37,178  \$38,553 \$39,980   \$41,459 \$42,993
March     \$35,851 \$37,178     \$38,553  \$39,980 \$41,459   \$42,993 \$44,584
April     \$37,178 \$38,553     \$39,980  \$41,459 \$42,993   \$44,584 \$46,233
May       \$34,568 \$35,847     \$37,173  \$38,549 \$39,975   \$41,454 \$42,988
June      \$35,847 \$37,173     \$38,549  \$39,975 \$41,454   \$42,988 \$44,579
July      \$34,569 \$35,848     \$37,174  \$38,550 \$39,976   \$41,455 \$42,989
August    \$35,848 \$37,174     \$38,550  \$39,976 \$41,455   \$42,989 \$44,580
September \$44,345 \$45,986     \$47,687  \$49,452 \$51,281   \$53,179 \$55,146
October   \$56,765 \$58,865     \$61,043  \$63,302 \$65,644   \$68,073 \$70,592
November \$45,678 \$47,368      \$49,121  \$50,938 \$52,823   \$54,777 \$56,804
December \$98,765 \$102,419    ######## \$110,139 ######## \$118,440 ########
Totals   ######## \$591,998   ######## \$636,617 ######## \$684,598 ########

1995
North NorthEast South SouthEast Central SouthWest West
January    \$67,565 \$67,295 \$67,026 \$66,757 \$66,490    \$66,224 \$65,960
February   \$44,343 \$44,166 \$43,989 \$43,813 \$43,638    \$43,463 \$43,289
March      \$23,456 \$23,362 \$23,269 \$23,176 \$23,083    \$22,991 \$22,899
April      \$65,432 \$65,170 \$64,910 \$64,650 \$64,391    \$64,134 \$63,877
May        \$76,543 \$76,237 \$75,932 \$75,628 \$75,326    \$75,024 \$74,724
June       \$66,554 \$66,288 \$66,023 \$65,759 \$65,496    \$65,234 \$64,973
July       \$55,467 \$55,245 \$55,024 \$54,804 \$54,585    \$54,366 \$54,149
August    \$87,658 \$87,307 \$86,958    \$86,610 \$86,264     \$85,919 \$85,575
September \$98,345 \$97,952 \$97,560    \$97,170 \$96,781     \$96,394 \$96,008
October   \$34,689 \$34,550 \$34,412    \$34,274 \$34,137     \$34,001 \$33,865
November \$21,456 \$21,370 \$21,285     \$21,200 \$21,115     \$21,030 \$20,946
December \$33,445 \$33,311 \$33,178     \$33,045 \$32,913     \$32,781 \$32,650
Totals   ######## \$672,253 ######## \$666,886 ########   \$661,562 ########
To goup and ungroup data, select the rows or columns
Remember to select row numbers, not cells
The Data is grouped and buttons appear in the
ow or column headings to expand or collapse the

Select rows 4 through 15 through the West column.
Choose Data/Group and choose Group
Select rows 20 through 31 and group
Select rows 37 through 48 and group
You cannot group multiple selections but must do them one at a time)
Notice than there is a "-" sign next to each group
Click on the minus sign to collapse the data.
Click on the "+" sign to expand the data.
To remove the grouping, select the rows again, and
choose Data/Group and the Ungroup command.
DATA TABLE FOR CALCULATING VALUES FOR WHAT IF OPERATIONS                                                    INDEX

Principal       Interest         Term
1000000            7.80%               25

Different Interest Rates
Amount               7.80%            8.00%           8.12%           8.14%            8.37%        8.50%
\$22,806.21       \$23,202.74      \$23,442.00      \$23,481.97       \$23,943.60   \$24,206.09
1,000,000      \$22,806.21       \$23,202.74      \$23,442.00      \$23,481.97      \$23,943.60   \$24,206.09
1,100,000      \$25,086.83       \$25,523.02      \$25,786.20      \$25,830.17      \$26,337.96   \$26,626.70
1,200,000      \$27,367.45       \$27,843.29      \$28,130.40      \$28,178.36      \$28,732.32   \$29,047.31
1,300,000      \$29,648.07       \$30,163.57      \$30,474.60      \$30,526.56      \$31,126.68   \$31,467.92
1,400,000      \$31,928.69       \$32,483.84      \$32,818.80      \$32,874.76      \$33,521.04   \$33,888.53
1,500,000      \$34,209.31       \$34,804.12      \$35,163.00      \$35,222.96      \$35,915.40   \$36,309.14
1,600,000      \$36,489.93       \$37,124.39      \$37,507.20      \$37,571.15      \$38,309.76   \$38,729.74
1,700,000      \$38,770.55       \$39,444.66      \$39,851.40      \$39,919.35      \$40,704.12   \$41,150.35
1,800,000      \$41,051.17       \$41,764.94      \$42,195.60      \$42,267.55      \$43,098.48   \$43,570.96
1,900,000      \$43,331.79       \$44,085.21      \$44,539.80      \$44,615.74      \$45,492.84   \$45,991.57
2,000,000      \$45,612.41       \$46,405.49      \$46,884.00      \$46,963.94      \$47,887.20   \$48,412.18
2,100,000      \$47,893.03       \$48,725.76      \$49,228.20      \$49,312.14      \$50,281.56   \$50,832.79
2,200,000      \$50,173.66       \$51,046.04      \$51,572.40      \$51,660.33      \$52,675.92   \$53,253.40
2,300,000      \$52,454.28       \$53,366.31      \$53,916.60      \$54,008.53      \$55,070.28   \$55,674.01
2,400,000      \$54,734.90       \$55,686.58      \$56,260.80      \$56,356.73      \$57,464.64   \$58,094.62
2,500,000      \$57,015.52       \$58,006.86      \$58,605.00      \$58,704.93      \$59,859.00   \$60,515.23
2,600,000      \$59,296.14       \$60,327.13      \$60,949.20      \$61,053.12      \$62,253.36   \$62,935.83
2,700,000      \$61,576.76       \$62,647.41      \$63,293.40      \$63,401.32      \$64,647.72   \$65,356.44
2,800,000      \$63,857.38       \$64,967.68      \$65,637.60      \$65,749.52      \$67,042.08   \$67,777.05
2,900,000      \$66,138.00       \$67,287.96      \$67,981.80      \$68,097.71      \$69,436.44   \$70,197.66
3,000,000      \$68,418.62       \$69,608.23      \$70,326.00      \$70,445.91      \$71,830.80   \$72,618.27

To use the table function on the Data Menu, you can create various options or a
scenario from a single formula by changing a particular value and create a table
showing these options. See Finished Table Below:
The following are the steps:
A. Enter data for a formula or function like an interest payment which is not
part of the range of variable data which you will use for your scenario
B. Set up a range of values which vary like the numbers above
C. To the right of this range and one cell above, write a forumula to calculate
the answer for a range of data. Use the data you entered at the top to
enter in the function wizard except the interest rate portion which is in
the new data and is different than above.
D. Writer this formula in the cell above and to the left of the first number in
the range.
E. Write similar forumlas for the other interest rates running in row 7 using
the principal and term figures from the initial data in row 4.
F. Then select all of the numbers in the variable range, including the first column along with the
column with the amounts to substitute, and the row including the formulas for the
different interest rates with formulas, then select Data/Table Command.
This is range A8:G29
G. Then point to the principal figure in cell A4 as the column input cell
H. Then select OK and watch the table fill in all of the mortgage payments
for the different principal amounts and interest rates.

Principal       Interest           Term
1000000            7.80%                 25

Different Interest Rates
Amount                7.80%            8.00%           8.12%           8.14%              8.37%           8.50%
\$22,806.21       \$23,202.74      \$23,442.00      \$23,481.97         \$23,943.60      \$24,206.09
1,000,000       \$22,806.21       \$23,202.74      \$23,442.00      \$23,481.97         \$23,943.60      \$24,206.09
1,100,000       \$25,086.83       \$25,523.02      \$25,786.20      \$25,830.17         \$26,337.96      \$26,626.70
1,200,000   \$27,367.45   \$27,843.29   \$28,130.40   \$28,178.36   \$28,732.32   \$29,047.31
1,300,000   \$29,648.07   \$30,163.57   \$30,474.60   \$30,526.56   \$31,126.68   \$31,467.92
1,400,000   \$31,928.69   \$32,483.84   \$32,818.80   \$32,874.76   \$33,521.04   \$33,888.53
1,500,000   \$34,209.31   \$34,804.12   \$35,163.00   \$35,222.96   \$35,915.40   \$36,309.14
1,600,000   \$36,489.93   \$37,124.39   \$37,507.20   \$37,571.15   \$38,309.76   \$38,729.74
1,700,000   \$38,770.55   \$39,444.66   \$39,851.40   \$39,919.35   \$40,704.12   \$41,150.35
1,800,000   \$41,051.17   \$41,764.94   \$42,195.60   \$42,267.55   \$43,098.48   \$43,570.96
1,900,000   \$43,331.79   \$44,085.21   \$44,539.80   \$44,615.74   \$45,492.84   \$45,991.57
2,000,000   \$45,612.41   \$46,405.49   \$46,884.00   \$46,963.94   \$47,887.20   \$48,412.18
2,100,000   \$47,893.03   \$48,725.76   \$49,228.20   \$49,312.14   \$50,281.56   \$50,832.79
2,200,000   \$50,173.66   \$51,046.04   \$51,572.40   \$51,660.33   \$52,675.92   \$53,253.40
2,300,000   \$52,454.28   \$53,366.31   \$53,916.60   \$54,008.53   \$55,070.28   \$55,674.01
2,400,000   \$54,734.90   \$55,686.58   \$56,260.80   \$56,356.73   \$57,464.64   \$58,094.62
2,500,000   \$57,015.52   \$58,006.86   \$58,605.00   \$58,704.93   \$59,859.00   \$60,515.23
2,600,000   \$59,296.14   \$60,327.13   \$60,949.20   \$61,053.12   \$62,253.36   \$62,935.83
2,700,000   \$61,576.76   \$62,647.41   \$63,293.40   \$63,401.32   \$64,647.72   \$65,356.44
2,800,000   \$63,857.38   \$64,967.68   \$65,637.60   \$65,749.52   \$67,042.08   \$67,777.05
2,900,000   \$66,138.00   \$67,287.96   \$67,981.80   \$68,097.71   \$69,436.44   \$70,197.66
3,000,000   \$68,418.62   \$69,608.23   \$70,326.00   \$70,445.91   \$71,830.80   \$72,618.27
CONVERT TEXT TO COLUMNAR INFORMATION                                                INDEX

Williams, John, 345 Birth Street, Pittsburgh, PA, 15108, 36               To convert text to columns, make sure all o
Gibson, Louis, 123 Fifth Avenue, Gibsonia, PA, 15214, 45                  one column. The entries can be any numbe
Grayson, Millie, 456 Levinson Blvd., New York, NY, 20002, 27              each different text entry in a row with a com
Clinton, William, 1600 Pennsylvania Ave., Washington, DC, 10015, 53       delimiter character per the list to the right wh
Williams, Ester, 457 Sunset Strip, Hollywood, CA, 16789, 68               This information could have come from a wo
Lewis, Helen, 2345 Groin Way, Coraopolis, PA, 15108, 18                   someone entered information this way. In o
Nelson, Juanita, 1200 Wilshire, Indianapolis, IN, 16744, 52               in columns in a worksheet, select all of the r
Mahilic, Joan, 18 Longhorn Ave., Greenwich, CT, 18765, 44                 text by selecting just the row cells in the firs
Cowell, Ronald, 33 Fourth Avenue, Pittsburgh, PA, 15324, 32               Then choose Data/Text to Columns from th
Barker, Robert, 17 Penchant Street, Monroeville, PA, 15137, 58            Indicate how the information is delimited (i.e
Donaldson, Linda, 345 17th Street, Harrisburg, PA, 16987, 51              Choose Next and indicate the delimited cha
Larson, Sonny, 56 Honeywell, Pittsburgh, PA, 15212, 21                    Choose Next and indicate the column data f
Moon, Walter, Apartment 5 - Westen Inn, Houston, TX, 16878, 34              selecting a column and choosing the form
Frank, Fred, 28 Heather Way, Seatonville, IL, 34521, 41                     column from the table you will create.
Smith, John, 13 Tomahawk Avenue, Jamestown, VA, 28765, 19                 Also select the destination cell where the tab
Smith, James, 167 Wiley Avneue, Kingston, NY, 20038, 29                     in the destination box and then on a cell in
Rooker, Lois, 18 Heartbreak Street, Clingon, WA, 13456, 44                  the data will be placed in columns left to ri
Peterson, Nancy, 1897 Straubs Lane, Troy, WI, 18734, 51                     default destination cell, the data will replac
Quincy, Melinda, 14 Hollow Avenue, Baton Rouge, LA, 34588, 91               beginning in the first column where you en
Trenton, Ruth, 87 Nylon Road, Lexington, KY, 14567, 57                      In this exercise, choose cell A26 as the de
Unger, Ralph, 90087 Ghost Avenue, Boise, ID, 56433, 72                      we can see both the original and converte
Choose Finish and watch the text be conver

Last        First                Address                      City     State      Zip
Williams     John         345 Birth Street                 Pittsburgh     PA          15108
Gibson       Louis        123 Fifth Avenue                 Gibsonia       PA          15214
Grayson      Millie       456 Levinson Blvd.               New York       NY          20002
Clinton      William      1600 Pennsylvania Ave.           Washington     DC          10015
Williams     Ester        457 Sunset Strip                 Hollywood      CA          16789
Lewis        Helen        2345 Groin Way                   Coraopolis     PA          15108
Nelson       Juanita      1200 Wilshire                    Indianapolis   IN          16744
Mahilic      Joan         18 Longhorn Ave.                 Greenwich      CT          18765
Cowell       Ronald       33 Fourth Avenue                 Pittsburgh     PA          15324
Barker       Robert       17 Penchant Street               Monroeville    PA          15137
Donaldson    Linda        345 17th Street                  Harrisburg     PA          16987
Larson       Sonny        56 Honeywell                     Pittsburgh     PA          15212
Moon         Walter       Apartment 5 - Westen Inn         Houston        TX          16878
Frank        Fred         28 Heather Way                   Seatonville    IL          34521
Smith        John         13 Tomahawk Avenue               Jamestown      VA          28765
Smith        James        167 Wiley Avneue                 Kingston       NY          20038
Rooker     Lois      18 Heartbreak Street   Clingon       WA   13456
Peterson   Nancy     1897 Straubs Lane      Troy          WI   18734
Quincy     Melinda   14 Hollow Avenue       Baton Rouge   LA   34588
Trenton    Ruth      87 Nylon Road          Lexington     KY   14567
Unger      Ralph     90087 Ghost Avenue     Boise         ID   56433
onvert text to columns, make sure all of the text is entered in
column. The entries can be any number of rows. Separate
different text entry in a row with a comma, space, or other
iter character per the list to the right which is comma delimited.
nformation could have come from a wordprocessing file where
eone entered information this way. In order to place this text
umns in a worksheet, select all of the rows containing the
by selecting just the row cells in the first column.
choose Data/Text to Columns from the Data Menu
ate how the information is delimited (i.e. by commas or spaces)
se Next and indicate the delimited character like a comma or tab.
se Next and indicate the column data format for each column by
ecting a column and choosing the format or electing to exclude this
umn from the table you will create.
select the destination cell where the table is to begin by clicking
he destination box and then on a cell in the worksheet where
data will be placed in columns left to right. If you accept the
ault destination cell, the data will replace the data you have
ginning in the first column where you entered the information
his exercise, choose cell A26 as the destination cell so that
can see both the original and converted data.
se Finish and watch the text be converted to columnar data.

Age
36
45
27
53
68
18
52
44
32
58
51
21
34
41
19
29
44
51
91
57
72
SUB-TOTALS                                                           INDEX

To automatically generate subtotals for repetitive data like above, select all of the data including the row

Then select Data/Subtotals and indicate the field for the subtotals which in this case would be company.
Pick the function you want to summarize on from the drop down list of functions, i.e. sum
Indicate the numeric column you want to subtotal, i.e. amount, tax, or total
Indicate if you want to summary to replace the current sub-totals and if the summary should be
placed below each change in the field you selected. Check only summary below data.
Then select OK to finish the subtotal function

Invoice     Date        CompNo                Company               Amount             Tax          Total
11560   9/22/1994             101    Blue Sky Airlines             \$3,299.00         \$197.94     \$3,496.94
11568   11/3/1994             101    Blue Sky Airlines             \$4,170.00         \$250.20     \$4,420.20
11578   ########              101    Blue Sky Airlines               \$654.00          \$39.24       \$693.24
11586   12/7/1994             101    Blue Sky Airlines             \$1,121.00          \$67.26     \$1,188.26
11588   12/9/1994             101    Blue Sky Airlines             \$1,546.00          \$92.76     \$1,638.76
Blue Sky Airlines Total      \$10,790.00         \$647.40    \$11,437.40
11563   10/3/1995               72   Diskriter                       \$455.00          \$27.30       \$482.30
11569   11/4/1994               72   Diskriter                       \$498.00          \$29.88       \$527.88
11573   ########                72   Diskriter                       \$658.00          \$39.48       \$697.48
11576   ########                72   Diskriter                     \$5,432.00         \$325.92     \$5,757.92
11582   12/3/1994               72   Diskriter                     \$1,265.00          \$75.90     \$1,340.90
Diskriter Total               \$8,308.00         \$498.48     \$8,806.48
11562    9/30/1994            106    NAPCO                         \$3,233.00         \$193.98     \$3,426.98
11566    10/7/1994            106    NAPCO                         \$1,234.00          \$74.04     \$1,308.04
11567    10/7/1994            106    NAPCO                         \$2,346.00         \$140.76     \$2,486.76
11571    11/5/1995            106    NAPCO                           \$987.00          \$59.22     \$1,046.22
11584    12/5/1994            106    NAPCO                         \$4,234.00         \$254.04     \$4,488.04
11585    12/6/1994            106    NAPCO                           \$711.00          \$42.66       \$753.66
NAPCO Total                  \$12,745.00         \$764.70    \$13,509.70
11561   9/25/1994               85   National Museum                 \$601.00          \$36.06       \$637.06
11565   10/7/1994               85   National Museum                 \$987.00          \$59.22     \$1,046.22
11570   11/4/1994               85   National Museum                 \$875.00          \$52.50       \$927.50
11574   ########                85   National Museum               \$2,233.00         \$133.98     \$2,366.98
11579   ########                85   National Museum                 \$729.00          \$43.74       \$772.74
11580   ########                85   National Museum                 \$811.00          \$48.66       \$859.66
11587   12/8/1994               85   National Museum               \$1,918.00         \$115.08     \$2,033.08
National Museum Total         \$8,154.00         \$489.24     \$8,643.24
11564 10/4/1994                 98   NewComm                         \$689.00          \$41.34       \$730.34
NewComm Total                   \$689.00          \$41.34       \$730.34
11572 11/8/1995                 66   Vrana Inc.                      \$432.00          \$25.92       \$457.92
11575   ########    66   Vrana Inc.          \$4,321.00     \$259.26    \$4,580.26
11577   ########    66   Vrana Inc.          \$2,345.00     \$140.70    \$2,485.70
11581   12/3/1994   66   Vrana Inc.            \$959.00      \$57.54    \$1,016.54
11583   12/4/1994   66   Vrana Inc.          \$3,245.00     \$194.70    \$3,439.70
Vrana Inc. Total   \$11,302.00     \$678.12   \$11,980.12
Grand Total        \$51,988.00   \$3,119.28   \$55,107.28
CONSOLIDATION                                                 INDEX

Sales for First Quarter
Product A        \$45,678   To perform a consolidation, first select the destination
Product E        \$55,467   cells to include both the labels and the data. To do a
Product F        \$67,876   sum, select cells F14 and G14, then goto Data/Consolidate.
Product H        \$34,567   Then select each of the four ranges separately, one at
Product I        \$56,789   a time, and click on the ADD button. When done, there
Product M        \$45,678   should be four ranges in the summary box.
Check use data labels in the left column for the table
Click okay to consolidate the information. Then select
Sales for Second Quarter   the consolidated data with left column headings and
Product B      \$23,456     data, and choose Data/Sort to place them in order.
Product C      \$43,567     Then use Autosum to get a total of the consolidated data.
Product F      \$87,654
Product H      \$67,832
Product I      \$77,654        Product      Annual Sales
Product M      \$44,689     Product A           \$124,580
Product D            \$91,308
Sales for Third Quarter    Product E            \$78,923
Product A       \$23,456    Product B            \$99,999
Product B       \$76,543    Product C           \$186,708
Product C       \$65,476    Product F           \$166,753
Product D       \$45,654    Product H           \$178,988
Product E       \$23,456    Product I           \$233,208
Product F       \$11,223    Product M           \$136,045
TOTAL             \$1,296,512
Sales for Fourth Quarter
Product A       \$55,446
Product C       \$77,665
Product D       \$45,654
Product H       \$76,589
Product I       \$98,765
Product M       \$45,678
9e2b7403-5159-4e09-bfd9-db9d115ee0ee.xls

SOLVER                                                                                               INDEX

Month                      Q1          Q2            Q3           Q4              Total        Use Tools/solver, Set the targer cell,
Seasonality                0.9         1.1           0.8          1.2                           changing cells, and constraints
per the coding in the box below.
Units Sold              3,858        4,038        3,542        4,304           15,741
Sales Revenue       \$154,318      \$161,517    \$141,670     \$172,149          \$629,653          Color Coding
Cost of Sales          96,449      100,948       88,544      107,593          393,533
Gross Margin           63,576       66,276       58,833       70,263          258,948                         Target cell = 90,000

Salesforce             22,000       22,000       22,000        22000             88000                        Changing cells - B7:E10
Advertising            12,000        8,000       13,000         7500             40500
Corp Overhead          23,148       24,227       21,251        25822             94448                        Constraints - F18 >=12%
Total Costs            43,148       40,227       43,251        42322            222948

Prod. Profit         \$20,429       \$26,048      \$15,583      \$27,940          \$90,000
Profit Margin             13%         16%           11%          16%               14%

Product Price       \$40.00
Product Cost        \$25.00

Month                      Q1          Q2            Q3           Q4              Total        Use Tools/solver, Set the targer cell,
Seasonality                0.9         1.1           0.8          1.2                           changing cells, and constraints
per the coding in the box below.
Units Sold              3,858        4,038        3,542        4,304           15,741
Sales Revenue       \$154,318      \$161,517    \$141,670     \$172,149          \$629,653          Color Coding
Cost of Sales          96,449      100,948       88,544      107,593          393,533
Gross Margin           57,869       60,569       53,126       64,556          236,120                         Target cell = 90,000

Salesforce             22,000       22,000       22,000        22000             88000                        Changing cells - B27:E30
Advertising            12,000        8,000       13,000         7500             40500
Corp Overhead          23,148       24,227       21,251        25822             94448                        Constraints - F38 >=12%
Total Costs            43,148       40,227       43,251        42322            222948

Prod. Profit         \$14,722       \$20,341       \$9,876      \$22,233          \$67,172
Profit Margin            10%           13%           7%          13%              11%

Product Price       \$40.00
Product Cost        \$25.00

The following examples show you how to work with the model above to solve for one value or several
values to maximize or minimize another value, enter and change constraints, and save a problem model.

Row        Contains                  Explanation
3         Fixed values              Seasonality factor: sales are higher in quarters 2 and 4,
and lower in quarters 1 and 3.
5         =35*B3*(B11+3000)^0.5 Forecast for units sold each quarter: row 3 contains
the seasonality factor; row 11 contains the cost of
6         =B5*\$B\$18                 Sales revenue: forecast for units sold (row 5) times

Page 77
9e2b7403-5159-4e09-bfd9-db9d115ee0ee.xls

price (cell B18).
7           =B5*\$B\$19                 Cost of sales: forecast for units sold (row 5) times
product cost (cell B19).
8           =B6-B7                    Gross margin: sales revenues (row 6) minus cost of
sales (row 7).
10           Fixed values              Sales personnel expenses.
12           =0.15*B6                  Corporate overhead expenses: sales revenues (row 6)
times 15%.
13           =SUM(B10:B12)             Total costs: sales personnel expenses (row 10) plus
15           =B8-B13                   Product profit: gross margin (row 8) minus total costs
(row 13).
16           =B15/B6                   Profit margin: profit (row 15) divided by sales revenue
(row 6).
18           Fixed values              Product price.
19           Fixed values              Product cost.
This is a typical marketing model that shows sales rising from a base figure (perhaps due to the sales
personnel) along with increases in advertising, but with diminishing returns. For example, the first
\$5,000 of advertising in Q1 yields about 1,092 incremental units sold, but the next \$5,000 yields only
You can use Solver to find out whether the advertising budget is too low, and whether advertising
should be allocated differently over time to take advantage of the changing seasonality factor.

Solving for a Value to Maximize Another Value
One way you can use Solver is to determine the maximum value of a cell by changing another cell. The
two cells must be related through the formulas on the worksheet. If they are not, changing the value in
one cell will not change the value in the other cell.
For example, in the sample worksheet, you want to know how much you need to spend on advertising
to generate the maximum profit for the first quarter. You are interested in maximizing profit by changing
n   On the Tools menu, click Solver. In the Set target cell box, type b15 or
select cell B15 (first-quarter profits) on the worksheet. Select the Max option.
In the By changing cells box, type b11 or select cell B11 (first-quarter advertising)
on the worksheet. Click Solve.
You will see messages in the status bar as the problem is set up and Solver starts working. After a
moment, you'll see a message that Solver has found a solution. Solver finds that Q1 advertising of
\$17,093 yields the maximum profit \$15,093.
n   After you examine the results, select Restore original values and click OK to
discard the results and return cell B11 to its former value.

Resetting the Solver Options
If you want to return the options in the Solver Parameters dialog box to their original settings so that
you can start a new problem, you can click Reset All.

Solving for a Value by Changing Several Values
You can also use Solver to solve for several values at once to maximize or minimize another value. For
example, you can solve for the advertising budget for each quarter that will result in the best profits for
the entire year. Because the seasonality factor in row 3 enters into the calculation of unit sales in row 5
as a multiplier, it seems logical that you should spend more of your advertising budget in Q4 when the
sales response is highest, and less in Q3 when the sales response is lowest. Use Solver to determine
the best quarterly allocation.

Page 78
9e2b7403-5159-4e09-bfd9-db9d115ee0ee.xls

n   On the Tools menu, click Solver. In the Set target cell box, type f15 or select
cell F15 (total profits for the year) on the worksheet. Make sure the Max option is
selected. In the By changing cells box, type b11:e11 or select cells B11:E11
(the advertising budget for each of the four quarters) on the worksheet. Click Solve.
n   After you examine the results, click Restore original values and click OK to
discard the results and return all cells to their former values.
You've just asked Solver to solve a moderately complex nonlinear optimization problem; that is, to find
values for the four unknowns in cells B11 through E11 that will maximize profits. (This is a nonlinear
problem because of the exponentiation that occurs in the formulas in row 5). The results of this
unconstrained optimization show that you can increase profits for the year to \$79,706 if you spend
\$89,706 in advertising for the full year.
However, most realistic modeling problems have limiting factors that you will want to apply to certain
values. These constraints may be applied to the target cell, the changing cells, or any other value that
is related to the formulas in these cells.

So far, the budget recovers the advertising cost and generates additional profit, but you're reaching a
point of diminishing returns. Because you can never be sure that your model of sales response to
advertising will be valid next year (especially at greatly increased spending levels), it doesn't seem
prudent to allow unrestricted spending on advertising.
problem that limits the sum of advertising during the four quarters to \$40,000.
dialog box appears. In the Cell reference box, type f11 or select cell F11
(advertising total) on the worksheet. Cell F11 must be less than or equal to \$40,000.
The relationship in the Constraint box is <= (less than or equal to) by default, so
you don't have to change it. In the box next to the relationship, type 40000. Click
OK, and then click Solve.
n   After you examine the results, click Restore original values and then click OK
to discard the results and return the cells to their former values.
The solution found by Solver allocates amounts ranging from \$5,117 in Q3 to \$15,263 in Q4. Total
Profit has increased from \$69,662 in the original budget to \$71,447, without any increase in the

Changing a Constraint
When you use Microsoft Excel Solver, you can experiment with slightly different parameters to decide
the best solution to a problem. For example, you can change a constraint to see whether the results
are better or worse than before. In the sample worksheet, try changing the constraint on advertising
dollars to \$50,000 to see what that does to total profits.
n   On the Tools menu, click Solver. The constraint, \$F\$11<=40000, should
already be selected in the Subject to the constraints box. Click Change. In
the Constraint box, change 40000 to 50000. Click OK, and then click Solve.
Click Keep solver solution and then click OK to keep the results that are
displayed on the worksheet.
Solver finds an optimal solution that yields a total profit of \$74,817. That's an improvement of \$3,370
over the last figure of \$71,447. In most firms, it's not too difficult to justify an incremental investment of
\$10,000 that yields an additional \$3,370 in profit, or a 33.7% return on investment. This solution also
results in profits of \$4,889 less than the unconstrained result, but you spend \$39,706 less to get there.

Saving a Problem Model
When you click Save on the File menu, the last selections you made in the Solver Parameters
dialog box are attached to the worksheet and retained when you save the workbook. However, you
can define more than one problem for a worksheet by saving them individually using Save Model in
the Solver Options dialog box. Each problem model consists of cells and constraints that you
entered in the Solver Parameters dialog box.

Page 79
9e2b7403-5159-4e09-bfd9-db9d115ee0ee.xls

When you click Save Model, the Save Model dialog box appears with a default selection, based
on the active cell, as the area for saving the model. The suggested range includes a cell for each
constraint plus three additional cells. Make sure that this cell range is an empty range on the
worksheet.
n   On the Tools menu, click Solver, and then click Options. Click Save Model.
In the Select model area box, type h15:h18 or select cells H15:H18 on the
worksheet. Click OK.

Note You can also enter a reference to a single cell in the Select model area box. Solver will use
this reference as the upper-left corner of the range into which it will copy the problem specifications.

To load these problem specifications later, click Load Model on the Solver Options dialog box,
type h15:h18 in the Model area box or select cells H15:H18 on the sample worksheet, and then
click OK. Solver displays a message asking if you want to reset the current Solver option settings with
the settings for the model you are loading. Click OK to proceed.

Month                       Q1           Q2           Q3            Q4             Total
Seasonality                 0.9          1.1          0.8           1.2

Units Sold              3,858        4,038        3,542         4,304            15,741
Sales Revenue        \$154,318     \$161,517     \$141,670      \$172,149          \$629,653
Cost of Sales          96,449      100,948       88,544       107,593           393,533
Gross Margin           57,869       60,569       53,126        64,556           236,120

Salesforce              22,000      22,000        22,000         22000            88000
Advertising             12,000       8,000        13,000          7500            40500
Corp Overhead           23,148      24,227        21,251         25822            94448
Total Costs             43,148      40,227        43,251         42322           222948

Prod. Profit          \$14,722      \$20,341        \$9,876      \$22,233           \$67,172
Profit Margin             10%          13%            7%          13%               11%

Product Price        \$40.00
Product Cost         \$25.00

Page 80
9e2b7403-5159-4e09-bfd9-db9d115ee0ee.xls

r, Set the targer cell,
and constraints
in the box below.

rget cell = 90,000

anging cells - B7:E10

nstraints - F18 >=12%

r, Set the targer cell,
and constraints
in the box below.

rget cell = 90,000

anging cells - B27:E30

nstraints - F38 >=12%

Page 81
Microsoft Excel 8.0 Answer Report                          INDEX
Worksheet: [Master Exercise.xlt]Solver
Report Created: 5/10/98 10:46:26 PM

Target Cell (Value Of)
Cell         Name         Original Value Final Value
\$F\$17 Prod. Profit Total          \$67,172     \$90,000

Cell         Name   Original Value Final Value
\$B\$7 Units Sold Q1            3,858        3,858
\$C\$7 Units Sold Q2            4,038        4,038
\$D\$7 Units Sold Q3            3,542        3,542
\$E\$7 Units Sold Q4            4,304        4,304
\$B\$8 Sales Revenue Q1      \$154,318    \$154,318
\$C\$8 Sales Revenue Q2      \$161,517    \$161,517
\$D\$8 Sales Revenue Q3      \$141,670    \$141,670
\$E\$8 Sales Revenue Q4      \$172,149    \$172,149
\$B\$9 Cost of Sales Q1        96,449       96,449
\$C\$9 Cost of Sales Q2       100,948     100,948
\$D\$9 Cost of Sales Q3        88,544       88,544
\$E\$9 Cost of Sales Q4       107,593     107,593
\$B\$10 Gross Margin Q1        57,869       63,576
\$C\$10 Gross Margin Q2        60,569       66,276
\$D\$10 Gross Margin Q3        53,126       58,833
\$E\$10 Gross Margin Q4        64,556       70,263

Constraints
Cell        Name            Cell Value    Formula     Status    Slack
\$F\$18 Profit Margin Total            14% \$F\$18>=0.12 Not Binding    2%
PIVOT TABLES                                            INDEX

Product   Year   Month   Sales   SalespersonRegion   To summarize data in a pivot table, you use the
Produce   1992    May     6329   Davolio    North    pivot table wizard. From the data menu, choose pivo
Meat      1992    Jul     5691   Buchanan   South    First you select a range of data to summarize. In this
Dairy     1993    Mar     7029   Davolio    North    case, select from cell A1 through F141. Next summa
Produce   1993    Mar     5477   Davolio    North    data by draggind the field names to the following area
Produce   1992    Mar     3523   Davolio    South    the pivot table wizard diagram.
Produce   1993    Jan     8462   Davolio    North    Drag sales to the data pane in the center
Produce   1993    Nov     8193   Buchanan   North    Drag Year first and then Salesperson uder in in the ro
Produce   1993    Mar     9528   Davolio    South    Drag Product to the Column pane.
Dairy     1993    Nov     3181   Davolio    North    Drag Region to the page pane
Produce   1992    May     4562   Davolio    South    Finally, indicate where pivot table to be place Cel
Meat      1992    May     6112   Buchanan   North    Pivot table is formed and notice down down arrow in
Meat      1992   Nov     9509    Davolio    North
Dairy     1993   May     5576    Buchanan   North    Pivot Table Exercise - New and Interactive
Produce   1993   May     7539    Davolio    North
Dairy     1992   Mar     3731    Davolio    South    Year         (All)
Dairy     1993   Jul     2742    Buchanan   South
Meat      1992   Mar      169    Buchanan   South    Sum of Sales
Meat      1993   Nov     7782    Buchanan   South    Region      Salesperson
Meat      1993   Jan     1132    Davolio    South    North       Buchanan
Dairy     1993   Sep     6082    Buchanan   South                Davolio
Meat      1992   Nov       31    Buchanan   South    North Total
Produce   1993   Jul     3106    Buchanan   North    South       Buchanan
Dairy     1992   Mar     8752    Buchanan   North                Davolio
Meat      1993   Jul     1361    Buchanan   South    South Total
Dairy     1992   Jan     7687    Buchanan   South    Grand Total
Produce   1993   Sep     8366    Davolio    North
Dairy     1993   Mar     9615    Buchanan   South
Dairy     1992   Sep     8089    Buchanan   South
Meat      1993   Nov     5327    Buchanan   North
Dairy     1992   Jan     3572    Buchanan   North
Produce   1992   Nov      156    Davolio    North
Meat      1993   Jan     6956    Buchanan   South
Dairy     1993   Jan     1648    Buchanan   South
Produce   1992   Jan      387    Buchanan   South
Dairy     1993   Mar     3947    Buchanan   North
Meat      1993   May     4231    Buchanan   North
Dairy     1993   Nov     8077    Buchanan   South
Meat      1992   Sep      669    Davolio    North
Produce   1993   May     7863    Buchanan   North
Dairy     1993   May    5448   Buchanan   South
Produce   1992   Mar    7347   Davolio    North
Meat      1993   Mar    2011   Davolio    South
Produce   1993    Jul   5972   Davolio    North
Produce   1992   May    6759   Buchanan   South
Meat      1992   Mar     797   Davolio    South
Meat      1992   Sep    6716   Buchanan   North
Meat      1992   Jan    1433   Davolio    North
Meat      1992   May    5605   Buchanan   South
Dairy     1992    Jul   3511   Buchanan   South
Produce   1992   Jan    8179   Davolio    South
Dairy     1992    Jul   9585   Davolio    South
Meat      1992    Jul   9963   Davolio    North
Produce   1992   Sep      30   Buchanan   North
Meat      1992   Mar     656   Buchanan   North
Produce   1993   Jan    3316   Buchanan   South
Dairy     1992    Jul   7612   Davolio    North
Produce   1992   Mar    7048   Buchanan   South
Dairy     1992    Jul   4953   Buchanan   North
Meat      1993   Mar    2420   Davolio    North
Dairy     1992   Nov    3965   Buchanan   South
Meat      1993   Nov    5594   Davolio    South
Dairy     1992   Nov    1898   Davolio    North
Produce   1992   May    5407   Buchanan   North
Meat      1993   Sep    8166   Davolio    South
Meat      1993   Sep    3563   Davolio    North
Dairy     1993   Sep    2219   Davolio    South
Dairy     1992   May    1914   Buchanan   South
Produce   1993   Mar    8868   Buchanan   North
Meat      1992   May    9663   Davolio    South
Produce   1992   Jan    2478   Buchanan   North
Dairy     1993    Jul   9082   Buchanan   North
Dairy     1993    Jul   3945   Davolio    South
Dairy     1992   Mar    9686   Davolio    North
Produce   1992   Jan     665   Davolio    South
Meat      1993   May     450   Buchanan   South
Meat      1993   Jan    7224   Davolio    North
Meat      1993   Nov    8243   Davolio    North
Dairy     1993   Jan    9248   Davolio    South
Dairy     1992   Sep      75   Buchanan   North
Dairy     1993   Nov    5416   Buchanan   North
Produce   1992   Sep    6805   Davolio    South
Meat      1993   Mar    6652   Buchanan   North
Dairy     1992   Sep   4873   Davolio    South
Produce   1993   Nov   1557   Davolio    South
Produce   1993   May   8726   Davolio    South
Meat      1993   May   7670   Davolio    South
Produce   1993   Jan   8334   Buchanan   North
Dairy     1992   Sep   3338   Davolio    North
Produce   1992   Jul   2243   Davolio    North
Meat      1992   Mar   2800   Davolio    North
Meat      1992   Sep   2687   Davolio    South
Dairy     1992   Mar   1441   Buchanan   South
Meat      1993   Sep   7925   Buchanan   South
Produce   1993   May   9225   Buchanan   South
Meat      1993   Jul   9065   Buchanan   North
Meat      1993   Mar   8671   Buchanan   South
Meat      1992   Sep   4653   Buchanan   South
Produce   1992   Sep   6127   Buchanan   South
Dairy     1993   Jul   2734   Davolio    North
Produce   1993   Sep   8312   Davolio    South
Meat      1993   May   4047   Davolio    North
Dairy     1992   Nov   5010   Davolio    South
Meat      1992   Jul   8860   Davolio    South
Meat      1992   Nov   3210   Buchanan   North
Dairy     1993   May   9434   Davolio    South
Produce   1993   Jul   9292   Davolio    South
Produce   1992   Jul   6712   Davolio    South
Produce   1992   Nov    556   Davolio    South
Meat      1992   Jan   2667   Davolio    South
Produce   1992   Nov   6085   Buchanan   North
Meat      1993   Jul   7191   Davolio    South
Produce   1993   Sep   9567   Buchanan   North
Produce   1993   Jan   8629   Davolio    South
Dairy     1992   Jan   3802   Davolio    South
Dairy     1992   May   6028   Davolio    North
Dairy     1993   Nov   7063   Davolio    South
Produce   1993   Nov   8013   Davolio    North
Dairy     1992   Nov   6596   Buchanan   North
Dairy     1993   Mar   5416   Davolio    South
Dairy     1992   Jan   6040   Davolio    North
Produce   1993   Mar   1328   Buchanan   South
Dairy     1993   Sep   1944   Davolio    North
Produce   1992   Nov    265   Buchanan   South
Dairy     1993   Jan   2529   Davolio    North
Dairy     1993   Sep     13   Buchanan   North
Dairy     1992   May   3306   Davolio    South
Produce   1993   Sep   5747   Buchanan   South
Dairy     1993   May   8517   Davolio    North
Dairy     1992   May   2421   Buchanan   North
Meat      1992   Nov   5720   Davolio    South
Meat      1992   May   6963   Davolio    North
Dairy     1993   Jan   7113   Buchanan   North
Meat      1993   Jan   8447   Buchanan   North
Meat      1993   Jul   9033   Davolio    North
Produce   1993   Nov   1451   Buchanan   South
Meat      1992   Jan   6739   Buchanan   North
Produce   1992   Mar   4923   Buchanan   North
Meat      1992   Jan   9494   Buchanan   South
Meat      1992   Jul   7345   Buchanan   North
Produce   1992   Sep   9979   Davolio    North
data in a pivot table, you use the
d. From the data menu, choose pivot table.
range of data to summarize. In this
cell A1 through F141. Next summarize
the field names to the following areas of
zard diagram.
data pane in the center
nd then Salesperson uder in in the row pane
he Column pane.
he page pane
where pivot table to be place Cell H 20
med and notice down down arrow in year

rcise - New and Interactive

Product
Dairy        Meat        Produce Grand Total
\$57,517      \$64,501     \$64,856 \$186,873
\$60,537      \$65,866     \$69,881 \$196,284
\$118,053     \$130,367    \$134,737 \$383,157
\$60,217      \$58,788     \$41,653 \$160,658
\$67,631      \$62,158     \$77,045 \$206,834
\$127,849     \$120,946    \$118,698 \$367,492
\$245,902     \$251,313    \$253,435 \$750,650
SCENARIOS                                           INDEX

Mortgage                             Best Case    Worst Case Likely Case Desired Case
Principal             \$90,000            90,000      110,000      90,000        90,000
Interest               7.75%             7.75%        9.50%       8.13%         6.25%
Term                       30                30           30          30            25
Monthly Mortgage     \$644.77            \$644.77      \$924.94    \$668.56        \$593.70

Car Payments                         Likely Case Worst Case Best Case      Desired Case
Principal             \$12,000          \$12,000      \$16,000    \$11,000        \$12,000
Interest               7.00%               7%           8%         4%             3%
Term                        4                4            5          4              3
Monthly Payment      \$287.35            \$287.35      \$324.42    \$248.37        \$348.97

See Scenario Summary on Next Sheet

Mortgage                             Best Case    Worst Case Likely Case Desired Case
Principal                                90,000      110,000      90,000        90,000
Interest                                 7.75%        9.50%       8.13%         6.25%
Term                                         30           30          30            25
Monthly Mortgage                        \$644.77      \$924.94    \$668.56        \$593.70

Car Payments                         Likely Case Worst Case Best Case      Desired Case
Principal                              \$12,000      \$16,000    \$11,000        \$12,000
Interest                                   7%           8%         4%             3%
Term                                         4            5          4              3
Monthly Payment                         \$287.35      \$324.42    \$248.37        \$348.97
esired Case

esired Case

esired Case

esired Case
INDEX
Scenario Summary
Current Values:   Home-Best Case    Home-Worst Case
Changing Cells:
\$B\$2                \$90,000         \$90,000            \$110,000
\$B\$3                  7.75%           7.75%               9.50%
\$B\$4                      30              30                  30
\$B\$10               \$12,000         \$12,000              \$12,000
\$B\$11                    7%              7%                  7%
\$B\$12                      4               4                   4
Result Cells:
\$B\$5               \$644.77         \$644.77             \$924.94
\$B\$13              \$287.35         \$287.35             \$287.35
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Home-Likely Case Home-Desired Case   Car-Likely Case   Car-Worst Case

\$90,000           \$90,000          \$90,000           \$90,000
8.13%             6.25%            7.75%             7.75%
30                25               30                30
\$12,000           \$12,000          \$12,000           \$16,000
7%                7%               7%                8%
4                 4                4                 5

\$668.56           \$593.70          \$644.77           \$644.77
\$287.35           \$287.35          \$287.35           \$324.42
Car-Best Case Car-Desirable Case

\$90,000            \$90,000
7.75%              7.75%
30                 30
\$11,000            \$12,000
4%                 3%
4                  3

\$644.77            \$644.77
\$248.37            \$348.97
GRAPHING DATA - SEE GRAPHS ON NEXT SHEET                                   INDEX
Bar Chart, Line Chart, Pie Chart, Dual Axis Chart

Chart I - Multiple Data Series - Bar Chart

Major Auto Manufacturers
Sales in Thousands by Quarters in 1993
Q1             Q2             Q3             Q4
Ford                539            633            703              469
GM                  521            595            759              324
Chrysler            610            544            698              567
Tyoto               435            510            494              421

Chart II - Info. Plotted Over Time - Line Chart

National Cash Register Inc.
Sales by Month and Region for 1993 in Thousands
Jan            Feb        Mar                   Apr            May       Jun      Jul     Aug
North           4,356          4,289          4,315            4,478       5,134       5,789   6,100     5,700
South           4,672          4,721          4,643            4,700       5,325       5,871   6,345     5,920
East            4,980          4,765          4,434            4,325       4,255       4,125   4,050     4,000
West            5,125          5,235          5,567            5,789       5,925       6,135   6,235     6,500

Chart III - Single Data Series - % of Whole - Pie Chart

Expenditure Budget - 1994
National Widget Company
Year        Salaries   Benefits         Equipment       Supplies         Marketing
1994      ########## \$6,926,950         \$1,500,000     \$2,300,500       \$4,875,000

Chart IV. - Two Data Series Plotted on Different Axes to Show Relationships

Wiegand Appliance Division - Client Chart
Sales Compared to Operating Profit
1. Select the Data and Years and Press F11 to Plot
2. Select the Sales Data Series by click on it, then choose Format/Selected Data Series
3. Then Choose Axis Tab and select Secondary Axis & Choose OK
4. Then Select the Second Data Series by clicking twice on the red bar to first selec the blue then the red bar
5. Then Use Right Mouse Button and Choose Chart Type and Select Line Chart
6. You now have two different data sets with widely different values on the same chart and can compare sales to operating pro
7. Notice that when sales go over \$100,000 the operating profit gets significantly bigger. Economies of Scale
1989     1990     1991     1992       1993      1994
Sales    \$ 75,582 \$ 85,302 \$ 90,432 \$    91,203 \$ 101,560    #######
10.1%
Operating Profit     11.3%    11.9%       11.9%   16.9%        17.0%
Sep        Oct          Nov     Dec
5,486          5,378   5,690   5,432
5,755          5,578   5,860   5,920
3,987          3,855   3,733   3,650
6,432          6,425   6,318   6,590

pare sales to operating profit
INDEX
Auto Sales

800
700
600
500
Ford
In 1,000's400
GM
300                                                      Chrysler
200                                                      Tyoto

100
0
Q1          Q2             Q3         Q4
1997

NCR Sales by Region

7,000

6,000

5,000
North
in 1,000's

4,000
South
3,000                                                              East
West
2,000

1,000

0
Jan Feb Mar   Apr May Jun    Jul   Aug Sep Oct Nov Dec
1997
Widget Expenditures

13%

6%
Salaries
4%
Benefits
Equipment
Supplies
59%
18%                                                        Marketing

Wiegand - Sales to Profit

18.0%                                                                     \$120,000

16.0%
\$100,000
14.0%

12.0%                                                                     \$80,000

10.0%
\$60,000
8.0%

6.0%                                                                      \$40,000

4.0%
\$20,000
2.0%

0.0%                                                                      \$-
1989        1990     1991        1992          1993        1994

Sales          Operating Profit
Sales   Operating Profit
INDEX
USER-DEFINED FUNCTION                                                               INDEX

To create a user defined function, you go to the Visual Basic Module sheet
and define the mathematical expressions for the function in Visual Basic
Code. Like all VB procedures, the code begins with a Function statement
and ends with an End Function Statement.
Example      We regularly must calculate net profit and know the mathematical expression
for writing a forumula to make the calculation. To create a new permanent
function which will be available through the function wizard, go to the VB
module page and enter the following code to create a new function for
calculating Net Profit based upon the four parameters required to calculate

Function NetProfit(UnitsSold, ProductionCost, SalePrice, IncomeTaxRate)
NetProfit =((UnitsSold*SalePrice) - (UnitsSold * ProductionCost)) *(1-IncomeTaxRate)
End Function

TEST IT      Select Cell G22, open the function wizard, choose user defined functions,
and select your new function NetProfit. Use the wizard to calculate NProfit.

Units Sold Sale Price Prod. Cost Inc. Tax RateNet Profit
432      34.76      25.37       15% #NAME?
356      12.80       9.34       15% #NAME?
567      13.87      10.13       15% #NAME?
654    123.56       90.20       15% #NAME?
345      78.96      57.64       15% #NAME?
676      43.76      31.94       15% #NAME?
555      33.44      24.41       15% #NAME?
444      67.89      49.56       15% #NAME?

Macro      To automatically open the Function Wizard, write the following
to bring   macro:
up Wizard             Sub OpenNetProfitFunction()
ActiveCell.FunctionWizard
End Sub
Web Queries - Data/GetExternalData/Web Query                           INDEX
Must have queries installed and can download from the Microsoft site as an Excel Extra

Vanguard Mutual Fund Prices, Average Annual Returns and Yields
Vanguard Money Market Fund Report.iqy

YTD values represent total returns from 01/01/1998 to 04/30/1998

Money Market Funds
Fund Name            Inception                      Ticker          NAV       NAV Change
MM-TREAS                               3/9/1983   VMPXX                      1                0
MM-FEDL                               7/13/1981   VMFXX                      1                0
MM-PRIME                               6/4/1975   VMMXX                      1                0
PRIME-INST                            10/3/1989   VMRXX                      1                0
National Tax-exempt Money Market Funds
Fund Name             Inception                    Ticker            NAV       NAV Change
MUNI-MMKT                             6/10/1980 VMSXX                        1                0
State Tax-exempt Money Market Funds
Fund Name            Inception                       Ticker         NAV       NAV Change
CA MNY MKT                             6/1/1987   VCTXX                      1                0
NJ MNY MKT                             2/3/1988   VNJXX                      1                0
NY MNY MKT                             9/3/1997   VYFXX                      1                0
OH MNY MKT                            6/18/1990   VOHXX                      1                0
PA MNY MKT                            6/13/1988   VPTXX                      1                0
Fixed Income Funds
Fund Name           Inception                       Ticker         NAV       NAV Change
ST TREAS                             10/28/1991   VFISX                 10.21               0
ST FEDERAL                           12/31/1987   VSGBX                 10.14            0.01
ST BND IDX                             3/1/1994   VBISX                  9.99            0.01
ST CORP                              10/29/1982   VFSTX                 10.82            0.01
ST CORP IS                            9/30/1997   VFSIX                 10.82            0.01
IT TREAS                             10/28/1991   VFITX                 10.68            0.03
IT BND IDX                             3/1/1994   VBIIX                 10.22            0.02
TOTAL BOND                           12/11/1986   VBMFX                  10.1            0.02
TOT BD IST                            9/18/1995   VBTIX                  10.1            0.02
GNMA                                  6/27/1980   VFIIX                 10.42            0.01
IT CORP                               11/1/1993   VFICX                  9.95            0.02
LT TREAS                              5/19/1986   VUSTX                 10.63            0.05
LT BND IDX                             3/1/1994   VBLTX                 10.78            0.06

Page 101
LT CORP                      7/9/1973 VWESX                    9.19           0.05
HIYLD CORP                 12/27/1978 VWEHX                    8.12           0.01
PREF STOCK                  12/3/1975 VQIIX                   10.41           0.04
Tax-exempt Income Funds
Fund Name    Inception                      Ticker       NAV        NAV Change
MUNI-SHORT                   9/1/1977   VWSTX               15.57                0
MUNI-LTD                    8/31/1987   VMLTX               10.74                0
MUNI-INTER                   9/1/1977   VWITX               13.34             0.02
MUNI-INSRD                  9/30/1984   VILPX               12.53             0.02
MUNI-LONG                    9/1/1977   VWLTX               11.24             0.02
MUNI-HIGH                  12/27/1978   VWAHX                10.9             0.02
State Tax-exempt Income Funds
Fund Name   Inception                        Ticker     NAV        NAV Change
CA INS IT                    3/4/1994   VCAIX                10.7             0.01
CA INSURED                   4/7/1986   VCITX               11.45             0.02
FL INSURED                   9/1/1992   VFLTX               11.29             0.02
NJ INSURED                   2/3/1988   VNJTX               11.78             0.02
NY INSURED                   4/7/1986   VNYTX               11.11             0.03
OH INSURED                  6/18/1990   VOHIX                11.8             0.02
PA INSURED                   4/7/1986   VPAIX               11.34             0.02
Balanced Funds
Fund Name    Inception                      Ticker       NAV        NAV Change
ASSET ALLO                  11/3/1988   VAAPX               23.43             0.02
BAL INDEX                   11/9/1992   VBINX               17.61            -0.03
STAR                        3/29/1985   VGSTX               18.96            -0.05
VTMF BAL                     9/6/1994   VTMFX               15.77            -0.01
WELLESLEY                    7/1/1970   VWINX               22.37             0.02
WELLINGTON                   7/1/2029   VWELX                31.8            -0.03
Vanguard Lifestrategy Funds
Fund Name   Inception                      Ticker       NAV        NAV Change
LIFE-INCM                   9/30/1994   VASIX               12.93             0.02
LIFE-CONSV                  9/30/1994   VSCGX               14.26                0
LIFE-MOD                    9/30/1994   VSMGX                16.2            -0.03
LIFE-GROW                   9/30/1994   VASGX               17.88            -0.07
Growth And Income Funds
Fund Name   Inception                      Ticker       NAV        NAV Change
CONV SEC                    6/17/1986   VCVSX               12.58             -0.1
EQUITY INC                  3/21/1988   VEIPX               24.27            -0.14
INDEX 500                   8/31/1976   VFINX               102.8            -0.26
INDEX TSMP                  4/27/1992   VTSMX               25.56             -0.1
TSMP IST                     7/7/1997   VITSX               25.56            -0.11
INDEX VALU                  11/2/1992   VIVAX               22.96            -0.14
VALUE INST                  4/20/1998   N/A                 22.96            -0.14
INST INDEX                  7/31/1990   VINIX              102.15            -0.26
INST PLUS                    7/7/1997   VIIIX              102.16            -0.26
GRO & INC                  12/10/1986   VQNPX               29.21            -0.18

Page 102
SELECT VAL                            2/15/1996   VASVX              13.19           -0.07
REIT INDEX                            5/13/1996   VGSIX              13.53               0
VSP UTIL                              5/15/1992   VGSUX              15.36               0
VTMF G&I                               9/6/1994   VTGIX              23.87           -0.06
TRUST-U.S.                            1/31/1980   VTRSX              39.99           -0.19
WINDSOR                              10/23/1958   VWNDX              19.24           -0.14
WINDSOR II                            6/24/1985   VWNFX              32.66           -0.11
International Funds
Fund Name             Inception                      Ticker       NAV      NAV Change
HRZN GLBAA                            8/14/1995   VHAAX               10.95          -0.03
HRZN GLBEQ                            8/14/1995   VHGEX               13.56          -0.16
IDX-EMGMKT                             5/4/1994   VEIEX                9.59          -0.27
IDX-EUROPE                            6/18/1990   VEURX               24.61          -0.47
IDX-PACIF                             6/18/1990   VPACX                7.38          -0.11
INT GROWTH                            9/30/1981   VWIGX               18.62          -0.37
TOTAL INTL                            4/29/1996   VGTSX               11.02           -0.2
INTL VALUE                            5/16/1983   VTRIX               26.17          -0.37
Growth Funds
Fund Name             Inception                      Ticker       NAV      NAV Change
INDEX EXTD                           12/21/1987   VEXMX               33.58          -0.29
EX MKT IST                             7/7/1997   VIEIX                33.6          -0.29
IDX-MIDCAP                            5/21/1998   N/A                 10.03           0.01
MIDCAP IST                            5/21/1998   N/A                 10.03           0.01
INDEX GROW                            11/2/1992   VIGRX               26.25           0.02
IDX GR IST                            4/20/1998   N/A                 26.25           0.02
MORGAN GRO                           12/31/1968   VMRGX                19.5          -0.15
PRIMECAP                              11/1/1984   VPMCX               43.36          -0.35
VTMF CAPAP                             9/6/1994   VMCAX               23.28          -0.08
US GROWTH                              1/6/1959   VWUSX               33.96           0.07
Aggressive Growth Funds
Fund Name             Inception                      Ticker       NAV      NAV Change
EXPLORER                             12/11/1967   VEXPX               58.58          -0.64
HRZN AGRGR                            8/14/1995   VHAGX                15.8          -0.12
HRZN CAPOP                            8/14/1995   VHCOX               11.84          -0.08
IDX-SMCAP                             10/3/1960   NAESX               25.32          -0.25
SM CAP IST                             7/7/1997   VSCIX               25.33          -0.25
SMCAP GR                              5/21/1998   N/A                 10.02           0.01
SM GR INST                            5/21/1998   N/A                 10.02           0.01
SMCAP VAL                             5/21/1998   N/A                 10.02              0
SM VAL IST                            5/21/1998   N/A                 10.02              0
VSP ENERGY                            5/23/1984   VGENX               25.06          -0.43
VSP GOLD                              5/23/1984   VGPMX                7.79          -0.15
VSP HEALTH                            5/23/1984   VGHCX               83.46           0.02

FN             Explanation

Page 103
CNNfn Currencies.iqy
EUROPE
Currency                         per U.S. \$      Last Trade Date     Last

Austrian Schilling                   12.5655                 N/A    N/A
Belgian Franc                          36.75                 N/A    N/A
* British Pound                        \$1.62           5/18/1998 ######
Danish Krone                          6.7951           5/18/1998 ######

Dutch Guilder
* European                            2.0143           5/18/1998 ######
Currency Unit                          \$1.10                 N/A    N/A

Finnish Markka                         5.4213          5/18/1998   ######
French Franc                            5.988          5/18/1998   ######
German Mark                             1.784          5/18/1998   ######
Greek Drachma                           308.8                N/A      N/A
* Irish Punt                            \$1.41          5/18/1998   ######
Italian Lira                           1760.5          5/18/1998   ######
Norwegian Krone                        7.5097          5/18/1998   ######
Portugese Escudo                       182.91                N/A      N/A
Spanish Peseta                         151.86          5/18/1998   ######
Swedish Krona                           7.791                N/A      N/A
Swiss Franc                            1.4845          5/18/1998   ######
* Value in U.S. dollars

ASIA & PACIFICA
Last
Currency                           per U.S. \$    Last Trade Date      Time
* Australian Dollar                    \$0.63           5/18/1998   ######
Hong Kong Dollar                      7.7488                 N/A       N/A
Indian Rupee                           40.55                 N/A       N/A
Japanese Yen                           135.7           5/18/1998   ######
Malaysian Rinngit                     3.8375           5/18/1998   ######
* New Zealand
Dollar                                  \$0.54          5/18/1998 ######
Pakistani Rupee                         44.04                N/A    N/A
Singapore Dollar                        1.653          5/18/1998 ######
Sri Lankan Rupee                        32.25                N/A    N/A
Thai Baht                                 39.2         5/18/1998 ######
* Value in U.S. dollars

AFRICA & MIDDLE EAST

Page 104
Last
Currency                                          per U.S. \$   Last Trade Date         Time
Bahraini Dinar                                       0.3771                N/A          N/A
Cyprus Pound                                         0.2584                N/A          N/A
Jordanian Dinar                                       0.354                N/A          N/A
Kenyan Schilling                                        63.5               N/A          N/A
Kuwaiti Dinar                                        0.3061                N/A          N/A
Maltese Pound                                        0.1959                N/A          N/A
Morrocan Diham                                       4.8575                N/A          N/A
Omani Rial                                            0.385                N/A          N/A
Qatari Rial                                           3.641                N/A          N/A
Saudi Arabian Rial                                   3.7505                N/A          N/A
South African
Rand                                                 5.0957                  N/A        N/A
Tanzanian
Schilling                                               N/A                  N/A        N/A
Tunisian Dinar                                       0.5726                  N/A        N/A
United Arab
Emirates Dirham
Zimbabwean                                            3.673                  N/A        N/A
Dollar                                                 9.15                  N/A        N/A

AMERICAS
Last
Currency                                        per U.S. \$     Last Trade Date    Time
Bermudan Dollar                                          1                 N/A     N/A
Market data provided by
S and P Comstock

DBC Dow Jones.iqy
Sym.                       Company Name                   Last               Change     %Chg
\$INDU                Dow Jones Industrial Average™                        9050.91      -45.09          -0.5
ALD                  Allied-Signal Inc.                                     42 5/8     - 8/23          -0.8
AA                   Aluminum Co. of America                              73 3/16     - 13/16          -1.1
AXP                  American Express                                      103 5/8     - 7/16          -0.4
T                    AT&T                                                 56 9/16      - 7/16          -0.8
BA                   Boeing Co.                                             48 5/8         -1           -2
CAT                  Caterpillar Inc.                                       58 3/8      9/16             1
CHV                  Chevron Corp.                                        82 11/16    -1 7/10           -2
KO                   Coca-Cola Co.                                          77 3/8        1/2          0.7
DIS                  Disney Co.                                          109 11/16    - 15/16          -0.8
DD                   DuPont                                                 80 3/4        1/4          0.3

Page 105
EK                  Eastman Kodak                                       70 1/2       7/8   1.3
XON                 Exxon Corp.                                        72 9/16    -1 1/8   -1.5
GE                  General Electric                                    82 1/2     - 1/8   -0.2
GM                  General Motors                                      73 5/8     - 1/4   -0.3
GT                  Goodyear Tire & Rub                                69 3/16     7/16    0.6
HWP                 Hewlett-Packard Co.                                 66 1/8   -3 5/16   -4.8
IBM                 International Business Machines                    124 5/8    - 5/16   -0.3
IP                  International Paper                               52 11/16    - 9/16   -1.1
JNJ                 Johnson & Johnson                                      71        5/8   0.9
MCD                 McDonald's Corp.                                  62 15/16     - 3/4   -1.2
MRK                 Merck & Co.                                        119 5/8     2 7/8   2.5
MMM                 Minnesota Mining/Mfg.                               95 7/8     uchg      0
JPM                 Morgan (J.P.)                                     128 9/16    -2 3/8   -1.8
MO                  Philip Morris Cos.                                 35 3/16    - 1/16   -0.2
PG                  Procter & Gamble                                    84 1/2   2 9/16    3.1
S                   Sears,Roebuck                                       62 5/8     - 7/8   -1.4
TRV                 Travelers Group Inc.                                62 5/8     uchg      0
UK                  Union Carbide                                      53 9/16   -1 3/16   -2.2
UTX                 United technologies                                    94     -1 5/8   -1.7
WMT                 Wal-Mart Stores Inc.                                54 3/8     5/16    0.6

Detailed Stock Quote by PC Quote.iqy
Microsoft Stock

Detailed Stock Quote
NASDAQ Index (MSFT )
Price Data                        Fundamental Data
Last Sale                                 86 1/16 52 Week High
Net Change                                  -3 3/8 52 Week Low
Exchange                                 NASDAQ Volatility
Time of Last Sale                            16:01 Ex-Dividend Date
Size of Last Sale                           40,000 Dividend Amount

Bid                                                 86 Dividend Frequency
Ask                                           86 1/16 Earnings per Share
Size of Bid & Ask                                12x88 P/E Ratio
Open                                            84 1/2 Yield
High                                            87 5/8 Shares Outstanding
Low                                             84 1/2 Market Cap.                    \$
Volume                                     20,183,600 Percent Change             3.92%
Previous Close                                89 7/16 Earnings                        \$
All non-subscription data is delayed 20 minutes unless noted, and is believed accurate
PC Quote, Inc.
Go to the PC Quote/Microsoft Excel Developer's Corner

Page 106
s an Excel Extra

Returns and Yields for 05/18/1998
und Report.iqy

01/01/1998 to 04/30/1998

Average Annual Return
as of 03/31/1998 ***
Yield FNote     YTD     1 year    5 year    10 year
4.95  C        1.68       5.17      4.58        5.52
5.14  C        1.73       5.34      4.75       4.66*
5.23  C        1.75       5.42      4.79        5.71
5.3  C        1.77       5.48      4.84        5.82
5.48  C        1.83       5.66      5.02       5.49*
Average Annual Return
as of 03/31/1998 ***
Yield FNote     YTD     1 year    5 year    10 year
3.65  C         1.1       3.53      3.21        4.03
Average Annual Return
as of 03/31/1998 ***
Yield FNote     YTD     1 year    5 year    10 year
3.55  C        1.04       3.38      3.13         3.9
3.45  C        1.03       3.33      3.04        3.91
3.5  C        1.05      1.92*        ---         ---
3.56  C         1.1       3.52      3.19       3.48*
3.62  C        1.08        3.5      3.16       3.97*
Average Annual Return
as of 03/31/1998 ***
Yield FNote     YTD     1 year    5 year    10 year
5.49  A        1.93       7.48      5.34       6.18*
5.6  A        1.92       7.66      5.47       5.90*
5.65  A        2.02       7.64      5.49        7.31
5.65  A        1.99       8.24     6.18*          ---
5.98  A        2.14       8.04       5.9        7.75
6.1  A        2.18      3.27*        ---         ---
5.64  A        1.97     11.85       6.47       8.12*
5.76  A           2     12.07       6.59       7.52*
5.95  A        2.09     12.44      7.46*          ---
6.08  A        2.07     11.83       6.88        8.67
6.19  A        2.11     11.95      7.68*          ---
6.63  A        2.18     11.43       6.96           9
6.33  A        2.28     11.67      6.61*          ---
5.84  A         1.7     18.98       8.64       10.32
6.19  A        1.85       19.2     9.17*          ---
5.96  A        1.78     19.08       8.68       9.68*

Page 107
6.44   A      1.96        17.9      8.51       10.58
8.16   A      3.42        15.1      10.4       10.22
5.18   B      2.64       14.46      9.35       10.87
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
3.82  A       1.1        4.56      3.84        4.91
4.06  A      0.75        6.02      4.62        5.96
4.4  A      0.34         8.1      6.18        7.94
4.73  A      0.04       10.27      6.73        8.64
4.78  A      0.26       10.94      7.01        8.95
4.83  A      0.62       11.21      7.06         9.1
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
4.33  A       0.19       8.52     6.86*          ---
4.71  A      -0.01      10.92      6.91        8.55
4.65  A       0.18      10.39      6.91       7.74*
4.66  A       0.15       9.66       6.4        8.39
4.71  A       0.14      10.41      6.64        8.51
4.6  A       0.29      10.05      6.54       8.30*
4.67  A       0.41        9.9      6.59        8.52
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
N/A        11.31        38.8      18.2     16.60*
3.17  B      9.46       32.41     15.17     15.59*
3.16  B      9.61       30.36     15.69       14.18
2.45  B      8.25       25.61    16.53*          ---
5.11  B      4.62       26.52     12.71       13.25
3.52  B      8.97       32.24     17.12       14.78
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
5.17  B         5       19.01    13.97*          ---
4.19  B       7.4       24.19    16.57*          ---
3.23  B      9.86       30.34    19.55*          ---
2.27  B     12.34       35.86    22.39*          ---
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
2.89  B      9.56       27.18     12.13       12.64
2.56  B     10.14       41.34     19.74       16.44
1.34  B     15.06       47.84     22.25       18.73
1.37  B     14.52       47.42     20.95     20.18*
1.47  B     14.56      23.06*        ---         ---
1.62  B     12.84       42.33     21.06     22.08*
1.72  D        ---         ---       ---         ---
1.44  B      15.1       48.04     22.41     18.98*
1.48  B     15.12      22.27*        ---         ---
1.26  B     15.19       51.48     22.41        19.1

Page 108
0.53   B     10.34       34.73    18.58*          ---
N/A         -3.76       18.01    25.77*          ---
3.58   B      6.11       37.84     12.93     14.92*
1.3   B     15.09          48    29.69*          ---
0.7   B        13       44.75     19.99       15.49
1.26   B     14.84       33.05     19.91       16.46
2.04   B     14.22       46.96     22.06       18.54
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
N/A           7.2       16.25    12.70*          ---
N/A         14.19       19.91    15.72*          ---
N/A          5.51      -15.45     3.28*          ---
N/A         22.66       42.56     22.44     14.86*
N/A          0.91      -15.14     -1.06     - 1.86*
N/A          14.7       14.74     15.93        9.81
N/A         14.29       13.58     6.78*          ---
N/A         18.61       11.71     11.24        8.55
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
1.07  B     12.89       46.41     19.18       16.28
1.21  B     12.93      24.73*        ---         ---
N/A  E        ---         ---       ---         ---
N/A  E        ---         ---       ---         ---
0.92  B     17.18       53.02     23.25     21.90*
1.02  D        ---         ---       ---         ---
0.72  B     15.73       49.48     20.72       17.13
0.92  B     12.79       44.27     24.82       18.72
0.56  B      16.8       46.17    27.22*          ---
0.58  B     18.26        44.6     21.78       19.47
Average Annual Return
as of 03/31/1998 ***
Yield FNote   YTD      1 year    5 year    10 year
0.22  B      9.58       36.12     16.14       14.34
0.95  B     11.16        39.7    26.83*          ---
0.11  B        15       14.15     5.71*          ---
1.21  B      10.7       44.42     18.61       14.93
1.32  B     10.75      22.73*        ---         ---
N/A  E        ---         ---       ---         ---
N/A  E        ---         ---       ---         ---
N/A  E        ---         ---       ---         ---
N/A  E        ---         ---       ---         ---
1.15  B      5.92       25.61      15.7       14.76
N/A        21.26      -29.32     -1.29        -0.3
1.01  B     17.38       44.11      28.2       22.77

Page 109
Page 110
High       Low        Vol.
9128.89    8999.85     54.2M
42 7/8   42 5/16     1.04M
74 5/16           73    631K
####### 102 13/16       919K
57 1/8   56 3/16     3.87M
49 3/8   48 1/16     3.89M
59      57 1/2     958K
84 1/2     81 3/4    1.24M
77 3/4     76 7/8    2.21M
112 1/2 109 1/16      3.02M
80 15/16   79 13/16    2.02M

Page 111
71 1/16    69 15/16   891K
73 15/16     72 3/8   2.89M
83 1/4        81    3.85M
74 5/8     73 1/2   1.57M
70 1/8        69    353K
70 3/16      65 5/8   6.58M
125 3/4 123 7/16     3.53M
53 7/16    52 1/16    830K
71 7/8     70 1/8   1.31M
63 3/4   62 15/16   1.28M
119 7/8    116 5/8   2.37M
96 1/4     95 3/8   624K
####### 128 9/16      572K
35 5/8     34 3/4   8.10M
84 13/16   82 5/16    1.76M
64      62 5/8   1.80M
63 3/4     62 3/8   4.04M
54 9/16      53 1/2   473K
96 1/8     93 1/2   807K
54 11/16     53 3/4   2.42M

Page 112
Page 113
Page 114
INFO BOXES                                                    INDEX

This is an example of creating information or input boxes running a Function along
with a sub or macro. The function sets up a new function in Excel called Stock
Price which defines three other functions called GrossProfit, MyCommission, and
MyTax. When you run the StockSale function, it calculates the sub functions
before it calculates StockSale because the formula for StockSale needs these
other functions to complete its formula.
In addtion, when the Macro DisplayProfit is run, it calls up information or input
boxes for you to input information to calculate StockSale using the functiions
identified below: TO RUN THIS EXAMPLE, COPY ALL OF THE LINES
BELOW TO A MODEULE SHEET AND THEN RUN THE MACRO DISPLAYPROFIT.

Function StockSale(OriginalPrice, NumberOfShares, SalePrice,BrokerFee,GainsTax)
'GrossProfit is a variable contaiing the gross profit
GrossProfit = NumberOfShares * (SalePrice - OriginalPrice)
Commission = (NumberOfShares * SalePrice) * BrokerFee
Tax = GrossProfit * GainsTax
StockSale = GrossProfit - Commission - Tax
End Function

Sub DisplayProfit()
OriginalPrice = InputBox("What was the purchase price?")
NumberOfShares = InputBox("How many shares did you buy?")
SalePrice = InputBox("What was the sale price?")
BrokerFee = InputBox("What was the broker fee IN DECIMALS?")
GainsTax = InputBox("What was the capital gains tax IN DECIMALS?")
Profit = StockSale(OriginalPrice, NumberOfShares, SalePrice,BrokerFee,GainsTax)
MsgBox "Your profit is " & Profit
End Sub
3-D WORKSHEET FORMULA                                INDEX

All Divisions

Item               QTR1       QTR2        QTR3     QTR4         TOTAL
Bonnets           11,677      7,544      12,719   10,267     \$ 42,207
Funnels            9,457     21,275      15,932   27,643     \$ 74,307
Reels             17,738     25,609      19,543    7,577     \$ 70,467
Trays             20,944     34,103      17,276    7,454     \$ 79,777
TOTAL           \$ 59,816 \$   88,531 \$    65,470 \$ 52,941     \$ 266,758

3-D Worksheet Formulas
1. Calculate Total for Trays for all four divisions
2. Begin formula with =SUM(
3. Then point to European worksheet and select the
B7 cell where the total for trays for QTR1 is
4. Then Hold Shift Key Down while clicking on the
US Sheet or the last sheet in the Range
5. Then hit the Enter Key to complete the formula
6. Use the Fill Handle to apply formula to other three
quarters
3-D WORKSHEET FORMULA                                   INDEX

European Division

Item             QTR1     QTR2     QTR3     QTR4        TOTAL
Bonnets          5,678    3,333    8,765    1,234   \$   19,010
Funnels            347    4,444    5,689    5,643   \$   16,123
Reels            2,389    5,555    3,456    5,678   \$   17,078
Trays            4,367    6,666      765    1,811   \$   13,609
TOTAL         \$ 12,781 \$ 19,998 \$ 18,675 \$ 14,366   \$   65,820
3-D WORKSHEET FORMULA                                     INDEX

Australian Division

Item               QTR1     QTR2     QTR3     QTR4        TOTAL
Bonnets              976      234    2,121    2,233   \$    5,564
Funnels              567    5,622    3,443    7,689   \$   17,321
Reels              8,765    4,477    5,665      345   \$   19,252
Trays              4,567    8,833    7,634      678   \$   21,712
TOTAL           \$ 14,875 \$ 19,166 \$ 18,863 \$ 10,945   \$   63,849
3-D WORKSHEET FORMULA                                       INDEX

Item                QTR1     QTR2     QTR3      QTR4        TOTAL
Bonnets               456    1,733      711     2,233   \$    5,133
Funnels               678    5,643    3,456     5,546   \$   15,323
Reels                 906    7,789    7,654       765   \$   17,114
Trays               2,134    9,883    7,890       398       20,305
TOTAL         \$     4,174 \$ 25,048 \$ 19,711 \$   8,942   \$   57,875
3-D WORKSHEET FORMULA                                      INDEX

U.S. Division

Item               QTR1     QTR2      QTR3     QTR4        TOTAL
Bonnets            4,567    2,244     1,122    4,567   \$   12,500
Funnels            7,865    5,566     3,344    8,765   \$   25,540
Reels              5,678    7,788     2,768      789   \$   17,023
Trays              9,876    8,721       987    4,567       24,151
TOTAL           \$ 27,986 \$ 24,319 \$   8,221 \$ 18,688   \$   79,214

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 7 posted: 11/30/2011 language: English pages: 120