Info project 4

all instructions provided below and example

INFO 1010 Project 4

Due Friday, March 16 at 5:00 pm

HEAVENLY SPICES SPICE SHOP

Heavenly Spices makes a variety of Spice Mixes with a variety of ingredients. They maintain a supply of various ingredients, some of which are costlier than others. Each day, Heavenly Spices mixes the appropriate ingredients for the varieties of spice mixes they wish to make and packages the spice mixes in their factory.

Some of their best-selling Spice Mixes are as shown in Table 1, along with the chief ingredients for each spice mixes and the amount of each ingredient.

Spice Mix Name	Ingredients (in grams) per spice mix box
Good Spice	5 gr Basil (BL) 17 gr Parsley (PA) 7 gr Thyme (TH) 8 gr Garlic Powder (GP) 4 gr Sage (SG) 2 gr Cloves (CL)
Market Pantry	18 gr Oregano (OR) 10 gr Basil (BL) 8 gr Sage (SG) 12 gr Thyme (TH)
Clear Your Sinuses	5 gr Black Pepper (BP) 10 gr White Pepper (WP) 18 gr Oregano (OR) 3 gr Parsley (PA) 1 6 gr Sea Salt (SS) 12 gr Marjoram Leaves (ML)
Sweetness	12 gr Cumin (CU) 15 gr Oregano (OR) 6 gr Cloves (CL) 8 gr Basil (BL) 3 gr Thyme (TH)
Palate’s Delight	6 gr Black Pepper (BP) 12 gr White Pepper (WP) 6 gr Sage (SG) 6 gr Sea Salt (SS) 15 gr Marjoram Leaves (ML) 3 gr Chili Powder (CP)
Head Rush	10 gr Black Pepper (BP) 15 gr White Pepper (WP) 7 gr Sea Salt (SS) 4 gr Parsley (PA) 8 gr Red Pepper (RP) 5 gr Ginger (GI) 6 gr Sage (SG)

Table 1: Spice Mixes

Each Spice Mix is packaged in a box that retails for $9.95 each. While price is the same no matter the variety, the cost of ingredients varies for each Spice Mix based on the type and amounts of ingredients in each. Table 2 shows the per-gram cost of each ingredient, and how much of each ingredient is in stock and available for mixing:

$0.15

$0.12

$0.09

$0.18

$0.09

$0.21

$0.15

Ingredient Name	Cost per gram in $	Availability in grams
Black Pepper (BP)		$0.12	9600
Basil (BL)		$0.18	13200
Parsley (PA)			$0.15	12500
Thyme (TH)		$0.21	9700
Garlic Powder (GP)	6700
Sage (SG)			$0.09	9500
White Pepper (WP)	12800
Oregano (OR)	$0.06	18400
Sea Salt (SS)	5400
Cumin (CU)	$0.24	2700
Marjoram Leaves (ML)	$0.27	3500
Cloves (CL)	5800
Chili Powder (CP)	3000
Red Pepper (RP)	6000
Ginger (GI)	2000

Table 2: Herbal Ingredients

1.

For inventory purposes, Heavenly Spices must produce at least 125 boxes of each variety of Spice Mix. This holds for both Parts 1 and 2.

Project Part 1:

A. Set up a Linear Programming model to maximize profit. Identify the objective function and the formulas for all constraints.

B. Run the model, and provide a copy of your answer report.

C. In a summary, identify how many boxes of each spice mixes will be produced (does it make sense to make .65 of a box?) and how much of each ingredient will be left over in inventory.

D. For each type of spice mix, identify the limiting factor(s) which prevented Heavenly Spices from making more of that spice mix.

Project Part 2:

A. With the exception of Market Pantry, Heavenly Spices is able to sell no more than 400 boxes of each spice mixes. Heavenly Spices is able to sell no more than 500 boxes of Market Pantry. Re-run your model and provide a copy of your answer report.

· Identify how many boxes of each spice mix will be produced and how much of each ingredient will be left over in inventory.

B. Continuing with demand constraints above, maintenance problems have
limited today’s production to no more than 1600 units
total. Re-run your model and provide a copy of your answer report.

· Identify how many boxes of each spice mix will be produced and how much of each ingredient will be left over in inventory.

Assignment Deliverables:

Create a spreadsheet that helps to answer these questions. Create a tab for each Project Part and produce an answer report within each tab. Clearly answer each question that is part of the project. Most importantly, create a dashboard with instructions on how to use the optimization you have created. It should have instructions on what things you could change (Recipes? Costs? Inventories? Other things?). It should have instructions on how to maximize profits. It should be well organized, well labeled, easy to use, and easy to understand.

3 of 3

Optimization Rubric

Dashboard

DO NOT JUST COPY THE EXAMPLE DASHBOARD!!!

Unacceptable – 10 points	Acceptable – 20 points	Excellent – 30 points
· No dashboard · Dashboard information is inaccurate · Dashboard is basically a copy of the example.			· Missing a few of these things, or these are not clearly described, or these are inaccurate, but overall acceptable	· Clear instructions on how to use the optimization created. · Instructions on what things the Manager could change · Instructions on how you created it · Well organized, well labeled, easy to use, and easy to understand · Highlights important aspects · Visually appealing

Part 1

· Missing a few of these things, or these are not clearly described, or these are inaccurate, but overall acceptable

	Unacceptable – 20 points		Acceptable – 30 points		Excellent – 45 points (5 points each)
· No part 1 · Information is inaccurate	· Correct objective function · Correct cost/profit calculations · Correct constraints · Correct boxes made · Identifies how many boxes of each tea will be produced · Identifies how much of each ingredient will be left over in inventory · Identifies the limiting factor(s) · Gives suggestions to manager · Worksheet has proper validation constraints

Part 2

Unacceptable – 20 points

Acceptable – 30 points

Excellent – 45 points
(5 points each)

· Missing a few of these things, or these are not clearly described, or these are inaccurate, but overall acceptable

· No part 2 · Information is inaccurate

· Correct objective function · Correct cost/profit calculations · Correct constraints · Correct boxes made · Identifies how many boxes of each tea will be produced · Identifies how much of each ingredient will be left over in inventory · Identifies the limiting factor(s) · Gives suggestions to manager · Worksheet has proper validation constraints

Writing

– 5 points for 1-2 typos or grammar problems

– 15 points for major editing concerns (i.e. it is hard to read past the errors)

This is for example only! Do NOT just copy this for your final project!

This is for exam
ple only! Do NOT just copy this for your final project!

This is for example only! Do NOT just copy this for your final project!

INFO 1010

DATA MANAGEMENT AND ANALYSIS

‹#›

1

General Stat Concepts
Statistics
Descriptive Statistics
Statistical Inferences
Sample vs. Population
Problem:
Data Deluge
Data Quality
1st Step
Determine Scales of Measurement of Variables

‹#›
Scales Of Measurement
Scales determine the amount of information in the data
Nominal
Ordinal
Interval
Ratio

‹#›
Quantitative vs Categorical
Categorical: Labels or names used to identify an attribute of each element. Can be numeric or nonnumeric
Quantitative: Indicates how much or how many. Always numeric. Can be discrete or continuous.
The statistical analysis that is appropriate depends on whether the data for the variable are categorical or quantitative.
In general, there are more alternatives for statistical analysis when the data are quantitative.

‹#›
Scales of Measurement
Categorical
Quantitative
Numeric
Numeric
Non-numeric
Data
Nominal
Ordinal
Nominal
Ordinal
Interval
Ratio

‹#›

Statistical Inference
Population
Sample
Statistical inference
Census
Sample survey
– the set of all elements of interest in a
particular study
– a subset of the population
– the process of using data obtained
from a sample to make estimates
and test hypotheses about the
characteristics of a population
– collecting data for the entire population
– collecting data for a sample

‹#›

Process of Statistical Inference
1. Population
consists of all tune-
ups. Average cost of
parts is unknown.
2. A sample of 50
engine tune-ups
is examined.
The sample data
provide a sample
average parts cost
of $79 per tune-up.
4. The sample average
is used to estimate the
population average.

‹#›

Ethical Guidelines for Statistical Practice
In a statistical study, unethical behavior can take a
variety of forms including:
Improper sampling
Inappropriate analysis of the data
Development of misleading graphs
Use of inappropriate summary statistics
Biased interpretation of the statistical results
You should strive to be fair, thorough, objective, and
neutral as you collect, analyze, and present data.
As a consumer of statistics, you should also be aware
of the possibility of unethical behavior by others.

‹#›
Ethical Guidelines for Statistical Practice
The American Statistical Association developed the
report “Ethical Guidelines for Statistical Practice”.
Professionalism
Responsibilities to Funders, Clients, Employers
Responsibilities in Publications and Testimony
Responsibilities to Research Subjects
Responsibilities to Research Team Colleagues
The report contains 67 guidelines organized into
eight topic areas:
Responsibilities to Other Statisticians/Practitioners
Responsibilities Regarding Allegations of Misconduct
Responsibilities of Employers Including Organizations,
Individuals, Attorneys, or Other Clients

‹#›

INFO 1010

MULTI VARIABLE DESCRIPTIVE STATISTICS

AND CHARTS

‹#›

1

Scatterplot: Summarizing Data for Two Variables Using Graphical Displays
Crosstabulation: Summarizing Data for Two Variables Using Tables
Data Visualization: Best Practices in Creating Effective Graphical Displays

‹#›

Price
Range
French Italian Duplex Appt
Total
< $250,000 > $250,000
18 6 19 12
55
45
30 20 35 15
Total
100
12 14 16 3
Home Style
Crosstabulation
Example: Daniel’s Homes
The number of homes sold for each style and price for the past two years is shown below.
quantitative
variable
categorical
variable

‹#›

A scatter diagram is a graphical presentation of the relationship between two quantitative variables.
One variable is shown on the horizontal axis and the other variable is shown on the vertical axis.
The general pattern of the plotted points suggests the overall relationship between the variables.
A trendline provides an approximation of the relationship.
Scatter Diagram and Trendline

‹#›

Scatter Diagram
Positive Trend

x
y

‹#›

Scatter Diagram
Negative Relationship

x
y

‹#›

Scatter Diagram
No Apparent Relationship

x
y

‹#›

1
3
2
1
3
14
24
18
17
30
x = Number of
Interceptions
y = Number of
Points Scored
Scatter Diagram
The Broncos football team is interested in
investigating the relationship, if any, between
interceptions made and points scored.

‹#›

Scatter Diagram and Trendline

y
x
Number of Interceptions
Number of Points Scored

5
10
15
20
25
30
0
35

1
2
3
0
4

‹#›

INFO 1010

SINGLE VARIABLE

CENTRAL TENDENCY MEASURES

‹#›

1

Measures of Location
If the measures are computed
for data from a sample, they
are called sample statistics.
If the measures are computed for
data from a population, they are
called population parameters.
A sample statistic is referred to as
the point estimator of the
corresponding population parameter.
Mean
Median
Mode
Percentiles
Quartiles
Weighted Mean
Geometric Mean

‹#›

Sample Mean

Number of
observations
in the sample
Sum of the values
of the n observations

‹#›

Population Mean m
Number of
observations in
the population
Sum of the values
of the N observations

‹#›
Weighted Mean

Denominator:
sum of the
weights
Numerator:
sum of the weighted
data values

If data is from
a population,
m replaces .
where:
xi = value of observation i
wi = weight for observation i

‹#›
Geometric Mean

= [(x1)(x2)…(xn)]1/n
Excel’s geometric mean function is:

=GEOMEAN(data cell range)

‹#›
Excel Formula Worksheet
Using Excel to Compute
the Mean, Median, and Mode

A
B
C
D
E
1
Apart-
ment
Monthly
Rent ($)
2
1
545
Mean
=AVERAGE(B2:B71)
3
2
715
Median
=MEDIAN(B2:B71)
4
3
530
Mode
=MODE.SNGL(B2:B71)
5
4
690
6
5
535

‹#›
Percentiles
A percentile provides information about how the data are spread over the interval from the smallest value to the largest value.
Admission test scores for colleges and universities are frequently reported in terms of percentiles.
The pth percentile of a data set is a value such that at least p percent of the items take on this value or less and at least (100 – p) percent of the items take on this value or more.
PERCENTILE.EXC(data range, p/100)

‹#›
Quartiles
Quartiles are specific percentiles.
First Quartile = 25th Percentile
Second Quartile = 50th Percentile = Median
Third Quartile = 75th Percentile
QUARTILE.EXC (data range, quartile number)

‹#›

INFO 1010

CHARTS AND DESCRIPTIVE STATISTICS

‹#›

1

Joe’s Diner Example
Guests eating at Joe’s Diner were asked to rate the
quality of their meal as being excellent,
above average, average, below average, or poor. The
ratings provided by a sample of 20 customers are:
Below Average
Above Average
Above Average
Average
Above Average
Average
Above Average
Average
Above Average
Below Average
Poor
Excellent
Above Average
Average
Above Average
Above Average
Below Average
Poor
Above Average
Average
Frequency Distribution

‹#›

Frequency Distribution

Poor
Below Average
Average
Above Average
Excellent

2
3
5
9
1
Total 20
Rating
Frequency

‹#›

Using Excel’s COUNTIF Function
to Construct a Frequency Distribution

‹#›

Using Excel’s COUNTIF Function
to Construct a Frequency Distribution

‹#›
Relative Frequency and
Percent Frequency Distributions

Poor
Below Average
Average
Above Average
Excellent
.10
.15
.25
.45
.05
Total 1.00
10
15
25
45
5
100
Relative
Frequency
Percent
Frequency
Rating
.10(100) = 10
1/20 = .05

‹#›

Using Excel to Construct Relative Frequency and Percent Frequency Distributions

‹#›

Using Excel to Construct Relative Frequency and Percent Frequency Distributions

‹#›

Poor
Below
Average
Average
Above
Average
Excellent
Frequency
Rating
Bar Chart

1
2
3
4
5
6
7
8
9
10

‹#›

Histogram
Another common graphical display of quantitative data is a histogram.
The variable of interest is placed on the horizontal axis.
A rectangle is drawn above each class interval with its height corresponding to the interval’s frequency, relative frequency, or percent frequency.
Unlike a bar graph, a histogram has no natural separation between rectangles of adjacent classes.

‹#›
Histogram
Days on Market for Home Sales

2
4
6
8
10
12
14
16
18

Frequency
10-19 20-29 30-39 40-49 50-59 60-69

When the Format Data Series dialog box appears Set the Gap Width to 0

‹#›

Symmetric
Histograms Showing Skewness

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0
Left tail is the mirror image of the right tail
Examples: Heights of People

‹#›
Histograms Showing Skewness
Moderately Skewed Left
A longer tail to the left
Example: Exam Scores

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

‹#›
Moderately Right Skewed
Histograms Showing Skewness
A Longer tail to the right
Example: Housing Values

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

‹#›
Histograms Showing Skewness
Highly Skewed Right
A very long tail to the right
Example: Executive Salaries

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

‹#›
Distribution Shape: Skewness
An important measure of the shape of a distribution is called skewness.
The formula for the skewness of sample data is

Skewness =

‹#›

Distribution Shape: Skewness
Symmetric (not skewed)

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0
Skewness = 0
Skewness is zero.
Mean and median are equal.

‹#›

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0
Distribution Shape: Skewness
Moderately Skewed Left

Skewness = – .31
Skewness is negative.
Mean will usually be less than the median.

‹#›

Distribution Shape: Skewness
Moderately Skewed Right

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

Skewness = .31
Skewness is positive.
Mean will usually be more than the median.

‹#›

Distribution Shape: Skewness
Highly Skewed Right

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

Skewness = 1.25
Skewness is positive (often above 1.0).
Mean will usually be more than the median.

‹#›

Seventy efficiency apartments were randomly
sampled in a college town. The monthly rent prices
for the apartments are listed below in ascending order.
Distribution Shape: Skewness
Example: Apartment Rents

525
530
530
535
535
535
535
535
540
540
540
540
540
545
545
545
545
545
550
550
550
550
550
550
550
560
560
560
565
565
565
570
570
572
575
575
575
580
580
580
580
585
590
590
590
600
600
600
600
610
610
615
625
625
625
635
649
650
670
670
675
675
680
690
700
700
700
700
715
715

‹#›

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

Skewness = .92
Distribution Shape: Skewness
Example: Apartment Rents

‹#›

A
B
C
D
1
Quality Rating
Quality Rating
Frequency
2
Above Average
Poor
=COUNTIF($A$2:$A$21,C2)
3
Below Average
Below Average
=COUNTIF($A$2:$A$21,C3)
4
Above Average
Average
=COUNTIF($A$2:$A$21,C4)
5
Average
Above Average
=COUNTIF($A$2:$A$21,C5)
6
Average
Excellent
=COUNTIF($A$2:$A$21,C6)
7
Above Average
Total
=SUM(D2:D6)
8
Above Average
A
B
C
D
1
Quality Rating
Quality Rating
Frequency
2
Above Average
Poor
2
3
Below Average
Below Average
3
4
Above Average
Average
5
5
Average
Above Average
9
6
Average
Excellent
1
7
Above Average
Total
20
8
Above Average

C
D
E
F
1
Quality Rating
Frequency
Relative
Frequency
Percent
Frequency
2
Poor
=COUNTIF($A$2:$A$21,C2)
=D2/$D$7
=E2*100
3
Below Average
=COUNTIF($A$2:$A$21,C3)
=D3/$D$7
=E3*100
4
Average
=COUNTIF($A$2:$A$21,C4)
=D4/$D$7
=E4*100
5
Above Average
=COUNTIF($A$2:$A$21,C5)
=D5/$D$7
=E5*100
6
Excellent
=COUNTIF($A$2:$A$21,C6)
=D6/$D$7
=E6*100
7
Total
=SUM(D2:D6)
=SUM(E2:E6)
=SUM(F2:F6)
8

C
D
E
F
1
Quality Rating
Frequency
Relative
Frequency
Percent
Frequency
2
Poor
2
0.10
10
3
Below Average
3
0.15
15
4
Average
5
0.25
25
5
Above Average
9
0.45
45
6
Excellent
1
0.05
5
7
Total
20
1.00
100
8

INFO 1010

SINGLE VARIABLE MEASURES OF VARIABILITY

‹#›

1

Measures of Variability
It is often desirable to consider measures of variability (dispersion), as well as measures of location.
For example, in choosing supplier A or supplier B we might consider not only the average delivery time for each, but also the variability in delivery time for each.

‹#›
Measures of Variability
Range
Interquartile Range
Variance
Standard Deviation
Coefficient of Variation

‹#›
Interquartile Range
The interquartile range of a data set is the difference between the third quartile and the first quartile.
It is the range for the middle 50% of the data.
It overcomes the sensitivity to extreme data values.

‹#›

525
530
530
535
535
535
535
535
540
540
540
540
540
545
545
545
545
545
550
550
550
550
550
550
550
560
560
560
565
565
565
570
570
572
575
575
575
580
580
580
580
585
590
590
590
600
600
600
600
610
610
615
625
625
625
635
649
650
670
670
675
675
680
690
700
700
700
700
715
715

Interquartile Range

3rd Quartile (Q3) = 625
1st Quartile (Q1) = 545
IQR = Q3 – Q1 = 625 – 545 = 80

‹#›

Variance
The variance is a measure of variability that utilizes all the data.
It is based on the difference between the value of each observation (xi) and the mean ( for a sample, m for a population).
The variance is useful in comparing the variability of two or more variables.
=VAR.S(data cell range)

‹#›
Standard Deviation
The standard deviation of a data set is the positive square root of the variance.
It is measured in the same units as the data, making it more easily interpreted than the variance.
STDEV.S(data cell range)

‹#›

Standard
deviation is
about 9%
of the mean
Variance
Standard Deviation
Coefficient of Variation
Sample Variance, Standard Deviation,
And Coefficient of Variation
Apartment Rents
s2 = = 2,996.16
s =
% =

‹#›

Measures of Distribution
Distribution Shape
z-Scores
Chebyshev’s Theorem
Empirical Rule
Detecting Outliers

‹#›

Distribution Shape: Skewness
An important measure of the shape of a distribution is called skewness.
The formula for the skewness of sample data is

Skewness =

‹#›

Distribution Shape: Skewness
Symmetric (not skewed)

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0
Skewness = 0
Skewness is zero.
Mean and median are equal.

‹#›

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0
Distribution Shape: Skewness
Moderately Skewed Left

Skewness = – .31
Skewness is negative.
Mean will usually be less than the median.

‹#›

Distribution Shape: Skewness
Moderately Skewed Right

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

Skewness = .31
Skewness is positive.
Mean will usually be more than the median.

‹#›

Distribution Shape: Skewness
Highly Skewed Right

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

Skewness = 1.25
Skewness is positive (often above 1.0).
Mean will usually be more than the median.

‹#›

Seventy efficiency apartments were randomly
sampled in a college town. The monthly rent prices
for the apartments are listed below in ascending order.
Distribution Shape: Skewness
Example: Apartment Rents

525
530
530
535
535
535
535
535
540
540
540
540
540
545
545
545
545
545
550
550
550
550
550
550
550
560
560
560
565
565
565
570
570
572
575
575
575
580
580
580
580
585
590
590
590
600
600
600
600
610
610
615
625
625
625
635
649
650
670
670
675
675
680
690
700
700
700
700
715
715

‹#›

Relative Frequency
.05
.10
.15
.20
.25
.30
.35
0

Skewness = .92
Distribution Shape: Skewness
Example: Apartment Rents

‹#›

Empirical Rule
When the data are believed to approximate a
bell-shaped distribution …
The empirical rule is based on the normal
distribution, which is covered in Chapter 6.
The empirical rule can be used to determine the
percentage of data values that must be within a
specified number of standard deviations of the
mean.

‹#›

Empirical Rule
For data having a bell-shaped distribution:
of the values of a normal random variable
are within of its mean.
68.26%
+/- 1 standard deviation
of the values of a normal random variable
are within of its mean.
95.44%
+/- 2 standard deviations
of the values of a normal random variable
are within of its mean.
99.72%
+/- 3 standard deviations

‹#›

Empirical Rule

x

m – 3s
m – 1s
m – 2s
m + 1s
m + 2s
m + 3s
m

68.26%

95.44%

99.72%

‹#›

z-Scores

Standardized Values for Apartment Rents

‹#›

The z-score is often called the standardized value.
It denotes the number of standard deviations a data
value xi is from the mean.

z-Scores
Excel’s STANDARDIZE function can be used to
compute the z-score.
=

‹#›

z-Scores
A data value less than the sample mean will have a
z-score less than zero.
A data value greater than the sample mean will have
a z-score greater than zero.
A data value equal to the sample mean will have a
z-score of zero.
An observation’s z-score is a measure of the relative
location of the observation in a data set.

‹#›

Z-Scores and Distributions

‹#›
Detecting Outliers
An outlier is an unusually small or unusually large
value in a data set.
A data value with a z-score less than -3 or greater
than +3 might be considered an outlier.
It might be:
an incorrectly recorded data value
a data value that was incorrectly included in the
data set
a correctly recorded data value that belongs in
the data set

‹#›

Chebyshev’s Theorem
At least (1 – 1/z2) of the items in any data set will be within z standard
deviations of the mean, where z is any value greater than 1.
Chebyshev’s theorem requires z > 1, but z need not be an integer.
At least of the data values must be
within of the mean.
75%
z = 2 standard deviations
At least of the data values must be
within of the mean.
89%
z = 3 standard deviations
At least of the data values must be
within of the mean.
94%
z = 4 standard deviations

‹#›

Five-Number Summaries
and Box Plots
Summary statistics and easy-to-draw graphs can be
used to quickly summarize large quantities of data.
Two tools that accomplish this are five-number
summaries and box plots.

‹#›

Five-Number Summary
1
Smallest Value
First Quartile
Median
Third Quartile
Largest Value
2
3
4
5

‹#›

Box Plot
A box plot is a graphical summary of data that is
based on a five-number summary.
A key to the development of a box plot is the
computation of the median and the quartiles Q1 and
Q3.
Box plots provide another way to identify outliers.

‹#›

Box Plot
Whiskers (dashed lines) are drawn from the ends of the box to the smallest and largest data values
inside the limits.

500
525
550
575
600
625
650
675
700
725

Smallest value
inside limits = 525
Largest value
inside limits = 715

‹#›

Data Dashboards:
Adding Numerical Measures
to Improve Effectiveness
The addition of numerical measures, such as the mean
and standard deviation of KPIs, to a data dashboard
is often critical.
Drilling down refers to functionality in interactive
dashboards that allows the user to access information
and analyses at increasingly detailed level.
Dashboards are often interactive.
Data dashboards are not limited to graphical displays.

‹#›

‹#›
-1.20
-1.11
-1.11
-1.02
-1.02
-1.02
-1.02
-1.02
-0.93
-0.93
-0.93
-0.93
-0.93
-0.84
-0.84
-0.84
-0.84
-0.84
-0.75
-0.75
-0.75
-0.75
-0.75
-0.75
-0.75
-0.56
-0.56
-0.56
-0.47
-0.47
-0.47
-0.38
-0.38
-0.34
-0.29
-0.29
-0.29
-0.20
-0.20
-0.20
-0.20
-0.11
-0.01
-0.01
-0.01
0.17
0.17
0.17
0.17
0.35
0.35
0.44
0.62
0.62
0.62
0.81
1.06
1.08
1.45
1.45
1.54
1.54
1.63
1.81
1.99
1.99
1.99
1.99
2.27
2.27

INFO 1010

MULTI VARIABLE

MEASURES OF VARIABILITY

‹#›

1

Measures of Association
Between Two Variables
Thus far we have examined numerical methods used
to summarize the data for one variable at a time.
Often a manager or decision maker is interested in
the relationship between two variables.
Two descriptive measures of the relationship
between two variables are covariance and correlation
coefficient.

‹#›

Covariance
Positive values indicate a positive relationship.
Negative values indicate a negative relationship.
The covariance is a measure of the linear association
between two variables.

‹#›

Covariance
The covariance is computed as follows:

for
samples
for
populations

=
=

‹#›

Correlation Coefficient
Just because two variables are highly correlated, it
does not mean that one variable is the cause of the
other.
Correlation is a measure of linear association and not
necessarily causation.

‹#›

The correlation coefficient is computed as follows:

for
samples
for
populations

Correlation Coefficient
=
=

‹#›

Correlation Coefficient
Values near +1 indicate a strong positive linear
relationship.
Values near -1 indicate a strong negative linear
relationship.
The coefficient can take on values between -1 and +1.
The closer the correlation is to zero, the weaker the
relationship.

‹#›

Sample Covariance
Sample Correlation Coefficient
Covariance and Correlation Coefficient

Example: Golfing Study
= = = -7.08
=

‹#›

Using Excel to Compute the
Covariance and Correlation Coefficient
Excel Formula Worksheet

A
B
C
D
1
Average
Drive
18-Hole
Score
2
277.6
69
Samp. Covariance
=COVARIANCE.S(A2:A7,B2:B7)
3
259.5
71
Samp. Correlation
=CORREL(A2:A7,B2:B7)
4
269.1
70
5
267.0
70
6
255.6
71
7
272.9
69
8

‹#›
Using Excel to Compute the
Covariance and Correlation Coefficient
Excel Value Worksheet

A
B
C
D
1
Average
Drive
18-Hole
Score
2
277.6
69
Samp. Covariance
-7.08
3
259.5
71
Samp. Correlation
-0.9631
4
269.1
70
5
267.0
70
6
255.6
71
7
272.9
69
8

‹#›

Twenty principles for

good spreadsheet practice
Second edition

icaew.com/itfacBUSINESS WITH CONFIDENCE

http://icaew.com/itfac

D Page footer

© ICAEW 2015
All rights reserved. If you want to reproduce or redistribute any of the material in this publication,
you should first get ICAEW’s permission in writing. ICAEW will not be liable for any reliance you
place on the information in this publication. You should seek independent advice.

ISBN 978-1-78363-150-6

CONTENTS

PREFACE 01

WHY TWENTY PRINCIPLES? 02

THE TWENTY PRINCIPLES EXPLAINED AND ILLUSTRATED 03

The spreadsheet’s business environment 04

Designing and building your spreadsheet 06

Spreadsheet risks and controls 13

Foreword by Mazars

Supported by

One year ago, ICAEW first published its Twenty principles for good spreadsheet practice.

The principles were launched as a response to the increased recognition of the risks and
waste caused by poor spreadsheet practice. We believe that having a set of principles
developed by an independent and respected body represents an important milestone
in addressing these issues. We have been actively involved in the development of the
principles and are strong advocates of their wider adoption. We are therefore delighted
that ICAEW is progressing this initiative by producing this updated edition and taking the
time to consider how the principles are being used in practice.

The Mazars team works on building and reviewing spreadsheets for a range of uses across
industry. Over the past year we have begun to use the principles in a number of different
ways to help drive change, for example:

• as a benchmark to evaluate and improve models for our clients; we find they provide an
excellent basis on which to recommend change in governance and implementation of
modelling best practice; and

• to reinforce with clients the importance of adopting spreadsheet standards, such as the
FAST Modelling Standard which we believe to be vital in order to improve productivity
and reduce risk when using spreadsheets across their organisations.

We have also taken an active role in helping promote the principles more widely across
industry. As an example, when it comes to public procurement, we can see that a
requirement for bidder financial models to be compliant with the principles would be
much more useful than the current obligations, which look to undefined notions of ’best
practice’. We have written about this in more detail in our blog which is dedicated to
financial model review – themodelauditor.com.

If you haven’t already reviewed the principles, we urge you and your teams to do so and
see whether you think that their adoption could help reduce the amount of time wasted
and the potential for costly and embarrassing error in your business.

After one year, the principles have begun to demonstrate their worth in practice and
we believe that their wider promotion and adoption offers a real opportunity to make
a step-change in industry practice. We wholeheartedly recommend them to you.

Jerome Brice
Partner and Head of Model Audit

A

Like it or not, spreadsheets are in use everywhere. They have become the
lingua franca of business; no matter what your system or requirement,
a spreadsheet can connect people like no other business tool.

However, the use of spreadsheets is not without risk, and approximately
90% of spreadsheets contain mistakes. Material errors such as incorrect
models, sending out sheets with hidden columns or careless use of
formulae, have been well publicised alongside the embarrassment and
financial loss that arise as a result.

In addition, there is a serious problem of waste arising from spreadsheets
that are created inefficiently or carelessly. 65% of members of the Excel
Community are self-taught, and with no formal methodology there is a
risk that sub-optimal models and processes become the norm.

This is why ICAEW’s Excel Community Advisory Committee came together
to develop Twenty principles for good spreadsheet practice that look to
reduce spreadsheet risk and inefficiency in all organisations regardless of
size or sector.

I would like to thank all the members of the committee for developing these
principles, and would encourage readers to act on the recommendations in
this report.

Michael Izza
Chief Executive Officer, ICAEW

Preface

01Twenty principles for good spreadsheet practice

Many spreadsheets evolve over time without well-structured design
or integrity checks, and are poorly documented. Making a relatively
simple change can often take a long time, have unexpected
consequences and/or result in errors from incorrect calculations or
input assumptions, as famously illustrated by debacles such as the
bidding process for the West Coast mainline franchise.1

Why twenty principles?

The purpose of these principles is to
help reduce the amount of time wasted,
and the number of errors caused, by
businesses (including accountancy
practices) as a consequence of the
way they and their employees use
spreadsheets.

There are several points to emphasise.
First, no set of principles or standards
can guarantee freedom from error. The
design, maintenance and operation
of spreadsheets are still carried out by
humans.

Secondly, this document is not only about
‘good spreadsheet design’. The business
environment in which spreadsheets are
created, maintained and used is at least
as important. So the first four principles
are ones we believe should be adopted
by an organisation before anyone starts
to work on any individual spreadsheet-
using project. They are intended to create
a framework, and to instil attitudes,
which encourage best-practice to flourish.
These principles are addressed not only
to those directly involved in the design
and use of spreadsheets but also to those
with managerial responsibility, including
responsibility for management of risk.
They may also be of interest and relevance
to those with responsibility for audit.

Third, these are ‘principles’, not
‘standards’. By way of example, Principle
2 requires clarity and consistency in the
use of formatting. This could mean using
a particular cell colour to denote cells
allowing user input. There might be any
number of different corporate standards,
or publicly available standards, that adhere
to this principle. One standard might
specify pink as the colour for input cells;
another might specify green. Either would
satisfy the principle.

Finally, this set of principles is not meant
to be comprehensive, nor is it meant
to be very detailed. Deliberately it
focuses mainly on traditional formula-
driven spreadsheet construction, which
still accounts for the vast majority of
spreadsheet use, rather than on pivot
tables, structured references etc. It is a ‘top
20’ list, with each principle set out simply
and concisely, and with some explanation
and illustration added.2 It would of course
be possible to provide much more detail
than this, and to produce a much longer
document. These principles are intended
to be very widely applicable, and are
intended to cover projects of all shapes
and sizes and degrees of complexity. As
technology, and the ways people use it,
evolves, the priorities set out here may
need to change, and so the IT Faculty
intends to keep these principles under
regular review.

1In October 2012, at significant cost to the taxpayer, the Department for Transport had to withdraw the
contract to run the West Coast Mainline rail service from the company that had ‘won’, after it was discovered
that there had been errors in the way the bids had been assessed. It was widely reported (for example
theguardian.com/politics/2012/oct/05/west-coast-civil-servant-transport) that the spreadsheet used for the
calculations was seriously flawed.
2Excel 2013 is used for the illustrations. If you are using an earlier version, some of the screenshots will look
different, and some features may not be available.

Twenty principles for good spreadsheet practice02

http://www.theguardian.com/politics/2012/oct/05/west-coast-civil-servant-transport

03Twenty principles for good spreadsheet practice

The twenty principles
explained and illustrated

The spreadsheet’s
business environment

Twenty principles for good spreadsheet practice04

1. Determine what role spreadsheets play in your business, and
plan your spreadsheet standards and processes accordingly

If you have spreadsheets that play a key or critical role in your organisation, ensure
that they are developed and tested, managed and monitored to an appropriate level.
Spreadsheets that form part of an organisation’s key business processes will need to be
managed differently from ad hoc spreadsheets for short-term use by an individual.

2. Adopt a standard for your organisation and stick to it
This might be one that is developed in-house, or adopted from outside and shared
with other organisations. A common standard within an organisation facilitates
collaboration, aids understanding and saves development time. The standard should
include, among other things, consistent conventions on use of cell formatting.
This may be achieved by using the ‘cell styles’ feature as illustrated below.

3. Ensure that everyone involved in the creation or use of
spreadsheets has an appropriate level of knowledge and
competence

For anyone designing, developing or maintaining (as distinct from just using) a
spreadsheet, this will include: awareness of the range of functions available, clear
understanding of such basic concepts as relative and absolute cell references, and an
appreciation of the importance of carefully checking the results of functions.

4. Work collaboratively, share ownership, peer review
The extent of collaboration and review needed will depend on the size and complexity
of your organisation and of each project.

Trademark acknowledgements: Excel is a registered trademark of Microsoft Inc. Screenshots reprinted by permission from Microsoft Corporation.

05Twenty principles for good spreadsheet practice

The twenty principles
explained and illustrated

Designing and building
your spreadsheet

5. Before starting, satisfy yourself that a spreadsheet is the
appropriate tool for the job

Spreadsheets are not the answer to every problem. A lot of time can be wasted, and
errors caused, by using a spreadsheet when some other application would be more
appropriate. Very often the more appropriate tool might be a word processor (if it’s
a table of text), a database (if it’s processing large quantities of similar data items) or
an existing software package (if it’s to undertake well-established processes, such as
bookkeeping, for which specialist packages are readily available). Even if a spreadsheet is
still the right answer it’s worth looking for existing templates before starting a new one
from scratch.

6. Identify the audience. If a spreadsheet is intended to be
understood and used by others, the design should facilitate this

If the only ‘audience’ envisaged is yourself, you might perhaps justify less explanation
and help. Even so, good documentation is helpful if you come back to a spreadsheet a
while after you created it; and many spreadsheets come to have a much wider audience
than originally intended. Ensure that adequate instructions, validation and help are
included to promote ease of use and avoid input errors. Even if parts of a spreadsheet
are ‘locked’, keep calculations visible.

Twenty principles for good spreadsheet practice06

07Twenty principles for good spreadsheet practice

7. Include an ‘About’ or ‘Welcome’ sheet to document
the spreadsheet

This should give such basic information as author, purpose, version number, and
description of general approach. Also include explanations of colour codes and other
formatting conventions, any sources of input data (with, where appropriate, hyperlinks
to the original data), and any macros and what they do. The more complex the
workbook, or the more it needs to be shared, the greater the requirement for good
documentation. Conversely, a simple spreadsheet to be used only by the person who
designs it might be less rigorously documented.

8. Design for longevity
Design spreadsheets to adapt to any reasonably foreseeable future changes in values
(tax rates, etc) or volume (eg, items in a data set) of data used in calculations. However,
the need for adaptability should be balanced against following the Agile principle of
‘The simplest thing that could possibly work’.

In the first example above, if an organisation were to add a new department, a new
worksheet could be added anywhere between DeptA and DeptD (DeptC1, for instance),
and there would be no need to change the formula as the new worksheet would
automatically be picked up by the formula. In the second example above, the formula
would need to be changed every time a new worksheet is added.

9. Focus on the required outputs
Work backwards: be clear about the purpose of the spreadsheet, what outputs achieve
that purpose and therefore what inputs and logic are required to derive the outputs.

10. Separate and clearly identify inputs, workings and outputs
A properly structured spreadsheet will be easier to understand and to maintain.
If pivot tables are used, it may be possible to relax this principle, but clarity remains
crucial. Design to ensure that any input should be entered only once.

Twenty principles for good spreadsheet practice08

09Twenty principles for good spreadsheet practice

11. Be consistent in structure
Use the same columns for the same things in each workbook, especially when working
with time series. A consistent convention within a workbook reduces the risk of error
where one sheet refers to another. For example, a common convention is that time
flows horizontally from left to right (and a specific column is always ‘Year 1’) and
calculations flow vertically from top to bottom. Such a structure will help to avoid
circular references.

12. Be consistent in the use of formulae
On any worksheet use the smallest practicable number of different formulae. Where
it is necessary to use different formulae, ensure that groups of cells using the different
formulae are clearly separated.

In the left-hand example above, the formula =$A15*B$11 in cell B15 has been copied
across and down to all the cells in the range B15:D24, whereas in the right-hand
example, because the $ sign was not used, a formula had to be entered manually into
each of the 30 cells in turn. This significantly increased the risk of error and the time
needed to review the worksheet. ‘Go To Special’ – ‘Column differences’ is an error-
checking process looking for inconsistencies. In the left-hand example it generates the
message ‘No cells were found’, meaning that there are no inconsistent formulae; in the
right-hand example cells K16:K24 remain highlighted, showing that the formulae in that
range are different from the one used in K15 at the top of the column.

13. Keep formulae as short and simple as practicable
Shorter formulae are easier to build (and therefore less likely to contain errors) and easier
to understand and to review. Stage a calculation through multiple cells rather than build
a long, complex formula.

14. Never embed in a formula anything that might change or
need to be changed

Instead, put such values into separate cells and reference them. This ensures that values
enter the spreadsheet only once, and if change is needed would happen in just one
place. It also allows for all formulae cells to be locked without denying access to input
values.

In the left-hand example above, the VAT amounts in B15:B24 and E15:E24 have been
calculated by a formula and the cells in those ranges have been locked and the sheet
protected (which is why clicking into one of them produces the message displayed). In
the right-hand example, the formulae in the equivalent cells included the VAT rate as a
figure rather than as a reference to a cell containing the rate. In this example, if the VAT
rate were to change, each formula containing the VAT rate as a figure would need to
be identified and changed manually – running the risk of introducing errors in the rate
entered or in the actual formula. Additionally, in the right-hand example the formulae
were not protected, and they appear to have been manually overwritten by mostly
wrong values.

Twenty principles for good spreadsheet practice10

15. Perform a calculation once and then refer back to that
calculation

Do not calculate the same value in multiple places (except perhaps for cross checking
purposes). This reduces risk of error, and is more efficient, since fewer calculations are
being performed.

16. Avoid using advanced features where simpler features could
achieve the same result

In particular, avoid using programming code unless necessary – in which case ensure
that it is clearly documented within the code itself, as well as in a documentation
worksheet. Similarly, avoid circular references, and control and document any
exceptions. Do not change the software’s key default settings (for example, do not turn
off automatic recalculation) unless essential, in which case include a prominent message
to warn users.

11Twenty principles for good spreadsheet practice

The twenty principles
explained and illustrated

Spreadsheet risks
and controls

Twenty principles for good spreadsheet practice12

13Twenty principles for good spreadsheet practice

17. Have a system of backup and version control, which should be
applied consistently within an organisation

The appropriate levels of backup and version control will depend on the organisation
and the nature of the work, but there should always be, at the very least, a reliable
means of preserving, identifying and restoring earlier versions of a workbook.

18. Rigorously test the workbook
The level of testing required will depend on the size, complexity and criticality of the
workbook, with riskier workbooks needing a greater degree of independent testing.

This example illustrates the use of ‘trace precedents’, which shows all the cells
which affect the value of the currently selected cell and ‘trace dependents’, which
shows all the cells containing formulae that refer to the active cell.

19. Build in checks, controls and alerts from the outset and during
the course of spreadsheet design

These checks might include, for example, tests to ensure that a balance sheet balances,
assets do not depreciate below zero, and so on. One approach would be to build in a
set of audit tests to check validity and use flags to signal compliance or non-compliance.
Use a master flag to summarise all the individual flags and place it prominently (on the
output sheet, or even throughout the workbook eg, on sheet headers) so that users are
bound to see it.

In the second of the two examples above, the actual interest rate that has been input
is 12%, which is 2% above the upper limit – hence the warning ‘red spots’ and the
explanatory error message.

Twenty principles for good spreadsheet practice14

15Twenty principles for good spreadsheet practice

20. Protect parts of the workbook that are not supposed to be
changed by users

The level of protection will vary according to the nature of the spreadsheet and the kind of
use/users it will have. It might include locking whole worksheets, all cells containing formulae,
or everything except designated input cells.

Twenty principles for good spreadsheet practice16

Acknowledgments

Twenty principles for good spreadsheet practice is the result of debate among members of the
IT Faculty’s Excel Community Advisory Committee, who saw the document through a number
of drafts over several months, and then took on board comments from the wider ICAEW
membership and the public. The members of the Excel Community Advisory Committee
were as follows:

Christopher Blunn Grace Frank
Tom Brichieri-Colombi Mazars
Roland Brook Smith & Williamson
Grenville Croll EuSpRIG
Daniel Emkes Harrow School
Glen Feechan needaspreadsheet.com
Simon Hurst The Knowledge Base
Alistair Hynd Baker Tilly
Tony Lee Global Aerospace
David Lyford-Smith BDO
Adrian Maconick Finsbury Solutions
Sanjay Magecha Financial Visibility
Vinit Patel Filtered
Rishi Sapra KPMG
John Tennent Corporate Edge
Paul Wakefield Paul Wakefield
Dave White White Bruce

Recognition of spreadsheet standards

The IT Faculty has developed a scheme
whereby spreadsheet standards, and other
products and services such as training,
can be formally recognised as compliant
with the Twenty Principles.

So far, the following products have
achieved recognition:

• The FAST Modelling Standard

• FinRobot’s Base, Topline, Case Builder and
Manufacturing standard models

• Best Practice Spreadsheet Modelling
Standards and bpmToolbox software

SPREADSHEET
PRINCIPLES

COMPLIANT

17Twenty principles for good spreadsheet practice

The twenty principles in brief

The spreadsheet’s business environment

1. Determine what role spreadsheets play in your business, and plan your
spreadsheet standards and processes accordingly.

2. Adopt a standard for your organisation and stick to it.

3. Ensure that everyone involved in the creation or use of spreadsheets has
an appropriate level of knowledge and competence.

4. Work collaboratively, share ownership, peer review.

Designing and building your spreadsheet

5. Before starting, satisfy yourself that a spreadsheet is the appropriate
tool for the job.

6. Identify the audience. If a spreadsheet is intended to be understood
and used by others, the design should facilitate this.

7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.

8. Design for longevity.

9. Focus on the required outputs.

10. Separate and clearly identify inputs, workings and outputs.

11. Be consistent in structure.

12. Be consistent in the use of formulae.

13. Keep formulae as short and simple as practicable.

14. Never embed in a formula anything that might change or need to be
changed.

15. Perform a calculation once and then refer back to that calculation.

16. Avoid using advanced features where simpler features could achieve the
same result.

Spreadsheet risks and controls

17. Have a system of backup and version control, which should be applied
consistently within an organisation.

18. Rigorously test the workbook.

19. Build in checks, controls and alerts from the outset and during the
course of spreadsheet design.

20. Protect parts of the workbook that are not supposed to be changed
by users.

!

© ICAEW 2015 TECPLM13983 06/15

ICAEW
Chartered Accountants’ Hall Moorgate Place London EC2R 6EA UK

T +44 (0)20 7920 8481
E itfac@icaew.com
icaew.com/itfac

facebook.com/icaew
twitter.com/icaew_Excel
linkedin.com – ICAEW IT Faculty

ICAEW is a world-leading professional membership organisation that promotes,
develops and supports over 144,000 chartered accountants worldwide. We
provide qualifications and professional development, share our knowledge,
insight and technical expertise, and protect the quality and integrity of the
accountancy and finance profession.

As leaders in accountancy, finance and business our members have the
knowledge, skills and commitment to maintain the highest professional
standards and integrity. Together we contribute to the success of individuals,
organisations, communities and economies around the world.

Because of us, people can do business with confidence.

ICAEW is a founder member of Chartered Accountants Worldwide and the
Global Accounting Alliance.

www.charteredaccountantsworldwide.com
www.globalaccountingalliance.com

Mazars is an international, integrated and independent organisation,
specialising in audit, advisory, accounting and tax services. The Group
operates in 73 countries and draws on the expertise of 15,000 professionals
to assist major international groups, SMEs, private investors and public
bodies at every stage in their development. In the UK, Mazars has over
1500 partners and staff serving clients from 19 offices, and is ranked as the
ninth largest firm nationally.

mazars.co.uk

twitter.com/mazars_uk

Mazars’ specialist financial modelling team has a wealth of experience in
infrastructure finance and its skills are increasingly called on for a wide
range of corporate applications. The team is recognised as a global leader in
financial model audit and has 45 specialist staff working in dedicated service
teams from our offices in London, Paris, New York and Delhi.

themodelauditor.com

twitter.com/themodelauditor

Excel is one of the most popular end-user tools in the accountant’s portfolio.
Spreadsheets enable us to quickly and flexibly perform analysis that otherwise
would be difficult or time-consuming; however, there is a tendency to place
undue trust in them. ICAEW’s Excel Community provides a ‘one-stop shop’
for accountants who want to use Excel better and understand and minimise
spreadsheet risk.

For more information about the Excel Community, please visit icaew.com/
excel For more information about the IT Faculty, please visit icaew.com/itfac

Supported by

http://itfac@icaew.com

http://icaew.com/itfac

www.sec.gov/divisions/corpfin/guidance/cfguidance-topic2.htm

http://twitter.com/icaew_Excel

http://linkedin.com – ICAEW IT Faculty

Home

Global Accounting Alliance

http://mazars.co.uk

http://twitter.com/mazars_uk

http://themodelauditor.com

Tweets by themodelauditor