Forecasting Based On Two Years Of Data
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
ADVERTISEMENT
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
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
View Related
Aug 28, 2007
I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies
View Related
Mar 28, 2009
Right now, in a column I would like to display an number (length of employment) based on the hire date.
In one cell the employee's hire date is entered. In a column of other cells the pay period ending date is displayed and in another column the length of employment is displayed.
Example:
03/17/2000
03/01/2009 8
03/15/2009 8
03/29/2009 9
04/12/2009 9
04/26/2009 9
How would I create a formula in the length of employment cells that would indicate the correct number of years based on the hire date and adjusts when the pay period date passes this hire date?
View 2 Replies
View Related
Dec 17, 2013
I have a list of 2000 pieces of inventory. Only the year of purchase is associated with each one, not a specific month or day. I'm trying to apply conditional formatting so that...
Less than 2 years old = green
Between 2 and 4 years old = yellow
Between 4 and 6 years old = orange
More than 6 years old = red
I've been trying =TODAY()-365 (365 replaced by number of days in the above named ranges) but the cell only changes to the color of the most recently applied formatting rule.
View 2 Replies
View Related
May 28, 2014
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
Book10.xlsx
View 4 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
Jul 6, 2014
I need to create a macro in excel that will take data for a particular year in vertical rows and transpose it to horizontal rows.
What the data originally looks like:
380204198401014.72
380204198402016.34
380204198403015.66
380204198404013.71
380204198405013.86
380204198406013.51
[Code] ........
What I want the transposed data to look like:
StationYearJanFebMarAprMayJunJulAugSepOctNovDec
38020419844.726.345.663.713.863.5113.69 3.611.362.812.522.25
38020419853.194.211.021.292.372.416.082.500.143.196.122.04
I have never written a macro before and have tons of records (over 40,000) so using this will save me a lot of time.
View 5 Replies
View Related
Aug 14, 2012
Is there any way to select specify month of the many years of data with any function?
View 7 Replies
View Related
Apr 17, 2007
I'm wondering if it's possible to do the following using Pivot tables. I have month period from 1-1-2006 until today by month. Is it possible to have in the pivot table a field that shows the total of 2006 and then another field that shows the total of 2007?
View 4 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
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
Aug 11, 2013
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
View 1 Replies
View Related
Feb 19, 2014
I have a blank file and a data file for current year with formulas.
So I am trying to take a couple of tabs from current year data file and pasting them into the blank file.
My question when is do that all the formulas get referenced to the current years file. i want to keep the same formulas but reference the tabs on blank file.
Is there a quicker way to do this rather and going into all the cells and changing reference from current years files data to blank files data.
View 3 Replies
View Related
Nov 4, 2008
I am working on a retirement calculation sheet. For retirement calculations the employees need to figure out their highest yearly income for three out of their last ten years. The three years can be based on any consecutive twelve months but each of the three years must be the same months, (example, Jan thru Dec, or July thru June). The three years cannot overlap.
Here is what I have so far. Column A, paydate. Column B, amount. Column C, year to date amount. Column D, amount for past 12 months.
I would like to list the three highest years in the first 3 rows of Column E.
Any help is greatly appreciated.
Pay Date Gross Year to Date Gross
Past 12 Months Highest 3 Years 08/10/06 3,594.58 3,594.58 3,594.58
08/24/06 3,332.79 6,927.37 6,927.37
09/07/06 2,595.59 9,522.96 9,522.96
09/21/06 2,457.36 11,980.32 11,980.32
10/05/06 2,457.36 14,437.68 14,437.68
10/19/06 2,457.36 16,895.04 16,895.04
11/02/06 6,212.51 23,107.55 23,107.55
11/16/06 3,206.09 26,313.64 26,313.64
11/30/06 4,104.56 30,418.20 30,418.20........
View 9 Replies
View Related
Apr 21, 2009
how to change years in a row after i put in a year in K i have add the file
View 2 Replies
View Related
Jan 14, 2009
I need to calculate YTD sums using the data that spans three years. I am using
SUM(B2:INDEX(B2:AC2,MATCH($B$10,$B$1:$AC$1,0))). This works fine if all of my data is contained in one year. However, when my data spans multiple years it does not. For example, if I have the following and today is April 2007 I want to calculate Jan-April 2007.
Labor Type09/0610/0611/0612/0601/0702/0703/0704/0705/0706/0707/0708/0709/0710/0711/0712/0701/08
Bus External2404526907569511,2018808059439237781,1216191,0639378001,156
Bus Internal1,4412,7942,9992,5914,0093,9444,2714,3254,7593,5493,0172,4371,9262,9722,3711,7582,931
IT External1,3483,3993,8653,8586,0247,75510,11110,40911,86911,42311,29710,4878,32713,13412,96912,41917,265
IT Internal3,3537,7428,7107,88412,73112,74614,91114,66215,30413,80513,13511,8248,63511,7728,8876,80911,283
Staff Aug12138195169195408796641626340403428469675604395637
View 9 Replies
View Related
Feb 10, 2009
I have 2 dates..
09-feb-09
15-dec-09
Is therea way to calculate the number of years? this is equivalent to 0.27777 years.
Is there a way to get this with the datedif or another functiom?
View 9 Replies
View Related
Dec 15, 2008
I have list of dates:
e.g: D/M/Y
1/03/1997
22/05/2005
13/09/1945
I want a new list that just shows the year and is formatted as a number
e.g:
1997
2005
1945
Is there a way of doing this without doing it manually, I have 20,000 observations.
View 9 Replies
View Related
Aug 7, 2009
The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference
View 2 Replies
View Related
Mar 25, 2009
i am trying to make and age table to see if a player is too old by years, months and days all i can do is Years i need something like the following
1/10/2009 cut off date birthday is 8/5/1991
all i can do is the following
=YEAR(A3)-YEAR(A2)
View 2 Replies
View Related
Oct 14, 2009
Please refer to the attached screenshot of my working spreadsheet.
I'm attempting to concatenate lines 86 and 87, month and year together.
The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"
View 4 Replies
View Related
Dec 15, 2008
I have two dates, one is a start date the other is todays date, I want to subtract the start date from todays date and show the number of years. But a small twist is I only want to take the years away from each other, ignore day/month. Start 01/05/2000 todays date 01/10/2008 years = 8. Start 10/10/2000 todays date 01/10/2008 years = 7, want it to still show 8 years and ignore the start day/month.
View 2 Replies
View Related
