What-If Analysis and Statistics in Excel
What-If Analysis
These tools are under Data > What-If Analysis.
Goal Seek
Goal Seek works backwards from a result. Suppose B1 holds units, B2 holds price, and B3 holds =B1*B2. To find the units needed for revenue of 50,000:
- Set cell:
B3 - To value:
50000 - By changing cell:
B1
Excel changes B1 until B3 reaches the target.
Data Tables
A data table shows how a formula’s result changes across a list of inputs. A one-variable table might list ten prices and show the revenue for each. A two-variable table shows revenue for every combination of price and units.
Scenario Manager
Scenario Manager stores named sets of input values, such as Best case, Expected and Worst case, and switches between them or produces a summary comparing them.
Descriptive statistics
| Measure | Function | Tells you |
|---|---|---|
| Mean | =AVERAGE(range) |
The arithmetic average |
| Median | =MEDIAN(range) |
The middle value |
| Mode | =MODE.SNGL(range) |
The most frequent value |
| Standard deviation | =STDEV.S(range) |
Typical distance from the mean, for a sample |
| Variance | =VAR.S(range) |
The standard deviation squared |
| Quartile | =QUARTILE.INC(range, 1) |
The value below which a quarter of the data falls |
| Percentile | =PERCENTILE.INC(range, 0.9) |
The value below which 90% of the data falls |
Use STDEV.S when your data is a sample from a larger group, and STDEV.P when it is the entire population.
The mean is pulled towards extreme values and the median is not. When they differ a lot, the data is skewed, and the median usually describes a typical value better.
Correlation
=CORREL(Sales[Units],Sales[Revenue])CORREL returns a value from -1 to 1. Values near 1 mean the two columns rise together, values near -1 mean one falls as the other rises, and values near 0 mean no straight-line relationship. Correlation does not show that one causes the other.
The Analysis ToolPak
The Analysis ToolPak is an add-in that produces full statistical reports. Turn it on under File > Options > Add-ins: choose Excel Add-ins in the Manage box, click Go and tick Analysis ToolPak. A Data Analysis button then appears on the Data tab.
Tools worth knowing:
- Descriptive Statistics produces mean, median, standard deviation, range and more in one table.
- Histogram counts values in bands.
- Correlation produces a matrix for several columns.
- Regression fits a line and reports how well it fits.
- t-Test compares the means of two groups.
A simple forecast
=FORECAST.LINEAR(x, known_ys, known_xs)This fits a straight line through past values and reads off the value at x. For time series with seasonality, select the dates and values and choose Data > Forecast Sheet.
You will meet these ideas again, in depth, in the statistics module of the Python course.
Practice
Find the mean and median revenue per order. Which is larger, and what does that tell you about the orders?