Homework Help for Business

993 results

Business is concerned with the study of organizations, entities or commercial activity that involves selling goods or services for a profit.

For unlimited access to Homework Help, a Homework+ subscription is required.

Avatar image
viridiangoose502 asked for the first time
Avatar image
cornelia asked for the first time
in Business·
1 Aug 2021

business analysis tools excel

Cornering the Tennis Ball Market

It is late 2020 and a friend of yours has invented a new manufacturing process for producing tennis 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. She has already done some preliminary data gathering and market analysis, key elements of which are provided below.

  • The primary advantage of the new tennis ball is that it is less expensive to produce than the current top-of-the-line ball manufactured by Slazenger. It is estimated that the full variable cost for the new ball will be £0.52 per ball, compared to £0.95 for Slazenger 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 that she needs £4.7 million in initial capital. She figures that if she can make a good case, she can get a 20-year loan at 6% APR from the bank and start making tennis balls a year from now (i.e. 2021)
  • Running costs for the manufacturing plant are expected to be £255,000 during the first year of operations. They 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 2021. This number is projected to grow 9.8% 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. Retailers then typically add a 100% mark-up to the price. Slazenger, for example, sells balls to retailers for £1.25, while the retail price paid by customers is £2.50. The selling price to retailers and retail price paid by customers are expected to rise by 2.1% per year.
  • In order to assess demand for the new balls, your friend got 200 tennis 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 Slazenger 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 x (PSB / PNB)) ‒ 24.867

 

  • Where MS is market share, PSB is the retail price for Slazenger balls and PNB is the retail price for the new ball. To ensure market share is the range 0-100, the price ratio for Slazenger balls versus new balls (PSB / PNB) should be no less than 0.6 and no more than 2.8, implying a minimum retail price to the consumer of £0.90 assuming that Slazenger keeps the retail price of their balls at £2.50.
  • Cash flows, which are made up of (i) projected revenue from ball sales to retailers, (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.

    1. Show in a one-way table how changes in the retail price of the new ball (from £0.90 to £4.50) will affect the NPV and market share. What should the price be for new balls and what would be the resultant market share? Comment on the trade-off between NPV and Market Share.
    2. Show in a two way table how changes in the discount rate from 1% to 10% would affect the NPV for retail prices of the new ball from £0.90 to £4.50
    3. How realistic do you believe this model is? Give your reasons. Are there any other aspects that could be taken into account in the model (e.g. uncertainty regarding market share, how their competitor Slazenger 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 so 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, etc).
    2. Produce a short written report of no more than 2 pages (about 800 words) describing your analysis and an explanation of why your recommended solution is the best. This should be done in Microsoft Word or some other word processing application. Please include tables and or figures as part of your presentation.

    Hints

    To carry out your analysis, it is a good idea to remember some key points about developing good, interactive models in Excel. First, remember to not hard-code any formulas. All data should go in their own cells separate from any calculations. Second, think about modularisation and flow!! Group inputs and calculations in a logical way, with inputs usually appearing above calculations. Third, keep things simple! Avoid having overly complex formulas by breaking calculations down into smaller, manageable bits. For example, when developing a multi-year income statement, you might think about breaking down your calculations as follows.

     

    2020

    2021

    2022

    INCOME

           

    Demand

     

    ####

    ####

    Market Share

     

       ##%

    ##%

    Unit Sales

     

    ####

    ####

    Selling Price

     

    £####

    £####

    Revenue

     

    £####

    £####

    EXPENSES

     

         

    Variable Cost

     

    £####

    £####

    Running Cost

     

    £####

    £####

    Loan Payments

     

    £####

    £####

    Total Cost

     

    £####

    £####

    PROFIT

    DISCOUNTED CASH FLOWS      

     

     

    Present Value

    £####

    £####

    £####

    NPV

    £####

                     

     

    -£####

    £####

    £####

     

     

     

Avatar image
anusanas098 asked for the first time
Avatar image
manishankar073 asked for the first time
Avatar image
mahcma asked for the first time

Start filling in the gaps now
Log in