Understands Mix Variances Between Products

Discussion in 'Accounting' started by Daz70r, Apr 7, 2006.

  1. Daz70r

    Daz70r Guest

    I'm not really sure if there is an answer to this question. Intuition
    says there must be, but inumerable hours scouring the net tells me
    there is not. If anyone could put me out of my misery one way or
    another I will be forever in awe/debt.

    I require a method of variance analysis to better report mix variances
    between products. For example, take the following information...


    Product Volume Volume Rate Rate
    Actual Budget Actual Budget
    A 700 400 3 4
    B 100 300 2 1
    C 300 300 1 2
    Total 1100 1000 2.36 2.50



    Conventional variance analysis defines

    Volume Variance
    A = (700-400) * 4 = 1200
    B = (100-300) * 1 = (200)
    C = (300-300) * 2 = 0

    Rate Variance
    A = (3-4) * 700 = (700)
    B = (2-1) * 100 = 100
    C = (1-2) * 300 = (300)


    This does not address mix issues however, i.e. where the weighting of
    more or less profitable products may be different to budget mix.

    I am also aware that the Volume Variance can be broken down into two
    components named the 'Sales-Mix' and 'Sales-Quantity'
    Variances. These are defined as follows:

    Sales Mix Variance

    (Act Vol - ((Bud Vol/Tot Bud Vol) * Tot Act Vol)) * Bud Rate

    A = (700 - ((400/1000)*1100)*4 = 1040
    B = (100 - ((300/1000)*1100)*1 = (230)
    C = (300 - ((300/1000)*1100)*2 = (60)

    Sales Quantity Variance

    (((Bud Vol/Tot Bud Vol) * Tot Act Vol) - Bud Vol) * Bud Rate

    A = (((400/1000) * 1100) - 400)*4 = 160
    B = (((300/1000) * 1100) - 300)*1 = 30
    C = (((300/1000) * 1100) - 300)*2 = 60

    I have managed to get this far but am unhappy with the results and
    believe that there must be a better way of measuring the mix effect. I
    am unhappy that the formula's are limited to looking at the mix
    variance using only Budget Rate. I think that it would be more
    appropriate to take into consideration when selling more (or less) of a
    product, how its budgeted margin compares to the weighted average
    budget margin.

    Take product B for example, the analysis above is indicating an adverse
    mix variance of 230. However this product has a budgeted rate of only 1
    which is well below the weighted average rate of 2.5. Hence there must
    exist some favourable variance given the fact that as a proportion of
    total sales product B is down from 30% in the budget to 9.1% in
    actuals.


    This led me to try and deconstruct the Sales Mix Variance into the
    following


    [A] (Act Vol - ((Bud Vol/Tot Bud Vol) * Tot Act Vol)) * (Bud Rate -
    Weighted Ave. Bud Rate)
    and
    (Act Vol - ((Bud Vol/Tot Bud Vol) * Tot Act Vol)) * (Weighted
    Ave. Bud Rate)


    [A] above I believe sort of gives me what I'm wanting in that it
    returns a favourable variance of 345 for product B. The second element
    however returns an adverse of 575 which of course takes me back to
    my original adverse 230.



    OK now for my question.

    I need formulas which report to me the variances (however many are
    necessary) the volume, rate and mix issues present - use the sample
    data to illustrate. I require that mix variances in particular take
    into account the weighted average budgeted rate. (It may need to take
    into account the weighted average Actual rate - but I don't think
    so). Of course the product variances need to add back to the grand
    total variance. I may be close to getting there or I may be well off
    track?
     
    Daz70r, Apr 7, 2006
    #1
    1. Advertisements

  2. Daz70r

    P-A Guest

    Have you tried a software called Controller's Comments? It
    automatically creates specifications where you can see how much of the
    variances are due to sales, margins or currency. It also gives you a
    complete break-down of the formulas and calculations. You can read more
    at www.obusoft.com
     
    P-A, Apr 9, 2006
    #2
    1. Advertisements

  3. Daz70r

    Daz70r Guest


    P-A,

    Thanks for your post. I have looked at the website and this software (I
    believe your own?) does not appear to address the specific mix variance
    issues that I highlighted.
     
    Daz70r, Apr 10, 2006
    #3


  4. You're quite close, you just need to set up the appropriate
    spreadsheet model for your requirements
    Regards

    David M Wicker
    www.reflexology-gemma.co.uk
    Guildford UK
     
    david m wicker, Aug 1, 2007
    #4
  5. Daz70r

    Emanuel

    Joined:
    Jun 16, 2013
    Messages:
    1
    Likes Received:
    0
    Last edited: Sep 9, 2014
    Emanuel, Jun 16, 2013
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.