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


ADVERTISEMENT

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

How To Forecast Delivery Dates Based On Several Complex Conditions

Apr 10, 2012

I am trying to project the delivery dates of my shipments which is sent via courier to different destinations from my Distribution Centre.

The courier company delivers the parcels across Monday to Saturday(Sunday being the off day for the courier). There are different transit days for different locations which works in deriving the project dates from the shipping date. I am looking for a formula which can satisfy all the following conditions and project the delivery dates accordingly:-

Condition1:-If the projected delivery date falls on Sunday and there is no holiday on the very next day(Monday) then the projected delivery dates needs to be on Monday.

Condition2:- If the projected delivery date falls on Sunday and the very next Monday is a Holiday then the projected delivery dates needs to be on Tuesday.

Condition3:- If the projected delivery date is a Holiday and the holiday falls on any day between Monday to Friday then the delivery date will be the next working day post the holiday(between Mon-Sat).

Condition4:-If the projected delivery date is a Holiday and the holiday falls on Saturday then the delivery date will be next Monday.

Condition5A/5B/5C:-In case there are more than one holidays at a stretch(2/3 days of holidays at stretch) and if the projected delivery dates falls within the holidays then the projected delivery dates needs to be on the next working day(Between Monday to Saturday).

I have tried to write a formula but it is only satisfying the Condition1,Condition2 & Condition3. I am providing a sample data with desired results based on the above conditions. Any single formula which can derive the desired results.

Sheet1  ABCDEFGH1Destination LocationDispatch DateTransit DaysProjected
Delivery Dates(Desired Result)Remarks  Courier Company Holiday List2A29-Mar32-Apr
Condition1:-Projected Delivery Date As Per Transit-Days Is Sunday(1st Apr) & There Is No Holiday On Next Monday(2nd Apr) & Hence REVISED Projected Delivery Date Needs To Be On 2nd Apr(Mon).

[Code] ....

View 3 Replies View Related

Use SumIF Formula To Add Sales Forecast Based On Three Conditions

Apr 4, 2014

I am trying to use sumif formula to add sales forecast based on three conditions but i also want to add the revenue for current month which i have but for the next one months as well as two months plus.. this will change based on the current month.. below is what I am using for the current month..

=IF($B$3=Reference_Data!E2,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E3,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E4,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),

[Code] ...........

View 2 Replies View Related

How To Make A Forecast For The Demand For The Time Periods Shown

Dec 3, 2006

how to Make a forecast for the demand for the time periods shown....

View 9 Replies View Related

Forecasting Purchasing: Setting Up A Spreadsheet That Will Allow Me To Look At Previous Orders And Forecast

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

Forecast An Estimated Budget Based On Original Budget And Percent Complete

May 21, 2006

In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.

View 2 Replies View Related

Sum Year To Date Forecast Based On Row And Date Criteria?

Feb 25, 2014

I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).

View 2 Replies View Related

Sum By Conditions & Rolling Monthly Total

Dec 5, 2008

I have a pivot table that summarizes expenses (cash advances, cashe remitted, etc.). The issue that I'm having is the way the data is displayed on my pivot table.

When I adjust the custom calculations to "show data as Running Total in MONTH" I get the desired outcome on my row totals, but I do not get the correct figures on the actual data within the Pivot Table. When I remove this custom calculation and just "sum by value" then the data is correct, but the row totals are not.

In a perfect world I would need the values to sum by value, while the row totals are set to "show data as running total in MONTH". I'm not smart enough to figure out how to produce both.

View 8 Replies View Related

Calculate Rolling Weekly And Monthly Average

Feb 19, 2012

I am wanting to calculate a rolling monthly average and a rolling weekly average.

The following cells have the headers k2 has Allan, Cell L2 has Bill, Cell M2 has Charlie, Cell N2 has Don, cell o2 has Ellen and Cell P2 has Flora

Column J3 to J14 respectivley has Jan to Dec

The balance of the cells will have the data.

I then need to plot the rolling averages for each person on a gaph as teh months data is filled.

Below is the table:

Monthly Totals 2012AllanBillCharlieDonEllenFloraJan0.0000.0000.0000.0000.0000.000
Feb0.0000.0000.0000.0000.0000.000Mar0.0000.0000.0000.0000.0000.000
Apr0.0000.0000.0000.0000.0000.000May0.0000.0000.0000.0000.0000.000
Jun0.0000.0000.0000.0000.0000.000Jul0.0000.0000.0000.0000.0000.000
Aug0.0000.0000.0000.0000.0000.000Sep0.0000.0000.0000.0000.0000.000
Oct0.0000.0000.0000.0000.0000.000Nov0.0000.0000.0000.0000.0000.000Dec0.0000.0000.0000.0000.0000.000

View 1 Replies View Related

Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

View 10 Replies View Related

Using Rolling 12 Month Sales And Minimum Monthly Sales?

Oct 17, 2013

I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.

Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....

View 6 Replies View Related







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