Please complete in Excel.
The president of your company is grateful for your quick reference guide on costs. Now she has a different request.
As she sets the company direction she needs help refining sales and production targets for next year. She has gathered the following information:
The company, a golf club manufacturer, sells six products: drivers, woods, sets of irons, hybrids, wedges, and putters.
The slowest seller is putters but there is a predictable sales mix. For each putter sold the following are sold: 2 drivers, 4 woods, 3 sets of irons, 6 hybrids, and 5 wedges.
The MSRP for each is as follows:
Set of irons $699
Hybrids $ 99
Wedges $ 79
Variable costs run consistently at 30% of the above MSRP
Fixed costs currently run $480,000 per year
Build a spreadsheet that allows her to change any of the following and immediately see the impact of those changes on both the required quantity of clubs and company profit. Do not show her the details (i.e. put the details and calculations on a separate worksheet).
Manufacturing cost percent of current MSRP
Retail on anything in the sales mix
Before tax profit goal
Restrictions and Advice
There is no such thing a half a club or part of a mix. Your work should always work in entire mixes, therefore you will not exactly hit a goal number; rather, you will exceed it by a small amount. In other words, your profit numbers will always jump in the increment of the full profit on your mix.
The problem set deals with issues under accounting and determining the break-even with multiple products.