21 Mar Please review the word docs and follow the steps for the excel.? Please answer the questions in the word doc on the second page.
Please review the word docs and follow the steps for the excel.
Please answer the questions in the word doc on the second page.
Thank you.
Step 1
Prepare the date – Replace all bundle prices (highlighted in YELLOW) on the Analysis tab of the working sheet — “Bundling – PSet1 – Part 2” — by following these steps:
Go to WTP tab and look for the row with total willingness to pay for each bundle (row 103, Bundle WTP).
Add a new sheet/tab and use Excel’s transpose function to copy/paste these values in a vertical array. Copy this array and paste ‘values only’ in a separate column. Sort this column from smallest to largest values. Copy/paste the sorted values in Analysis tab to replace the bundle prices.
Observe the new Bundle Demand and Bundle Profit curves.
Step 2
For students in EU: You may need to change an Excel setting so all periods '.' do not get changed to commas ','. This may be the case for both Windows (see the adjacent cell) and Macintosh users. See instructions below to avoid this problem. (https://resrequest.helpspot.com/index.php?pg=kb.page&id=277). Instructions about how to change column labels from numbers to letters:
Select the File tab in the toolbar at the top of the screen
Click on Options in the menu
This will pop open the Excel Options window, click on the Formulas option on the left
Uncheck the option called "R1C1 reference style"
Click on the OK button to save changes
Step 3
In the Analysis tab, calculate the empty cells highlighted in YELLOW to answer the Part 2 questions below.
Hint: Do not forget that you have other tabs to utilize for this assignment. Review the prompts and formulas available in the spreadsheet as you work through each question.
For numeric values, you should enter up to 2 decimal places.
Question 1 What is the optimal price of a bundle? (Format as $0.00) _______
Question 2 What are the maximum profits of the firm? (Format as $0.00) _______
Question 3 How much consumer surplus do consumers keep? (Format as $0.00) _______
Question 4 At this price, what proportion of songs with positive value are not sold? (Format as 0.00%) _______
Question 5 Do any consumers' bundles include songs that they did NOT rate?
Yes
No
Not enough information
Question 6 How much social value is not created? (Format as $0.00) Blank 1
Question 7 Total sales have increased, but does this mean total surplus kept by consumers has also increased?
No. An even higher proportion of consumer surplus has been transferred to the seller.
No. Consumers keep none of their surplus.
Yes. Because sales increase, consumer surplus also increases.
Yes. Because the seller has captured less surplus.
Question 8 What other change should be made to the original pricing strategy if the MC of each good is MC=$0.12 rather than MC=$0?
Return to independent good pricing
Drop all items with WTP < $0.12 from each person’s individual bundle
Raise bundle price by $0.12
No change, keep as-is
,
Analysis
| ITEM SALES | ITEM PRICE | PROFIT | BUN SALES | BUN PRICE | BUN PROFIT | ||||||||||||||
| 8000 | 0.10 | 800.00 | 100 | 1 | 100.00 | <———— | Bundled price must change to the sorted (and transposed) Total WTP for each Consumer from the WTP sheet | ||||||||||||
| 6000 | 0.25 | 1500.00 | 99 | 2 | 198.00 | ||||||||||||||
| 4000 | 0.50 | 2000.00 | 98 | 3 | 294.00 | ||||||||||||||
| 1700 | 0.90 | 1530.00 | 97 | 4 | 388.00 | ||||||||||||||
| 200 | 1.50 | 300.00 | 96 | 5 | 480.00 | ||||||||||||||
| 95 | 6 | 570.00 | |||||||||||||||||
| 94 | 7 | 658.00 | |||||||||||||||||
| 93 | 8 | 744.00 | |||||||||||||||||
| 92 | 9 | 828.00 | |||||||||||||||||
| 91 | 10 | 910.00 | |||||||||||||||||
| 90 | 11 | 990.00 | |||||||||||||||||
| 89 | 12 | 1068.00 | |||||||||||||||||
| 88 | 13 | 1144.00 | |||||||||||||||||
| 87 | 14 | 1218.00 | |||||||||||||||||
| 86 | 15 | 1290.00 | |||||||||||||||||
| 85 | 16 | 1360.00 | |||||||||||||||||
| 84 | 17 | 1428.00 | |||||||||||||||||
| 83 | 18 | 1494.00 | |||||||||||||||||
| 82 | 19 | 1558.00 | |||||||||||||||||
| 81 | 20 | 1620.00 | |||||||||||||||||
| 80 | 21 | 1680.00 | |||||||||||||||||
| 79 | 22 | 1738.00 | |||||||||||||||||
| 78 | 23 | 1794.00 | |||||||||||||||||
| 77 | 24 | 1848.00 | |||||||||||||||||
| Best Price: | $ 0.50 | <- Choose cell with price that maximizes profit (this will adjust item Consumer Surplus ) | 76 | 25 | 1900.00 | Best Price: | <- Choose cell with price that maximizes profit (this will adjust Bundle Consumer Surplus) | ||||||||||||
| Max Profit: | $ 2,000.00 | <- Choose cell with max profit | 75 | 26 | 1950.00 | Max Profit: | $ 2,550.00 | <- Choose cell with max profit | |||||||||||
| Total Available CS | $ 3,740.20 | Total consumer value of all songs | 74 | 27 | 1998.00 | Total Available CS | $ 3,740.20 | Total consumer value of all songs | |||||||||||
| Avg Item WTP | $ 0.47 | How much consumers are willing to pay for items on average | 73 | 28 | 2044.00 | Avg Bundle WTP | $ 37.40 | How much consumers are willing to pay for bundles on average? | |||||||||||
| Mode Item WTP | Mode of the distribution for single item WTP | 72 | 29 | 2088.00 | Total CS kept | Total (WTP – Price Paid) across all sold bundles | |||||||||||||
| Total CS kept | Total (WTP – Price Paid) across all sold songs | 71 | 30 | 2130.00 | % Unsold | Total WTP such that 0 < WTP < Price | |||||||||||||
| % Unsold | Total WTP such that 0 < WTP < Price | 70 | 31 | 2170.00 | Missing Surplus | Unsold bundles = Total Avail CS – Profit – Kept CS | |||||||||||||
| Missing Surplus | Total Value of Unsold items = Total Avail CS – Profit – CS Kept | 69 | 32 | 2208.00 | |||||||||||||||
| 68 | 33 | 2244.00 | |||||||||||||||||
| 67 | 34 | 2278.00 | |||||||||||||||||
| 66 | 35 | 2310.00 | |||||||||||||||||
| 65 | 36 | 2340.00 | |||||||||||||||||
| 64 | 37 | 2368.00 | |||||||||||||||||
| 63 | 38 | 2394.00 | |||||||||||||||||
| COMMENT: | "=COUNTIF(WTP!$B$2:$CW$101,CONCATENATE(">=",TEXT(B2,"0.00")))" | 62 | 39 | 2418.00 | |||||||||||||||
| 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 | 40 | 2440.00 | |||||||||||||||
| 60 | 41 | 2460.00 | COMMENT: | "=SUM(WTP!B106:CW106)/(COUNTIF(WTP!B2:CW101,">0"))" | |||||||||||||||
| COMMENT: | "=(COUNTIF(WTP!B2:CW101,"<"&B26))/(COUNTIF(WTP!B2:CW101,">0"))" | 59 | 42 | 2478.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 | 43 | 2494.00 | |||||||||||||||
| 57 | 44 | 2508.00 | |||||||||||||||||
| 56 | 45 | 2520.00 | |||||||||||||||||
| 55 | 46 | 2530.00 | |||||||||||||||||
| 54 | 47 | 2538.00 | |||||||||||||||||
| 53 | 48 | 2544.00 | |||||||||||||||||
| 52 | 49 | 2548.00 | |||||||||||||||||
| 51 | 50 | 2550.00 | |||||||||||||||||
| 50 | 51 | 2550.00 | |||||||||||||||||
| 49 | 52 | 2548.00 | |||||||||||||||||
| 48 | 53 | 2544.00 | |||||||||||||||||
| 47 | 54 | 2538.00 | |||||||||||||||||
| 46 | 55 | 2530.00 | |||||||||||||||||
| 45 | 56 | 2520.00 | |||||||||||||||||
| 44 | 57 | 2508.00 | |||||||||||||||||
| 43 | 58 | 2494.00 | |||||||||||||||||
| 42 | 59 | 2478.00 | |||||||||||||||||
| 41 | 60 | 2460.00 | |||||||||||||||||
| 40 | 61 | 2440.00 | |||||||||||||||||
| 39 | 62 | 2418.00 | |||||||||||||||||
| 38 | 63 | 2394.00 | |||||||||||||||||
| 37 | 64 | 2368.00 | |||||||||||||||||
| 36 | 65 | 2340.00 | |||||||||||||||||
| 35 | 66 | 2310.00 | |||||||||||||||||
| 34 | 67 | 2278.00 | |||||||||||||||||
| 33 | 68 | 2244.00 | |||||||||||||||||
| 32 | 69 | 2208.00 | |||||||||||||||||
| 31 | 70 | 2170.00 | |||||||||||||||||
| 30 | 71 | 2130.00 | |||||||||||||||||
| 29 | 72 | 2088.00 | |||||||||||||||||
| 28 | 73 | 2044.00 | |||||||||||||||||
| 27 | 74 | 1998.00 | |||||||||||||||||
| 26 | 75 | 1950.00 | |||||||||||||||||
| 25 | 76 | 1900.00 | |||||||||||||||||
| 24 | 77 | 1848.00 | |||||||||||||||||
| 23 | 78 | 1794.00 | |||||||||||||||||
| 22 | 79 | 1738.00 | |||||||||||||||||
| 21 | 80 | 1680.00 | |||||||||||||||||
| 20 | 81 | 1620.00 | |||||||||||||||||
| 19 | 82 | 1558.00 | |||||||||||||||||
| 18 | 83 | 1494.00 | |||||||||||||||||
| 17 | 84 | 1428.00 | |||||||||||||||||
| 16 | 85 | 1360.00 | |||||||||||||||||
| 15 | 86 | 1290.00 | |||||||||||||||||
| 14 | 87 | 1218.00 | |||||||||||||||||
| 13 | 88 | 1144.00 | |||||||||||||||||
| 12 | 89 | 1068.00 | |||||||||||||||||
| 11 | 90 | 990.00 | |||||||||||||||||
| 10 | 91 | 910.00 | |||||||||||||||||
| 9 | 92 | 828.00 | |||||||||||||||||
| 8 | 93 | 744.00 | |||||||||||||||||
| 7 | 94 | 658.00 | |||||||||||||||||
| 6 | 95 | 570.00 | |||||||||||||||||
| 5 | 96 | 480.00 | |||||||||||||||||
| 4 | 97 | 388.00 | |||||||||||||||||
| 3 | 98 | 294.00 | |||||||||||||||||
| 2 | 99 | 198.00 | |||||||||||||||||
| 1 | 100 | 100.00 |
ITEM DEMAND
ITEM PRICE 8000 6000 4000 1700 200 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 90 89 88 87 86 85 84 83 82 81 80 79 78 77 76 75 74 73 72 71 70 69 68 67 66 65 64 63 62 61 60 59 58 57 56 55 54 53 52 51 50 49 48 47 46 45 44 43 42 41 40 39 38 37 36 35 34 33 32 31 30 29 28 27 26 25 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
Bundle Sales
Bundle Price
PROFIT 0.1 0.25 0.5 0.9 1.5 800 1500 2000 1530 300
BUNDLE PROFIT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 100 198 294 388 480 570 658 744 828 910 990 1068 1144 1218 1290 1360 1428 1494 1558 1620 1680 1738 1794 1848 1900 1950 1998 2044 2088 2130 2170 2208 2244 2278 2310 2340 2368 2394 2418 2440 2460 2478 2494 2508 2520 2530 2538 2544 2548 2550 2550 2548 2544 2538 2530 2520 2508 2494 2478 2460 2440 2418 2394 2368 2340 2310 2278 2244 2208 2170 2130 2088 2044 1998 1950 1900 1848 1794 1738 1680 1620 1558 1494 1428 1360 1290 1218 1144 1068 990 910 828 744 658 570 480 388 294 198 100
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 | 1 | 2 | 2 | 1 | 1 | 5 | 2 | 3 | 5 | 3 | 3 | 1 | 3 | 1 | 4 | 1 | 2 | 3 | 1 | 2 | 2 | 3 | 2 | 2 | 1 | 3 | 3 | 5 | 3 | 2 | 3 | 2 | 1 | 3 | 3 | 1 | 2 | 5 | 3 | 3 | 5 | 5 | 4 | 2 | 2 | 2 | 3 | 2 | 3 | 2 | 1 | 1 | 4 | 4 | 2 | 3 | 2 | 3 | 5 | 2 | 3 | 1 | 4 | 3 | 4 | 3 | 2 | 1 | 4 | 5 | 4 | 3 | 3 | 1 | 1 | 3 | 2 | 5 | 3 | 5 | 2 | 2 | 1 | 1 | 2 | |||||||||||||||
| Ain't No Sunshine (When She's Gone) | 1 | 2 | 1 | 3 | 5 | 1 | 4 | 2 | 2 | 2 | 2 | 1 | 3 | 3 | 3 | 1 | 1 | 5 | 2 | 5 | 4 | 5 | 3 | 5 | 1 | 2 | 2 | 2 | 1 | 5 | 2 | 1 | 2 | 3 | 3 | 1 | 4 | 3 | 1 | 4 | 1 | 3 | 3 | 2 | 1 | 4 | 5 | 5 | 3 | 3 | 3 | 3 | 1 | 5 | 3 | 2 | 1 | 3 | 2 | 2 | 3 | 5 | 2 | 2 | 2 | 1 | 3 | 3 | 3 | 3 | 1 | 4 | 1 | 5 | 1 | 1 | 4 | |||||||||||||||||||||||
| Ain't Talkin' 'Bout Love | 2 | 2 | 2 | 4 | 3 | 3 | 2 | 5 | 2 | 3 | 4 | 2 | 3 | 1 | 3 | 3 | 1 | 2 | 4 | 3 | 2 | 4 | 5 | 2 | 1 | 2 | 4 | 1 | 1 | 1 | 2 | 3 | 3 | 2 | 2 | 2 | 2 | 3 | 2 | 1 | 1 | 4 | 1 | 4 | 1 | 2 | 1 | 1 | 1 | 5 | 5 | 2 | 3 | 3 | 1 | 2 | 3 | 1 | 1 | 3 | 2 | 5 | 3 | 2 | 3 | 2 | 4 | 2 | 2 | 2 | 5 | 2 | 2 | 1 | 2 | 1 | 2 | 2 | 2 | 5 | 3 | 1 | 3 | |||||||||||||||||
| Another One Bites The Dust | 4 | 2 | 2 | 1 | 4 | 4 | 5 | 3 | 1 | 3 | 1 | 4 | 4 | 3 | 3 | 1 | 2 | 5 | 3 | 2 | 1 | 2 | 3 | 4 | 3 | 3 | 5 | 3 | 4 | 3 | 3 | 2 | 5 | 1 | 3 | 2 | 1 | 3 | 4 | 5 | 2 | 1 | 1 | 5 | 2 | 3 | 2 | 1 | 4 | 3 | 1 | 5 | 1 | 2 | 3 | 2 | 1 | 1 | 3 | 1 | 2 | 1 | 1 | 2 | 2 | 3 | 2 | 3 | 4 | 5 | 4 | 2 | 4 | 1 | 4 | 2 | 1 | 3 | 3 | 5 | 3 | 2 | 3 | 2 | ||||||||||||||||
| Big City Blues | 1 | 3 | 4 | 2 | 5 | 3 | 3 | 1 | 1 | 3 | 3 | 3 | 4 | 1 | 3 | 2 | 1 | 3 | 4 | 1 | 2 | 1 | 2 | 3 | 3 | 2 | 3 | 2 | 4 | 2 | 5 | 3 | 1 | 3 | 4 | 2 | 2 | 2 | 2 | 1 | 1 | 1 | 1 | 4 | 3 | 2 | 3 | 4 | 2 | 1 | 3 | 2 | 2 | 3 | 3 | 3 | 3 | 2 | 1 | 1 | 4 | 2 | 3 | 3 | 1 | 1 | 2 | 3 | 4 | 2 | 2 | 3 | 1 | 2 | 2 | 3 | 5 | 1 | 4 | 3 | 5 | 1 | ||||||||||||||||||
| Blister In The Sun | 1 | 4 | 4 | 4 | 3 | 1 | 4 | 4 | 1 | 3 | 4 | 4 | 5 | 3 | 1 | 3 | 3 | 4 | 2 | 4 | 2 | 3 | 1 | 2 | 3 | 2 | 3 | 3 | 5 | 2 | 1 | 4 | 3 | 2 | 2 | 4 | 3 | 2 | 1 | 4 | 2 | 3 | 2 | 3 | 2 | 1 | 4 | 1 | 2 | 1 | 3 | 2 | 2 | 3 | 4 | 2 | 3 | 3 | 2 | 2 | 1 | 1 | 1 | 2 |