SEASONALITY Based On Names Of Months In Forecasting Model
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%.
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.
I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.
The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.
The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.
We are trying to work out a pricing model based on how many units we will produce. In production the price per unit will be really high when you produce a low quanity, and the price per unit will be lower with the more units you produce (but never reach zero). What we are trying to accomplish is to create a formula to build a pricing structure that accounts for this curve without having to price every possible scenario.
For example, if we have the following data where for 1 unit we charge $3 per unit to produce, for 5 units we charge $2 per units to produce, etc...:
1 units @ $3 per unit 5 units @ $2 per unit 10 units @ $1 per unit
What I need to figure out is how much it would cost per unit if we were to produce 3 units. I'm guessing it would be somewhere around the $2.40 mark give or take. Or say 8 units, would be something like $1.50 per unit.
Is there a formula that can calculate this? I've tried thinking of it as a moving average, or prorating the pricing.
I have been conducting seasonal characteristics of the availability of certain fruits and vegetables in the region I am in (Italy), now I want to compare the data from my first seasonal characteristics region (Texas). My goal is to see trends, view characteristics of climate in that given month in an easy view.
I have data for both lists containing over 50 vegetables / fruits each. Along with different side notes that show factors of each month like climate, availability, flavor profiles, and cooking techniques.
Is it possible to put it into format showing a 12 month period, (jan, feb, mar,......) and then putting the type of fruit/vegetable to see trends? along with adding the side notes...
I have the catalog #s of some products in column A, and I have the brand in column B, Product name in Column C, and the model # in Column D. I want to create a formula that if I select the catalog # it should autopopulate the brand in column B, product name in column C and the model # in column D!!
And I have the brand/product & model # in a different sheet!!
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
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'm attempting to sum figures based on the current date. I have a month that's returned from a today function lookup in column B2 (currently 'October').
In F17:Q18, I have the following: F April G May H June I July J August
[Code] .....
I need to be able to look up today's month (in B2), and sum the row up to that point (so for October, sum Q18:L18). When it ticks into the next month, I want the sum to automatically update to sum to column M
I'm struggling with a SUMIF, my current one sums everything in the row and ignores my "<=B2"
I also tried (MONTH(TODAY()) with corresponding month numbers (Jan 1, Feb 2 etc) in Row 19, but that breaks because Jan-March are 1-3, and it sums them too.
As you can see it's built on a financial year. How to sum the rows up to the current month, and include last year once we're into January-March?
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 have 20 sheets in a work book (1-20) with similar row details(80 rows)...but the columns have amounts for 12 months. I would like to do a sum if function based on months chosen.
For eg: If I select "sheet 1" and choose month as june...I want the sum of amounts from Jan-June.
i've attached a file of what i'm working with. only the Data Entry worksheet and Data View worksheet are what i am concerned with
i'm building this billing workbook based on a monthly calendar. i want to have the summary column be a SUM of only the months billed thus far. for example, if the current month is november and i'm entering in billing information for the month of october, how would i be able to show a SUM of only months already billed? in other words, how can i summarize july:september, then see that summary in a column, and then bill october without seeing the effect on the summary column.
currently, the summary column is a summary of all months. i need it to be relative to the Current Date entered in the Data Entry Worksheet.
I have 11 months of sales commission data, and need to estimate the value for December. However, the catch is, for the first 7 months, the values are significantly higher than the most recent 4 months. I'm currently using the TREND function to guesstimate the December value, but with the wide fluctuation between the 2 time periods, I'm thinking the result of the TREND value may be way off.
Is there a way to 'weight' the data to reflect the higher values earlier in the year, with the much lower values later in the year?
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...
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:
I have a spread sheet with towns listed in Column G from row 3-81. I months going across row 2 as column labels (they are dates in Jan-14 format). They go from column H-AF.
What I want to happen is to have a formula (not in VBA) which will compare the month in row 2 versus the current month. If it is more than 5 months ago I would like it to return the town in column G for the corresponding row. The only thing I am using to "mark" the last time the town was worked is an X so that is all it needs to look for.
In Column D of my work book are a list of months, I'm trying to use conditional formatting to do the following:
Highlight green the cells in Column D where the month is due (i.e the month is now). Highlight orange the cells that are one month passed the due month. Highlight red the cells that are two or more months passed the due month.
I'm trying to interrogate a sheet for specific data based on months, I can't seem to set the input to the range any help much appreciated Mike54
Private Sub updatestats_Click()
Dim Mth As Variant Mth = InputBox("Please enter the month you wish to analyse") Dim AL As Integer Dim January As Range Dim February As Range Dim cl As Range
Set January = Range("B4:B57") Set February = Range("B58:B113")
I have dates in Column D (mm/dd/yyyy). I'm trying to write a macro to compare the date in any cell in Column D to the current date. Then, if the date in that cell is within a month of the current date, color it red. If the date is within two months, color it orange. Etc, for up to six months. I've read up a little on dates in VBA, but I'm pretty lost.
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 have contarct for amount $5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie $1666.66/per month.
I know I will earn $5,000 of monthly revenue from a client. $5,000 is represented in a monthly revenue cell. I have 12 columns showing the 12 months of the year. There is an additional cell showing the customers implementation date.
If a company's implementation date is on or after the 15th of the month (example: 3/28/2014) then the next month (April 2014) is skipped and the $5,000 is returned to columns May through December. All months prior to May return $0.00. If the implementation date is before the 15th day of the month (example: 3/13/2014) then the next month (April 2014) and all months after will return $5,000. All months including March and prior must return $0.00. If the implementation date is unknown then 12/31/2099 would be in the implementation date cell and $0.00 is returned for all 12 months.
Essentially, if the implementation date is prior to the 15th of the month the revenue will show as of the following month. If the implementation date is on or after the 15th of the month the revenue skips the following month and will show the month after.
