# Weighted Average Problem

Printable View

• Aug 29th 2012, 05:37 AM
rshewett
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?
• Aug 30th 2012, 09:25 AM
Wilmer
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?
• Aug 30th 2012, 10:20 AM
rshewett
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.
• Aug 30th 2012, 11:31 AM
Wilmer
Re: Weighted Average Problem
Welcome my fellow Canadian!!
• Aug 30th 2012, 12:20 PM
rshewett
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...
• Aug 30th 2012, 12:29 PM
rshewett
Re: Weighted Average Problem
And I figured it out :) Correct forumla should read

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

Thanks Wilmer!
• Aug 30th 2012, 01:11 PM
Wilmer
Re: Weighted Average Problem
YES. Sorry, used wrong line...I edited correction.
• Aug 30th 2012, 04:22 PM
Wilmer
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