Excel formula

[marc8]

Hi all
I know this is a bit of topic just doing excel spread sheet for costing, want to enter amount of stuff in one column & use a cost per 100 as I go along is there a formula, example first row amount of fixings next unit cost per 100 last unit sum of the last 2 ( per 100 units) ?????

 

[tazz]

can you explain, more do you want to multiply by quantity per 100....so 1=100, 2=200 etc.?

 

[marc8]

Hi tazz
other way have price per 100, but in some sections not using 100 but would like that part of cost on a sum so it puts it in. Suppose I could divide into 1 then do a multiplicatiion per row

 

[tazz]

Two ways to do this..... price per unit (ie divide by 100) or in quantity enter 1 for 100, 0.5 for 50, 0.25 for 25 etc.

 

[marc8]

Hi tazz
just trying out using excel for bill of qauntitys

 

[Octopus]

Don't forget to make your calculation include VAT and mark up!

 

[marc8]

Hi Murdoch
that may be a little out of my league but had thought of that I will add columns then add the vat & profit

 

[Schneker]

Hi all
I know this is a bit of topic just doing excel spread sheet for costing, want to enter amount of stuff in one column & use a cost per 100 as I go along is there a formula, example first row amount of fixings next unit cost per 100 last unit sum of the last 2 ( per 100 units) ?????

Hi mate pick a column/row where you want answer shown (eg D5)

Type in =((xx/100)*yy)

Where xx is the column/row cost per 100 is shown (eg C5) and yy is the column/row where quantity used is shown (eg B5)
This'll work as long as I've explained it correctly.

Cheers.

 

[tazz]

Which is divide by 100...!!!!

 

[Schneker]

Hi Murdoch
that may be a little out of my league but had thought of that I will add columns then add the vat & profit

Same idea mate,

In the the column/row you want to show a price plus VAT/ mark up etc type

=zz*?

wher zz is the column/row you are wanting to mark up add to and ? is the percentage you want to add.

Hope this makes sense!

Cheers

 

[Octopus]

I would use a "floating" value for mark up so that by changing 1 cell value it will change the margin in multiple lines so you can adjust it if necessary.

 

[Andy78]

The way I have it in my excel materials costings sheet is this
column D = quantity
column E = per
column F = trade price
column G contains the sum =SUM(F8*D8)/E8

You can alter the value of E to per 1, 100, 1000, etc and it should sum out correctly

The next few columns take care of percentage of discount from trade price, discount values, net totals, and markup percentage.

 

[marc8]

Murdoch
never used this before if I put in floating formula will excel have a example ?

 

[Schneker]

Which is divide by 100...!!!!

Which is how I interpreted the OP, looked to me like minimum quantity is 100 and quote may only need say 35, did you read #3 Tazz b4 diving in?

 

[Octopus]

Murdoch
never used this before if I put in floating formula will excel have a example ?

A bit complicated to describe on here!

The reason I use it, is that if you have a job where you have a few "expensive" items and adding 25% mark up across the board is really going to show, so doing this you can lower the margin and it instantly recalculates the totals - and by adding up the columns at the bottom you can see the affect on overall margin of the job.

Having a "handle" on your costs and margins makes sure that you make money.

Its like when a client insists on supplying the parts - I up my hourly rate to compensate for lost margin.

 

[tazz]

Because you are making it difficult for a non experienced excel user.....just put the quantity of each unit, no need for a formula.....if a box of screws are £10 then they are 10p each...simples

 

[Octopus]

Because you are making it difficult for a non experienced excel user.....just put the quantity of each unit, no need for a formula.....if a box of screws are £10 then they are 10p each...simples

I wouldn't go as far as including screws - thats "consummables" and I include a "guess" with the minimum being £1.00

 

[Schneker]

Because you are making it difficult for a non experienced excel user.....just put the quantity of each unit, no need for a formula.....if a box of screws are £10 then they are 10p each...simples

Which is not what OP asked for, he asked if there is a formula he could use to have a price per 100 and then add quantity used and have Excel do the rest. Fail to see how I'm making it difficult when just supplying the basic formula OP requested.

 

[tazz]

As murdoch says its hard to put down be easier to show...
ok, say all you total quantities are in column C so C1 to C10 and C11 (format cells to currency)is your sub costs then
sum= (C1:C10)
say G3 is you markup percentage(format cell to percentage) and G4 is you Total cost so G4 ...sum=(G3*G4)
So profit would be sum=(G4-C11)

 

[marc8]

Hi all thanks
I am underway & have just decided to add a figure at end for Zebs fixings & bolts ect that way much easier at present & will look into building a better template as I go on