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]...
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 RelatedI am trying to create a Excel file for my job. I work in a warehouse where I have many different orders all of which are made up of various different numbers of items and priorities. For example:
Order 1 = 45 items, Priority 1
Order 2 = 78 items, Priority 1
Order 3 = 48 items, Priority 2
Order 4 = 34 items, Priority 3
The total number of items is divided by the number of staff to give each staff member a total for the evening.
I plan to have a list of these orders in the excel file.
What I would then like to do is to have each staff member allocated orders so that the their total is reached. The sheet would automatically allocate orders to each person from the list of orders so that based on priority first then by the largest number of items to the smallest.
I think this should be a fairly simple sheet to create and I have a view of creating an application for it if I can get it working.
Excel 2003
I have a macro and want to allocate it to a "button" . The macro is all ok.
So I make visible the FORMS floating toolbar / icon set.
There are 16 icons on this floating toolbar / icon set.
But the icon for button is missing, in that it is just 100% blank.
The other 15 icons are all ok, such as "Group Box".
Normally I would click this "button" icon, then draw my button, then allocate my macro to it.
I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls
Here are some of the rules for the raw data structure:
1. First BOM level at very beginning is always "1"
2. All children of BOM level "1" have a BOM level of "2"
3. All children of BOM level "2" have a BOM level of "3"...
4. There are potentially many BOM levels (approx. 15 levels for full set of data)
5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc.
6. Data is always sequential so the children (and grandchildren) will always come after the parent
I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same.
In order to ensure we are on the same page let's use the following associations:
Y = Quantity
X = Cost Per Item
Z = Order Total
So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
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
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies View RelatedI 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)
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies View RelatedI 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]....
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 RelatedI 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.
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
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.
How to calculate the monthly sales based on the data given ?
View 4 Replies View RelatedI 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
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
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] ...........
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
I am writing a formula that will sum data for a 12 month period that is not based on a calendar year. For example may have data starting in June 2011 running to date, but not every month. If I set a start date for Year 1, say April 2012, I need to see Year 0 numbers (anything prior to April 2012), Year 1 numbers (April 2012 to March 2013), Year 2 numbers (April 2013 to March 2014), and so on. Date format is currently mmm-yy.
If this could be performed via a macro which references the database (spreadsheet), then even better. The database will contain multiple columns, of which two will contain the date and corresponding number respectively.
Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
I have made a dynamic calculator which will calculate the total sale based on 3 criteria in 3 different cells as follows:-
I would like to derive the sales qty in cell C5 if all the 3 conditions in the range B2:B4 is true.
In this case the sales qty for Style-Colour-MRP combination(9103-PINK-399) should be 6.
I am unable to figure out with the formula for the same....
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.
My Vlookup result may relate to a number of occurances of the same source product code. I want to allocate the result so that the last occurance gets the balance. See attached file.
View 4 Replies View RelatedI need to allocate an existing subroutine (its code below) to a new button I'd like to add to my sheet. Two questions arise: to use "form control" or "activeX"?, also should I redfine the first line of the code?
[Code] .........
I allocate result of formula FORMULA R1C1 into the active cells,,,AND NOW I wan to allocate it into a variable x or y (I dont want that the result appears in the spreadsheet, just into a variable to work the code!)
View 9 Replies View RelatedThe number of transactions is scaled down to provide this example.
Rebate scheme (Cum Sales = Cumulative sales) based on the total turnover of a group of customers.
Cum Sales....Rate....Sales Band...Rebate
20,000........1.0%.......20,000........200
35,000........2.0%.......15,000........300
45,000........2.5%.........3,400.........85
...................Total......38,400.......585 average rebate 1.5%
I would like to correctly allocate the £585 rebate across the individual customers who have generated the turnover i.e. NOT by using the simple 1.5%.
Clearly the large sales value customers will have earned a higher % rebate than the lower sales value customers - but how to calculate?
Customer....Sales value...Rebate
A...................15,000
B.......................900
C....................7,000
D....................3,000
E.......................500
F.....................9,000
G.....................2,000
H.....................1,000
Total...............38,400.........585