# Faith in an extrapolated result

I would like to be able to predict when I will exhaust a particular resource.

My situation is analogous* to a water tank. Each day zero or more rain will fall, filling up the tank. I can not tell in advance how much rain will fall each day, if any at all. There will never be any outflow from the tank. Each day I can measure how much is in the tank and I know that tank's capacity. Since there is a cost to having spare tanks on hand, just in case, and also to filling a tank when no spare is available, I'd like to use my daily measurements to predict when the tank will be full.

Experience so far suggests the tank has about four year's capacity. It will take us something like three to six months to get a new tanks set up and working depending on what else is going on. It is something we want to plan for in advance. Of course emergency tanks can be rushed through if a deluge is imminent, but the cost is huge and this should be avoided.

I've calculated a linear regression of the daily water levels. This gives me a slope and intercept from which I can calculate the date when the trend line will cross the tank's capacity. This gives me no indication of the faith I can place in this number, however. Is it legitimate to simply use the Confidence Interval formula to similarly calculate when the CI curve crosses the capacity? Is there a better way of achieving this?

The data will look like this (invented values): *The actual case is a data warehouse computer system. The rows have an auto-incrementing column. As a new row is written the value in this column goes up. It never goes down. There is no equivalent of "draining" the water-tank analogy. If I measure the largest value in this column today, and no rows are added overnight, I will get the same vlaue when I ask tomorrow. If rows were written overnight, tomorrow's measurement will be larger than today's. Even if all rows are removed the auto-increment will not reset to zero.

The column is defined as an integer which has a maximum value of just over two billion. If we reach this limit we cannot add rows to the table, we have system down time, my boss's head explodes, the oceans turn to blood etc. We don't want that to happen. The disks storing this data cost tens of thousands of dollars, however, so we don't want to have the capital outlay any earlier than we have to, obviously. The definition of "have to" is the thrust of my question.

Appendix: My results

I have been able to re-create what my data would have been for the years 2012/13 and 2013/14. One typical sample set looks like this. Blue and red are the two years' data. Black and dashed are the least-squares regression for each year.

I used 2012/13's data to predict the value at the end of 2013/14. The trend line through the first year is a little steeper than that through the second so I would expect my answer to be a little earlier than what actually occurred. I used three techniques:

1. Simple geometry. Take the first and last point of the sample. Draw a straight line through them. Calculate the date this line cuts the upper limit.
2. Calculate a line's parameters from a least squares regression. Use these parameters to calculate the date this line cuts the upper limit.
3. Use 2012/13's data as the sample set for a Monte Carlo simulation.

Simple geometry was very fast, easy to code and understand.

Least squares was difficult to get right but runs quite quickly. The result is closer to the "real" value, but not by much.

Monte Carlo is easy to code and maintain. It takes a long time to run, however. I take the median-crossing date as the date of resource exhaustion (where green crosses red in this chart): All three technique produced an answer within three weeks of the "actual" result, which is good enough for my purposes.

In terms of the original question, Monte Carlo is the only technique that indicates how likely I am to run out of resource sooner than predicted. Because it takes so long to execute I will be adopting a hybrid approach. I will run the simple geometric calculation each day as data arrives. If this indicates an exhaustion date which would be of concern (less than one year in the future) I will run a Monte Carlo to see how bad the situation is.

• What are you regressing exactly? $\text{Netflow} \sim \text{What?}$ – CarrKnight Jul 8 '14 at 11:06
• Can there be much faith in it unless you also have a model for the rainfall process? – Glen_b Jul 8 '14 at 11:20
• @CarrKnight - I'm regressing water level i.e. my daily measurements. – Michael Green Jul 8 '14 at 11:25
• @Glen_b - I'm no statistician, I don't understand your question. I'm math-literate so if you can suggest a reference which would explain I can try to work through that. – Michael Green Jul 8 '14 at 11:27
• It's not really a statistical issue, but a matter of simple logic. Rainfall is not a constant process but an intermittent one, plus there's seasonality, and dependence over time (it's more likely to rain today if it rained yesterday than if it didn't rain yesterday). So prediction of the time when the tank might be full or empty would have to take account not just of the (linear-ish*) trend in use but also the way the rainfall works. $\,$ *(of course usage is also seasonal, and somewhat dependent over time) – Glen_b Jul 8 '14 at 11:35

$\text{Netflow} \sim\alpha + \epsilon$
Intercept only. The $\alpha$ will be your average daily netflow.

Now, let's say that you want to know when will the data storage fill up? Simulate. Data storage will follow this formula:
$Y_{t+1} \sim Y_t + \alpha + \epsilon_t$
You can simulate a path that your data storage will follow by sampling a bunch of $\epsilon$ (either assuming they are normal or resampling bootstrap-like from the residuals).

Simulate a 1000 paths. Each of them will have a simulated day of when the storage is full. Use this distribution of days to manage the overfilling risk. You can also update this distibution daily by redoing regression and simulation as more data comes up.

• I'm reading your suggestion as: say I have 40 weeks of data I should calculate the regression using all 40 week's measurements and from that the exhaustion date. Then calculate it again using only weeks 1 through 39, and again for 1 through 38 and so forth. Then I calculate confidence intervals on all the exhaustion dates calculated above. Did I understand you correctly? – Michael Green Jul 9 '14 at 11:23
• You have 40 weeks of data? Run the regression on week 1 to 39, then see how well that regression predicts the real week 40. Then fit a new regression from week 1 to week 38, see how well it predicts week 39. And so on. Do it a bunch of times and you can gleam what the prediction error will look like. – CarrKnight Jul 9 '14 at 13:30
• @MichaelGreen changed my answer entirely. Do you feel you can implement it? – CarrKnight Jul 11 '14 at 1:10
• I'll be able to implement that. Not sure if the processing overhead is managable. It'll take a while to work through, though. – Michael Green Jul 16 '14 at 9:55

To expand on my comments, a typical simple "level of dam" or "level of tank" type model might produce results that look vaguely something like this, say: It's not really appropriate to fit a linear regression to this kind of process, for a variety of reasons (not least, dependence and barrier effects). You need a model that more reasonably describes the process, and will measure the uncertanties appropriately.

I wouldn't have much faith in the predictions of a model that really doesn't describe what's going on. With a good model, you can at least use simulation to get some idea of when the tank may be full or unlikely to be full.

Stochastic models of this kind are included under stochastic fluid models, what used to be called 'dam models', but they're also used in computing-related applications and ruin theory in insurance. The books on ruin theory that don't necessarily make much reference to the other literature. In ruin theory the process is "flipped" relative to a dam-type model - increases are continuous and close to linear over time (premiums), and the decreases are jumps (claims); for dam models the jumps are increases and decreases more continuous.