# Do some data entry

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).

Skills: Excel

( 0 reviews ) United Kingdom

Project ID: #16013629

## 29 freelancers are bidding on average £144 for this job

shoguna2m

I am a professional in business administration with more than 4 years on this type of projects. I have conducted several financial analyses in this time for both real practice and for academic use. Relevant Skills and More

£150 GBP in 3 days
(61 Reviews)
6.1
The24CarrotsInc

Greetings for the day!! We have experience working on this subject. We ensure 100% QUALITY with ON-TIME Deliveries. Please allow us to take this opportunity. Thanks & Regards, Team@The24CarrotsInc Relevant Skills an More

£250 GBP in 3 days
(53 Reviews)
5.6
rumarahman

Hi, I am an expert excel worker and I do programming on VBA Excel to gather data from internet automatically. So that I am the suitable one you are searching for, Sir. Thanks. Relevant Skills and Experience Excel D More

£200 GBP in 2 days
(29 Reviews)
4.9
£150 GBP in 1 day
(13 Reviews)
3.8
Arjuna5421

I feel that I am the exact match for your requirements after thoroughly reviewing the job description and desired skills Relevant Skills and Experience Excel Proposed Milestones £77 GBP - milestone

£77 GBP in 3 days
(0 Reviews)
0.0
neehuie

A proposal has not yet been provided

£132 GBP in 5 days
(0 Reviews)
0.0
£220 GBP in 3 days
(0 Reviews)
0.0
agarwal2807

I am good in excel and analysis. I can deliver as per your expectation Relevant Skills and Experience Excel Proposed Milestones £20 GBP - 100

£20 GBP in 3 days
(0 Reviews)
0.0
£150 GBP in 3 days
(0 Reviews)
0.0
udithaekanayake

I'm a fresher so I will do your job for least cost but with high quality Relevant Skills and Experience I have good excel skills Proposed Milestones £142 GBP - To do my best

£142 GBP in 2 days
(0 Reviews)
0.0
£20 GBP in 2 days
(0 Reviews)
0.0
MohamedYAMeguid

A proposal has not yet been provided

£166 GBP in 1 day
(0 Reviews)
0.0
edupedia17

A proposal has not yet been provided

£111 GBP in 1 day
(0 Reviews)
0.0

Respected sir i am so excited to work on this project. sir please give a chance.

£133 GBP in 3 days
(0 Reviews)
0.0
abigailbingham

I am excellent user for excel. I am certified in different levels to use [login to view URL] youg lady my brain is fresh and ready for this job. Relevant Skills and Experience Microsoft Office which entails, excel, database, pow More

£333 GBP in 3 days
(0 Reviews)
0.0
Rohit157

hello i understand your requirements and i can do your work with quality and with your full satisfaction as i m expert in this work. Please send me details in my inbox so we can start it Relevant Skills and Experience More

£222 GBP in 3 days
(0 Reviews)
0.0
sreeshawon007

I feel that I am the exact match for your requirements after thoroughly reviewing the job description and desired skills Relevant Skills and Experience Excel Proposed Milestones £150 GBP - milestone

£150 GBP in 3 days
(0 Reviews)
0.0
Emanuela01

A proposal has not yet been provided

£100 GBP in 5 days
(0 Reviews)
0.0
vinnymiranda28

DEAR SIR / MAM , HI I AM VINNY FROM SPAIN . AS PER YOUR JOB WE HAVE THREE PEOPLE TEAM HERE IN OUR OFFICE WE ALL HAVE MORE THAN 10 YEARS EXPERIENCE IN EXCEL Relevant Skills and Experience 10 Y Proposed Milestones £ More

£150 GBP in 3 days
(0 Reviews)
0.0
TPLMworking1

I would be glad to work on this interesting project about the business case of introducing a new Squash ball on the market. With my extensive Excel and VBA skills, I will make this project fly ;),. Relevant Skills an More

£250 GBP in 10 days
(0 Reviews)
0.0