Quizzes & Puzzles3 mins ago
excel spreadsheet help needed
i have doors to sell the prices are governed by the size.
there are 150 sizes in each range & 12 ranges.
i have created a spreadsheet matrix containing all sizes and the cost price for each size.
what i would like to do is the following.
increase all cost prices by a set % if costs rise ( all prices change throughout the matrix ).
now for the tricky part from this cost matrix automatically produce a retail price matrix ( i.e. cost price + 76.5% this calculation will add the v.a.t. + profit ) again all prices change throughout the matrix .
i have a sample cost spreadsheet available to send if anyone can help.
p.s.not a computer genius only learning would appreciate plain simple english.
thanks.
there are 150 sizes in each range & 12 ranges.
i have created a spreadsheet matrix containing all sizes and the cost price for each size.
what i would like to do is the following.
increase all cost prices by a set % if costs rise ( all prices change throughout the matrix ).
now for the tricky part from this cost matrix automatically produce a retail price matrix ( i.e. cost price + 76.5% this calculation will add the v.a.t. + profit ) again all prices change throughout the matrix .
i have a sample cost spreadsheet available to send if anyone can help.
p.s.not a computer genius only learning would appreciate plain simple english.
thanks.
Answers
1 to 5 of 5
Best Answer
No best answer has yet been selected by demo. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
76.5%?
you should be able to afford to get microsoft in!
It's relatively straight forward ... you need to create a reference area - say sheet2 - (already done) - say cols a-n, rows 1-20
now add to it all your variables and constants
vat, prices etc in a column (Z)
Now you need a "results" or a "working - frontend" overlay sheet ... (sheet1)
Using exactly the same layout as the matrix
build formulas to show the end result - ie
cost = �2.56
overhead = 1.05
vat=1.175
margin = 1.765
the formula is then
price = cost *overhead*vat*margin
the actual formula looks something like
==Sheet2!a1*=Sheet2!$z$1*=Sheet2!$z$2*=Sheet2!$z$3
you can then copy the formula to all the other cells coresponding to the matrix layout
sheet 1 now shows your prices ... any changes are made to sheet 2
I consult at �50 per half hour - �85 per hour or �500 per day (+vat)
you should be able to afford to get microsoft in!
It's relatively straight forward ... you need to create a reference area - say sheet2 - (already done) - say cols a-n, rows 1-20
now add to it all your variables and constants
vat, prices etc in a column (Z)
Now you need a "results" or a "working - frontend" overlay sheet ... (sheet1)
Using exactly the same layout as the matrix
build formulas to show the end result - ie
cost = �2.56
overhead = 1.05
vat=1.175
margin = 1.765
the formula is then
price = cost *overhead*vat*margin
the actual formula looks something like
==Sheet2!a1*=Sheet2!$z$1*=Sheet2!$z$2*=Sheet2!$z$3
you can then copy the formula to all the other cells coresponding to the matrix layout
sheet 1 now shows your prices ... any changes are made to sheet 2
I consult at �50 per half hour - �85 per hour or �500 per day (+vat)
You need to use a column or row (depending on your spread sheet) and let's call it RetailPrice.
Let's say you have the first price in cell E12, the second in E13, third in E14 etc. Put the RetailPrice column in column F. (as it's beside column E where E12, e13 etc are, although you can use any that you want.)
You need to enter a formula in the first cell that you want to work on. In the case that you want to add 76.5%, this formula will be =(E12*76.8%)+E12 (you must put the equals sign in.)
Now, you have your costs in E12, and your retail price beside it in F12.
To expand this through the entire range, just look in the bottom right of F12 - see the cell corner is darker/bolder? Just hold your cursor over the emboldened corner until it becomes a plus, and then right click. Leaving the button pressed, you will then be able to drag the formula through the entire column.
When you want to change the formula, just change the one in cell F12, and repeat the click and drag.
Let's say you have the first price in cell E12, the second in E13, third in E14 etc. Put the RetailPrice column in column F. (as it's beside column E where E12, e13 etc are, although you can use any that you want.)
You need to enter a formula in the first cell that you want to work on. In the case that you want to add 76.5%, this formula will be =(E12*76.8%)+E12 (you must put the equals sign in.)
Now, you have your costs in E12, and your retail price beside it in F12.
To expand this through the entire range, just look in the bottom right of F12 - see the cell corner is darker/bolder? Just hold your cursor over the emboldened corner until it becomes a plus, and then right click. Leaving the button pressed, you will then be able to drag the formula through the entire column.
When you want to change the formula, just change the one in cell F12, and repeat the click and drag.
1 to 5 of 5
