Results 1 to 8 of 8

Math Help - Weighted Average Problem

  1. #1
    Newbie
    Joined
    Aug 2012
    From
    Canada
    Posts
    4

    Angry Weighted Average Problem

    Campaign Name List 1 List 2 List 3 Totals
    Targeted Dials 500 4000 1000 5500
    % of Total 0.09 0.73 0.18 1.00
    Targeted Conversion 3.00% 2.00% 7.50% 3.09%
    Targeted ASP $275.00 $200.00 $375.00 $238.64
    Expected Revenue $4,125.00 $16,000.00 $28,125.00 $40,568.18

    Revenue generated is done by multiplying conversion % by list size by the ASP (Average Sales Price). IE: in List 1 this gives us $4, 125.00... Under totals it needs to be weighted as each list has different size (hence more hours worked). The problem is the total revenue from the 3 lists should be $48, 250.00 ... when i apply a weighted average it is being reduced to $40568.18....

    Any ideas?
    Last edited by rshewett; August 29th 2012 at 06:07 AM.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,106
    Thanks
    68

    Re: Weighted Average Problem

    Your ASP average is incorrect; here:
    Code:
    Campaign Name            List 1         List 2        List 3        Totals 
    Targeted Dials              500           4000          1000          5500 
    Targeted Conversion         .03            .02          .075         .030909...
                                ---            ---          ----         -----
    Converted                    15             80            75           170
    % of all converted         .08824         .47059        .44117         1.00
    Targeted ASP            $275.00        $200.00       $375.00        $283.8225 ******
    Expected Revenue      $4,125.00     $16,000.00    $28,125.00     $48,250.00
    ****** 275(.08824) + 200(.47059) + 375(.44117) = 283.8225

    OK?
    Last edited by Wilmer; August 30th 2012 at 01:10 PM. Reason: ****** correction
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Aug 2012
    From
    Canada
    Posts
    4

    Re: Weighted Average Problem

    So I was missing the % of all Converted to gain the correct ASP Average. I knew something was off but I couldn't figure this out for the life of me. Thank you so much Wilmer.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,106
    Thanks
    68

    Re: Weighted Average Problem

    Welcome my fellow Canadian!!
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    Aug 2012
    From
    Canada
    Posts
    4

    Re: Weighted Average Problem

    Whoa, I just tried this in excel and it's not adding up. Also not sure how this is calculating the correct ASP ? The formula seems incomplete. When I do this I end up with $72.06 ? I have to be missing something here Love that I know it's possible now...
    Last edited by rshewett; August 30th 2012 at 12:27 PM.
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Newbie
    Joined
    Aug 2012
    From
    Canada
    Posts
    4

    Re: Weighted Average Problem

    And I figured it out Correct forumla should read

    275(.08824) + 200(.47059) + 375(.44117) = 283.8225

    Thanks Wilmer!
    Follow Math Help Forum on Facebook and Google+

  7. #7
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,106
    Thanks
    68

    Re: Weighted Average Problem

    YES. Sorry, used wrong line...I edited correction.
    Follow Math Help Forum on Facebook and Google+

  8. #8
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,106
    Thanks
    68

    Re: Weighted Average Problem

    Code:
    Campaign Name            List 1         List 2        List 3        Totals 
    Targeted Dials              500           4000          1000          5500 
    Targeted Conversion         .03            .02          .075         .0309  [1]
                                ---            ---          ----         -----
    Converted                    15             80            75           170
    Targeted ASP            $275.00        $200.00       $375.00        $283.82 [2]
    Expected Revenue      $4,125.00     $16,000.00    $28,125.00     $48,250.00
    Btw, I was showing the "individual converted percentages" to illustrate the breakdown.
    But that's not really required in your Excel program: the "Totals column" can be "defaulted":

    [1] (15+80+75) / 5500 = 170 / 5500 = .0309

    [2] (4125 + 16000 + 28125) / 170 = 48250 / 170 = 283.82
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Weighted Average Predictions
    Posted in the Statistics Forum
    Replies: 2
    Last Post: July 2nd 2012, 08:39 PM
  2. Weighted average?
    Posted in the Business Math Forum
    Replies: 0
    Last Post: October 24th 2011, 03:05 AM
  3. Weighted Average
    Posted in the Statistics Forum
    Replies: 2
    Last Post: April 19th 2011, 06:25 PM
  4. Weighted Average / Pop Mean
    Posted in the Advanced Statistics Forum
    Replies: 1
    Last Post: February 16th 2010, 06:03 PM
  5. weighted average
    Posted in the Business Math Forum
    Replies: 1
    Last Post: January 20th 2010, 12:02 PM

Search Tags


/mathhelpforum @mathhelpforum