Chat with us, powered by LiveChat 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: G - Writeedu

## 14 Mar 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: G

1. Instructions

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:

1. Go to WTP tab and look for the row with total willingness to pay for each bundle (row 103, Bundle WTP).
2. 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.
3. Observe the new Bundle Demand and Bundle Profit curves.
4. 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:
5. Select the File tab in the toolbar at the top of the screen
6. Click on Options in the menu
7. This will pop open the Excel Options window, click on the Formulas option on the left
8. Uncheck the option called "R1C1 reference style"
9. Click on the OK button to save changes
10. 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. Follow the format exactly as given. No exception will be allowed for typo mistakes.
• Using the numbers created in your “Bundling – PSet1 – Part 2” Excel spreadsheet, answer the following questions.
2. What is the optimal price of a bundle? (Format as \$0.00) Blank 1
• Blank 1
3. What are the maximum profits of the firm? (Format as \$0.00) Blank 1
• Blank 1
4. How much consumer surplus do consumers keep? (Format as \$0.00) Blank 1
• Blank 1
5. At this price, what proportion of songs with positive value are not sold? (Format as 0.00%) Blank 1
• Blank 1
6. Do any consumers' bundles include songs that they did NOT rate?
1. Yes
2. No
3. Not enough information
7. How much social value is not created? (Format as \$0.00) Blank 1
• Blank 1
8. Total sales have increased, but does this mean total surplus kept by consumers has also increased?
1. No. An even higher proportion of consumer surplus has been transferred to the seller.
2. No. Consumers keep none of their surplus.
3. Yes. Because sales increase, consumer surplus also increases.
4. Yes. Because the seller has captured less surplus .
9. 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?
2. Drop all items with WTP < \$0.12 from each person’s individual bundle
3. Raise bundle price by \$0.12
4. No change, keep as-is
10. o upload your Excel Spreadsheet — "Bundling – PSet1 – Part 2". You are not required to type into the open field (skip it).

## Analysis

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteEdu. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.

Do you need an answer to this or any other questions?

Do you need help with this question?

Get assignment help from WriteEdu.com Paper Writing Website and forget about your problems.

WriteEdu provides custom & cheap essay writing 100% original, plagiarism free essays, assignments & dissertations.

With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Chat with us today! We are always waiting to answer all your questions.

 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