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.

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.