Excel help ;o) … pleaaasseeeee ;)

Home Forum Chat Forum Excel help ;o) … pleaaasseeeee ;)

Viewing 17 posts - 1 through 17 (of 17 total)
  • Excel help ;o) … pleaaasseeeee ;)
  • 0303062650
    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
    Member

    =AVERAGE(A1:A10)

    or whatever your cell range is

    Premier Icon Stoner
    Subscriber

    =average(b1:b100)

    where b1:b100 is the array you want averaged.

    0303062650
    Member

    Wors & Stoner

    You clever people – thank you very much 😉

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

    thanks again

    Jonathan 😉

    Premier Icon Stoner
    Subscriber

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

    Premier Icon Stoner
    Subscriber

    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
    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
    Member

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

    Premier Icon Stoner
    Subscriber

    indeed, he/she is.

    0303062650
    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

    Premier Icon Stoner
    Subscriber

    I wouldnt trust your accountant with this stuff….

    🙂

    Premier Icon mboy
    Subscriber

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

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

    🙂

    Premier Icon mboy
    Subscriber

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

    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.