Which of your cost lines are really one risk?
Twenty-four months of four cost lines, run through covariance and then through correlation. The largest covariance in the matrix belongs to the second-strongest relationship, and the reason is a lesson worth having before you build a risk model.
Finance Advanced Statistics free
After you install, this is the model to open.
Which Two Costs Move Together?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Covariance crowns
- 399.9 freight and agency labor, the biggest entry
- Correlation crowns
- 0.988 diesel and freight, the pair that move as one
- Why they disagree
- 1,200.8 freight's variance against diesel's 44.9: scale, not strength
- Into the risk model
- 0.99 link diesel and freight; packaging at 0.18 stays free
Twenty-four months, four cost lines. Click Run: the covariance matrix comes back with freight and agency labor at 399.9, freight and diesel at 229.5, agency labor and diesel at 75.1, and packaging barely engaging with anything, its largest pairing being 38.1 against agency labor. Read on those numbers alone and freight against agency labor is the strongest relationship on the sheet.
It is not. Now run the identical range through Correlation and the order changes: diesel and freight come back at 0.988, freight and agency labor at 0.789, diesel and agency labor at 0.766, and packaging tops out at 0.554. Diesel and freight are the pair that move as one, and covariance ranked them second because covariance carries the units of both columns.
Freight is the biggest number on the sheet, averaging $259.8K a month against diesel's $47.1K, so every pairing that includes freight is inflated by freight's own scale. The diagonal makes it obvious: freight's variance is 1,200.8 and diesel's is 44.9, and the diagonal of a covariance matrix is nothing more than each column's variance against itself.
Covariance answers whether two things move together and in which direction. Correlation answers how tightly, by dividing the scale back out. Use covariance where the units matter to you, for instance in portfolio arithmetic where variances add. Use correlation when you are choosing which pair to link. That choice has a direct consequence in the Risk panel, which takes correlation coefficients between simulation inputs: the pair to enter there is diesel and freight at 0.99, and modeling those two as independent would understate the tail of any total-cost distribution, because the months when both are high are the months that break a budget.
Packaging against diesel at 0.18 can be left independent with a clear conscience. Two limits worth stating. Both matrices measure straight-line agreement only, so a cost that rises with volume up to a contracted break and then flattens will read as a weaker relationship than it is. And twenty-four months is two winters, which is enough to see a seasonal pair travel together and not enough to tell a seasonal pair from a causal one. To use your own ledger, paste one month per row with numeric columns only, and widen the range.
The model
It arrives on a tab called Template: Which Costs Move Together, carrying these columns:
- Diesel ($K)
- Freight ($K)
- Packaging ($K)
- Agency labor ($K)
- Month
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- What does half a point of fee cost the investor?What Fee Does the Fund Need to Clear the Hurdle?
- What gross multiple does the hurdle actually demand?Will the Fund Clear Its Hurdle?
- What does your target return cost you in risk?The Cheapest Way to Hit the Target Return
- Which fund risks are worth paying to cover?What Could Go Wrong in the Fund?
- Simulate five portfolio positions, each with a chance of not surviving the year and a value tied to the same funding market, to see the downside range. Free.What Does a Bad Year Look Like?
- Is that margin gap real or just a wider spread?Do Our Two Service Lines Earn the Same Margin?
Every model like this one, and the method behind them: Statistics in Google Sheets.