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?

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?

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.

Welcome my fellow Canadian!!

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

And I figured it out :) Correct forumla should read

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

Thanks Wilmer!

YES. Sorry, used wrong line...I edited correction.

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