Excel Case Study: Differential Analysis

In July $2011,$ Netfilix 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 thsplit 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 had about $25$ million subscribers who were paying an average subscription fee of $$10$ per month.

After the split, Netflix estimated the following:

$21$ million subscribers would continue with the streaming video service.

$12$ million of those subscribers would also continue with the DVD$-$by$-$mail service.

$3$ million users would subscribe to DVD$-$by$-$mail only.

The new subscription fee for each service is $$8($or $$16$ for both services$)$ per month. $($ $$8$ 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\mathrm{.}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:

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 A$1$ Type in a Title that describes your analysis and Merge and Center it across cells A$1-$D$1$

b$.$ Type in Column headings $($You will have to resize your columns$)$:

i$.$ Cell B$2$: NetFlix Old System $($Option $1)$

ii$.$ Cell C$2$: NetFFlix New System $($Option $2)$

iii. Cell D$2$: Differential of Selecting the New System

c$.$ Beginning in cell A$3$ Type in Row Headings $($Monthly Revenues from DVD by Mail $($Cell A$3),$ Monthly Revenues from Steaming $($Cell A$4),$ Total Month$/$ Revenues $($Cell A$5)$ and calculate these for both the Netflix Old System and the Netfilix New System $($Use formulas in Excel$)$

d$.$ Under your Revenues, you can begin a new row heading in Cell A$7$ 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 A$9,$ you can begin a new row titled Monthly income$/$loss and calculate Monthly net income or loss for both options in cells B$9$ and C$9.($Use formulas in excel$)$