I am trying to decipher how to calculate commissions for my sales reps. I have made just a simple spreadsheet to give you an idea of what I am doing. I have tried to us an IF formula but I think there are too many options( I have 9 reps). Basically I pay them either 10 or 15% so I need a formula to take the sales price - cost times their apporpriate %.
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.
I need to write a formula that will look at a number in a cell and compute an amount at 1% up to 55,000. Then anything above the 55,000 compute an amount at 3.25%. Then add the two together.
So a baseline of 55,000 at 1%. Then above the baseline 3.25%.
I'm trying to calculate periodically sales for new products, which have been in the market for max 6 monts. After that 6 months the sales of the product is not to be calculated. I have a huge amount of products, where this information should be calculated, so manually calculating is not an option. The products are in rows, and periods are in columns. As the data concerns several years data there is a problem, that some products have in some months zero sales, and in the next month again some sales. This messes up always my calculations. How to truly take only the first 6 months, and leave all the rest uncalculated?
I'm trying to create a simple sales report. No VBA code, only excel formulas. I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week.
Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only. I want it done automatically.
I've included a zipped excel sheet example of the worksheet for a visual example.
I'm trying to calculate a running total of ticket sales with 5 different groups (adult, youth, senior day, senior night, and child) to where once the number of tickets are bought it will update automatically. I enter the tickets in cells B5-B9 where it then calculates the amount due and the change due. I need to put the running total in cells B18-B22.
My company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.
I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?
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.
if sales target is say 1000 if they dont reach 60% no commission payable if the reach 60% and over to sales target they get 3% if they get over target they get 5% of everything over target
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.
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.
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]....
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.
I have a new sale structure to put in place the commission is paid in the following way:
below 1500 zero commission between 1501 and 3000, commission at 16% between 3001 and 8000, commission at 23% above 8001, commission paid at 30% Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )
I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.
I have a formula in a cell that configures Sales Tax. How do I add to my existing formula so that if someone is tax EXEMPT to not display or calculate ANYTHING in the Sales Tax Cell? I want to add this to my existing formula in my sales tax cell: IF(Y33="YES") and then I want it to override any existing formula and display nothing in that cell at all.
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.
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've a spreadsheet (see attached) which shows sales of 10 different fruits. I've used a LARGE formula (Cells O15:O17) to display the three highest sales and an OFFSET formula (Cells B15:B17) to show what the are.
I only ever want to show the top three sales but I need to take into account instances where more than one fruit has the same sales figure (for example, if lemons and pears both had sales figures of 27, they'd have to be shown along with grapes and strawberries).
In my spreadsheet, I have data for each sale in each row. Column C is the buy price, and column D is the sell price. I need column C to be subtracted from column D to get the profit into column E so that I can AutoSUM any cells in column E for total profit over any time period. The problem I'm having is that in columns C and D, I have either a dollar sign in front, or the letter m following without a space (ie $45.00 or 150m). Many cells in column C also contain another word following the buy price, and many cells in column D have a date following the sell price (ie $32.00 12/11 or 42m buyout).
Examples of how my spreadsheet looks: A1: 12/13/12 B1: Item #1 C1: 100m D1: 152m A2: 12/14/12 B2: Item #2 C2: $20.00 D2: $35.00 A3: 12/15/12 B3: Item #3 C3: 90m bid D3: 120m 12/16
So looking at my example above, I would need cell E1 to automatically show "52m", cell E2 "$15.00", and cell E3 "30m"
How I can get a formula for all of column E that will give me my profit for both $ and "m" for each item (row). I have tried the SUM function but just keep getting the #VALUE! error, it does not ignore the text like many people say it should.
I'm trying to create a report that will include current month and previous month by territory. Unfortunately, i'm getting the same values for both months.
PriorMonth=CALCULATE([Sum of TotalDue]),DATEADD(Date[Date],-1,month)) Date =Date table ( Date, Month, year) Relationship= Date.Date=InternetSale.OrderDate Current Month=Sum([Amount])
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..
I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.
I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.
This is basically what i'm trying to do: If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?