Search the forum using the power of Google

Viewing 17 posts - 1 through 17 (of 17 total)
• Excel help ;o) … pleaaasseeeee ;)
• 0303062650
Free Member

Dear Wealth Of Knowledge / STW,

I have an excel quote/costing sheet I use, and, in one column, I have the % of what I make on the equipment, this % is not a fixed amount, and varies from line to line.

Is there a way I can work out the total average %?

thanks,
Jonathan

wors
Full Member

=AVERAGE(A1:A10)

or whatever your cell range is

Stoner
Free Member

=average(b1:b100)

where b1:b100 is the array you want averaged.

0303062650
Free Member

Wors & Stoner

You clever people – thank you very much 😉

My ‘guestimate’ total profit didnt work out too bad at all ;))

thanks again

Jonathan 😉

Stoner
Free Member

by the way – dont forget that an average percentage where the percentages are on varied ratios (such as in this case I assume) is pretty meaningless and doesnt give you your overall perfrormance margin. For that you should use a weighted average.

0303062650
Free Member

Stoner,

thanks for the reply 😉 – I’m not entirely sure what you mean!

a an example, we have an amplifier that we make 10% on, but then the crimp connectors and cable, we’ll make 45% on, is this what you mean by a variable ratio?

confused slightly 😉

cheers,
Jonathan 😉

Stoner
Free Member

Assume:

Name; cost price; sale price; margin; units sold

Amp; £100; £110; 10%; 5
Crimps; £0.10; £0.15; 50%; 100

Your average calculation above will give you 25.5%

In reality, your OVERALL margin is total revenue over total cost (5x£110+100×0.15)/(5x£100+100×0.10) = 565/510 = 11% margin

This is then a weighted average (i.e. each component weighted by it’s contribution as a ratio of all business turnover)

antigee
Full Member

not sure on above – think the suggestions mean you are averaging an average
eg 10%,5%,5%,10% average of these averages is (10+5+5+10)/4 = 7.5%

but if you are looking at £ gross profit like this
Sales value £100 GP at 10% = £10
Sales value £1000 GP at 5% = £50
Sales value £20 GP at 5% = £2
Sales value £100 GP at 10% = £10

Total Sales value = £1220
Total £GP = £72
average GP = (72/1220)*100 = 5.9%

that is you need to take your totals and recalculate the average NOT average the average

antigee
Full Member

when i said above – i didn’t mean stoner’c comment – he/she is saying same as me

Stoner
Free Member

indeed, he/she is.

0303062650
Free Member

I knew there was a reason why I had an accountant.

complex wiring systems, sending audio/video all over the house = no problem.

this stuff, oh my!

it makes sense though 😉 thanks for explaining it, now you mention it, a 37% ‘average’ doesnt equate to earning £2,800 on a £10k job!

I think I’ll get my accountant to sort the excel side of it out, I don’t want to make a mistake!

thanks again chaps,
Jonathan

Stoner
Free Member

I wouldnt trust your accountant with this stuff….

🙂

mboy
Free Member

DEFO listen to Stoner on this one, he obviously knows what he’s talking about.

I’ll not go on as I could bore you senseless about statistics, and how useful/useless they are etc. But the key is to keep things accurate. If you use an average of the percentages you make per item, then it’s about as useful as a chocolate teapot. Stoner’s weighted average will be far more useful, but would still not necessarily be totally accurate.

THE ONLY way to do this, is to sum the cost of everything you sold in a period at the cost you sold it at, and in another column the cost you bought it at. The work out the one versus the other as a percentage of markup, as you have done for each individual item so far.

It’s hard to describe without showing people first hand what the effects of dud statistics can be, and just how often people lead themselves astray! But then I have the inside knowledge, and in fact have conducted training sessions ironically titled “how to lie with statistics” in a past job. 😉

WorldClassAccident
Free Member

Can I mention cost at time of purchase against current replacement costs?

You bought it for £10
You sold it for £20
It now cost £15 to put back into stock.

how much did you make on that deal?

chewkw
Free Member

Are you referring to absolute value?

In that case you need to put the “\$” sign in before and after the cell. For example, \$C6\$ or \$number\$ or \$1234\$ …

By doing so the cell will not change.

Try it.

🙂

mboy
Free Member

Can I mention cost at time of purchase against current replacement costs?

You bought it for £10
You sold it for £20
It now cost £15 to put back into stock.

how much did you make on that deal?

£10

But you’ll only make £5 NEXT time unless you put your prices up!

Stock is a necessity in order to be able to sell something sadly, so you HAVE to invest before you sell something in general. Purchase price of said item next time round does not have anything to do with what has gone in the past, except that is to say your margin will be reduced unless you put your sales price up to compensate.

Sorry to be a pedant, but please please please don’t try to confuse the issue eh WCA! 😉

WorldClassAccident
Free Member

sorry mboy, your probably right

Viewing 17 posts - 1 through 17 (of 17 total)

The topic ‘Excel help ;o) … pleaaasseeeee ;)’ is closed to new replies.

Search the forum using the power of Google

Thanks for popping by - why not stay a while?IT'S FREE

Sign up as a Singletrack Member and you can leave comments on stories, use the classified ads, and post in our forums, do quizzes and more.

Join us, join in, it’s free, and fun.