*Need to use Excel Formulas

In July 2011, Netflix decided to separate its DVD-by-mail service from its streaming video service. In addition to the hassle of receiving two bills instead of one, Netflix subscribers now had to pay $16 for both services, when they previously only paid $10. Many Netflix customers were outraged by this decision, and the company reportedly lost about 1 million of its 25 million subscribers due to this decision In response to the uproar from customers and investors, Netflix co-founder and CEO posted a letter of explanation on the company website. He did not apologize for the decision to split the services and raise prices, which he maintained was the right strategic decision given the importance of streaming video to Netflix's future. However, he acknowledged that he should have done a better job of communicating the rationale for the change to customers in advance of making the change. Consider the following additional information and estimates: Prior to the split Netflix has about 25 million subscribers who were paying an average subscription fee of $10 per month. After the split, Netflix estimated the following: o 21 million subscribers would continue with the streaming video service. o 12 million of those subscribers would also continue with the DVD-by-mail service. o 3 million users would subscribe to DVD-by-mail only. The new subscription fee for each service is $8 per month. (S8 for the DVD-by-mail service and $8 for the streaming service) Assume that variable costs of the DVD-by-mail service (for shipping, handling, and DVD replacement) are $0.40 per movie exchange and that the average user exchanges 5 movies per month. The variable costs of the streaming video service are insignificant. . Required: 1. Using differential analysis, determine how much Netflix's monthly profit would increase or decrease with the new pricing and subscription structure. Do this by setting up a Microsoft Excel document using formulas to calculate the following: a. In Cell A1 Type in a Title that describes your analysis and Merge and Center it across cells A1-D1 b. Type in Column headings (You will have to resize your columns): i. Cell B2: NetFlix Old System (Option 1) ii. Cell C2: NetFlix New System (Option 2) iii. Cell D2: Differential of Selecting the New System c. Beginning in cell A3 Type in Row Headings (monthly revenues from DVD by mail (Cell A3), Monthly Revenues from Steaming (Cell A4), Total Monthly Revenues (Cell A5) and calculate these for both the Netflix Old System and the Netflix New System (Use formulas in Excel) d. Under your Revenues, you can begin a new row heading in Cell A7 for Total Monthly Variable costs of DVD by mail and calculate this in the correct columns for both options. (Use formulas in excel) e. In Cell A9, you can begin a new row titled Monthly income/loss and calculate Monthly net income or loss for both options in cells B9 and 09. (Use formulas in excel) f. Calculate the differential analysis in Column D. Your differential analysis should consider the total monthly revenue, the total monthly variable costs of DVD by mail and the total monthly Income/Loss. (Use formulas in excel) g. Format all of the numbers you calculated to Accounting with zero decimal places. 2. Which option generated the most revenue? 3. Which option costs the least in variable costs? 4. In your opinion was Netflix's decision to separate the streaming video from DVD-by-mail a good one? Why or why not? 5. Do you think Netflix made the change in order to boost short-term or long-term profits? Explain. Netflix Netflix Old System (Option 1) Netflix New System (Option 2) Differential of Selecting the New System Monthy Revenues from DVDs by Mail Monthy Revenues from Streaming Total Monthly Revenues Total Monthly Variable costs of DVD by Mail Monthly Income Loss