Do some data entry -- 2

I have some work, in an Excel spreadsheet. Cornering the Squash Ball Racket Market It is late 2017 and a friend of yours has invented a new manufacturing process for producing squash balls. Your friend has offered to make you a partner in the business but has asked you to perform an in-depth market analysis for him in return. He has already done some preliminary data gathering and market analysis, key elements of which are provided below. ▪ The primary advantage of the new racket ball is that it is less expensive to produce than the current top-of-the-line ball manufactured by Woodrow, Ltd. Your friend estimates that the full variable cost for the new ball will be £0.52 per ball, compared to £0.95 for Woodrow balls. Variable costs includes all costs of production, marketing, and distribution which vary with output. Variable costs exclude the cost of plant and equipment, overhead, etc. ▪ Because the manufacturing process for the new ball is unlike known production methods, the only realistic alternative is to build a specially designed plant to produce balls. Your friend has determined he needs £4.7 million in initial capital. He figures that if he can make a good case, he can get a 20-year loan at 10% APR from the bank and start making squash balls a year from now. ▪ Running costs for the manufacturing plant are expected to be $250,000 during the first year of operations. Running costs are then expected to rise by 2.5% per year. ▪ According to the industry trade association, it is expected that 10.9 million balls will be sold by the end of 2017. This number is projected to grow 10% per year for the next 10 years and then level off (i.e., no longer increase). ▪ The only reasonable way for your friend to make the business work is to sell balls to retail outlets. Retailors then typically add a 100% mark-up to the price. Woodrow, for example, sells balls to retailors for £1.25, while the retail price paid by customers is £2.50. ▪ In order to assess demand for the new balls, your friend got 200 squash players to use the new ball over a three-month period and then asked them which ball they would buy, either the new ball or a Woodrow ball for various theoretical prices. Based on this, the following formula was derived for calculating percent market share of the new ball: MS = 44.971 ´ PWB / PNB – 24.867 where MS is market share, PWB is the retail price for Woodrow balls and PNB is the retail price for the new ball. To ensure market share is the range 0-100, the price ratio for Woodrow balls versus new balls (PWB / PNB) should be no less than 0.5 and no more than 3.0. ▪ Cash flows, which are made up of (i) projected revenue from ball sales to retailors, (ii) variable costs of manufacturing balls, (iii) plant running costs, and (iv) bank loan repayments, are assumed to occur at the end of each year. The discount rate is set at 7.5%. Your friend would like you to develop a 20-year cash flow analysis to answer the following questions. a) What should the price be for new balls and what would be the resultant market share? b) How would a change in the discount rate affect the optimal price? c) How realistic is your model? Are there any other aspects that could be taken into account (e.g., running costs, uncertainty regarding market share, how Woodrow might respond)? Instructions 1. You will need to build a spreadsheet model in Excel to carry out your analysis. The model should be fully functional and interactive in that the inputs can be changed by a user (i.e., your seminar leader) and the model’s results automatically calculated and displayed. Please adhere to good spreadsheet practice. Additional points will be awarded for ingenuity and creativity and for the use of any advanced Excel functionalities (e.g., range names, special functions, charts, “what-if” analysis tools, controls and macros).