# 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:

26-a-b-c-d-f-g-h-i-j-k-50

So how do i do this now?

View 9 Replies
ADVERTISEMENT
Nov 21, 2011

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.

30-Apr-0931-May-0930-Jun-0931-Jul-098/31/20099/30/200910/31/200911/30/200912/31/20091/31/2010

2/28/20103/31/20106075443580901511256025304030-Apr-1031-May-1030-Jun-1031-Jul-108/31/20109/30/2010

10/31/201011/30/201012/31/20101/31/20112/28/20113/31/2011100150756070859611045663544

30-Apr-1131-May-1130-Jun-1131-Jul-1131-Aug-1130-Sep-1131-Oct-1130-Nov-1131-Dec-1131-Jan-1228-Feb-1230-Mar-12

View 3 Replies
View Related
Jul 7, 2008

I always use manual calculation with Excel, does it affect the calculation within a Macro when I run it?

View 9 Replies
View Related
Mar 8, 2008

in this example below i need to work out what the divsor would be for values inbetween the data?

View 9 Replies
View Related
Apr 17, 2014

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.

Test3.xlsm

View 5 Replies
View Related
Mar 14, 2014

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.

View 2 Replies
View Related
Mar 2, 2009

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

View 14 Replies
View Related
Jul 24, 2008

how do i download historical stock prices for approx a hundred stocks i.e what code would suffice?

View 9 Replies
View Related
Jul 10, 2012

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!!

View 3 Replies
View Related
Feb 21, 2012

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

[Code]....

View 1 Replies
View Related
Sep 14, 2006

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?

View 9 Replies
View Related
Apr 2, 2009

I'm trying to figure out how to do seasonal forecasting based on volumes. I tried to use FORECAST but the numbers are not accurate.

I have actual data to work with to come up with my forecasting on a monthly basis. Our yearly calender is from Nov 08 to Oct 09.

Here's a sample of the data....

View 12 Replies
View Related
Jul 15, 2009

I have a row of totals in a spreadsheet and I want to calculate a forecasted total based on the previous month's totals.

For example I have two months and I want to know how to calculate the forecasted Jun 09 total: ...

View 10 Replies
View Related
Oct 19, 2009

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.

View 5 Replies
View Related
Dec 27, 2013

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?

View 5 Replies
View Related
Jun 19, 2008

I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).

I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2

>D2),(B2-C2)*F6+F2,IF((B2

View 9 Replies
View Related
Dec 8, 2008

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?

View 10 Replies
View Related
Sep 21, 2013

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...

How to modify the code a bit...

VB:

Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown

I tried to change the code, but it did not worked.

View 8 Replies
View Related
Jul 16, 2007

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%.

View 9 Replies
View Related
Apr 11, 2007

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.

View 8 Replies
View Related
Jul 21, 2006

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

View 8 Replies
View Related
Feb 10, 2007

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,

View 9 Replies
View Related
Oct 16, 2008

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?

View 7 Replies
View Related
Mar 29, 2013

I have to convert the first to the second program, but i don't know how to do this:

MAD.png

ABStolinear.xlsx: the first sheet is the first program which is correct (i think), the second sheet is a failed attempt to write the second program

View 5 Replies
View Related
Feb 10, 2009

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.

View 6 Replies
View Related
Jan 4, 2007

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.

View 2 Replies
View Related
Oct 4, 2007

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?

View 6 Replies
View Related
Oct 9, 2008

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.

GROR 0.0%5.0% 8.0% 11.0% 14.0% 18.0%+

payout %10.0%10.0%14.6%19.2%23.8%30.0%

View 2 Replies
View Related
Jan 27, 2013

If I have a set of numbers 1,2,3,4,5,6,7,8,9 in cells, how can I get a line of best fit, linear regression, in the cells next to them?

View 1 Replies
View Related
Nov 25, 2006

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.

View 9 Replies
View Related