Many sellers offer rebates to customers. For example, a seller may sell a product at $100 with $20 rebate. In such a case, each customer purchases the product at $100. If the customer claims the rebate, the seller will pay the customer $20 back. On the other hand, if the customer fails to claim the rebate, he will not receive any money back.
BestWare is trying to determine whether to give a $10 to promote its software next week. Currently, 5000 units of the software are sold at $50 per unit each week. A $10 rebate will increase the sales by B (B is a parameter indicating the percentage increase in sales.For example, if B is equal to 10%, it means the sales is increased from 5000 to 5500), which means BestWare is uncertain about B. But BestWare knows that 60% of the customers who buy the software will claim the rebate and get the rebate from BestWare. The variable cost of the software is determined by how many units could be sold next week:
|
Units Sold
|
Variable Cost per Unit
|
|
6200 or more
|
$3
|
|
5300 ~ 6199
|
$4
|
|
5100 ~ 5299
|
$5
|
|
Less than 5100
|
$6
|
For example, if 5200 units are sold, each will cost $5.
1) Build an Excel model that can accept B as input, and then calculate the profit earned.
First of all, only green cells and the yellow cell above have numbers entered. All other cells in figure above should have formulas entered. Do not enter numbers directly in formulas. Instead, use cell references. If you don't understand this, please read "Separate Data from Formulas" located in the Slides/Excel Slides folder on blackboard. Violating this rule will result in up to 40 points off.
New demand is calculated based on current demand and B. Its formula is current demand * (1+B) or equivalently (current demand + current demand * B).
Revenue is new demand multiplied by price.
Unit cost is calculated based on the following table:
|
Units Sold
|
Variable Cost per Unit
|
|
6200 or more
|
$3
|
|
5300 ~ 6199
|
$4
|
|
5100 ~ 5299
|
$5
|
|
Less than 5100
|
$6
|
For example, if 5200 units are sold, unit cost will be $5.
Here you need to use NestedIF function to do the calculation. Again, make sure you enter cell references rather than values such as 6200 into the NestedIF formula.
Total cost is unit cost multiplied by new demand.
Rebate claimed is Rebate * New Demand * (% claimed). For example, suppose 10,000 units are sold, then 60% of customers claim their rebates and each receives a rebate check of $10. Therefore total rebate claimed will be 10,000*60%*10=$60,000.
Profit is revenue - (total cost + rebate claimed).
2 Use One-Way data table to calculate the profit earned next week, given that B is from 1% to 100%, with an increment of 1% (i.e., 1, 2%, 3%, ..., 98%, 99%, 100%). The table will be very tall with 100 rows. For example, in the figure blow, it shows that if demand is increased by 1%, profit will be $191,900. Here you need to use one-way data table. No points will be given if other methods are used.