Please solve using Excel solver. Solve all the parts will definitely upvote,else will give downvote.

needed asap.

Refer to the Racquetball Racket case below:

a. Develop a base case. You may create any data you need for this purpose.Why is this base case appropriate for this situation? b. Perform an appropriate sensitivity analysis. Which parameters have the most significant impact on the results? Can you find applications for the Parametric Sensitivity, Tornado Chart, and Scenario Manager tools? c. Identify applications of Goal Seek in this situation. (For example, what percentage of the market must they achieve to break even on their investment?) d. Identify potential applications of optimization in this case. e. Identify potential applications of simulation in this case.

THE RACQUETBALL RACKET It is early in 2000, and a friend of yours has invented a new bank, he can borrow the capital at about a 10 percent interest manufacturing process for producing racquetballs. The rate and start producing racquetballs in a year. resulting high-quality ball has more bounce, but slightly less Your friend has offered to make you a partner in the durability, than the currently popular high-quality ball, business and has asked you in return to perfom a market which is manufactured by Woodrow, Ltd. The better the analysis for him. He has already hired a well-known market players, the more they tend to prefer a lively ball. The research fim, Market Analysis, Ltd., to do some data primary advantage of the new ball is that it can be manu- gathering and preliminary market analysis. The key factured much more inexpensively than the existing ball. elements of their final report are given below. Current estimates are that full variable costs for the new ball Your problem is to determine how the new balis should are $0.52 per ball as compared to $0.95 for the existing ball. be priced, what the resultant market shares will be, and (Variable costs include all costs of production, marketing, whether the manufacturing plant is a good investment. Your and distribution that vary with output. It excludes the cost of friend is especially concerned about the risks involved and plant and equipment, overhead, etc.) would like some measures of how solid the investment Because the new process is unlike well-known production appears to be. He would like you to make a formal processes, the only reasonable alternative is to build a man- presentation of your analysis. ufacturing plant specifically for producing these balls. Your friend has calculated that this would require 84 - 6 million of 'Adapted from a class assignment developed by Dick Smalwood initial capital. He figures that if he can make a good case to the and Peter Morris. RACQUETBALL MARKET ANALYSIS Market Analysis, Ltd. January 20, 2000 1. The market for this type of high-quality balliscurrently 1987 dominated by a single major competitor, Woodrow, Ltd 1988 Woodrowspecializes in manufacturing balls for alltypes of 1989 sports. It has been the only seller of high-quality racquet- 1990 balls since the late 1970s. Its current price to retail outlets is $1.25 per ball (the retail markup is typically 100 percent, so these balk retail around $2.50 each, or $5.00 for the typical pack of two). b. Historical data on the number of people playing the sport, the average retail price of balls, and the (esti- mated) total sales of balls is given in the following table: 1991 1992 1993 1994 1995 1996 1997 1998 655 700 730 762 812 831 877 931 967 1,020 1,077 1,139 $1.80 $1.90 $1.90 $1.90 $2.00 $2.20 $2.45 $2.45 $2.60 $2.55 $2.50 $2.50 6.506 6.820 7.161 7.895 7.895 8.224 8.584 9.026 9.491 9.996 10.465 10.981 Number Players (Thousands) Retail Price (per ball) Year Balls Sold (millions) 5.932 6.229 1985 1986 600 635 $1.75 $1.75 C. According to industry trade association projections, the total number of players will grow about 10 percent a Price Ratio* year for the next 10 years and thenstabilize at a relatively constant level. d. In order to assess relative preferences in the market- place, a concept test was performed. In this test, 200 customers were asked to use both balls over a three- month period, and then specify which ball they would buy at various prices. Many customers indicated they would pay a premium for the Woodrow ball, based on their satisfaction with it and its better durability. Never- theless, about 11 percent of the customers interviewed indicate da preference for the new, bouncier ball at equal prices. The actual observed distribution of price pre- miums is as follows: 0.5 1.0 1.5 2.0 2.5 3.0 Per cent Who Would Buy New Ball 0 11 41 76 95 100 *Price of Woodrow ball / Price of new ball. THE RACQUETBALL RACKET It is early in 2000, and a friend of yours has invented a new bank, he can borrow the capital at about a 10 percent interest manufacturing process for producing racquetballs. The rate and start producing racquetballs in a year. resulting high-quality ball has more bounce, but slightly less Your friend has offered to make you a partner in the durability, than the currently popular high-quality ball, business and has asked you in return to perfom a market which is manufactured by Woodrow, Ltd. The better the analysis for him. He has already hired a well-known market players, the more they tend to prefer a lively ball. The research fim, Market Analysis, Ltd., to do some data primary advantage of the new ball is that it can be manu- gathering and preliminary market analysis. The key factured much more inexpensively than the existing ball. elements of their final report are given below. Current estimates are that full variable costs for the new ball Your problem is to determine how the new balis should are $0.52 per ball as compared to $0.95 for the existing ball. be priced, what the resultant market shares will be, and (Variable costs include all costs of production, marketing, whether the manufacturing plant is a good investment. Your and distribution that vary with output. It excludes the cost of friend is especially concerned about the risks involved and plant and equipment, overhead, etc.) would like some measures of how solid the investment Because the new process is unlike well-known production appears to be. He would like you to make a formal processes, the only reasonable alternative is to build a man- presentation of your analysis. ufacturing plant specifically for producing these balls. Your friend has calculated that this would require 84 - 6 million of 'Adapted from a class assignment developed by Dick Smalwood initial capital. He figures that if he can make a good case to the and Peter Morris. RACQUETBALL MARKET ANALYSIS Market Analysis, Ltd. January 20, 2000 1. The market for this type of high-quality balliscurrently 1987 dominated by a single major competitor, Woodrow, Ltd 1988 Woodrowspecializes in manufacturing balls for alltypes of 1989 sports. It has been the only seller of high-quality racquet- 1990 balls since the late 1970s. Its current price to retail outlets is $1.25 per ball (the retail markup is typically 100 percent, so these balk retail around $2.50 each, or $5.00 for the typical pack of two). b. Historical data on the number of people playing the sport, the average retail price of balls, and the (esti- mated) total sales of balls is given in the following table: 1991 1992 1993 1994 1995 1996 1997 1998 655 700 730 762 812 831 877 931 967 1,020 1,077 1,139 $1.80 $1.90 $1.90 $1.90 $2.00 $2.20 $2.45 $2.45 $2.60 $2.55 $2.50 $2.50 6.506 6.820 7.161 7.895 7.895 8.224 8.584 9.026 9.491 9.996 10.465 10.981 Number Players (Thousands) Retail Price (per ball) Year Balls Sold (millions) 5.932 6.229 1985 1986 600 635 $1.75 $1.75 C. According to industry trade association projections, the total number of players will grow about 10 percent a Price Ratio* year for the next 10 years and thenstabilize at a relatively constant level. d. In order to assess relative preferences in the market- place, a concept test was performed. In this test, 200 customers were asked to use both balls over a three- month period, and then specify which ball they would buy at various prices. Many customers indicated they would pay a premium for the Woodrow ball, based on their satisfaction with it and its better durability. Never- theless, about 11 percent of the customers interviewed indicate da preference for the new, bouncier ball at equal prices. The actual observed distribution of price pre- miums is as follows: 0.5 1.0 1.5 2.0 2.5 3.0 Per cent Who Would Buy New Ball 0 11 41 76 95 100 *Price of Woodrow ball / Price of new ball