Cost Price Vs Quantity Discount
I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Figuring List Price - Cost To Show Discount Percentage
List Price $46.98 (e2) Net Cost $19.53 (e3) How do I enter a calculation that will show me my discount percentage from my supplier? (e4) I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item.
View Replies!
View Related
Discount Prices Based On Order Quantity And Package Quantity
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 Replies!
View Related
Quantity*price Returns An Error
I am using MS Query to join 3 worksheets together. I need to calculate one field but it keeps returning an error. the 2 fields are part of the same table calle "Trades" The fields are : Quantity and USD Price USD Price is calculated initially in the worksheet using the current exchange rate. I can display each fields separately (no problem) but when I try to multply the 2: I get a syntax error. Things i have tried: I tried to multiply Quantity by 2 and it works. when I try to multiply USD Price by 2 I get the syntax error. I also tried to copy the values (not the formula) into a new column called USD Price2 and: - Quantity*USD Price2 : syntax error - USD Price2*2: Syntax error
View Replies!
View Related
Calculate Price Based On Quantity
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 Replies!
View Related
Lookup Price Per Unit Based On Quantity Sold
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 Replies!
View Related
Sum Up The Totals Of 'quantity X Unit Price' For Each Product
i am currently working on cost analysis of large projects. I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product. I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.
View Replies!
View Related
Conditional Statements Using IF: Get A Discount (no Discount) Not To Apply The 10%
I have attached a 2004 excel document that has a conditional statement that says, if an amount is over $400.00 you get a 10% discount, else, the cell says no discount. That is working fine. I also have a absolute cell ready to do the math for the 10% amount. There is also a column that shows the discount amount and column that shows the amount after the discount has been applied. Since I have been just doing one calculation and auto-filling, my problem is, how do I get the record that doesn't get a discount (no discount) not to apply the 10%?
View Replies!
View Related
Lookups For An Item's Price From A Price List
The analysis basically has 2 data components to it: The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time. The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased. What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below: 1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2) 2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least) 3) Pull the price for this "closest quantity" I have uploaded a worksheet showing the structure of that data. [url] Is there some VB code I need to do this, or can it just be a few simple formulas?
View Replies!
View Related
Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current) Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines Sheet2 (has current material codes plus current pricing), has about 36000 lines I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
View Replies!
View Related
Looking For The Closest Price To A Reference Price
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price? I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances. I would also like to reference the source on the next column.
View Replies!
View Related
Making Average Buy Price And Average Sell Price
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
View Replies!
View Related
Discount A Row
Is there a way to insert a discounted row at the top of an existing spreadsheet? I'd like to name it "0" or "Row Zero" or "MasterRow" or something similar, such that the existing rows retain their original numbering, and none of the existing code is effected.
View Replies!
View Related
Discount Function In Macro
I have a quote system which takes data from tabbed sheets if there value is greater than 0, then does a simply formula which is then times by a discount which found by searching for the sheet name and then attaching the appropirate discount. In short this means that the whole forumale doesn't work and is not displayed
View Replies!
View Related
Discount Based On Amount?
I need to create a price list a we give a discount based on the price of the product for example:.... All products over £100 have a 10% Discount, how do this and also at the sametime round the value up to 0.99p. ie Product ZXY123 is £123.99 10% off is £111.59 we need the calculated price to be £111.99.
View Replies!
View Related
Calculating A Discount Based On Certain Rules
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100. We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on I use Excel ver 2003. Trust my query is reasonably clear?
View Replies!
View Related
Track The Levels: Club Is 0 To 19 Points And Yields An 11% Discount
I have a 4-level program. The levels are Club, Bronze, Silver and Gold. Each is awarded when a certain amount of points are gathered. Club is 0 to 19 points and yields an 11% discount. Bronze is 20 to 44 points and yields an 12% discount. Silver is 45 to 59 points and yields an 11% discount. Gold is 60 and better and yields an 11% discount. I am looking for a way to total the points and have the spreadsheet automatically calculate the leve and percentage. So if my total points is 22, I would need the formula to return "Bronze" in one cell, then "11%" in the next.
View Replies!
View Related
Calculate Amount Of Days Paid In Advance And Apply Percent Discount
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message. give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount? attached is the start of the assesment question. You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid. Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received: • If students pay the course fee less than 7 days prior to the course commencing then they receive no discount. • If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%. • If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%. • If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
View Replies!
View Related
Total Quantity Calculation
I have a INDENTED list from a cad software that looks like this. It gives me the quantaty of each position. For instant in the list I have 5 of 1.1 but because 1.1 is part of 1 makes my total 3x5=15 I have 2 of 1.1.1 but because 1.1.1 is part of 1.1 which again is part of 1 makes the total 2x5x3=30 POS QTY Total QTY 1 3 3 (=3) 1.1 5 15 (=3x5) 1.1.1 2 30 (=2x5x3) 1.2 2 6 (=2x3) 1.3 1 3 (=3x1) 2 1 My question how to write a formula that automatically calculates the total quantity.
View Replies!
View Related
Print Quantity As Formula
I want to have the print quantity to be the following: textbox1 divided by =VLOOKUP(textbox2,Kits!$B$3:$F$25030,3,FALSE) I need to really do the following: ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:= "formula = textbox2/VLOOKUP(textbox2,Kits!$B$3:$F$25030,3,FALSE)", Collate _ :=True
View Replies!
View Related
Sum Quantity Of First Column
I have 3 columns: in first cell (A1) i have a 553482300040 in second cell (B1) quantity: 1000 and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55. In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 500.
View Replies!
View Related
Cumulative Value From Quantity Lookup
I'm guessing it will be some sort of nested loop but I'm just going round in circles trying to solve it. I need to calculate a cumulative value based on the following criteria: 1. Pre Qty - can be any integer value 2. Qty - can also be any integer value Accumulate values by looking up the Pre Qty + 1 then add all quantities * values based on those provided in a table giving different to - from ranges. Attached is a spreadsheet
View Replies!
View Related
Calculate Two Different Prices Based On A Given Quantity
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 Replies!
View Related
Summarise A Varying Quantity Of Worksheets?
I have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook. In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.
View Replies!
View Related
Counting Quantity Of Numbers, With Conditions
in a2:a277 i have numbers 1-10, randomly. in m2:m277 i have numbers 0-4, also randomly. i want to count how many of the 1s in column a have a corresponding 0, how many have a 1, how many have a 2, etc. if you just give me one formula i can manipulate it for all 40 outputs i need,
View Replies!
View Related
Calculate The Quantity Of Each Size Of An Item
I need a formula that will calculate the quantity of each size of an item (XS,S,M,L,XL). For example to calculate the quantity for Mediums, I need the formula to look at column C, if M is entered, then look at the adjacent cell in column A for the quantity and formulate a total number (from rows 4-21) and enter the total quantity in C27. See attachment.
View Replies!
View Related
Formula To Suggest Purchase Quantity
I am trying to enter a formula that will automatically suggest a purchase quantity by comparing the pieces needed against the required package quantity. I have attached a small example workbook, which has a description of the requirements for the formula, but I will also incude that description in this post. Problem Description: Our purchasing guideline is that we MUST order product in package quantities, as the vendor won't ship a partial package. The required package quantity for each part is shown in column B. If the pcs needed qty in column A is less than half the buy pack qty, you buy zero. If it is half or above, you buy a full package. I want to put a formula in column C that will recommend the suggested buy quantity. The formula would need to compare the pcs needed to the buy pack qty, and put the suggested buy qty in column C. It should also consider multiples of the buy pack qty, such as if the buy pack is 5, and the pcs needed is 12, the formula would suggest 10 pcs. If the buy pack is 5, and pcs needed is 13, the formula would suggest 15 pieces, etc. I have manually entered the sugg buy qty for the first 22 rows, to give examples of what value the formula should return.
View Replies!
View Related
Automatically Insert Rows With Identified Quantity
How do I automatically insert rows given the identified quantity to insert and copy the contents of the previous item on the insert rows. Does that make any sense a1 - abc a2 - def a3 - ghi I need to insert 10rows below a1, and copy a1 across the inserted rows. Now I have a ton of this case currently do it manuaaly, it might take me a lifetime to complete.
View Replies!
View Related
Calculate Prices Based On Quantity Discounts
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 Replies!
View Related
IF Calculation: Calculating Costs Of Products Ordered Which Imports Quantity And Descriptions From Another Sheet
I have a form for calculating costs of products ordered which imports quantity and descriptions from another sheet. The imported info and basic calculation of appropriate cost based on ordered quantity seems to work okay. These formulas are as follows: First column: =IF(ORDER!A5>0, ORDER!A5, "") inserts the quantity ordered Second Column: =IF(ORDER!A5>0, "Part Description and Part Number", "") inserts the product description Both those formulas return a blank cell if nothing is ordered. Then using an outlying cell (G3) I enter the following formula to determine appropriate cost based on total order quantity e.g. $2.57ea if total ordered quantity is less than 50 and $2.37 if total quantity is greater than 50.. {Q: How do I put a third pricing level in here??} =IF(SUM(A5:A27)>50,"$2.57","$2.37")................
View Replies!
View Related
Calculating Cost Per Second
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network. Edit: Charges to network A is 1,79,- per minute after the first 20 minutes are spent. Charges to network B is 2,29,- per minute after the first 20 minutes are spent. To sum up: 1. The customer makes a call. 2. If there there are available free minutes, these should be spent first. 3. The customer is charged per minute, depending on network called.
View Replies!
View Related
Least Cost Routing
im working on a VoIP company, and having a hard time to figure out a result on a little time. the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns: I am including an image, sorry if the image seems to be heavy and large: as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers. While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate. Red marks=are not suitable or non-profitable buy rate for the sale rate. Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.
View Replies!
View Related
Calculating Cost
Problem - billing spreadsheet for prisoner fee. 1 - 8 hrs = $55 9 - 24 hrs = $55 + $65 or $120 Anything over 24 hrs - $65 for each additional (24 hrs) ($185) So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.
View Replies!
View Related
Cost Vs Units Graph
I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company): Cost per unit for 0-10 units: $1 Cost per unit for 11-20 units: $3 Cost per unit for 21-30 units: $5 Cost per unit for 31-40 units: $8 Fixed cost: $100 (this is regardless of the number of units produced) Basically I'm looking to construct a cost vs units graph.
View Replies!
View Related
Transferring Cost From One Sheet To Another
I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?
View Replies!
View Related
Cost Based Mark Up
set up a cost based markup spreadsheet? I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value. I only know how to mark up values by a single percentage.
View Replies!
View Related
Calculate X Percentage Of Cost
I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.
View Replies!
View Related
Cost Average Effect
Example number 1: You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms Example number 2: In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years. How big will the difference be between example 1 and 2 ? *** Background. If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect. Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.
View Replies!
View Related
Time And Cost Calculations ..
I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit. The source data I have is; 1 - Start time and end time of the job 2 - Any breaks taken during the job 3 - The number of staff it took to do it 4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates 5 - The number of punnets packed. With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39
View Replies!
View Related
Calculating Cost Based On Several Factors
i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago. I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information. For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month. Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month. Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.
View Replies!
View Related
Way To Check Whether Any Cost Centre Has Been Missed Out ...
I attached a workbook and it shows data in row 2 - 960. and in cells BA1574 -BM1604 are the summary by using 'sum if' function. As you can see, row 160 countercheck the results and it shows differences. So my questions are 1) any formula that i can use to ensure all cost centres are taken up in Col AZ1574 - AZ1604? I am sure that i have missed something out. 2) My goal is to summarise the total of each cost centre. Is there other options to reach this goal apart from using 'sum if' function? I would like to learn something new.
View Replies!
View Related
Manufacturing To Retail Cost Calculator
I am trying to make an EASY Manufacturing Cost to Retail pricing calculator. This calculator would have ability to include cost of goods, labor, markup etc of components manufactured and sold as retail products, example: small bookshelves versus large bookshelves or cabinets all have different materials (wood types, stain etc) in determining the final retail product costs that would reflect time of labor hours involved in producing to determine final retail costs. Should be simple in Excel 2000 (my version). I have the basic template created and have used Data Validation Drop Down Lists and utilized LOOKUP function. While my knowledge is limited in Excel, I am frustrated as how the LOOKUP function works, I can only get it to work where in the formula, the costs per unit are input manually, whereas I would prefer to have the data input automatically from columns of calculated wholesale cost plus markup per square foot data, ie: =lookup(A1, X1:X30, Y1:Y30) but get errors when doing this. Instead this works: =lookup(A1,{"pine", "oak", "birch"},{"3.99","7.87","5.15"}) and using this cell (A1) in my calculations for including the square foot costs in the final retail calculations. While this seems to work, it is not easily modifiable as costs change rapidly and would like to easily input the cost per square foot of the different woods in their own cells rather than in the formula calculation of the lookup. Hope that makes sense..... any suggestions? perhaps lookup is not the best function for what I am attempting? - sample file is attached, light green are notes of where my data is located.
View Replies!
View Related
|
TRACKER
