A company is considering entry into the gold ball market with two separate golf balls, the X ball and the Y ball (the company founder as a rocket scientist who did a lot of work with x, y graph paper). It is felt the X ball will sell for $40 per dozen and the Y ball will sell for $30 per dozen. The objective for the company is to maximize revenue from the sale of the X and Y golf balls.

The company faces constraints in terms of three essential manufacturing components: assembling the core of the ball (Core constraint), putting a cover on the core (Cover constraint), and cleaning up the ball by smoothing out the cover and painting on the X and Y logo (Cleaning constraint). There are up to 54 hours available in Core, 2 hours in Cover, and 35 hours in Cleaning. Each dozen X balls requires 3 hours in Core, 2 hours in Cover, and 1 hour in Cleaning, while each Y dozen requires 2 hours in each area.

Formulate the problem in Excel to maximize revenue given the constraints (and negative amounts of either ball can be manufactured). Generate the appropriate Answer Report and Sensitivity Report and answer the following questions.

a. How many dozen of each golf ball type should be made to maximize revenue given all the constraints? On which report did you find this information?

b. What is the value of revenue at the maximum revenue point? On which report did you find this information?

c. If the revenue per dozen Y balls changes from 30 to 32, will the optimal amount of either type of ball change and will the maximum revenue change? If so to either part, what will be the new values? On which report did you find this information and in which section of that report?

d. While again considering the revenue as $30 per dozen Y golf balls, how valuable in terms of revenue would 4 more hours be in the Cover area? On which report did you find this information and in which section of that report?