Excel help ;o) … pleaaasseeeee ;)

[0303062650Member]

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

[worsMember]

=AVERAGE(A1:A10)

or whatever your cell range is

[StonerSubscriber]

=average(b1:b100)

where b1:b100 is the array you want averaged.

[0303062650Member]

Wors & Stoner

You clever people – thank you very much 😉

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

thanks again

Jonathan 😉

[StonerSubscriber]

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.

[0303062650Member]

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 😉

[StonerSubscriber]

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)

[antigeeMember]

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

[antigeeMember]

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

[StonerSubscriber]

indeed, he/she is.

[0303062650Member]

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

[StonerSubscriber]

I wouldnt trust your accountant with this stuff….

🙂

[mboySubscriber]

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. 😉

[WorldClassAccidentMember]

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?

[chewkwMember]

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.

🙂

[mboySubscriber]

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! 😉

[WorldClassAccidentMember]

sorry mboy, your probably right