Problem:
Please complete in Excel.
The Circumstance
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:
Putter $129
Driver $299
Woods $149
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
Assignment:
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).
Sales mix
Fixed costs
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.