Forecasting Using Historical Seasonally Effected Non-linear Data
Jan 15, 2010
Forecasting using historical seasonally effected data
I organize my thoughts in numbers so it will be easier to understand.
[1] Say I have this data from January 2009 to December 2009:
1-4-7-10-13-14-15-16-18-20-22-25
As you can see, the growth is not linear.
[2] So then, I start with this data point for Janaury 2010.
26-a-b-c-d-f-g-h-i-j-k-l
[3] I now want to forecast that data point for the rest of 2009...using the growth in [1]. SO i guess that's an easy task right? Take the percentage change from Jan 08 and Feb 08...and apply that to the 26...and so on. (am i correct here at least).
[4] But lets say we have a GOAL. So, this become a forecast/plan. In December 2010, i want to grow double the amount of that in December 09. So 25 x 2 = 50. Now, the "known" data is like this:
Have to forecast/create a trend for the current year based on the data available for last two years. Below is the format of data. The current year data is kept blank as the data needs to be filled based on forecasting/ trending.
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that will move data by date from an (Entry) sheet to a (Historical) sheet. I want to enter a date in B3 on the (Entry) sheet. I then enter data into C3. What I would like to happen is when the data is entered into C3 the sheet goes and finds the same date that I entered in B3 and copies that data from C3 into Column E of the (Historical) sheet.
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
I have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007
I am looking for this one function i need for a school project on historical stock prices in excel.....basically i need a way to perform this function:
In row 1, i have 30 RANDOM historical dates, starting in B1 and going right In column A, i have 10 stock symbols, starting in A2 and going down
Is there a way to automatically grab the ADJUSTED CLOSING PRICE for each symbol for each corresponding date?? if not i have to do it manually!!
How to loop a macro which downloads historical stock information into Excel. I had the macro working for a single sheet, but I want it to update all of the sheets in the work book except a few. Now I keep getting a Error # 1004 unable to open error on line .Refresh BackgroundQuery:=False
Code:
Sub GetData() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ' Exclude certain sheets If ws.Name "Porfolio" Or ws.Name "Benchmark" Then
[Code]....
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A5")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With
If I have a database of 1000 records that are logged and counted as to which of these records are successful and unsuccessful sales,
can excel be programmed to give an accurate forecast of what will be the number/ percentage of successful sales records over the next 100, 50, 30 and 10 new record entry ranges, using the database history?, then which of the new records within each selected range are most likely to be successful sales using the database history?
If so, what components or data references are needed in a worksheet to meet these requirements to allow for creating an accurate forecasting application within excel?
I am trying to come up with the appropriate formula that can be used for possible forecasting purposes. Countif proves to be futile due to more than one criteria that needs to be met. Sumproduct seems to be more feasible but I am unable to get the correct formula or maybe I am wrong.
Here's the objective...I need to be able to plot the total number of people working on a project on a daily basis - # of people working on a Monday, Tuesday and so forth by means of referring to start and end date column..I am open to revisions so as to streamline everything.
I do forecasting for my company. We have a forecast every quarter. Any good templates (simple or complex) for presenting different quarters (or time periods) and the actual results for management?
I have a base model for site openings and opening schedule. I need to build a 5 yr model which reflects the base model for each opening. I have matched the month in the model to the opening month but how do I get the base model subsequent months to follow the opening month?
I thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...
I have historical sales for each month from the last 3 years. I would like to set up a seasonality adjustment for forecasting. I know that it won't be perfect and some may even suggest using regression, but I'd rather not. I'd like to do the following:
In a control sheet, list the months in chronological order beginning in cell A1; January, A2, February; etc., etc. through December.
Next, below each month, I'd like to have a factor. For example, in B1 beneath January, I'd like to be able to plug in 75%. This would say that for each January going forward, that it is 75% of the annual historical average for all periods. Whereas, say in June, it is 140%.
how I would go about setting up a spreadsheet that will allow me to look at previous orders and forecast what I need to buy. He never got into trouble for ordering too much, but he did get caught on ordering too little. I am thinking something along the lines of the attached. However, if anyone is using something that looks or works better (or those who solve this little problem have done a better looking or working sheet) let me know.
There will be some Vlookups that need to bring in description and price (or some other function if there is a better way to do it). The min need and max need will be populated and brought in by vlookup. The order column should be where the forecasting happens based on previous orders. I am willing to have all the data in the same workbook. The second or third sheet can be used to store data in what ever format that is needed to be done.
I was handed the attached file. understand everything except how the values in row 6 were derived No formula present when I received the file, just the numbers. Row 7 is hard entered scores the units achieved
need an excel simple formula or function that returns values of y for given x in a two-dimensional array and works in the following way.actually a combination of linear interpolation and lookup/linear search. as in following example
x y 1 10 2 15 3 20
if x=1.5 then y=12.5 (answer available thru FORECAST but incomplete) if x=2 then y=15 (answer available thru LOOKUP but incomplete)
so for whatever the x i need/get a valid y thru a single formula/method ! sorry m not good in math,
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Any (simple) source code allowing me to conduct a regression with three input parameters?
I have 3 equations and have created the relevant chart from them. What I would like to do is put in a value of x & y and get result z. Having done a search through previous threads I found some BiLinear Interpolation code that works using the table in a workbook, but what I'd really like is the Interpolation of the formulae rather than the resulting table and for it to be embedded in VBA.
I am using the Forecast formula to give me a value from a Linear trend. However I need to limit the resulting value that is displayed within a min and max value. Background: This is to allow me to calculate the amount of bonus someone will earn depending on the Percentages of their target they achieve. The min bonus is 25% when 80% of target is achieved and max bonus is 100% when 125% or greater of target is achieved. So the Forecast formular works great using the existing values, but i need help limiting the results to between 25% and 100%. I was trying to use a sumif formula to copy the resulting cell but only if the value fell between the 25 and 100%. I've attached the spreadsheet as I think it will highlight what i'm trying to do better than I can explain it.
In the attached sheet, I have measured data from a device. It shows Input, Output, gain and delta. You can see as input increases, delta comes down. I want to find the output corresponding to delta od -1. In the table, I have enteries for delta of -0.982 then -1.382, not for -1. How to find output corresponding to delta of -1?
I need a formula to calculate a linear sliding scale from 10%-30% (no higher, no lower). Based on the data in the below chart, if the GROR (let's say this is known and the result resides in cell A1) is less than or equal to 0.0% the payout % is 10.0% and gradually increases as the GROR increases.
The payout % is capped at 30% which is if GROR is 18% or greater. Below are just some examples of how certain thresholds should calculate.
I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.
The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.
