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

Convert 52 Week Rolling Forecast To Monthly Forecast

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

How To Get Monthly KWh Usage When Bill Data Spans Months

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

Macro For Creating Months - Not Working

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

Creating Simple Monthly Work Schedule For 6 Employees But Need To Tally Hours

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

Creating View By Filtering 2 Columns By Date (both Within 3 Months Of Today Date)

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

Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total

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

Pulling Data Into Two Columns Labeled “Monthly” & “Non-Monthly”

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

Conditional Format - Dates Greater Than 18 Months And 24 Months?

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

Creating Monthly Data Table From Weekly Data Table

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

Elapsed Months (many Months Have Gone By Since Todays Date)

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

Add Months & Return Decimal As Years & Months

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

Forecast The Value

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

Lookup Future Forecast?

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

Forecast Linear Trend Between Min & Max

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

Economic Statistics Forecast

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

Calculate Exponential Forecast

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

Forecast Calculation - Once Month Has Passed

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

How To Capture A Set Of Forecast Dates In A Spreadsheet

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

Product Forecast - Optimizing Dataset

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

Weather Forecast Spreadsheet Userform

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

Days On Hand Against Forecast Table

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

Different Results When Using Forecast And Trend Function

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

How To Forecast Project Completion Date

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

TREND Function For Passenger Forecast

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

Forecast Quantity Based On Sales History?

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

Updating Forecast / Actual Sheet With A Macro

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

How To Forecast Pickup Dates Based On Several Conditions

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

Calculate Variance Between Actual And Forecast Data

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

Show Trends To Forecast Annual Inventory

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







Copyrights 2005-15 www.BigResource.com, All rights reserved