Share
Explore BrainMass

Accounting: Break-Even Analysis

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.

Solution Summary

The problem set deals with issues under accounting and determining the break-even with multiple products.

$2.19