math help
teron50
racquetballcase_noi.xlsdata provided - setup NPV
| New Ball | $ 0.52 | |||||
| Wodraw Ball | $ 0.95 | |||||
| Rate | 10% | |||||
| Low fixed Cost | 4,000,000 | |||||
| High fixed Cost | 6,000,000 | |||||
| Year | # of players per thousands | retail price per ball | Ball sold in millions | Balls/Player(000) | ||
| 1985 | 600 | $ 1.75 | 5.932 | 0.01 | ||
| 1986 | 635 | $ 1.75 | 6.229 | 0.01 | ||
| 1987 | 655 | $ 1.80 | 6.506 | 0.01 | ||
| 1988 | 700 | $ 1.90 | 6.820 | 0.01 | ||
| 1989 | 730 | $ 1.90 | 7.161 | 0.01 | ||
| 1990 | 762 | $ 1.90 | 7.895 | 0.01 | ||
| 1991 | 812 | $ 2.00 | 7.895 | 0.01 | ||
| 1992 | 831 | $ 2.20 | 8.224 | 0.01 | ||
| 1993 | 877 | $ 2.45 | 8.584 | 0.01 | ||
| 1994 | 931 | $ 2.45 | 9.026 | 0.01 | ||
| 1995 | 967 | $ 2.60 | 9.491 | 0.01 | ||
| 1996 | 1,020 | $ 2.55 | 9.996 | 0.01 | ||
| 1997 | 1,077 | $ 2.50 | 10.465 | 0.01 | ||
| 1998 | 1,139 | $ 2.50 | 10.981 | 0.01 | ||
| Player Growth Rate for 10 yrs | 10% | |||||
| Price Ratio | % who would buy new ball | |||||
| 0.50 | 0% | |||||
| 1.00 | 11% | |||||
| 1.50 | 41% | |||||
| 2.00 | 76% | |||||
| 2.50 | 95% | |||||
| 3.00 | 100% | |||||
| Net Present Value if selling the ball at | $ 0.65 | |||||
| Price Ratio | $ 3.85 | |||||
| Incremental Years | Year | # of players per thousands | retail price per ball | Ball sold in millions | Profit | PV |
| 0 | 1999 | $ (5,000,000.00) | $ (5,000,000.00) | |||
| 0 | 1999 | 1,253 | $ 0.65 | 12.079 | $ 1,570,283 | $ 1,570,283 |
| 1 | 2000 | 1,378 | $ 0.65 | 13.287 | $ 1,727,311 | $ 1,570,283 |
| 2 | 2001 | 1,516 | $ 0.65 | 14.616 | $ 1,900,042 | $ 1,570,283 |
| 3 | 2002 | 1,668 | $ 0.65 | 16.077 | $ 2,090,047 | $ 1,570,283 |
| 4 | 2003 | 1,834 | $ 0.65 | 17.685 | $ 2,299,051 | $ 1,570,283 |
| 5 | 2004 | 2,018 | $ 0.65 | 19.454 | $ 2,528,956 | $ 1,570,283 |
| 6 | 2005 | 2,220 | $ 0.65 | 21.399 | $ 2,781,852 | $ 1,570,283 |
| 7 | 2006 | 2,442 | $ 0.65 | 23.539 | $ 3,060,037 | $ 1,570,283 |
| 8 | 2007 | 2,686 | $ 0.65 | 25.893 | $ 3,366,041 | $ 1,570,283 |
| 9 | 2008 | 2,954 | $ 0.65 | 28.482 | $ 3,702,645 | $ 1,570,283 |
| 10 | 2009 | 3,250 | $ 0.65 | 31.330 | $ 4,072,910 | $ 1,570,283 |
| NPV ------> | $ 12,273,113 | |||||
| I have done it for 10 years - you can stretch it further if need be |
Mean of initial investment
Assuming ball/player ratio will remain constant in the future
since price ratio is greater than 3 adoption rate will be 100%
10% per year growth in players
Based on int rate of 10%
data provided - setup NPV
Chart for Q7
% who would buy new ball
part a - b
| A) Base Case | |||||||||
| Base Case in this situation would be the worst adoption rate; ie, what would happen if we go ahead and produce the balls and there is minimal adoption | |||||||||
| Incremental Years | Year | # of players per thousands | retail price per ball | Ball sold in millions | Profit | PV | |||
| 0 | 1999 | $ (5,000,000.00) | $ (5,000,000.00) | ||||||
| 0 | 1999 | 1,253 | $ 2.50 | 1.329 | $ 2,630,827.98 | $ 2,630,828 | |||
| 1 | 2000 | 1,378 | $ 2.50 | 1.462 | $ 2,893,910.78 | $ 2,893,911 | |||
| 2 | 2001 | 1,516 | $ 2.50 | 1.608 | $ 3,183,301.86 | $ 3,183,302 | |||
| 3 | 2002 | 1,668 | $ 2.50 | 1.769 | $ 3,501,632.04 | $ 3,501,632 | |||
| 4 | 2003 | 1,834 | $ 2.50 | 1.945 | $ 3,851,795.25 | $ 3,851,795 | |||
| 5 | 2004 | 2,018 | $ 2.50 | 2.140 | $ 4,236,974.77 | $ 4,236,975 | |||
| 6 | 2005 | 2,220 | $ 2.50 | 2.354 | $ 4,660,672.25 | $ 4,660,672 | |||
| 7 | 2006 | 2,442 | $ 2.50 | 2.589 | $ 5,126,739.47 | $ 5,126,739 | |||
| 8 | 2007 | 2,686 | $ 2.50 | 2.848 | $ 5,639,413.42 | $ 5,639,413 | |||
| 9 | 2008 | 2,954 | $ 2.50 | 3.133 | $ 6,203,354.76 | $ 6,203,355 | |||
| 10 | 2009 | 3,250 | $ 2.50 | 3.446 | $ 6,823,690.24 | $ 6,823,690 | |||
| NPV ------> | $ 43,752,313 | ||||||||
| I have done it for 10 years - you can stretch it further if need be | |||||||||
| B) Sensitivity Analysis | |||||||||
| Price Ratio | 0.5 | 1.0 | 1.5 | 2.0 | 2.5 | 3.0 | |||
| Selling Price | $ 5.00 | $ 2.50 | $ 1.67 | $ 1.25 | $ 1.00 | $ 0.83 | |||
| % who would buy new ball | 0% | 11% | 41% | 76% | 95% | 100% | |||
| Incremental Years | Year | # of players per thousands | Potential Balls that can be sold (millions) | Ball sold in millions | Ball sold in millions | Ball sold in millions | Ball sold in millions | Ball sold in millions | Ball sold in millions |
| 0 | 1999 | 1,253 | 12.079 | - 0 | 1.329 | 4.952 | 9.180 | 11.475 | 12.079 |
| 1 | 2000 | 1,378 | 13.287 | - 0 | 1.462 | 5.448 | 10.098 | 12.623 | 13.287 |
| 2 | 2001 | 1,516 | 14.616 | - 0 | 1.608 | 5.992 | 11.108 | 13.885 | 14.616 |
| 3 | 2002 | 1,668 | 16.077 | - 0 | 1.769 | 6.592 | 12.219 | 15.273 | 16.077 |
| 4 | 2003 | 1,834 | 17.685 | - 0 | 1.945 | 7.251 | 13.441 | 16.801 | 17.685 |
| 5 | 2004 | 2,018 | 19.454 | - 0 | 2.140 | 7.976 | 14.785 | 18.481 | 19.454 |
| 6 | 2005 | 2,220 | 21.399 | - 0 | 2.354 | 8.774 | 16.263 | 20.329 | 21.399 |
| 7 | 2006 | 2,442 | 23.539 | - 0 | 2.589 | 9.651 | 17.889 | 22.362 | 23.539 |
| 8 | 2007 | 2,686 | 25.893 | - 0 | 2.848 | 10.616 | 19.678 | 24.598 | 25.893 |
| 9 | 2008 | 2,954 | 28.482 | - 0 | 3.133 | 11.678 | 21.646 | 27.058 | 28.482 |
| 10 | 2009 | 3,250 | 31.330 | - 0 | 3.446 | 12.845 | 23.811 | 29.764 | 31.330 |
| Selling Price | $ 5.00 | $ 2.50 | $ 1.67 | $ 1.25 | $ 1.00 | $ 0.83 | |||
| Contribution Margin Analysis | Year | Contribution Margin (MILLIONS) | |||||||
| 1999 | $ - 0 | $ 2.63 | $ 5.68 | $ 6.70 | $ 5.51 | $ 3.78 | |||
| 2000 | $ - 0 | $ 2.89 | $ 6.25 | $ 7.37 | $ 6.06 | $ 4.16 | |||
| 2001 | $ - 0 | $ 3.18 | $ 6.87 | $ 8.11 | $ 6.66 | $ 4.58 | |||
| 2002 | $ - 0 | $ 3.50 | $ 7.56 | $ 8.92 | $ 7.33 | $ 5.04 | |||
| 2003 | $ - 0 | $ 3.85 | $ 8.31 | $ 9.81 | $ 8.06 | $ 5.54 | |||
| 2004 | $ - 0 | $ 4.24 | $ 9.15 | $ 10.79 | $ 8.87 | $ 6.10 | |||
| 2005 | $ - 0 | $ 4.66 | $ 10.06 | $ 11.87 | $ 9.76 | $ 6.70 | |||
| 2006 | $ - 0 | $ 5.13 | $ 11.07 | $ 13.06 | $ 10.73 | $ 7.38 | |||
| 2007 | $ - 0 | $ 5.64 | $ 12.17 | $ 14.37 | $ 11.81 | $ 8.11 | |||
| 2008 | $ - 0 | $ 6.20 | $ 13.39 | $ 15.80 | $ 12.99 | $ 8.92 | |||
| 2009 | $ - 0 | $ 6.82 | $ 14.73 | $ 17.38 | $ 14.29 | $ 9.82 |
10% per year growth in players
Assuming ball/player ratio will remain constant in the future
Mean of initial investment
Based on int rate of 10%
since price ratio 1, adoption rate will be 11%
since worst adoption is at price ratio of 1, so it means we will also sell same price as competition
part a - b
$5.00
$2.50
$1.67
$1.25
$1.00
$0.83
Goal Seek
| B)Goal Seek | ||||||||||
| Price Ratio | 0.5 | 1.0 | 1.5 | 2.0 | 2.5 | 3.0 | ||||
| Selling Price | $ 5.00 | $ 2.50 | $ 1.67 | $ 1.25 | $ 1.00 | $ 0.83 | ||||
| % who would buy new ball | 1% | 2% | 3% | 5% | 8% | 12% | <-- these are calculated using goal seek | |||
| Incremental Years | Year | # of players per thousands | Potential Balls that can be sold (millions) | Ball sold in millions | Ball sold in millions | Ball sold in millions | Ball sold in millions | Ball sold in millions | Ball sold in millions | |
| 0 | 1999 | 1,253 | 12.079 | 0.101 | 0.230 | 0.396 | 0.604 | 0.947 | 1.451 | |
| 1 | 2000 | 1,378 | 13.287 | 0.112 | 0.253 | 0.436 | 0.664 | 1.042 | 1.596 | |
| 2 | 2001 | 1,516 | 14.616 | 0.123 | 0.278 | 0.480 | 0.731 | 1.146 | 1.755 | |
| 3 | 2002 | 1,668 | 16.077 | 0.135 | 0.306 | 0.528 | 0.804 | 1.260 | 1.931 | |
| 4 | 2003 | 1,834 | 17.685 | 0.149 | 0.336 | 0.580 | 0.884 | 1.386 | 2.124 | |
| 5 | 2004 | 2,018 | 19.454 | 0.163 | 0.370 | 0.638 | 0.973 | 1.525 | 2.336 | |
| 6 | 2005 | 2,220 | 21.399 | 0.180 | 0.407 | 0.702 | 1.070 | 1.678 | 2.570 | |
| 7 | 2006 | 2,442 | 23.539 | 0.198 | 0.447 | 0.772 | 1.177 | 1.845 | 2.827 | |
| 8 | 2007 | 2,686 | 25.893 | 0.217 | 0.492 | 0.850 | 1.295 | 2.030 | 3.110 | |
| 9 | 2008 | 2,954 | 28.482 | 0.239 | 0.541 | 0.935 | 1.424 | 2.233 | 3.421 | |
| 10 | 2009 | 3,250 | 31.330 | 0.263 | 0.595 | 1.028 | 1.567 | 2.456 | 3.763 | |
| Selling Price | $ 5.00 | $ 2.50 | $ 1.67 | $ 1.25 | $ 1.00 | $ 0.83 | ||||
| Contribution Margin Analysis | Year | Contribution Margin (MILLIONS) | ||||||||
| 1999 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | ||||
| 2000 | $ 0.50 | $ 0.50 | $ 0.50 | $ 0.48 | $ 0.50 | $ 0.50 | ||||
| 2001 | $ 0.55 | $ 0.55 | $ 0.55 | $ 0.53 | $ 0.55 | $ 0.55 | ||||
| 2002 | $ 0.61 | $ 0.61 | $ 0.60 | $ 0.59 | $ 0.61 | $ 0.60 | ||||
| 2003 | $ 0.67 | $ 0.67 | $ 0.67 | $ 0.65 | $ 0.67 | $ 0.67 | ||||
| 2004 | $ 0.73 | $ 0.73 | $ 0.73 | $ 0.71 | $ 0.73 | $ 0.73 | ||||
| 2005 | $ 0.81 | $ 0.81 | $ 0.81 | $ 0.78 | $ 0.81 | $ 0.81 | ||||
| 2006 | $ 0.89 | $ 0.89 | $ 0.89 | $ 0.86 | $ 0.89 | $ 0.89 | ||||
| 2007 | $ 0.97 | $ 0.97 | $ 0.97 | $ 0.95 | $ 0.97 | $ 0.97 | ||||
| 2008 | $ 1.07 | $ 1.07 | $ 1.07 | $ 1.04 | $ 1.07 | $ 1.07 | ||||
| 2009 | $ 1.18 | $ 1.18 | $ 1.18 | $ 1.14 | $ 1.18 | $ 1.18 | ||||
| Financial Analysis | Selling Price | $ 5.00 | $ 2.50 | $ 1.67 | $ 1.25 | $ 1.00 | $ 0.83 | |||
| (All Values in Millions) | Year | PV Analysis | ||||||||
| 0 | 1999 | $ (5) | $ (5) | $ (5) | $ (5) | $ (5) | $ (5) | |||
| 0 | 1999 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 1 | 2000 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 2 | 2001 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 3 | 2002 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 4 | 2003 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 5 | 2004 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 6 | 2005 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 7 | 2006 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 8 | 2007 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 9 | 2008 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| 10 | 2009 | $ 0.45 | $ 0.45 | $ 0.45 | $ 0.44 | $ 0.45 | $ 0.45 | |||
| NPV | $ (0) | $ (0) | $ (0) | $ (0) | $ (0) | $ (0) |
Chart below shows Contribution Margin (in millions) for different pricing strategis across the years - it can be seen that based on price sensitivity of adoption of the balls, it is most desirables to price it at $1.67 a ball
Note - Fixed initial investment is not considered in the pricing analysis as it will be a mute point and change among different pricing options
Contribution Margin in this case is total revenue less total variable cost; it doesnt include fixed cost
Using Goal seek, we are trying to determine what the minimum response rate should be for each of the pricing strategies to break even for the next 10 years
it shows that we will definitely break even
Potential application of optimization in this case would be determine what is the best price to sell the balls at given that adoption rate is dependent on the pricing strategy; higher the price lower the adoption rate
Objective - Max Profit
Constraints - price ratio can range from 0.5 to 3, price cannot be -ve, adoption cannot be -ve, adoption can range from 0% to 100% which will be a function of price
Note - the sensitivity analysis on 'solutions tab' is an optimization through simulation of this case where we determine that best pricing is at $1.67 per ball
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Potential application of simulation in this case would be determine what the total profit would be for all the different pricing strategies given that adoption rate is dependent on the pricing strategy; higher the price lower the adoption rate
Another situation where simulation can be used is to determine break even points (ie, what should be the adoption rate for each pricing be so that we can break even - like we did using goal seek above)
