Formula To Calculate Sales Tax From Total Sales
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula In A Cell That Configures Sales Tax
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.
View Replies!
View Related
Formula's For Calculate Sales Commissions
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 %. AgentSales Price CostComm Pd AS150 75 JK255 185 JD325 250 JD125 50 AS50 10 AS50 10 AS335 250 JW75 25
View Replies!
View Related
Countif To Find Total Sales
I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product. Countif(Sales!B:B,"Dave") works, counting all the instances of Dave. But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for. While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?
View Replies!
View Related
Calculate Periodically Sales For New Products
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?
View Replies!
View Related
Calculate Weekly Sales With A Midweek Start
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.
View Replies!
View Related
Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated. Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details. I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly? Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file? attached is book.xls and sales summary.xls for reference.
View Replies!
View Related
Sales Commission Formula Required
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.
View Replies!
View Related
List Of Sales By Salesperson From A List Of All Sales
I have one sheet that shows a list of all vehicle sales for a month: with a customer column and a salesperson column and a gross profit column. I would like to give a printout to each salesperson from a different sheet that only shows that salespersons transactions on it. Can excel parse that information out and list it in order row by row showing each sale for just one salesperson per sheet?
View Replies!
View Related
Conditional Formula Worksheet With Monthly Sales Figures
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?
View Replies!
View Related
Formula To Calculate How Much Tax To Pay
Working from cell D19 I am trying to find a formula to calculate how much tax to pay. I have a TAXABLE INCOME figure (in cell D18) , from this figure The FIRST £1520 should be multiplied by 0.1 (in cell C19,) The next £26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20) Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)
View Replies!
View Related
Predicting Sales Data
I have data for average turnover per hour of a business and have fitted it to a polynomial (order 6) trendline. So I have for example, running along the xaxis i have 8am9am, 9am10am, 10am11am etc and on the yaxis the average turnover for the relevant hour. What I would like to be able to do is use the trendline's formula to be able to predict sales for any given day. So if I were to enter the first few hours of turnover on any given day, I would like excel to predict the rest of the day's turnover based on the trendline.
View Replies!
View Related
Summarizing Sales Data
I'm stumped on what I know is a pretty basic problem. Maybe i'm just trying to over think it. I have a table of sales data...One field is the date it was sold, one field is the amount it sold for. The date field isn't in order and it contains dates over the past 12 months. I need a way to total the amount of sales in each month and not through a pivot table. I am able to count how many entries there are, but I can't find a easy way to do a count of how much was sold in each month.
View Replies!
View Related
Calculating Sales Price
using Excel 2002 on XP. My partner and I are selling products. He gets 5% of the sales price, then I get the rest. But I want to make at least $2 on every sale. So, let's say the item cost us $50. If he wants 5% off the top, and I want at least $2, how do we calculate what to sell it for? I tried the following, but it didn't work: $50 (cost) + $2 (my profit) + 5% (partner's profit)  $54.60 (sales price) It doesn't work because I end up with $1.87. $54.60 (sales price)  5% (partner's profit)  50 (cost)  $1.87 (my profit) I've tried other things, but I always end up under $2. Is it possible to calculate this? or do I need to have a percentage for myself? If Excel can't do it, do you know of any calculators out there than can?
View Replies!
View Related
Sales Order Sheet
I want is for the the cover sheet to provide a simplified version of the price list for people to use that will auto complete price to customer once product has been selected.  how do I create a sublist? so if a user selects a product group from a drop down list in cell b1 then cell b2 will automatically display only the items from that groups subset.  how do I link prices? If a user selects data from a drop down menu in b2, is it possible to display the price for that item in cell b3?
View Replies!
View Related
Sales Commission Calculation
I need to know what formulas to put into the cells in excel to make the following sales compensation example compute properly: Time Period Draw Paid Actual Commissions Owed to Company Commission Paid Total Earnings Month 1$3,000$4,000$1,000$4,000Month 2 $3,000$2,000$1,000$3,000Month 3 $3,000$5,000($1,000)$1,000$4,000TOTALS$9,000$11,000$2,000$11,000
View Replies!
View Related
Sheet For Daily Sales
I have a query regarding making a Excel Sheet for Daily sales. here I go, Well i want to make an Excel Sheet where in I just need to enter the Date, Invoice Number , Product , No of Product and rest it should calculate the VAT (Rounding Off) amount N den the Grand Total.. M givin you an example in the Below Sheet.
View Replies!
View Related
Extrapolation: Sales Data
So I have a Sales Data Extrapolation question. I have August sales data up until August 17th. I need to extrapolate the data to determine what my sales numbers will look like for the entire month. Here are some of the specifics: 17 days of sales so far, sold 12,598 units. Total stores 1,500
View Replies!
View Related
Predicting Sales Data In Future
I need to predict sales data in future using multiple independent varaibles.I used FORECAST function to predict sales value for single independent varaiables.But i dont know how to predict sales using multiple varaiables.
View Replies!
View Related
Sales Runrate. Too Tough For Me
I'm trying to calculate a sales runrate which will change on a daytoday basis, to predict the endofmonth sales total. The invoice values are in the data range H17:H74 (I don't want the final formula to add up the refunds in this field i.e. negative values) The date field is in data range C17:C74. So basically the formula will need to add all invoice totals (excluding refunds) and divide by the current number of days worked in the month (not duplicating days in the date field) and then multiply this by the average number of working days in a month (21). This should should give a predicted end of month sales total. Am I just making things up that are impossible to do on excel? I've had a bet with the other guy in the sales office because I said excel can do a lot more than he thinks. He's under the impression that excel begins and ends with what they teach you at school.
View Replies!
View Related
Number Of Days (Sales) Inventory
I am looking to calculate how many days worth of inventory I'm currently holding (in stock and on order from supplier) based on my sales over the past 30 days. I've seen a number of formulas around... and honestly am not sure I'm on the right track. On the attached I have used the following: (Stock on Hand + Stock on Order) * 30 / ( Units of the item sold in the past 30 days)
View Replies!
View Related
State And Local Sales Taxes
Our state carries a 4% sales tax on all items except food and prescriptions. Our county carries a 3% sales tax on everything. Attached on my work sheet: Column "C" determines if an item is either food or nonfood. "G5" is the subtotal of column G "G4" is the S/tx on "G5" at 3% "G3" is the S/tx on "G5" at 4%. "G2" is the gross pay out. My question is: I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C". If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".
View Replies!
View Related
SumIf: Keep Stocks, Based On 2 X The Sales
As you can see I am using the code below in ( I ) =IF(OR(G5="",H5=""),"",INT((INT(2*G5/C5)*C5H5)/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 Replies!
View Related
YTD And Period Sales Report
I have an Access DB that I query with excel and I pull two years worth of sales data. I have tried using a pivot table report to display the following data, but I can't figure out how to display the data in the following format. The pivot table will give period and YTD but the totals for YTD are not cumulative for the year up to that period (it seems to total the period only). For the current Year period (month) and YTD (only up to the period displayed). For the last year period and YTD (only up to the period displayed). The fields I query are Customer, City, Product, Salesperson, Period(month), Year and Sales I have tried putting the queried data on one sheet and then using formulas on another but I am not having any luck. I would also like to be able to select which period I am viewing but this is secondary. I can upload an example if necessary.
View Replies!
View Related
Combining Or Consolidating Sales Data
At the end of every month I receive a sales report from our ERP system setting out sales quantities by Customer ID e.g. ABC001 and Product ID e.g. FB3000. I need to collect the data for each month and gradually build a report for a 12 month period. My problem is that each monthly report does not include every Customer ID and every Product ID, it only includes cases where sales quantity was > 0. So as each month's data arrives I need to make sure that my report has all necessary Customer ID and Product ID pairs so I am not missing any sales.
View Replies!
View Related
SQL To Excel  Stock Sales By Month
I am fairly new to VBA / Excel programming. I have been trying to write a report out of excel from our company DB (SQL2005). The database is run by our frontend accounting application  so i cant mess with it at all, must only run queries. I need to pull the last 24 months of stock sales data(by stock code or category) out of our DB into excel by counting transactions on Customer Invoices / credits. Into a table as follows.. Stock CodeMonth1Month2Month3 ABC1433319 ABC221025 I have managed to make a script that fullfills this need but it takes about 15 minutes to run(Due to having to loop many times per item/ per month).... I was just wondering if anyone had any tips / advice on different ways to do this..??? Ive had a quick look at Pivottables but havent gone very far in, maybe they are the answer, but this amateur does not know.
View Replies!
View Related
Counting Week To Date Sales By Branch
I have to produce a report which shows how many sales each of my company's eight branches has sold this week. The problem lies in the fact that my manager wants the week to start on a Friday, so effectively I need to count the number of sales for each branch since the last friday (including Friday but not today, ie today's report would count all sales from 05/06/2009 to 08/06/2009). Is this possible? My data is as follows: Branch name is in column P, Date Sold is in column S. I have added a column which is next to Date in column T which has "=WEEKDAY(S2)" to give me the week number which I was using to try and count but failed miserably!
View Replies!
View Related
SUM A Range Of Sales Based On Month
I am trying to add a specific range of data Column A include a code Column BX 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 Replies!
View Related
Using The SUM Function To Record Weekly Sales
I would like to have a set of cells that add up all the sales within a given week. I know how to do this simply for one week, but how do I get Excel to automatically take this function and create the rest for future weeks? After entering the SUM function in one cell, I click and drag on the box to try to get Excel to correctly input the functions in the next cells (like how Excel will correctly input the next date, week, or month). But Excel doesn't do it correctly.
View Replies!
View Related
Ividing Sales Leads By Zipcode Equally
The data is a column of zipcodes, a column of timezones for each zipcode, along with a column of this past years sales leads, showing a count of the number of leads from each zipcode. I want to assign territories to 10 sales agents based on an equal division of the number of leads in each time zone; that is, based on last years leads each agent would be assigned zipcodes in each time zone so that all agents would end up with the same number of leads in each time zone.
View Replies!
View Related
Inserts A Blank Record In The Third Row Of The Sales History Worksheet
#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 ToolsMacrorecord 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 Replies!
View Related
Calculate The Tax On Any Income
I'm trying to write a function that takes a single argument grossIncome of type Currency. It should calculate the tax on any income using the following tax schedule 1) If income is less than or equal to $15000 there is no tax. 2) If income is greater than $15000 and less than or equal to $75000 the tax is 15% of all income greater than $15000 3) If income is greater than $75000 the tax is 15% of all income between $15000 and $75000 plus 20% of all income greater than $75000 Then I want to write a sub that asks for the user's income and have the above function calculate the tax on the income and say the tax in a message box.
View Replies!
View Related
