Outline

Introduction to spreadsheets

Introduction to MS Excel

Formatting 

Creating  formula 

Functions

CAT I (Take away)

Functions II

Functions III

Functions IV

Cat II 

Charts 

Data Analysis

Topic 1: Spreadsheets

Definition

Spreadsheet: application package designed to store, organize and manipulate numerical data and charts.

• Also called electronic ledger.
• Examples: MS Excel, Lotus 1-2-3

Uses of Spreadsheets

Accounting 

• Prepare budgets
• Calculate profits

Statistical analysis

• Calculating statistical values e.g. mean, median etc.

Data management 

• Organizes data in tabular manner
• Operations include sorting, filtering etc.

Tracking value of assets 

• Calculating appreciation and depreciation.

Forecasting

• Its automatic recalculation feature enables ‘what-if’ analysis

Topic 2: Introduction to MS Excel

• A product of Microsoft Corporation.
• Versions: Excel 97, 2000, XP, 2003, 2007, 2010, 2013.

Excel Terminology

1. Cell – intersection of a row and a column.
2. Cell Address - consists of column letter and row number.
3. Range – a group of cells
4. Active cell – highlighted by thick borders.

• location for typing into a worksheet.

       5.Worksheet- MS Excel working area.

• Consists of rows and columns
• Contains 1,048,576 horizontal rows (numbered 1 through 1048576) and 16,384 vertical columns from column A to column XFD.

       6.Workbook – refers to an MS Excel spreadsheet file. 

• By default, consists of 3 worksheets.

The Excel Cell Referencing System

• Each cell has a unique address.
• An address consists of column letter and row number.
• There are two cell referencing styles, the: 

1. A1 style - An address consists of column letter and row number e.g. B3, D2 etc.
2. R1C1 style – both the row and the column are numbered e.g. R2C4 etc.

The Excel window

Continued

Excel Window

• The Quick Access Toolbar (QAT) - provides a faster access to commands. 
• By default, the Quick Access Toolbar contains the Save, Undo, and Redo command buttons in addition to the Customize Quick Access Toolbar button.
• You can add or remove command buttons on the Quick Access Toolbar to help you quickly perform common tasks.

1. Title bar – contains the name of the workbook and  the name of the software
2. Microsoft Office Excel Help button – used to access Excel Help.
3. Minimize Window button – minimizes the current workbook to a title bar icon inside the Excel window.
4. Restore Window button – restores the active workbook to a smaller window inside the Excel window.
5. Close Window button – closes the active workbook.

1. Scroll bars – vertical and horizontal scroll bars used to change the vertical or horizontal view of worksheet areas
2. Status bar – a customizable bar below the worksheet area that is used to display various messages, the View Toolbar, the Zoom button and the Zoom Slider.
3. View Shortcuts – contains view command buttons used to change the view of the active worksheet
4. Zoom button – a command button used to change the magnification or “zoom” of the worksheet view
5. Zoom Slider – a slide control used to change the magnification of “zoom” of the worksheet view

• The Ribbon contains a series of grouped command buttons organized around specific tasks. 
• There are 3 parts to the ribbon – tabs, groups and commands.

1. Tab – designed to be task oriented.
2. Tab group – divides a task into subtasks
3. Command – carries out a command operation or displays a command menu.

1. The Name Box- shows the cell reference of the active cell in the worksheet.
2. The Formula Bar- shows the contents of the active cell. 

1. Column Headers - are the letters only at the top of the worksheet.
2. Row Headers - are the numbers on the left side of each row of the worksheet.
3. Active Cell - is the currently selected cell, displayed with a thick black border around the cell.
4. Status bar-provides a message area, for example the Caps Lock or Num Lock “on” warning message, in addition to displaying the View Shortcuts, the Zoom command button, and the Zoom Slider.
5. Sheet Tabs –

• Identify the current worksheet.
• Used to navigate from one worksheet to another.

Navigating in a Worksheet

• You can use the mouse pointer to navigate from one cell to another, thereby making the destination cell the active cell. 

Keyboard shortcuts for navigating in a worksheet

Excel Data Types

Labels

Any text and alphanumeric data

Used to make worksheet data more readable.

Text 

Any text and alphanumeric data

Cannot be manipulated mathematically

Numbers

Numerical values that can be manipulated mathematically

Date

Calendar values

Time

Time values

Formula

User defined mathematical expression

Function 

Excel inbuilt mathematical/text operations

Excel Continued

Entering Text and Numbers

• Make the appropriate cell the active cell and type in data e.g. B2 below and type the word Region
• By default, text is aligned to the left while numbers to the right.

Entering numbers with fractions

• To enter a fractional value into a cell, leave a space between the whole number and the fraction. 
• For example, to enter 67⁄8: enter 6, press spacebar then 7/8 and then press Enter. 
• If you have a fraction only e.g. 1⁄8: you must enter a zero first, like this: 0   1/8 
• Otherwise, Excel will likely assume that you’re entering a date.
• N/B: Excel automatically simplifies fractions e.g 4/8 = 1/2

Using AutoFill to enter a series of values

• AutoFill- feature that inserts a series of values or text items in a range of cells.

Topic 3: Formatting Numbers

• Values that you enter into cells normally are unformatted (General format).
• You format the numbers so that they’re easier to read or are more consistent in terms of the number of decimal places shown.

1. General

• The General format is Excel's standard number format;.
• Every cell starts out with the same number format: General. 
• This format comes with a couple of basic rules:

1. If a number has any decimal places, Excel displays them, provided they fit in the column. If the number's got more decimal places than Excel can display, it leaves out the ones that don't fit. i.e.  It rounds up the last displayed digit, when appropriate.
2. Excel removes leading and trailing zeros. Thus, 004.00 becomes 4. 

2. Number

• The Number format is like the General format but with three refinements:

1. First, it uses a fixed number of decimal places (which you set). 
2.  It also allows you to use commas as a separator between groups of three digits.
3. Can display negative numbers displayed with the negative sign, in parentheses, or in red lettering.

3. Currency

• The Currency format displays the currency symbol before the number. 
• Always includes commas. 
• Also supports a fixed number of decimal places (chosen by you), and it allows you to customize how negative numbers are displayed.

4. Accounting

• The Accounting format is modeled on the Currency format. 
• It also allows you to choose a currency symbol, uses commas, and has a fixed number of decimal places. 
• The currency symbol's always at the far left of the cell (away from the number), and there's always an extra space that pads the right side of the cell. 
• Also, the Accounting format always shows negative numbers in parentheses, which is an accounting standard. 
• The number 0 is never shown when using the Accounting format. Instead, a dash (-) is displayed in its place. 

5. Percentage

• The Percentage format displays fractional numbers as percentages. For example, if you enter 0.5, that translates to 50%. 
• You can choose the number of decimal places to display.

6. Fraction

• The Fraction format displays your number as a fraction instead of a number with decimal places. 
• The Fraction format doesn't mean you have to enter the number as a fraction

7. Scientific

• Scientific notation displays the first non-zero digit of a number, followed by a fixed number of digits, and then indicates what power of 10 that number needs to be multiplied by to generate the original number. For example, 0.0003 becomes 3.00 x 10-4 (displayed in Excel as 3.00E-04). 
• The number 300, on the other hand, becomes 3.00 x 102 (displayed in Excel as 3.00E02). 

8. Text

• The Text format simply displays a number as though it were text, although you can still perform calculations with it.
•  Excel positions it against the left edge of the column. 

Steps for Formatting a Number

1. Select the cell or range of cells
2. Right-click the selection
3. Choose Format Cells option from the context menu. 
4. Choose a category
5. Choose a format similar to the one you desire

Topic 4: Creating Formula

• A formula is a mathematical expression that returns a value
• A formula is written using operators that combine different values, returning a single value that is then displayed in the cell.
• The most commonly used operators are arithmetic operators.

• Excel reads a formula containing these operators from left to right and performs the calculations following these strict rules of precedence:

1. Parenthetical calculations first

2. Division or multiplication next in the order in which the calculations appear from left to right

3. Addition or subtraction next in the order in which the calculations appear from left to right

• For example, consider the following formula:

=5 + 2 * 2 ^ 3 - 1
To arrive at the answer of 20, Excel first performs the exponentiation (2 to the power of 3):

=5 + 2 * 8 - 1
and then the multiplication:

=5 + 16 - 1
and then the addition and subtraction:

=20

• To control this order, you can add parentheses.
• 5 + (2 * (2 ^ 3)) - 1 = 20
• 5 + 2 * 2 ^ (3 - 1) = 13
•  (5 + 2) * 2 ^ 3 - 1 = 55 
• (5 + 2) * 2 ^ (3 - 1) = 28

Creating a Simple Formula

• A formula is expressed by reference to cell addresses e.g. = A1+ A2
• Must be preceded by an equal (=) sign.

Copying a Formula

• Drag the Autofill handle through the cells that the formula is to be copied.
• Alternatively

1. Right click on the cell that contains the formula
2. Make cell where the formula is to be copied the active cell
3. Right click on the active cell (cell where the formula is to be copied)
4. Select Paste from the Shortcut Menu

Types of Cell References

• There are three types of references:

Relative: The row and column references can change when you copy the formula to another cell. 

Is the default cell reference mode.

Example: A2

Absolute: The row and column references do not change when you copy the formula.

Necessary when there is a constant in a formula.

Both the column letter and row number are preceded by the dollar ($) sign

Example: $A$2

Mixed: Either the row or column reference is relative, and the other is absolute.

Example: A$2, $A2

Common Formula Errors

1.    #VALUE!

• You used the wrong type of data. E.g. you might have used a function or created a simple arithmetic formula with a cell that contains text instead of numbers.

2.    #NAME?

• Excel can't find the name of the function you used. 
• This error code usually means you misspelled a function's name.

1. NUM!

• This error code appears when a calculation produces a number that's too large or too small for Excel to deal with.

4.    #DIV/0

• You tried to divide by zero. 
• This error code also appears if you try to divide by a cell that's blank, because Excel treats a blank cell as though it contains the number 0 for the purpose of simple calculations with the arithmetic operators.

5.    #REF!

• Your cell reference is invalid. 
• This error most often occurs if you delete or paste over the cells you were using.

6.    #N/A

• The value isn't available. 
• This error can occur if you try to perform certain types of lookup or statistical functions that work with cell ranges. 

7.    #NULL!

• You used the intersection operator incorrectly. 
• The intersection operator finds cells that two ranges share in common. This error results if there are no cells in common. 

1. ########

•  Excel has successfully calculated your formula. However, the formula can't be displayed in the cell using the current number format. 
• To solve this problem, you can widen the column, or possibly change the number format.

Topic 5: Functions

• Inbuilt MS Excel text or mathematical operation that returns a value given a range of values (arguments). 
• Identified by its name e.g. SUM (), PRODUCT (), etc.

Function Categories

• Functions are grouped into broad categories by some common features among them. 

1. Financial 

• Analyze investments including appreciation, depreciation, compound interest etc.

1. Date & Time

• Manipulates date and time values.

1. Math & Trig

• Includes general math and trigonometric functions.

1. Statistical 

• Performs calculations on list of values.

1. Lookup & reference

• A lookup formula essentially returns a value from a table by looking up another related value.

1. Database 

• Performs statistical calculations and queries on databases. 

1. Logical 

• Capable of making a decision based on the outcome of a Boolean expression.

1. Information  

• Returns information about a cell e.g. the formatting features applied to the cell.

1. Engineering

• Includes common engineering calculations

1. Text

• Manipulates text data 

General Mathematical functions

1. SUM() 
2. PRODUCT()
3. EXP()
4. POWER()
5. ROUND()
6. SQRT()

SUM ( )

• Adds up a group of cells.
• Format: =SUM(range)
• Range can be specified in 2 ways:

1. =SUM(A1,A2) -adds two cells.
2. =SUM(A2:A12)- adds the range of 11 cells from A2 to A12.

PRODUCT ()

• The PRODUCT( ) function takes a list of numbers, multiplies them together, and gives the result. 

1. =PRODUCT(A1,A2,A3)
2. =PRODUCT(A1:A10)

• Takes a range of values as its argument

Rounding Numbers

1.    ROUND( )

• Rounds a numeric value to a specified number of significant figures e.g. decimal places. 
• Format: ROUND(value,d.p.)   
• For example
•     =ROUND(3.987, 2) The result is 3.99.
• If you specify 0 for the number of d.p., then Excel rounds to the nearest whole number. 

2.        ROUNDDOWN( )

• Rounds numbers down, towards zero.
• the result of ROUNDDOWN(1.9, 0) is 1, 

3.        ROUNDUP( ): 

• Rounds numbers up, away from zero.
• the result of ROUNDUP(1.1, 0) is 2 

POWER()

• POWER( ) works out exponents.
•  =POWER(2,3) => 8
• Takes two arguments, the base and the index. 

SQRT()

• SQRT() finds the square root of a number.
• =SQRT(9)=->3
• Takes a single argument 

Statistical Functions

1. COUNT()
2. MAX()
3. MIN()
4. LARGE()
5. SMALL()
6. RANK()
7. AVERAGE()
8. MEDIAN()
9. MODE()

Counting Values

COUNT ()

Returns the number of cells that contain a numeric value or date value.

Format: = COUNT(Range)

Example: =COUNT(A1:A10)

Function ignores blank cells and cells with text data.

COUNTA ()

Returns the number of cells with any data type.

Used to determine the number of nonblank cells.

Format: = COUNT(Range)

Example: =COUNTA(A1:A10)

Function ignores blank cells

COUNTBLANK ()

Returns the number of blank cells

Format: = COUNT(Range)

Example: =COUNTBLANK(A1:A10)

Maximum and Minimum Values 

The MAX( ) 

Pick the largest value out of a series of cells.

Format: =Max(range)

Example: =Max(A1:A10)

MIN( ) 

Returns smallest value out of a series of cells.

Format: =Min(range)

Example: =Min(A1:A10)

Note:

The MAX( ) and MIN( ) functions ignore any non-numeric content, which includes text, empty cells, and Boolean (true or false) values.

Excel includes dates in MAX( ) and MIN( ) calculations because it stores them internally as the number of days that have passed since a particular date.

Ranking Your Numbers

LARGE( )

Returns the k-th largest value in a list e.g. the 5th largest value in a list.

Example: =Large(A1:A10,5)

SMALL()

Returns the k-th smallest value in a list e.g. the 5th smallest value in a list.

Example: =SMALL(A1:A10,5)

Takes two arguments: range and position in the list (range).

RANK( ) 

Function finds where a specific value falls in the list.

Format : =RANK(number, range, [order_type])

Example :

=RANK(A1,A1:A10) – ascending order

=RANK(A1,A1:A10,1) – descending order

Measuring Central Tendency 

AVERAGE( )

Finds the mean of a list of values

Format: =AVERAGE(A1:A10)

Takes one argument: the range of values.

Function ignores all empty cells or text values.

MEDIAN( )

Finds the median of a list of values.

If the list is ordered in ascending order, the median is the value that lies in the middle position.

Format: =MEDIAN(A2:A12)

MODE( )

Returns the value that appears the highest number of times in a list (range) of values.

Format: =MODE(A1:A10)

Takes one argument: the range.

It ignores text values and empty cells 

Text Functions 

CONCATENATE( ) function 

• The CONCATENATE( ) function lets you join together text in exactly the same way the & operator does.

LEN( ) Function

• LEN( ) (short for LENgth) counts the number of characters in a string of text. For example, the result of the following formula is 5:

    =LEN("Hello")

Manipulating Text

The concatenation operator (&)

Joins text together.

DATE/TIME Functions

TODAY() Function

The function displays the current date in a cell:

=TODAY() 

DAY()Function

Function takes a date argument and returns a number representing the day (1 to 31).

MONTH()

Function takes a date argument and returns a number representing the month (1 to 12).

YEAR()

Function takes a date argument and returns a number representing the year (1900 to 9999). 

Example, if you place the date 1/1/2007 in cell A1, the following formula displays a result of 2007:

    =YEAR(A1)

The NETWORKDAYS() Function

The NETWORKDAYS() function calculates the number of work days between two dates, excluding weekend days (Saturdays and Sundays). 

As an option, you can specify a range of cells that contain the dates of holidays, which are also excluded. 

Format:

=NETWORKDAYS(StartDate,EndDate,[HolidayRange])

Example:

=NETWORKDAYS(A15,16,B2:B11)

WORKDAY() function

The WORKDAY() function gives an offset work day. 

Example: function to determine the date that is ten working days from January 4, 2008:

=WORKDAY(1/4/2008,10)

The WEEKDAY() function 

The WEEKDAY() function accepts a date argument and returns an integer between 1 and 7 that corresponds to the day of the week. 

Sunday -1,Monday-2…………Saturday-7

=WEEKDAY(11/1/2011)

Manipulating Dates and Times

Date and Time values can be involved in calculations like addition, subtraction, and so on. For example, consider this formula:

    =A2-A1+1

If A2 contains the value 10/30/2007, and A1 contains the value 3/20/2007, the result is 224, which is the number of days between these two dates.

Lookup Functions

HLOOKUP() 

is the horizontal lookup function. 

HLOOKUP( ) works by scanning the values in a single row from left to right. Once it finds the entry you're looking for, it can then retrieve other information from the same column.

Format:

=HLOOKUP(SearchValue, TableRange, rowNumber, [rangeLookup])

Example:

VLOOKUP() 

Is the vertical lookup function.

 VLOOKUP( ) works by scanning the values in a single column from top to bottom. 

Once it finds the entry you're looking for, it can then retrieve other information from the same row.

Format:

VLOOKUP(search_for  value, table_range, column_number) 

Financial Functions

Financial Concepts

1. Present Value (PV). - The value of an investment or loan at the very beginning of its life. This number's also called the principal.
2. Rate - The rate at which an investment or loan will increase or decrease. 

1. Future Value (FV) - The value of an investment or loan at some point in the future.
2. Payment -An amount of money that's being contributed to an investment or loan. It's a regular contribution that's usually made at the same time the interest is calculated.
3. Number of Payment Periods (NPER)-The total number of payment periods between the present value and the future value of an investment or loan. 
4. If you've got a three-year car loan (with payments due monthly), then the NPER equals 36. In other words, there are 12 payment periods each year, for three years.

FV( ): Future Value Function

The FV( ) function lets you calculate the future value of an investment, assuming a fixed interest rate.

 It lets you factor in regular payments, which makes it perfect for calculating how money's accumulating in a retirement or savings account.

Format:  

=FV(rate, nper, payment, [pv], [type]) 

Rate -the interest rate your investment's earning.

nper -the number of interest payments. 

If your account receives interest once a year and you invest your money over a two-year period, then the nper will be 2. 

If you're making regular contributions, this value also specifies the number of contributions you're making. FV( ) assumes that every contribution's made on the same day as the interest's generated.

3. payment -the amount of the contribution you want to make regularly. 

Set this to 0 if you don't want to add anything.

4. pv -the present value, or the initial balance of your account. 

If you omit this value, then Excel assumes you start with nothing. As a result, you'll need to include something other than 0 for the payment.

5. type - indicates the timing of the payment. If you specify 0 (or omit this value), then the payment's made at the end of the period. 

If you specify 1, then the payment's made at the beginning, giving your interest just a little bit more time to compound.

N/B:- make sure both the payment and the initial balance (pv) are negative numbers (or zero values).  In Excel's thinking, the initial balance and the regular contributions are money you're handing over, so these numbers, consequently, need to be negative. The final value is positive because that's the total you're getting back.

the formula that calculates the return on a $10,000 investment after one year earning 5 percent annual interest:

=FV(5%, 1, 0, -10000,0)

what happens if you switch to an account that pays monthly interest?

 You now have 12 interest payment periods per year, and each one pays a twelfth of the total 5 percent interest

=FV(5%/12, 12, 0, -10000)
The new total earned is a slightly improved $10,511.62.

Loan Repayment

FV( ) works just as well on loan payments.

Example -you take out a $10,000 loan and decide to repay $200 monthly. Interest is set at 7 percent and calculated monthly.  

FV() can tells you your outstanding balance that is, the amount that you still owe,  after three years as follows:

=FV(7%/12, 3*12, -200, 10000)

PV( ): Present Value Function

The PV( ) function calculates the initial value of an investment or a loan (which is also called the present value). 

Format:

=PV(rate, nper, payment, [fv], [type]) 

the real purpose of PV( ) is to answer hypothetical questions.

Consider this formula:

=PV(10%/12, 25*12, 0, 1000000)
The question Excel answers here is: In order to end up with $1,000,000, how much money do I need to invest initially, assuming a 10 percent annual interest rate (compounded monthly) and a maturation period of 25 years? The PV( ) function returns a modest result of $82,939.75.

You can supplement your principal with a regular investment. The following formula assumes a monthly payment of $200, paid at the beginning of each month. Note that you should type in a negative number, because it's money you're giving up:

=PV(10%/12, 25*12, -200, 1000000) 

PMT( ) Function

The PMT( ) function calculates the amount of the regular payments you need to make, either to pay off a loan or to achieve a desired investment target. 

You specify the present value and future value of the investment and the rate of interest over its lifetime, and the function returns the payment you'd need to make in each time period. Here's how the function breaks down:

Format:

=PMT(rate, nper, pv, [fv], [type])

If you don't specify a future value, then Excel assumes it's 0 (which is correct if you're performing the calculation to see how long it'll take to pay off a loan). 

Once again, the type argument indicates whether you make payments at the beginning of the payment period (1) or at the end (0).

Example :- If you have a 7 percent interest rate (compounded monthly) and a starting balance of $10,000, how much do you need to pay monthly to top it up to $1,000,000 in 30 years? The PMT( ) function provides your answer:

=PMT(7%/12, 12*30, -10000, 1000000)
The result$753.16is a negative number because this is money that you're giving up each month.

A loan calculation is just as easy, although, in this case, the present value becomes positive, since it represents money you received when you took out the loan. To determine the payments needed to pay back a $10,000 loan (that comes with a 10 percent annual interest rate) over five years, you need this formula:

=PMT(10%/12, 12*5, 10000, 0)
Assuming you make payments at the end of each month, the monthly payment is $212.47. If you add a type argument of 1 to pay at the beginning of the month, then this amount decreases to $210.71.

NPER( ): Number of payment Periods Function

The NPER( ) function calculates the amount of time it will take you to pay off a loan or meet an investment target, provided you already know the initial balance, the interest rate, and the amount you're prepared to contribute for each payment. 

Format : 

=NPER(rate, pmt, pv, [fv], [type])

Example : If you're ready to contribute $150 a month into a savings account that pays 3.5 percent interest, you can use the following formula to determine how long it'll take to afford a new $4500 plasma television, assuming you start off with an initial balance of $500:

=NPER(3.5%/12, -150, -500, 4500)
The answer is 25.48 payment periods. Remember, a payment period in this example is one month, so you need to save for over two years.

A similar calculation can tell you how long it'll take to pay off a line of credit. Assuming the line of credit's $10,000 at 6 percent, and you pay $500 monthly, here's the formula you would use:

=NPER(6%/12, -500, 10000, 0)
In this case, the news isn't so good: It'll take 21 months before you're rid of your debt.

Charts 

Definition 

A chart is a visual (graphical) representation of numeric values.

Chart elements

Inserting a chart 

1 Click a cell in the data list you want to summarize.

2 Click the Insert tab.

3 Click the type of chart you want to create.

4 Click the chart subtype you want to use. 

Adding chart elements

Show or Hide a Chart Legend

1 Click the chart you want to format.

2 Click the Layout tab.

3 Click Legend.

4 Click a legend display option.

Add Titles

1 Click the chart you want to format.

2 Click the Layout tab.

3 Click Chart Title.

4 Click the title display option you want.

5 Click the title, and then type the new title for the chart. 

Add and Remove Data Labels

1 Click the chart you want to format.

2 Click the Layout tab.

3 Click Data Labels.

4 Click a data label option.

Show or Hide Chart Gridlines

1 Click the chart you want to format.

2 Click the Layout tab.

3 Click Gridlines.

4 Click the set of gridlines you want to change.

5 Click a gridline display option. 

Add a New Series

1 Click the chart you want to change.

2 Click the Design tab.

3 Click Select Data.

4 Click Add.

5 Type the name you want to assign to the series.

6 Click in the Series Values field.

7 Select the cells you want to add.

8 Click OK.

9 Click OK.

What-If Analysis

• One of the most appealing aspects of Excel is its ability to create dynamic models. 
• A dynamic model uses formulas that instantly recalculate when you change values in cells that are used by the formulas. 
• When you change values in cells in a systematic manner and observe the effects on specific formula cells, you’re performing a type of what-if analysis.

Types of What-If Analyses

1. Manual what-if analysis: Plug in new values and observe the effects on formula cells.
2. Data tables: Create a special type of table that displays the results of selected formula cell as you systematically change one or two input cells.
3. Scenario Manager: Create named scenarios and generate reports that use outlines or pivot tables.

Manual what-if analysis

• Manual what-if analysis is based on the idea that you have one or more input cells that affect one or more key formula cells. You change the value in the input cells and see what happens to the formula cells.