Forecasting

[Abby]

I'm attempting to forecast the amount of money spent on the purchase of
company cars for the remaining of the fiscal year. (Column 1: Costs of cars
and Column 2: Volume of cars) I've done a simple regression but the Adjusted
Square value was 80%. I dont think this would be the best way to forecast. I
created an XY scatter chart to view the relationship and it is a direct
relationship. I'm not sure which formula to use to obtain a very accurate
forecast. Any help is appreciated.

 

[Abby]

I also forgot to mention that I have about three fiscal years worth of
historical data to assist with my forecasts.

 

[Gerrie Mostert]

I want to do a scatter graph of a retailer with the correlation between sales and gross profit. how do i do it?

 

[joeu2004]

I want to do a scatter graph of a retailer with the
correlation between sales and gross profit.
how do i do it?

This is very basic, but difficult to explain click-by-click if you are
not savvy enough to figure this out for yourself. Also, the click-by-
click instructions vary depending on the version of Excel, which you
neglected to mention. My instructions are for XL2003.

Suppose sales is in D213 and gross profit is in G2:G13. Ideally,
select both columns. One way: select D213, then press and hold the
Ctrl key while you select G2:G13.

Now click the Chart Wizard icon on the toolbar. If it is not there,
click View, then click Chart.

In the first menu of the Chart Wizard, click the XY Scatter chart
type. You can also click a chart subtype. Then click Finish.

If the "curve" (imaginary or actual lines through the data) looks like
it follows a pattern, you might want to also chart a trendline.

Click the chart, point to the data points, right-click, and click Add
Trendline. In the Type tab, select one of linear, logarithm, power or
exponential, whichever you think might best fit the curve. I also
like to select Display Equation and Display R-Squared in the Options
tab. Then click OK.

Do not expect the trendline to fit the data exactly. But you are
looking for a large R-squared.

It is quite possible (likely) that none of the standard trendlines
fits the data well. All that means is: none of the standard
forecasting methods work well with your data. On to Plan B ;-).

By the way, resist the temptation to use a polynomial trendline, even
if fits the data exactly. An n-order polynomial trendline should fit n
+1 or fewer data points exactly. But it will usually be a poor
predictor of past or future data points, unless you have some reason
to believe your data has the kind of geometry.