Spreadsheet Models for Managers
Glossary of Terms
This page contains brief definitions of terms we use in this course.
eside each entry is a number indicating the number of the session in which the term is first introduced. Click that number to go to the session page for that session. Some entries also have small blue squares to the left of the session number. These squares are linked to sections of the Readings that discuss the term in more detail.
You'll probably find this page useful not only as a place to look things up, but also as a place to find useful links.
absolute reference
1
A reference that has the property that no matter where you copy, move or fill it, it continues to point to the same range. See also
mixed reference and
relative reference.
The separation or decomposition of a whole into component parts. The partner of
synthesis.
The elements of a
compound range. For instance, A1:C2 is an area of the compound range (A1:C2,D5:G9).
array
2
A rectangular range.
array formula
2
A kind of formula in Excel in which a contiguous rectangular range of cells can share the formula, and produce a result together that is apportioned amongst the cells.
An array macro is a
function macro that returns a rectangular array of values.
arrival mechanism
12
In a
service system, this is how customers enter the service line.
assignment statement
13
In Visual Basic for Applications (
VBA, Excel's macro language) a statement that assigns a value to a variable.
One of the three basic financial statements, it reports assets and liabilities, which must always balance.
A chart form for a few discrete, usually unordered, categories.
Long-term, non-cancelable contract transferring most risks and rewards of ownership. It is often used for equipment, and title transfers to lessee at the end of the lease term. See also
operating lease.
Either a purchase or a sale, with impact beyond the current reporting period. Capital transactions tend to affect all three financial statements.
One of the three basic financial statements, it reports changes in the cash position of the organization, in the form of inflows and outflows.
The abscissa (horizontal axis) of a series.
checklist
7
In a
review or
inspection, the set of standards with which the work product must comply.
circular reference error
3
A condition that arises when a chain of cell dependencies closes on itself — where a cell's value depends, ultimately, on its own value. Excel begins to chase its own tail, then gives up.
command macro
13
A
macro that changes the state of Excel or its objects. Since it is equivalent to some sequence of menu commands, keystrokes and mouse gestures, it is recordable.
A compound range is a collection of ranges. For instance (A1:C2,D5:G7) is a compound range with two elements. The elements of a compound range are called
areas.
convolution
4
An operation performed on two data
streams. Convolving a stimulus with a system's
base response yields the response of the system to the stimulus.
Crowding is the opposite of
cushioning, in which you intentionally plan for insufficient resources. Usually it's bad business, but if you have to do it, it pays to know how to model it correctly.
Cushioning is the practice of including safety margins in your models to mitigate the effects of approximations and change. The opposite of
crowding.
data type
13
One of several possible kinds of data supported by
VBA, including Integer, Long, Double, String, Boolean, Object and Variant, and many others.
departure distribution
12
In a
service system, the statistical distribution of service times. We assume an exponential distribution.
A
VBA structure that provides the ability to declare the dimensions of an array at runtime.
dynamic model
1
A
model of a system whose behavior varies with time.
Economic Order Quantity
11
In inventory management, the optimum order quantity for constant demand. Assumes that the reorder point occurs at exactly the moment of inventory exhaustion.
A
model is said to be faithful when it exhibits behavior analogous to the system it models.
Said of a
model. A model has fidelity when it is
faithful — when it emulates reality with respect to characteristics of interest.
function macro
13
A
macro that returns a value. Function macros are not recordable.
function statement
13
The statement needed for defining a
VBA function.
The value in the future of an asset
stream.
global name
1
A global name is a name that has workbook-level scope. If referenced without a worksheet qualifier, the value returned is the value of the name on the sheet from which it is referenced, or if no name like that is defined on that sheet, the value returned is the value of the global name.
if statement
13
A
VBA statement that provides branching on a boolean expression
One of the three basic financial statements, it reports revenue, expenses, depreciation and other items needed to compute net income.
inspection
7
A detailed collaborative examination of a work product.
issue
7
In a
review or
inspection, an element of a work product identified as a deviation from standard.
iteration statement
14
A
VBA statement that provides looping control
Lease Characteristic Array
10
Summarizes the effect of a one-time lease event on the three financial statements. Each row of the array represents one of the financial effects of the lease, such as interest payments, principal payments, and so on. Use the LCA with
convolution, with a stream of lease events, to obtain the results of that stream.
A chart form for continuous or time-evolution data.
local name
1
A local name is a name that has worksheet-level scope. If referenced without a worksheet qualifier, the value returned is the value of the name on the sheet from which it is referenced.
A (usually small) program written in conjunction with a large application. In Excel, most macros are written in a language called Visual Basic for Applications (
VBA).
matrix
2
A rectangular array of data.
matrix product
2
A kind of product of two matrices that yields a third matrix. It is required that the number of columns of the first matrix equal the number of rows of the second. Order matters. In Excel, obtained by means of the
worksheet function MMULT as an
array formula.
A simulation of a system, such as a business or business process.
Modeling is the art of simulating a system, such as a business or business process.
moderator
7
In a
review or
inspection, the owner of the review/inspection process. The moderator recruits the team, chairs the meeting, and ensures that all issues discovered are addressed by the author after the review meeting adjourns.
A component of a workbook that holds a
VBA macro. Modules are not visible as sheet tabs. To see them, you must open the VBA editor, which you can access through the Visual Basic Editor on the Visual Basic toolbar. If you plan to do much macro work, make the Visual Basic toolbar visible.
named parameter
1
A range or value that has a user-defined name in Excel. Usually it is a
parameter of the
model.
A type of lease, often used for facilities, in which the lessor retains ownership. Payments under terms of an operating lease are treated as rent.
parameter
1
An element of a
model that represents one of the attributes of the system that is being modeled. It can be a single number, or a set of numbers.
A range of cells that holds the values of all model
parameters. This is a good practice. When you want to change a parameter, you know exactly where it is in the parameter block.
parameter sprinkling
1
A practice in which the value of a
model parameter appears multiple times in a model. This is a bad thing to do. When you want to change the value of that parameter, you have to chase around and find all the copies, changing each one.
A chart style suitable for a single series. This is the familiar circular form, in which the relative magnitude of each data point is represented as a pie slice of an appropriate angle.
Planned margin is the difference between what you actually need and what you plan to have. It can be positive (
cushioning), or negative (
crowding).
A statistical distribution used to model independent events. We use it to model customer arrivals in a
service system.
The value now of a
stream of assets received in the future.
In organizations, a way of characterizing the process in use as to its overall effectiveness. Usually, process patterns are organized into a hierarchy, sometimes called a 'maturity model.'
Quantization effects occur when a
model attribute is restricted to certain values or bands. For example, price might 'staircase' according to the volume ordered, or you might be able to manufacture only in lots of a specific size.
reader/reviewer
7
In a
review or
inspection, the people on the review team responsible for identifying issues.
reference
1
An element of a formula that denotes another cell on the worksheet. For instance, S12 denotes the cell in column S, row 12.
reference operator
4
An operator that takes references as operands, and produces a reference result. Excel's reference operators are space (for intersection), comma (for union) and colon (for range).
relative reference
1
A reference that has the property that when copied or filled, its new instances point to a range that bears the same relation to the cell that contains the reference, as the original did. See also
mixed reference and
absolute reference.
review
7
A collaborative examination of a work product, usually at a high level.
A running difference is a cell-by-cell subtraction of a range of cells. It is sometimes called a 'derivative' of the
stream it differences.
A running sum is a cell-by-cell cumulative sum of a range of cells. It is sometimes called an 'accumulation' or an 'integration' of the
stream it sums. To compute a running sum of a stream, you need a stream and an initial value.
A chart style best used for displaying correlations.
scope
1
The scope of a name definition is the realm in which it is valid. There are two possible scopes — local (sheet-level) and global (book-level).
scribe
7
In a
review or
inspection, the person responsible for recording and disseminating the information generated.
service system
12
A service facility containing servers, customer entry and exit facilities, and a waiting facility.
Sum-of-the-Years-Digits depreciation is depreciation method in which the amount of useful life consumed in year k is (N-k+1)/(N*(N+1)/2), where N is the useful life of the asset.
A chart form for time evolution of a pie chart.
static model
1
A
model of a system whose behavior does not vary with time. All parameters are constant. Alternatively, a static model can be a model of a time-varying system at a single moment.
straight-line depreciation
8
A depreciation method in which an equal amount of the assets cost is considered to have been consumed in each year of the asset's useful life.
A range of cells, usually a portion of a single row. We call it a 'stream' because we think of it as representing a time series of a model attribute. For example, it could represent a revenue stream.
The composition or combination of parts or elements into a whole. The partner of
analysis.
The behavior of a
model's attributes with time, or actually, simulated time.
temporal response
4
How a system responds, over time, to a stimulus.
In Visual Basic, a name that can hold
VBA data.
variable declaration
13
A
VBA statement (beginning with Dim) that declares that a given
variable has a given
data type.
Visual Basic for Applications. Excel's
macro language, used for many Microsoft products.
An ordered collection of items with 1 or more dimensions of index.
In a
service system, the facility for storing customers. It might not actually be a physical line. For example, in the doctor's office, we wait in chairs, and the chairs are in no particular order. Indeed, sometimes we are even called into an inner examination room, where the wait resumes. So, waiting lines need not be lines, and need not even be physically co-located. Perhaps the best example of this is the electronic waiting line our computer files pass through when we send them to the printer.
worksheet function
1
A worksheet function is similar to a mathematical function. It accepts arguments, and returns a value or values. You can use a variety of worksheet functions in cell formulas.
For more about my spreadsheet consulting
and training services, visit SpreadsheetAce.com.
Last Modified: Tuesday, 19-Aug-2008 20:57:48 EDT
f you use Excel to model businesses, business processes, or
business transactions, this course will change your life. You'll learn how to create tools for yourself that will amaze
even you. Unrestricted use of this material is available in two ways.
