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
ADVERTISEMENT
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
Oct 21, 2007
#17. Create a macro named "AddSale" that perform the following tasks:
-Switches to the Sales History worksheet, and then inserts a blank record in the third row of the Sales History worksheet, shift the rest of the records down.
i did create the Addsale to the macro i use Tools-Macro-record a new macro (is that right?) and i switch to the sales history worksheet insert a blank row. (am i right?) it state inserts a black record, i can't find record anywhere so i assume its blank row.
View 5 Replies
View Related
Nov 23, 2009
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6
Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................
View 5 Replies
View Related
Jul 2, 2009
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.
View 3 Replies
View Related
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
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
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
Apr 21, 2009
I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns
0 - 99,999 0.05
100,000 - 499,999 0.04
500,000 - 999,999 0.03
1,000,000+ 0.03
On a separate worksheet I want to add I6+I7, depending on the value multiply it by the value in the second column. So if the total is 520,000 then multiply it by 0.03.
View 5 Replies
View Related
Aug 29, 2006
I want to be able to calculate a price based on a given quantity. But here is the trick.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 36 widgets, then the cost is 36 x $1.00.
If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 4 widgets then the price is 4 x $1.15
But, if the customer orders 35. Then the first 24 are calculated at 24 x $1.00 and the remaining 11 are calculated at 11 x $1.15.
View 14 Replies
View Related
Jun 18, 2013
I have an issue when I use the sumproduct formula, it only sum the quantity and price orderly which is not matched
I have attache the file for example : Sale monthly.xlsx
View 9 Replies
View Related
Mar 15, 2014
I have a data where i need to collect a total quantity bases on different product. And these product will repeataly appear in a table several time. I have different datasheet every time i need to collect.
View 9 Replies
View Related
Dec 5, 2007
with outputting a table with calculations based on the quantity that the user enters, and depending on that quantity, will calculate the price based on if he/she will receive the discount.
I think I should use an IF statement and calculate depending on if they receive the discount or not, but I don't know how to do that for one type of product code and not have to go manually put it in one by one.
I've attached a speadsheet to show how I should output the data. The prices will be listed in column E, with the prices depending on the quantity that the user will input with an input box and then if he/she qualifies for a discount.
View 5 Replies
View Related
Nov 22, 2007
I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.
Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.
View 7 Replies
View Related
Jan 5, 2012
I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....
All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.
Column A Column B
201 $200
202 $300
203 $450
View 8 Replies
View Related
Sep 21, 2009
I am trying to rank stores based on a quantity of run outs they have. I have a list of stores in order with the run out quantities. I use the large function to figure out the rank but when you have two stores with the same quantity it only finds the first on in the list.
View 2 Replies
View Related
Oct 27, 2011
Using excel 2010
In cell U24 I have this formula:
=SUMPRODUCT(($E$3:$E$2000=51014)*($F$3:$F$2000=101)*($K$3:$K$2000))
Currently, as you can see, this is summing up the criteria specified from row 3 to row 2000...(which i made the last row 2000 becuase I will never exceed row 2000 with info)
What I am trying to do is in cell T22 and U22 be able to plug in date ranges and make the above formula give me the sums of thos date ranges.
For example in T22 put in 8-1-11 and in U22 put in 10-1-11, and have the sum of that date range populate in cell U24.
Now ther kicker is this: some dates are repeated so I may have six rows of date 8-3-11....and they will have to be part of the sum total.....and ALSO the dates are not in chrological order....I may have six rows of 8-3-11.......then four rows of 8-10-11.....then maybe another three rows of 8-3-11, and so on and so on...this is for all dates. Sorting the dates in order is not an option.
View 2 Replies
View Related
Dec 10, 2012
I'm trying to return a distinct list of rows that filter based on the latest date and largest quantity for each distinct AccountID and ProductID combination.
I tried some variant of the =max function, but I need two filters.
The purpose of this is to create a data set of all company accounts with the most recent number of products used to upload to a database.
The simplified and original data set is as follows:
AccountID
ProductID
Company
Date
[Code].....
View 7 Replies
View Related
Feb 4, 2013
I wanted to get the data from data sheet where i have entered the data manually for sales what have done
Now I wanted is when i enter date i should get the product and price in other sheet
Example:
If I enter date in a1 cell
I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet
And c1 - c10 price (if i sold 10 item @that price)
View 4 Replies
View Related
Feb 20, 2008
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies
View Related
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
Feb 15, 2013
I'm using excel 2003 and using VBA I want to convert data from my original data below.
The first row is just to identify the columns below it.
REFR1,REFERENC2,COMPANYNAME01,CURNTDATE,QUANTITY,ITEMNUMBER,PRICE
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 TB,65.9900
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 CH,86.5800
71721,121221142,EXAPLECOMPANY,2-13-2013,1.0000,MPG PB01,75.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,2.0000,MPLC001,146.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,1.0000,MPMC001,120.9900
71720,121238748,EXAPLECOMPANY,2-13-2013,1.0000,MPS007,63.9900
To be converted to the following:
"COMPANYNAME01",
"TOTALITEMS","REFR1","REFERENC2","CURNTDATE","0","0.00","0.00"
"ITEMNUMBER","QUANTITY","PRICE","TOTALPRICE",
"EXAPLECOMPANY",
"2","71723","121222010","2-13-2013","0",,"0.00","0.00"
"MPG TBS01 TB","1.0000","65.9900","65.99",
"MPG TBS01 CH","1.0000","86.5800","86.58",
[Code] ..........
The converted output data above can be within the same document or output to a text file.
View 1 Replies
View Related
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
Jan 5, 2010
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.
View 4 Replies
View Related
Jan 4, 2005
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.
View 9 Replies
View Related
May 6, 2006
I am trying to add a specific range of data
Column A include a code
Column B-X include actual data
Culumn X- AI include budget figures.
Also in cell A1i have the number of the month
For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on
View 9 Replies
View Related
Oct 8, 2008
As you can see I am using the code below in ( I ) =IF(OR(G5="",H5=""),"",-INT(-(-INT(-2*G5/C5)*C5-H5)/C5)*C5)
What I am trying to do is keep stocks, based on 2 x the sales, as you can see G5 I have 15 I still have stock of 35 so I should not need any stock but it has put 50 in. The 50 is a layer rate that I need to order in if I need any. If I had 20 sales and 19 in stock I would want it to order 50. It is the same for all the ones listed in the sheet apart from I8 where it should have ordered but only 40.
View 5 Replies
View Related
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
Jun 9, 2014
How to calculate the monthly sales based on the data given ?
View 4 Replies
View Related
Jun 26, 2013
I want to allocate the Quantities which is available in DC based on Sales, Store OH and In Multiples of Pallet Rounding (PR).
For Eg. If I have 200 Qtys in DC, Excel should allocate these 200 Qty's in multiples of PR to highest selling stores and also consider the store On Hand.
Below is the Format
Article #
Store #
Region
PR
Model
[Code]...
View 4 Replies
View Related
