Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast Formula help
Ok, I have read and reread the forecast function and I still have questions....
Scenario: I need to forecast a dollar value for the 2 quarters based on the last 6 quarters dollar value. Example: FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4 $2.76 $2.40 $2.07 $2.36 $2.00 $2.17 Issue: Can't figure out how to set the forumla up to forecast FY08Q3 and FY08Q4 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast Formula help
Hey Jason.
Here's what I did... see if it works for you. Insert a row somewhere near your quarter headings (I did above my version of your data). Then instead of using the year and Q#, just input 1 above FY07Q1, 2 above FY07Q2, etc. These will represent your known X values. In the cell under FY08Q3 use the formula wizard to select forecast. In the X value, place a 3 (since you are trying to find a forecasted value for FY08Q3.) For the known y's, select your current data points (2.76, 2.40, 2.07 etc). And for the known x's you'll need to select the numbers in the new row you added. Once you hit OK you'll have a value for FY08Q3. You'll need to repeat the process in FY08Q4, changing the X from 3 to 4 and then selecting your data all over again. Hope this helps. -- Just a fellow Excel user here to help when I can..... "Jason Hall" wrote: Ok, I have read and reread the forecast function and I still have questions.... Scenario: I need to forecast a dollar value for the 2 quarters based on the last 6 quarters dollar value. Example: FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4 $2.76 $2.40 $2.07 $2.36 $2.00 $2.17 Issue: Can't figure out how to set the forumla up to forecast FY08Q3 and FY08Q4 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast Formula help
I think it worked but, wouldn't FY08Q3 actually be 7 and not 3?
"Red" wrote: Hey Jason. Here's what I did... see if it works for you. Insert a row somewhere near your quarter headings (I did above my version of your data). Then instead of using the year and Q#, just input 1 above FY07Q1, 2 above FY07Q2, etc. These will represent your known X values. In the cell under FY08Q3 use the formula wizard to select forecast. In the X value, place a 3 (since you are trying to find a forecasted value for FY08Q3.) For the known y's, select your current data points (2.76, 2.40, 2.07 etc). And for the known x's you'll need to select the numbers in the new row you added. Once you hit OK you'll have a value for FY08Q3. You'll need to repeat the process in FY08Q4, changing the X from 3 to 4 and then selecting your data all over again. Hope this helps. -- Just a fellow Excel user here to help when I can..... "Jason Hall" wrote: Ok, I have read and reread the forecast function and I still have questions.... Scenario: I need to forecast a dollar value for the 2 quarters based on the last 6 quarters dollar value. Example: FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4 $2.76 $2.40 $2.07 $2.36 $2.00 $2.17 Issue: Can't figure out how to set the forumla up to forecast FY08Q3 and FY08Q4 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast Formula help
Yes is would. it would be 3 if you were only forecasting FY08 without the
FY07 historicals. Just a fellow Excel user here to help when I can..... "Jason Hall" wrote: I think it worked but, wouldn't FY08Q3 actually be 7 and not 3? "Red" wrote: Hey Jason. Here's what I did... see if it works for you. Insert a row somewhere near your quarter headings (I did above my version of your data). Then instead of using the year and Q#, just input 1 above FY07Q1, 2 above FY07Q2, etc. These will represent your known X values. In the cell under FY08Q3 use the formula wizard to select forecast. In the X value, place a 3 (since you are trying to find a forecasted value for FY08Q3.) For the known y's, select your current data points (2.76, 2.40, 2.07 etc). And for the known x's you'll need to select the numbers in the new row you added. Once you hit OK you'll have a value for FY08Q3. You'll need to repeat the process in FY08Q4, changing the X from 3 to 4 and then selecting your data all over again. Hope this helps. -- Just a fellow Excel user here to help when I can..... "Jason Hall" wrote: Ok, I have read and reread the forecast function and I still have questions.... Scenario: I need to forecast a dollar value for the 2 quarters based on the last 6 quarters dollar value. Example: FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4 $2.76 $2.40 $2.07 $2.36 $2.00 $2.17 Issue: Can't figure out how to set the forumla up to forecast FY08Q3 and FY08Q4 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast Formula help
"Jason Hall" wrote:
I need to forecast a dollar value for the 2 quarters based on the last 6 quarters dollar value. Example: FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4 $2.76 $2.40 $2.07 $2.36 $2.00 $2.17 Before you use any Excel function, it is important first to graph the data that you have. For example, FORECAST assumes a linear trend. If you graph your data, I think you will see that is not a likely assumption. You can graph the data quickly by simply selecting your data and using the Chart Wizard on the standard toolbar to plot an XY chart. In fact, if you select a linear trendline and extend it forward 2 units, you will see the result that FORECAST gives you. You be the judge as to whether or not the fits your data. (Note that the RSQ value is around 55%. A good fit would be 85% or higher.) What I see might be a decaying sinusoidal curve. I don't know of an Excel function that plots a trendline for such a curve. Alternatively, the data reflects a "sideways" trend (a flat horizontal line). Caveat: Don't be fooled by selecting a 5- or 6-degree ploymomial trendline. Of course, that fits you data exactly due to a mathematical principle. If you extend the curve forward 2 units, you will see that it's misleading. Moreover, my comments assume a sequential time series of the data. You have not said anything one way or another to justify that assumption. Perhaps the trend to look for year-over-year per quarter. But IMHO, you do not provide sufficient information to project such a trend. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast Formula help
I too am at a loss regarding this forecasting thing. Where does the 3 or 7
come from? According to what I read in the Excel Help: X is the data point for which you want to predict a value. Known_y's is the dependent array or range of data. Known_x's is the independent array or range of data. I understand the Known-y's and Known-x's, but is the plain old x and where does it come from? How would you know which to use. I'd appreciate some assistance on this. Thanks "Red" wrote: Yes is would. it would be 3 if you were only forecasting FY08 without the FY07 historicals. Just a fellow Excel user here to help when I can..... "Jason Hall" wrote: I think it worked but, wouldn't FY08Q3 actually be 7 and not 3? "Red" wrote: Hey Jason. Here's what I did... see if it works for you. Insert a row somewhere near your quarter headings (I did above my version of your data). Then instead of using the year and Q#, just input 1 above FY07Q1, 2 above FY07Q2, etc. These will represent your known X values. In the cell under FY08Q3 use the formula wizard to select forecast. In the X value, place a 3 (since you are trying to find a forecasted value for FY08Q3.) For the known y's, select your current data points (2.76, 2.40, 2.07 etc). And for the known x's you'll need to select the numbers in the new row you added. Once you hit OK you'll have a value for FY08Q3. You'll need to repeat the process in FY08Q4, changing the X from 3 to 4 and then selecting your data all over again. Hope this helps. -- Just a fellow Excel user here to help when I can..... "Jason Hall" wrote: Ok, I have read and reread the forecast function and I still have questions.... Scenario: I need to forecast a dollar value for the 2 quarters based on the last 6 quarters dollar value. Example: FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4 $2.76 $2.40 $2.07 $2.36 $2.00 $2.17 Issue: Can't figure out how to set the forumla up to forecast FY08Q3 and FY08Q4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with formula to forecast revenue | Excel Discussion (Misc queries) | |||
Formula-Compound Sales Forecast | Excel Discussion (Misc queries) | |||
forecast formula question | Excel Worksheet Functions | |||
I can't get the forecast formula to work? | Excel Worksheet Functions | |||
Forecast Formula | Excel Discussion (Misc queries) |