Creating Monthly Forecast Over 9 Months
Jan 11, 2013
I'm trying to create a monthly forecast over 9 months. I know the planned cost for month 1 and month 9 but need to figure out how to spread the remaining amount over months 2-8 using a downward slope.
Here's the problem:I have 9 months to spend $28M. I know in month 1 I plan to spend $6M and month 9 $3M. That leaves $19 for months 2-8. I need to figure out what the burn rate would be in months 2-8 ramping down from $6M to $3M and not exceeding the available amount (19M)
Is there a way to caluclate this in Excel?
View 1 Replies
ADVERTISEMENT
Apr 4, 2014
I have a 52 week rolling forecast that I would like to have displayed in each calendar month that it corresponds to. I have come up with a solution where it does lump the data together into months but it is not a smooth lumping of the weeks as some weeks cross over from one month to the next. Is there a way to lump each week into its respective month. My current solution places in some months 4 weeks worth of data and in other months 5 weeks worth of data. Attached is the spreadsheet that I am using. The tab "Weekly Sales" is the 52 week data which has specified the exact dates on the calendar that that week represents. The "Monthly Sales" tab has the 12 week data which has specified the exact dates on the calendar that that month represents. I've tried SUMPRODUCT but that is giving me 4 weeks in some months and 5 weeks in others.
View 1 Replies
View Related
Jun 8, 2013
In the attached sheet, I have logged my gas energy consumption (kWh) from 2006 to 2012. I want to analyse my relative consumption over time, on a monthly basis.
However, the bills have been generated by different suppliers for irregular periods (eg. 10-May-2006 to 24-Jul-2006, or 23-Feb-2010 to 6-Mar-2010). So, how can I re-tool my sheet to arrive at rough figures that can be allocated against each month?
Or, if there is no dependability about that method, formulae which allow for effective comparison over time? Let's say, daily?
View 3 Replies
View Related
May 20, 2014
I have macro for creating months. Code worked fine, but now I use merged cells in range where days should be created, and code no longer works (Error : "Cannot change part of merged cells").
Tried with unmerging cells, but then code inserts days in shorter range that I need, so code needs to be changed.
[Code] .....
Attached File : Macro_months.xls‎
View 14 Replies
View Related
Aug 19, 2013
Maybe it is the layout but I was trying to keep it simple and just put hours into a cell for each employee.
I have 2 employees per day, one in the morning and one in the evening. How to get excel to add the hours for each employee at the bottom per month. Simple right?
Here is an example of what I thought would be so easy (first week in excel spreadsheet format)
sun mon tue wed thu fri sat
emp1 5 emp3 6 emp5 5 emp1 4 emp3 5 emp5 5 emp1 6
emp2 5 emp4 5 emp6 5 emp2 5 emp4 5 emp6 6 emp2 6
Is there a way to associate cells to look at values for each employee and add all the cells for the month?
View 5 Replies
View Related
Mar 20, 2014
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
View 5 Replies
View Related
Mar 6, 2013
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
View 1 Replies
View Related
Aug 3, 2009
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
View 9 Replies
View Related
Dec 6, 2013
I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
View 6 Replies
View Related
Jun 1, 2009
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009
13/04/2009
20/04/2009
27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009
13/04/2009
20/04/2009
27/04/2009
04/05/2009
11/05/2009
etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables:
=IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
View 14 Replies
View Related
Jul 19, 2009
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies
View Related
Jul 31, 2007
I am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.
View 9 Replies
View Related
Mar 19, 2009
I'm trying to forecast values for B48:B56. Charted the data and a polynomial 6th order fits best. Known values in B2:B47.
View 5 Replies
View Related
May 16, 2014
I have a list of african countries and their C02 emissions from 1990 to 2010. The question I'm asked is, who will be the top 5 emitters in the year 2020 given the current trends. I have done a lookup command and compiled a list of the top 5 emitters. My concern is though i do not know how to get the 2020 forecast of the top 5 emitters rather than the current datas.
View 4 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
Mar 3, 2007
I try to predict some macro economic statistics but any attempt till now didn't make sense. the attached file. Note: when i used the FORECAST function the predicted values showed an unlogical drop while there seems to be a positive trend.
View 4 Replies
View Related
Jun 18, 2008
I have inherited a series of data relating to a change in a specification over a period of time and a number of cycles.
See attached.
There is already a chart which shows the data and has an exponential line.
I want to find the value of Cycles where the Average Flash exponential from the chart line is 0.131.
FYI this is to plot deterioration in a piece of tooling, 0.131 being the accepted warning level. If you feel there is a better statistical model to use for that application then I'm all ears!
View 9 Replies
View Related
Jan 31, 2014
I would like to know a way to sum the future months dollars only once the month has passed to consider that amount only in my forecast. For eg. If I have a Vendor A contract from Jan - April for $1000/per month in total for $4000. My Forecast should only be Feb-April = $3000. So my total column should only display $3000. Once Feb has passed , the forecast should only be March-April i.e $2000. How to get rolling month sum of forecast once month has passed.
Attached is a sample spreadsheet with different vendors.
Rolling sum after month has passed.xlsx‎
View 5 Replies
View Related
Jul 23, 2013
I'm trying to capture a set of forecast dates in a spreadsheet. We have column 1 = forecast date, column 2 = actual date.
I also have at the top of the sheet (which I will hide when I get the forumula to work), today's date, today's date less 13 days and today's date - 27 days.
In column 3, I want a traffic light system whereby if the date in column 2 is equal to or less than (before) each of the 3 hidden dates it the cell colour will turn green, amber then red..
View 7 Replies
View Related
Aug 9, 2013
I've attached a file that is the result of a product forecast.
What I'd like to do is minutely adjust the data in B4:K12 so that the totals shown in blue colour on Row 15 and Column O are respected.
Book1.xlsx
View 3 Replies
View Related
Jun 25, 2014
I have added a user form to this spreadsheet to make it a little more user friendly to edit/add/delete some information. Now the API used only needs longitude/latitude (lat/lon) to be input by the user. In this case it would be good that for each lat/lon to have a custom name added by the user.
How to link the userform, that's already made, to the information in the spreadsheet?
I was thinking it would be convenient to have the "Site Name" added to a column in the sheet named "Site List" and then have the "Fore Cast Data" pull the names from there.
View 13 Replies
View Related
Jul 12, 2012
I am having trouble finding the days on hand for each SKU. I am trying to get a formula to look at how many days will the total SKU Qty cover in the Forecast_qty. I used a vlookup to make sure the sku = sku but i cant get the days on hand number.
SKU
Total
DOH
ITEM_CODE
FORECAST_DT
FORECAST_QTY
596459
450
?
596459
7/11/2012
54
[Code] ............
View 1 Replies
View Related
Aug 29, 2013
I am forecasting some numbers and I wanted to see which function would be best suited for my task. I used the TREND function and then the FORECAST, I was expecting the same results but appeared to have slight differences.
View 2 Replies
View Related
Sep 28, 2008
I have project start date in cell C2((MMDDYYYY format).In cell D2 I have put the total days needed to complete the project.In cell E2:E6 I have got the scheduled Holidays.
I need to calculate the project completion date in F2.We work from Monday to Saturday,Sunday being off day.
The detail is as follows:-
View 9 Replies
View Related
Feb 10, 2008
So I'm trying to forecast the number of passengers for an airport based on their past 7 years performance. I used "TREND" to forecast the passenger number from 2008 through to 2012 with a little problem and the numbers seems reasonable. However, When I do a scatter diagram and add a trend line, my R squared value is VERY low something around 0.03. I have a limited statistical knowledge but I assume this value should be above 0.90 in order for a good fit with trend line.
View 2 Replies
View Related
Mar 29, 2013
I need to find a way of populating a column of forecasts based upon previous sales amount and price. For instance if I have apples on special for $2 and previously sold 200 units on multiple occasions at this price but once off sold 1000 apples at special $1, but normally they are $3 selling on average 50. I would want to get a result of Forecast: 200, not 50 or anything else to far off
I've attached the sheet I currently use for work.
Dated tab: is my working sheet MerchTrend: Previous sales history, which is imported from POS system and unfortunately cells will change based upon sales
On the Dated Tab, price column includes multi buy prices (ie 2 for $3) but the Merch Trend refers to these as individual sales (ie 2 sales for $1.50) On the Merch trend, Price Type refers to promo style. (N for Normal Price, IA, S, R, IR, P are promotional)
promo sort example.xls
View 1 Replies
View Related
Jun 6, 2014
So I am trying to make a macro that will update monthly forecast data to what the actual production/consumption data was. The production/consumption numbers come in separate workbooks (i.e. "Jan14", "Feb14") and need to be linked to the 'Book' main file. The path needs to the Jan14 needs to come into the 'Book' file, not just the values. I would like to be able to just have a button above each month that will just automatically load that month's data when it becomes available.
View 1 Replies
View Related
Apr 18, 2012
I am trying to project a dispatch date from my DC against the received customer orders.
The following are some important points for consideration of the projected dispatch date:-
1)Customer order is received 24X7(365 days via internet).
2)DC works between Monday-Saturday.
3)Courier company picks-up material from DC between Monday-Saturday.
4)Customer order processing CUT-OFF-TIME is 16PM(on any working day).
5)There are holidays for both DC & courier company on and above SUNDAY.
I am looking for a formula which can forecast the proposed pick-up date from the DC based on the following conditions:-
1)CUT-OFF-TIME:-If the customer order is received on any working day(except Sunday & Holiday) on or before the CUT-OFF-TIME of 16PM then the order will be dispatched on the same day or the dispatch will be made on the next working day.
2)SUNDAY/HOLOIDAY:-If the customer order is received on SUNDAY/designated HOLIDAYs(irrespective of any time) then the order will be dispatched on the next working day.
I am providing few probable scenarios with the desired result. Formula which can populate the desired result across D2:D11.
Sheet3 Â ABCDEFGH1Customer Order DateCustomer Order TimeOrder Processing CUT-OFF-TIME
Proposed Dispatch Date(Desired Result)Explanation For The Proposed Dispatch Date(Desired Result)
List Of Weekly Offs(Both DC & Courier)DC Holidays Courier Holidays
24-Apr14:2016:004-Apr4th April is a working day & order time is before the Cut-Off Time of 16PM and hence the dispatch will be done on 4th April.
[Code] ..........
View 1 Replies
View Related
Mar 29, 2007
what is the formula to calculate variance in Excel between the actual data and the forecasted one?
View 9 Replies
View Related
Nov 29, 2007
I have 200 items that have 22 months of usage information each and need to:
1. In one column, show trend (up or down) based on the 22 months' activity for each item.
2. In another column, show/ forecast how much inventory will be needed for the next 12 months.
View 9 Replies
View Related
