New York University



Solution to Microsoft Rebate vs. Price Cut[1]

Microsoft is trying to determine whether to give a $10 rebate, a $6 price cut, or have no price change on a software product. Currently 40,000 units of the product are sold each week for $45. The variable cost of the product is $5. The most likely case appears to be that a $10 rebate will increase sales 30% and half of all people will claim the rebate. For the price cut, the most likely case is that sales will increase 20%.

a. Given all other assumptions, what increase in sales from the rebate would make the rebate and price cut equally desirable?

First, note that there are three possible profit functions. Under the current policy,

|Profit |= Variable Revenue – Variable Cost |

| |= Volume*(Price – Variable Cost) |

| |[pic] |

| |[pic] |

Under the rebate policy:

|Profit |= Variable Revenue – Variable Cost – Rebate Cost |

| |= Volume*(Price – Variable Cost) – (Claim Volume*Rebate) |

| |[pic] |

| |[pic] |

With the price cut:

|Profit |= Variable Revenue – Variable Cost |

| |= Volume*(Price – Variable Cost) |

| |[pic] |

| |[pic] |

Here is a spreadsheet model with all three possible profit scenarios.

[pic]

Based on all of the current assumptions, the current policy is not optimal, and Microsoft ought to use the rebate policy. However, the question is, what does the effect of the rebate on sales (the value in cell B9 in our model) have to be for the rebate to be preferable to the price cut?

We are basically trying to find the break-even point between the two possible policies. Goal Seek is a good method for this sort of problem.

[pic]

[pic]

Answer: The rebate is preferable as long as the improvement in sales is expected to be at least 16.57%.

b. Microsoft does not really know the increase in sales that will result from a rebate or price cut. However, it is quite sure that the rebate will increase sales by between 15% and 40% and that the price cut will increase sales by between 10% and 30%. Perform a sensitivity analysis that could be used to help determine Microsoft's course of action.

In this case, we need to study the effects of two input variables on Microsoft’s decision. A two-way data table is a good tool for this.

First, we’ll use the “if” function to set up cell E1 to tell Microsoft which policy is best:

[pic]

Now, we create the input values for our two-way data table:

[pic]

Our output cell is E1 (the one with the “if” statement telling us which policy is best), so we type “=E1” in the upper left cell of our data table (cell D8).

Select the range D8:I14, and choose Data — Table. Fill in the dialog box as shown:

[pic]

Excel generates the following table:

[pic]

It would appear that the rebate is a reasonably robust strategy; it is optimal over a wide range of scenarios.

-----------------------

[1] Based on 2-7 (p. 59) in Practical Management Science (2nd ed., Winston and Albright, 2001 Duxbury Press). Solution by David Juran, 2001.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download