Forecast function

[tbeigi]

Hello,

I am trying to forecast the price by city from a data set. Here is an example of what it looks like:

[TABLE="class: cms_table, width: 1024"]
<tbody>[TR]
[TD]CITY[/TD]
[TD]6/17/2011[/TD]
[TD]12/2/2011[/TD]
[TD]2/26/2012[/TD]
[TD]4/30/2012[/TD]
[TD]8/2/2012[/TD]
[TD]11/21/2012[/TD]
[TD]1/11/2013[/TD]
[TD]2/14/2013[/TD]
[TD]5/3/2013[/TD]
[TD]7/30/2013[/TD]
[TD]11/8/2013[/TD]
[TD]1/9/2014[/TD]
[TD]2/11/2014[/TD]
[/TR]
[TR]
[TD]AIKEN, SC[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ 14.74[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[/TR]
[TR]
[TD]AKRON, OH[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$16.73[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ 12.99[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[/TR]
[TR]
[TD]ALBUQUERQUE, NM[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ 15.08[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[/TR]
</tbody>[/TABLE]


What I want to do is use this historical data to identify what the price will be (by city) in May! That means that I only want to use historical data from May (not ALL of the months). I know there is a FORECAST formula, but I don't know if that will work with the way my data sheet is organized. Any ideas?

Thank you! I appreciate your help.

 

[StephenCrump]

Sorry, you haven't given us a lot to go on here ...

Do we assume that prices in each city are independent? If so, you'll need more than one data point to forecast anything. Or can we assume that prices in each city will vary at any point in time, but will move up or down in parallel?

Your question implies that there is seasonality in the data, given you want to use only historical data for May? Is there any data for May in previous years? The date differences in the data also appear quite random, which will make it difficult to identify any seasonality?

Finally (and probably most importantly) what are these prices? If the ~20% reduction in Akron over 2012-13 is any indication, you're modelling something quite volatile, dependent on various external factors other than time, i.e. you'll need something more sophisticated than Excel's FORECAST function?

 

[tbeigi]

Hello! Thank you for your response.

Yes, the prices in each city are independent of one another. In terms of seasonality, we can ignore the fluctuation in dates and focus more on months. In this scenario, we can consider 4/30/2012 as May. Unfortunately, we only have 2 years of back data for May though.

These are prices for bids of dehydrated mashed potatoes (in the K-12 segment) that have been awarded by the USDA (meaning this is the winning price) over the course of the last 4 years. The zeroes do not mean that the price was zero - It means that there were no bids for that city in that given time period; therefore, no prices were recorded. I hope that helps!

Let me know if there are anymore questions or concerns.

Here is another example of the data I am working with:

[TABLE="width: 1024"]
<tbody>[TR]
[TD="class: xl66, width: 145"][/TD]
[TD="class: xl67, width: 68"]6/17/2011[/TD]
[TD="class: xl67, width: 68"]12/2/2011[/TD]
[TD="class: xl67, width: 68"]2/26/2012[/TD]
[TD="class: xl67, width: 68"]4/30/2012[/TD]
[TD="class: xl67, width: 64"]8/2/2012[/TD]
[TD="class: xl67, width: 75"]11/21/2012[/TD]
[TD="class: xl67, width: 68"]1/11/2013[/TD]
[TD="class: xl67, width: 68"]2/14/2013[/TD]
[TD="class: xl67, width: 64"]5/3/2013[/TD]
[TD="class: xl67, width: 68"]7/30/2013[/TD]
[TD="class: xl67, width: 68"]11/8/2013[/TD]
[TD="class: xl67, width: 64"]1/9/2014[/TD]
[TD="class: xl67, width: 68"]2/11/2014[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1024"]
<colgroup><col><col span="4"><col><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]BUFFALO, NY[/TD]
[TD] $ 18.89[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ 14.92[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]CAGUAS, PR[/TD]
[TD] $ - [/TD]
[TD] $ 23.05[/TD]
[TD] $ 21.96[/TD]
[TD] $ 21.12[/TD]
[TD] $ - [/TD]
[TD] $ 15.12[/TD]
[TD] $ 15.31[/TD]
[TD] $ - [/TD]
[TD] $15.12[/TD]
[TD] $ 13.92[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]CALDWELL, ID[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $18.20[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]CAPE GIRARDEAU, MO[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ 14.34[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
</tbody>[/TABLE]

Sorry, you haven't given us a lot to go on here ...

Do we assume that prices in each city are independent? If so, you'll need more than one data point to forecast anything. Or can we assume that prices in each city will vary at any point in time, but will move up or down in parallel?

Your question implies that there is seasonality in the data, given you want to use only historical data for May? Is there any data for May in previous years? The date differences in the data also appear quite random, which will make it difficult to identify any seasonality?

Finally (and probably most importantly) what are these prices? If the ~20% reduction in Akron over 2012-13 is any indication, you're modelling something quite volatile, dependent on various external factors other than time, i.e. you'll need something more sophisticated than Excel's FORECAST function?

 

[StephenCrump]

In very general terms, one way to produce a simple seasonally adjusted forecast is to:

1. Identify seasonal factors: Let's say for a given dataset with two years' history, we identify that in Winter, the price is 85% of the annual average, and 105% in Spring, Summer, Autumn.

2. De-seasonalise the data, i.e. divide Winter price data by 85%, and data for the other three seasons by 105%.

3. Linear regression forecast of de-seasonalised data, e.g. using Excel's FORECAST() function.

4. Re-seasonalise, i.e. multiply the forecast Winter price by 85%, other seasons by 105%.

However, in this case there are a number of complicating factors ...

For starters, we're talking commodity prices, which raises additional questions such as:

- Are these forward prices (i.e. the given dates are future delivery dates), or spot prices?
- Are you setting a forward price now for some future delivery date, or trying to estimate the expected future spot price?

And even though we're talking about standardised commodity contracts, the variation between prices in different cities suggests there are other factors at work, e.g.

- Regulatory overlays with USDA involvement, e.g. quotas, subsidies?
- Structural issues, e.g. do we expect prices for Puerto Rico always to be +$1.00 (say) to allow for additional transport costs?

In short, although I am sure you can use Excel as a tool to help you forecast prices (and there will be statistical add-ins that may also help) I think the analysis goes beyond any standard Excel built-in like the Forecast function?

 

[tbeigi]

Hello!

Thank you so much for your helpful response. I am guessing you work/have worked in the Foodservice industry as well. For this particular analysis, I need to look by month (not by season) since the bids typically come out around the same time each year (i.e. the next one is coming out in May as historical data shows.)

These are spot prices and I am trying to predict the expected future spot price for May based on what the data shows has won in the past. In terms of additional factors, we do not need to consider that at the moment for this analysis.

With these questions being answered, do you think there is a way I can forecast the future prices via Excel? I was hoping to use the FORECAST function, but the way my data is set-up does not seem to present accurate results.

In very general terms, one way to produce a simple seasonally adjusted forecast is to:

1. Identify seasonal factors: Let's say for a given dataset with two years' history, we identify that in Winter, the price is 85% of the annual average, and 105% in Spring, Summer, Autumn.

2. De-seasonalise the data, i.e. divide Winter price data by 85%, and data for the other three seasons by 105%.

3. Linear regression forecast of de-seasonalised data, e.g. using Excel's FORECAST() function.

4. Re-seasonalise, i.e. multiply the forecast Winter price by 85%, other seasons by 105%.

However, in this case there are a number of complicating factors ...

For starters, we're talking commodity prices, which raises additional questions such as:

- Are these forward prices (i.e. the given dates are future delivery dates), or spot prices?
- Are you setting a forward price now for some future delivery date, or trying to estimate the expected future spot price?

And even though we're talking about standardized commodity contracts, the variation between prices in different cities suggests there are other factors at work, e.g.

- Regulatory overlays with USDA involvement, e.g. quotas, subsidies?
- Structural issues, e.g. do we expect prices for Puerto Rico always to be +$1.00 (say) to allow for additional transport costs?

In short, although I am sure you can use Excel as a tool to help you forecast prices (and there will be statistical add-ins that may also help) I think the analysis goes beyond any standard Excel built-in like the Forecast function?