What will a tonne of steel cost you next month?

Twenty-four months of a structural steel price that rose, fell and rose again. At one damping setting the forecast is $1,501 a tonne and at another it is $1,422, and on a 400-tonne package that setting is worth $31,632.

Construction Starter Forecasting free

Install free How forecasting works here

After you install, this is the model to open.

Where Is This Material Price Going?

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

Next month, damping 0.3: $1,501 a tonne, tracking both turns
Same data, damping 0.8: $1,422 $31,632 apart on a 400-tonne package
Standard error: 52.3 vs 128.8 the responsive setting wins on this series

Twenty-four months of a structural steel price that went up, came back down and went up again, which is the ordinary life of a commodity and the reason nobody trusts a straight line through it. Click Run at a damping factor of 0.3: the smoothed line follows the series through both turns and the final value, $1,500.79 a tonne, is the forecast for the next month.

Change damping to 0.8 and run again: the forecast comes back at $1,421.71. The block beside the series prices that difference at $31,632 on a 400-tonne package, from one setting in one box. Now watch what the two settings did at the bottom of the fall, the month the price hit $1,218. Read the forecast the report printed against that month: at damping 0.3 it was $1,265.73, which is $47.73 high, and at damping 0.8 it was $1,327.42, which is $109.42 high and had barely noticed the fall at all.

A high damping factor is a long memory, and a long memory is exactly wrong on a series with real turning points in it. The standard error agrees: the report ends at 52.25 for damping 0.3 against 128.83 for damping 0.8. So on this data the responsive setting is the right one and the smooth one is a picture of last year. Do not read that as a rule.

On a genuinely noisy series with no turns, high damping is right and low damping chases noise straight into your bid. The way to decide is the standard error the report prints, not which line looks nicer. What no setting can do is see the next mill outage. The forecast is a statement about momentum, and the event column is a reminder that momentum is not what moves this price: the two biggest moves in two years both have a cause written beside them and neither was in the numbers beforehand.

Price a fixed quantity forward whenever the exposure is worth more than the spread between these two forecasts. To use your own material, paste the price history in month order into the price column and widen the input range to match.

The model

It arrives on a tab called Template: Where Is This Price Going, carrying these columns:

Month
Structural steel ($/tonne)
Change on the month
What the setting is worth

with the model computed beside the data:

Difference on the package ($)	31,632
Mill outage	67
Import quota lifted	-28

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

Every model like this one, and the method behind them: Forecasting in Google Sheets.

Install free All 379 templates