# Cost Price Vs Quantity Discount

Apr 7, 2008
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 9 Replies
ADVERTISEMENT
Apr 25, 2006

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 9 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 14, 2014

How would I create a simple user form to display a price based off the category selected.

User types in List Price, then selects 1 of 4 discount categories, it then displays that price.

An added bonus would be copying that price to another sheet, but not necessary..

View 2 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
Jul 15, 2008

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 9 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
Nov 23, 2009

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 9 Replies
View Related
Nov 23, 2013

I am trying to get and average price for a total quantity of jacks

Material Price row G cells 4 - 56

Material Labor Price row I cells 4 - 56

Jack Quantity row L cells 4 - 9

I had this =SUM(G4:56)+SUM(I4:56)/SUM(L4:9) just wont work?

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
Jul 18, 2014

For this ordering tool, I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the ordering sheet (we still fax orders...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.

I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.

I looked and messed around with For loops, but I can't make it work.

View 1 Replies
View Related
Apr 22, 2006

I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price

View 2 Replies
View Related
Dec 22, 2009

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 4 Replies
View Related
Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...

B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))

J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies
View Related
Jun 9, 2008

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies
View Related
Jan 4, 2010

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 4 Replies
View Related
Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

View 5 Replies
View Related
Jul 24, 2006

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 7 Replies
View Related
Feb 12, 2010

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 12 Replies
View Related
Mar 17, 2009

I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009

$23.95 $24.15

Using excel 2007

View 3 Replies
View Related
Dec 24, 2007

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 5 Replies
View Related
Feb 6, 2009

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 2 Replies
View Related
Dec 3, 2009

how to get the discount to work the way I need to. As is, It requires a person to put in the actual amount that I will discount the product for each product sold. I would like to simply key in the discount percentage. EX: 10% or 13%

View 14 Replies
View Related
May 8, 2014

If I explain all the 8 steps , it will be lengthy and not easy to explain , thus I will go step by step

See file attached raw 01 , copy it on drive c directory called DISC , see file attached the result file

I want the the result file to go fetch data from the folder created DISC in which there is file called raw 01 and do the following with pulling headers , since the header is already plugged.

a to a

b to b

c to c

d is thus empty for the second step of the macro ( explain following after this fetching is achieved )

d to e

e to f

f to g

g to h

h to i

j to k

k it should not copy

View 5 Replies
View Related
Mar 30, 2007

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 11 Replies
View Related
Jan 14, 2014

A customer has an overdue account, we will say in the amount of $500.00. We offer various discounts, 30%, 20%, 0% depending on the circumstance.

What I had previously worked flawlessly, that is until the higher ups changed how things had to be calculated.

Previously, we would take the amount owing and apply the discount (ie 20%) which would become the new amount due ($400).

We would then take the discounted amount and separate it into monthly payments:

$400.00

1st payment 60.00

2nd payment 60.00 (and so on)

6th payment 60.00

7th payment 40.00

Done.

The new way they want it calculated is (Same situation $500 owing, 20% discount).

$500.00

1st payment 60.00

2nd payment 60.00 (and so on)

6th payment 60.00

7th payment 60.00 *

8th payment 60.00 *

9th payment 20.00 *

Done

(*these would be the discounted amount, it would be stated that these payments would be removed if payments are made on time and in full yadda yadda).

The trouble I am having, is figuring out a way to tell excel to flag the specific payments. The reason behind it (i guess) is so that if a client becomes delinquent, the full amount owing would be sent to collections, not the discounted amount owing.

View 1 Replies
View Related
Jun 25, 2014

I need a formula to take away a percentage according the amount (number) ot procedures.

For example:

- procedure - price for one - 5 of it - 10% discount - final price

The idea is, for example, if I type amount of 5 procedures, automaticly to make 10% discount, if I type 10 procedure - make 15% discount etc.

View 6 Replies
View Related
Feb 11, 2009

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 7 Replies
View Related
Nov 9, 2013

I'm very new to excel, my boss request me to create a discount calculator. A cost of price is given. We have a fix discount rate of 5% of that cost value. Some new customer want more percent of discount to the item they purchase. To deal with that, a markup price will be quoted then we will make a discount according to what the customer prefer but the final discounted value will be always be the discounted 5% of our cost value. I've problem creating it because as example; the customer request for 15% discount, when I add the cost + 10% then minus it back to 15%, the value I get will no be the same of the fixed reduced 5% value of my cost value.

ac16xpZ.jpg

View 5 Replies
View Related
Jun 7, 2006

I would like to ask the formulas for this... for example:

$35 .. discount 10% + 25%

instead of having so many cells to calculate $40 x (100%-10%) = $36

$36 x (100% - 25%) = $27

View 2 Replies
View Related