Solve the following case using the solver function in excel:

Screenshot what your model looks like, specially showing the constraints, inputs, and action plan.

Until just last year, Xtreme Inc. was operating with plenty of excess capacity. All that changed at the start of the current fiscal year when one of their surfboards was featured in a popular surfing magazine, SurfsUp! The increased exposure led to a dramatic increase in demand for their surfboards and they have been unable to keep up with market demand.

In-House Production

Xtreme has just one machine. Fixed costs of $225,000 for the year are incurred in using it, regardless of production. This is allocated on an absorption basis to products based on time of usage and is NOT included in the costs which follow.

Xtreme believes that it could sell 20,000 surfboards annually, at a selling price of $358 each. However, at full production it can only produce 15,000 of its own surfboards given their machine's limited capacity of 7,500 hours.

Surfboards

The variable costs associated with Xtreme's surfboards are: Variable costs per surfboard

Direct materials 240 Variable machine cost 8 Manufacturing overhead costs 15 Selling costs 10

Boogie Boards

Xtreme's product manager has suggested that the company can make better use of its manufacturing capacity by producing boogie boards instead. Boogie boards are smaller than surfboards and use less direct material. Xtreme could expect to sell 24,000 boogie boards annually at a price of $120 per boogie board. It would them 15 minutes to produce a boogie board. Estimated variable costs to manufacture (and sell) the boogie boards are:

Variable costs per boogie board Direct materials 30 Variable machine cost 4 Manufacturing overhead costs 7.50 Selling costs 12

Part 1: Which product or products should Xtreme make to maximize contribution margin? Show all calculations.

Part 2: Outsourcing

SunSport, Inc., a new entrant to the surfing market has offered to provide Xtreme with up to 18,000 surfboards per year at a cost of $270 per surfboard. They have promised that the quality of their surfboards would be the same as Xtreme's, however, they are still very new to the market so there may be some issues around production time. Re-selling outsourced boards would cost Xtreme an additional $10 per board in marketing costs above their purchase cost.

Make an independent assessment to answer each of the questions below:

A) If it would cost Xtreme $500,000 to implement the changes necessary to outsource the surfboards, would you recommend they do this? What risks are inherent in following your conclusion? How could Xtreme reduce those risks?

B) If SunSport reduced their cost per surfboard by $20 and there were no additional implementation costs, what recommendations would you have for Xtreme?

Part 3:

Assume that Xtreme outsourced production to SunSport. After four years of working together, SunSport planned to increase their price to $320 per surfboard in year 5. Assume SunSport has proven themselves as a reliable, high-quality supplier. Should Xtreme continue to outsource their production? Give reasons for your answer.