Excel Forecasting

Hello everybody,

I'm new to this forum and this is my first post. I hope I'm in the right forum. I'm working on my thesis and just ran into a road block at the very end. The issue is that I have a set of variables to forecast the change in cost and duration in a project at different phases. I was able to graph the data and fitted an exponential trendline for the values. However, when I want to validate the data, the result was way much larger than the the data sample. I need some help. I have attached the data. I have attached the data I'm working with. I purposefully left the graph. But if you wish, can can delete the graph. If you hover over the equation, you'll see which phase or graph the equation refers to. I want to validate the equation by selecting a number arbitrarily and get a result close to what I'll get if reading off the values from the graph.

Originally Posted by benchiko1

The issue is that I have a set of variables to forecast the change in cost and duration in a project at different phases. I was able to graph the data and fitted an exponential trendline for the values. However, when I want to validate the data, the result was way much larger than the the data sample. I need some help.

First, note that RSQ is very small in all cases. That means the trendlines are a poor fit in the first place.

Second, I don't see any formulas that demonstrate how you "validate the data" and see that the result is very different from the sample data. I suspect you are translating the formula as it appears in the chart. For example, for Engr %Complete: =42.046*EXP(-0.005*A2).

The problem is: the constants are rounded to very low precision. At the very least, you should format the trendline to display all constants with 15 significant digits. Click on the trendline formula, right-click and click on Format Trendline Label, click on the Number tab, then click on Scientific and enter 14 into the Decimal Places field. Then copy-and-paste each constant into your Excel formula or into a row of cells, as you would for LINEST (below).

Usually, it is better to use LINEST to generate the coefficients in Excel directly. However, the form of the LINEST formula depends on the trendline. I suspect an exponential trendline is not the best one to use in all cases. Refer to http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas for the various forms of LINEST to use. However, instead of calculating each coefficient separately, select multiple cells in a row and array-enter the LINEST formula one time. Then use SUMPRODUCT or SERIESSUM to create the formula to calculated estimated-y for each data point.

Even so, at best that should duplicate the chart trendline very closely, not your actual data. Again, since RSQ is very low, there is not a good correlation between the trendline and actual data, as your chart demonstrates.

Thanks Joeu2004 for your quick response.

However, instead of calculating each coefficient separately, select multiple cells in a row and array-enter the LINEST formula one time. Then use SUMPRODUCT or SERIESSUM to create the formula to calculated estimated-y for each data point.

Can you elaborate on steps to use the SUMPRODUCT or SERIESUM. I've attached the method I used for the validation for the Bid phase.

Thanks MrShorty, I tried using the scatter plot and was getting values very close to the trendline values. I think that will work for now. But if there is additional suggestions to make the result even better, I'm all for it. Otherwise thanks everyone that contributed.

Originally Posted by benchiko1

Can you elaborate on steps to use the SUMPRODUCT or SERIESUM.

A misdirection. Sorry. I had something else in mind. Looks like MrShorty identified the key problem. But it is also important to use coefficients with sufficient precision. I notice you used Number with 15 decimal places in your second attachment. Generally, that is not as flexible as Scientific with 14 decimal places, as I suggested. But it might be sufficient for these particulars curves.

Thank you very much. I'll change the decimal to 14 places.