- Excel help ;o) … pleaaasseeeee ;)
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,Posted 9 years ago
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)Posted 9 years agoantigeeMember
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 averagePosted 9 years ago
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,Posted 9 years ago
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. 😉Posted 9 years agomboySubscriber
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?
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! 😉Posted 9 years ago
The topic ‘Excel help ;o) … pleaaasseeeee ;)’ is closed to new replies.