+1443 776-2705 panelessays@gmail.com
  

  

Q1 What is the average willingness to pay for a single song? (Format as $0.00) _____

Q2 What is the optimal price of a song to generate the most profit? (Format as $0.00) _____

Q3 How many songs sell at this price? (Format as number) _____

Q4 At this price, what are the maximum profits of the firm? (Format as $0.00) _____

Q5At this price, what proportion of songs with positive value are not sold? (Format as 0.00%) _____

Q6For all songs that are sold, how much consumer surplus do consumers keep? (Format as $0.00) _____

Q7How much social value is not created? This is also called ‘value of unsold items’ or the ‘missing surpluses. (Uncaptured consumer surplus or Total WTP of those consumers who are ready to pay a non-negative price) (Format as $0.00) _____

Analysis

ITEM SALES ITEM PRICE PROFIT BUN SALES BUN PRICE BUN PROFIT
8032 0.10 803.20 <———— See below for explanation of equation in Column A 100 25.85 2585.00 <———— Bundled price is the sorted (and transposed) Total WTP for each Consumer from the WTP sheet
5845 0.25 1461.25 99 27.8 2752.20
3738 0.50 1869.00 98 29.85 2925.30
1555 0.90 1399.50 97 30.5 2958.50
762 1.50 1143.00 96 30.85 2961.60
95 31 2945.00
94 31.2 2932.80
93 31.45 2924.85
92 31.5 2898.00
91 31.75 2889.25
91 31.75 2889.25
89 31.9 2839.10
88 32.05 2820.40
87 32.4 2818.80
86 32.45 2790.70
85 32.65 2775.25
84 32.7 2746.80
83 32.85 2726.55
82 33.05 2710.10
81 33.2 2689.20
80 33.35 2668.00
79 33.4 2638.60
79 33.4 2638.60
77 33.45 2575.65
Best Price: $ 0.50 <- Choose cell with price that maximizes profit (this will adjust item Consumer Surplus ) 77 33.45 2575.65 Best Price: $ 30.85 <- Choose cell with price that maximizes profit (this will adjust Bundle Consumer Surplus)
Max Profit: $ 1,869.00 <- Choose cell with max profit 75 33.6 2520.00 Max Profit: $ 2,961.60 <- Choose cell with max profit
Total Avail CS $ 3,693.65 Total consumer value of all songs 74 33.65 2490.10 Total Available CS $ 3,693.65 Total consumer value of all songs
Avg Item WTP $ 0.46 How much consumers are willing to pay for items on average 73 33.7 2460.10 Avg Bundle WTP $ 36.94 How much consumers are willing to pay for bundles on average?
Mode Item WTP $ 0.10 Mode of the distribution for single item WTP 73 33.7 2460.10 Total CS $ 618.05 Total (WTP – Price Paid) across all sold bundles
Total CS $ 1,079.20 Total (WTP – Price Paid) across all sold songs 71 34.05 2417.55 % Unsold 3.85% Total WTP such that 0 < WTP < Price
% Unsold 53.46% Total WTP such that 0 < WTP < Price 70 34.15 2390.50 Missing Surplus $ 114.00 Unsold bundles = Total Avail CS – Profit – Kept CS
Missing Surplus $ 745.45 Total Value of Unsold items = Total Avail CS – Profit – CS Kept 69 34.2 2359.80
68 34.35 2335.80
67 34.6 2318.20
67 34.6 2318.20
65 34.7 2255.50
65 34.7 2255.50
63 35 2205.00
COMMENT: “=COUNTIF(WTP!$B$2:$CW$101,CONCATENATE(“>=”,TEXT(B2,”0.00″)))” 62 35.15 2179.30
COMMENT: “Count each WTP item for which user value is >= price. The CONCATENATE and TEXT create a test string based on price in B2 carried to 2 digits.” 61 35.2 2147.20
60 35.3 2118.00 COMMENT: “=SUM(WTP!B106:CW106)/(COUNTIF(WTP!B2:CW101,”>0″))”
COMMENT: “=(COUNTIF(WTP!B2:CW101,”<“&B26))/(COUNTIF(WTP!B2:CW101,”>0″))” 60 35.3 2118.00 COMMENT: “Ratio of all songs in unsold bundles to count of all songs”
COMMENT: “Ratio of all songs less valuable than price relative to all songs more valuable than zero.” 58 35.45 2056.10
57 35.85 2043.45
57 35.85 2043.45
55 36.1 1985.50
54 36.35 1962.90
53 36.4 1929.20
52 36.55 1900.60
51 36.6 1866.60
51 36.6 1866.60
49 37.1 1817.90
48 37.2 1785.60
47 37.35 1755.45
46 37.55 1727.30
45 37.65 1694.25
44 37.7 1658.80
43 37.8 1625.40
43 37.8 1625.40
41 37.95 1555.95
40 38 1520.00
39 38.15 1487.85
38 38.3 1455.40
37 38.35 1418.95
36 38.5 1386.00
36 38.5 1386.00
36 38.5 1386.00
33 38.95 1285.35
32 39 1248.00
31 39.35 1219.85
30 39.55 1186.50
29 39.6 1148.40
28 39.8 1114.40
27 39.85 1075.95
26 40 1040.00
25 40.15 1003.75
24 40.3 967.20
24 40.3 967.20
22 40.35 887.70
21 40.5 850.50
21 40.5 850.50
19 40.65 772.35
18 40.85 735.30
17 41 697.00
16 41.5 664.00
15 42.05 630.75
14 42.35 592.90
13 42.8 556.40
13 42.8 556.40
13 42.8 556.40
10 43.1 431.00
9 43.55 391.95
8 43.6 348.80
7 43.85 306.95
6 44.3 265.80
5 44.7 223.50
4 44.9 179.60
3 45.85 137.55
2 45.9 91.80
1 47 47.00

ITEM DEMAND

ITEM PRICE 8032 5845 3738 1555 762 0.1 0.25 0.5 0.9 1.5

Item Sales

Item Price

BUNDLE DEMAND

BUN PRICE 100 99 98 97 96 95 94 93 92 91 91 89 88 87 86 85 84 83 82 81 80 79 79 77 77 75 74 73 73 71 70 69 68 67 67 65 65 63 62 61 60 60 58 57 57 55 54 53 52 51 51 49 48 47 46 45 44 43 43 41 40 39 38 37 36 36 36 33 32 31 30 29 28 27 26 25 24 24 22 21 21 19 18 17 16 15 14 13 13 13 10 9 8 7 6 5 4 3 2 1 25.850000000000009 27.800000000000018 29.850000000000016 30.500000000000014 30.85 31.000000000000021 31.200000000000014 31.450000000000014 31.500000000000011 31.750000000000007 31.750000000000021 31.90000000000002 32.050000000000011 32.400000000000013 32.450000000000017 32.650000000000013 32.70000000000001 32.85 33.050000000000018 33.200000000000003 33.350000000000016 33.400000000000006 33.400000000000013 33.450000000000003 33.450000000000017 33.600000000000016 33.650000000000006 33.700000000000003 33.70000000000001 34.050000000000018 34.150000000000006 34.200000000000017 34.350000000000009 34.600000000000016 34.600000000000016 34.700000000000003 34.700000000000003 35.000000000000007 35.150000000000013 35.200000000000003 35.300000000000004 35.300000000000011 35.450000000000003 35.85 35.850000000000016 36.100000000000016 36.35 36.400000000000006 36.550000000000011 36.6 36.600000000000016 37.100000000000009 37.20000000000001 37.35 37.549999999999997 37.650000000000006 37.70000000000001 37.799999999999997 37.800000000000011 37.950000000000024 38.000000000000014 38.15000000000002 38.299999999999997 38.350000000000016 38.5 38.500000000000007 38.500000000000007 38.950000000000003 38.999999999999993 39.350000000000009 39.550000000000018 39.600000000000009 39.8000000000 00011 39.850000000000016 40.000000000000007 40.150000000000006 40.300000000000011 40.300000000000011 40.350000000000009 40.500000000000007 40.500000000000007 40.650000000000013 40.850000000000016 41.000000000000007 41.500000000000014 42.050000000000018 42.349999999999994 42.800000000000004 42.800000000000004 42.800000000000011 43.100000000000009 43.550000000000004 43.600000000000009 43.850000000000009 44.3 44.7 44.900000000000006 45.850000000000009 45.90000000000002 47

Bundle Sales

Bundle Price

PROFIT 0.1 0.25 0.5 0.9 1.5 803.2 1461.25 1869 1399.5 1143

25.850000000000009 27.800000000000018 29.850000000000016 30.500000000000014 30.85 31.000000000000021 31.200000000000014 31.450000000000014 31.500000000000011 31.750000000000007 31.750000000000021 31.90000000000002 32.050000000000011 32.400000000000013 32.450000000000017 32.650000000000013 32.70000000000001 32.85 33.050000000000018 33.200000000000003 33.350000000000016 33.400000000000006 33.400000000000013 33.450000000000003 33.450000000000017 33.600000000000016 33.650000000000006 33.700000000000003 33.70000000000001 34.050000000000018 34.150000000000006 34.200000000000017 34.350000000000009 34.600000000000016 34.600000000000016 34.700000000000003 34.700000000000003 35.000000000000007 35.150000000000013 35.200000000000003 35.300000000000004 35.300000000000011 35.450000000000003 35.85 35.850000000000016 36.100000000000016 36.35 36.400000000000006 36.550000000000011 36.6 36.600000000000016 37.100000000000009 37.20000000000001 37.35 37.549999999999997 37.650000000000006 37.70000000000001 37.799999999999997 37.800000000000011 37.950000000000024 38.000000000000014 38.15000000000002 38.299999999999997 38.350000000000016 38.5 38.500000000000007 38.500000000000007 38.950000000000003 38.999999999999993 39.350000000000009 39.550000000000018 39.600000000000009 39.800000000000011 39.850000000000016 40.000000000000007 40.150000000000006 40.300000000000011 40.300000000000011 40.350000000000009 40.500000000000007 40.500000000000007 40.650000000000013 40.850000000000016 41.000000000000007 41.500000000000014 42.050000000000018 42.349999999999994 42.800000000000004 42.800000000000004 42.800000000000011 43.100000000000009 43.550000000000004 43.600000000000009 43.850000000000009 44.3 44.7 44.900000000000006 45.850000000000009 45.90000000000002 47 2585.0000000000009 2752.2000000000016 2925.3000000000015 2958.5000000000014 2961.6000000000004 2945.0000000000018 2932.8000000000011 2924.8500000000013 2898.0000000000009 2889.2500000000005 2889.2500000000018 2839.1000000000017 2820.400000000001 2818.8000000000011 2790.7000000000016 2775.2500000000009 2746.8000000000006 2726.55 2710.1000000000017 2689.2000000000003 2668.0000000000014 2638.6000000000004 2638.6000000000008 2575.65 2575.6500000000015 2520.0000000000014 2490.1000000000004 2460.1000000000004 2460.1000000000008 2417.5500000000011 2390.5000000000005 2359.8000000000011 2335.8000000000006 2318.2000000000012 2318.2000000000012 2255.5 2255.5 2205.0000000000005 2179.3000000000006 2147.2000000000003 2118.0000000000005 2118.0000000000009 2056.1000000000004 2043.45 2043.450000000001 1985.5000000000009 1962.9 1929.2000000000003 1900.6000000000006 1866.6000000000001 1866.6000000000008 1817.9000000000003 1785.6000000000004 1755.45 1727.3 1694.2500000000002 1658.8000000000004 1625.3999999999999 1625.4000000000005 1555.950000000001 1520.0000000000005 1487.8500000000008 1455.3999999999999 1418.9500000000005 1386 1386.0000000000002 1386.0000000000002 1285.3500000000001 1247.9999999999998 1219.8500000 000004 1186.5000000000005 1148.4000000000003 1114.4000000000003 1075.9500000000005 1040.0000000000002 1003.7500000000001 967.20000000000027 967.20000000000027 887.70000000000016 850.50000000000011 850.50000000000011 772.35000000000025 735.3000000000003 697.00000000000011 664.00000000000023 630.75000000000023 592.89999999999986 556.40000000000009 556.40000000000009 556.40000000000009 431.00000000000011 391.95000000000005 348.80000000000007 306.95000000000005 265.79999999999995 223.5 179.60000000000002 137.55000000000001 91.80000000000004 47

Ratings

SONG C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14 C15 C16 C17 C18 C19 C20 C21 C22 C23 C24 C25 C26 C27 C28 C29 C30 C31 C32 C33 C34 C35 C36 C37 C38 C39 C40 C41 C42 C43 C44 C45 C46 C47 C48 C49 C50 C51 C52 C53 C54 C55 C56 C57 C58 C59 C60 C61 C62 C63 C64 C65 C66 C67 C68 C69 C70 C71 C72 C73 C74 C75 C76 C77 C78 C79 C80 C81 C82 C83 C84 C85 C86 C87 C88 C89 C90 C91 C92 C93 C94 C95 C96 C97 C98 C99 C100
Acid Tracks 4 1 4 2 1 1 3 3 1 2 2 2 3 1 4 1 1 2 1 3 1 2 4 2 3 2 5 3 5 2 4 5 3 2 3 2 2 2 1 1 2 1 2 2 3 5 2 2 1 2 5 3 3 1 3 1 3 2 2 1 1 1 1 1 5 3 5 3 3 1 3 2 2 3
Ain’t No Sunshine (When She’s Gone) 3 3 5 3 4 2 2 1 3 4 3 4 3 5 5 2 2 4 1 2 3 3 2 4 1 4 3 1 4 2 3 3 1 1 1 2 5 3 5 1 4 5 5 1 1 3 1 2 4 2 2 1 3 3 1 3 1 1 2 2 3 1 3 1 2 1 2 3 2 3 3 1 1 5 1 1 1 5 4 3 3 2 3 1 1 4
Ain’t Talkin’ ‘Bout Love 1 2 4 3 1 3 2 5 3 2 3 3 1 4 3 3 2 3 5 1 1 2 2 3 2 2 5 5 1 2 3 3 3 3 2 5 2 4 2 1 2 1 2 1 3 1 3 3 3 1 3 5 1 5 3 3 1 1 3 2 1 4 3 3 2 2 4 3 2 5 1 3 3 1 3 5 3 1 3 2 4 2 1 2 1 1 5 1
Another One Bites The Dust 4 3 2 3 5 3 1 1 1 5 3 3 3 5 1 1 2 3 4 1 3 2 3 2 3 5 2 2 3 2 3 2 1 3 2 5 1 1 3 5 1 1 3 1 1 1 1 1 3 1 2 4 2 2 1 3 3 2 2 2 1 1 3 1 3 4 2 2 1 1 3 4 3 1 3
Big City Blues 3 2 5 3 1 3 2