# Convert Accounting Statements Homework into Excel

chamie

Problem 3-22 Marlin Company Sales Mix; Multiproduct Break-Even Analysis (LO 3-9) 1. Marlin Company, a wholesale distributor, has been operating for only a few months. The company sells three products – sinks, mirrors,

vanities. Budgeted sales by product and in total for the coming month are shown below:

Product Sinks Mirrors Vanities Total Percentage of sales……… 48% 20% 32% Sales……………………………. \$ 240,000 100% \$100, 000 10% \$160, 000 100% \$500, 000 100% Variable expenses………. 72,000 30% 80, 000 80% 88, 000 55% 240, 000 48% Contribution Margin….. \$ 168,000 70% \$ 20,000 20% \$ 72,000 45% 260, 000 52% Fixed expenses…………… 223, 600 Net operating income…. \$ 36, 400 Fixed Expenses = \$223,600 = \$430,000 Dollar sales to break-even = CM ration 0.52 As shown by these data, net operating income is budgeted at \$36,400 for the month, and break-even at \$430,000. Assume that actual sales for month total \$ 500,000 as planned. Actual sales by products are: Sink, \$160,000; Mirror, \$200,000; and vanities, \$140,000 Required:

1. Prepare a contribution format income statement for the month based on actual sales data Present the income statement in the format shown above.

2. Compute the Break-Even Point in sales dollars for the month, based on your actual data. 3. Considering the fact that the company met its \$500,000 sales budget for the month, the president is shocked at the results shown on

your income statement in (1) above. Prepare a brief memo for the president explaining why both the operating results and the Break-Even-Point in sales dollars are different from what was budgeted.

Solution 3-22

1. Product

Sinks Mirrors Vanities Total

Percentage of total sales .......... 32% 40% 28% 100%

Sales .......................................... \$160,000 100 % \$200,000 100 % \$140,000 100 % \$500,000 100 %

Variable expenses ..................... 48,000 30 % 160,000 80 % 77,000 55 % 285,000 57 %

Contribution margin ................. \$112,000 70 % \$ 40,000 20 % \$ 63,000 45 % 215,000 43 %*

Fixed expenses ......................... 223,600

Net operating income (loss) ..... \$ (8,600) *\$215,000 ÷ \$500,000 = 43%.

2. Break-even sales:

Fixed expensesDollar sales = to break even CM ratio

\$223,600 = = \$520,000 in sales

0.43

3. Memo to the president: Although the company met its sales budget of \$500,000 for the month, the mix of products

sold changed substantially from that budgeted. This is the reason the budgeted net

operating income was not met, and the reason the break-even sales were greater than

budgeted. The company’s sales mix was planned at 48% Sinks, 20% Mirrors, and 32%

Vanities. The actual sales mix was 32% Sinks, 40% Mirrors, and 28% Vanities. As shown by these data, sales shifted away from Sinks, which provides our greatest

contribution per dollar of sales, and shifted strongly toward Mirrors, which provides our

least contribution per dollar of sales. Consequently, although the company met its budgeted

level of sales, these sales provided considerably less contribution margin than we had

planned, with a resulting decrease in net operating income. Notice from the attached

statements that the company’s overall CM ratio was only 43%, as compared to a planned

CM ratio of 52%. This also explains why the break-even point was higher than planned. With

less average contribution margin per dollar of sales, a greater level of sales had to be

achieved to provide sufficient contribution margin to cover fixed costs.

Problem : 3 - 25 Break-Even Analysis: Pricing ( LO 3-1, LO 3-4, LO 3-6) Demer holdings AG of Zurich, Switzerland has just introduced a new fashion watch for which The company is trying to find an optional selling price. Marketing studies suggest that the company can increase sales by 5,000 units for each SFr 2 per unit reduction in the selling price. (SFr2 denotes 2 Swiss Francs). The company ‘s present selling price is SFr90 per unit, and variable expenses are SFr60 per unit. Fixed expenses are SFr840,000 per year. The present annual sales volume (at the SFr90 selling price) is 25,000 units. Required:

1. What is present yearly net operating income or loss? 2. What is the present Break – Even –Point in units and in Swiss Francs sales? 3. Assuming that the marketing studies are correct, what is the maximum profit that the company

can earn yearly? At how many units, and at what selling price per unit would the company generate this profit?

4. What would be the Break-Even-Point in units and in Swiss Francs sales using the selling price you determined in (3) above (i.e., the selling price at the level of maximum profits) ? Why is this Break-Even-Point different from the Break-Event-Point you computed in (2) above?

Solution 3-25

1. Sales (25,000 units × SFr 90 per unit) ............................................. SFr 2,250,000

Variable expenses

(25,000 units × SFr 60 per unit) .................................................. 1,500,000

Contribution margin ....................................................................... 750,000

Fixed expenses ................................................................................ 840,000

Net operating loss .......................................................................... SFr (90,000)

2. Fixed expensesUnit sales = to break even Unit contribution margin

SFr 840,000 = = 28,000 units

SFr 30 per unit

28,000 units × SFr 90 per unit = SFr 2,520,000 to break even.

3. See the next page.

4. At a selling price of SFr 80 per unit, the contribution margin is SFr 20 per unit. Therefore:

Fixed expensesUnit sales = to break even Unit contribution margin

SFr 840,000 =

SFr 20 per unit

= 42,000 units

42,000 units × SFr 80 per unit = SFr 3,360,000 to break even.

This break-even point is different from the break-even point in (2) because of the change in

selling price. With the change in selling price, the unit contribution margin drops from SFr 30

to SFr 20, resulting in an increase in the break-even point.

3. Unit SellingP

rice Unit Variable

Expense Unit Contribution

Margin Volume

Total Contribution

Margin Fixed

Expenses

Net Operating

Income

(SFrs) (SFrs) (SFrs) (Units) (SFrs) (SFrs) (SFrs)

90 60 30 25,000 750,000 840,000 (90,000)

88 60 28 30,000 840,000 840,000 0

86 60 26 35,000 910,000 840,000 70,000

84 60 24 40,000 960,000 840,000 120,000

82 60 22 45,000 990,000 840,000 150,000

80 60 20 50,000 1,000,000 840,000 160,000

78 60 18 55,000 990,000 840,000 150,000

The maximum profit is SFr 160,000. This level of profit can be earned by selling 50,000 units at a selling price of SFr 80 per unit.

Problem 3 – 26 Changes in cost Structure; Break-Even Analysis; Operating Leverage; Margin of Safety ( LO 3 -4, LO 3 -6, LO 3 – 7, LO 3 -8) Frieden Company’s contribution format income statement for the most recent month is given below: Sales (40,000 units) ……………………………………….. \$ 800, 000 Variable expenses ………………………………………….. 560, 000 Contribution margin ……………………………………….. 240, 000 Fixed expenses ………………………………………………. 192, 000 Net operating income …………………………………….. \$ 48, 000 The industry in which Frieden Company operates is quite sensitive to cyclical movements in the economy. Thus profits vary considerable from year to year according to general economic conditions. The company has a large amount of unused capacity and is studying ways of improving profits. REQUIRED: 1. New equipment has come on the market that would allow Frieden Company to automate a

portion of its operations. Variable expenses would be reduced by \$6 per unit. However, fixed expenses would increase to a total of \$432,000 each month. Prepare two contribution format income statements, one showing present operations and one showing how operations would appear if the new equipment is purchased. Show an amount column, a Per Unit column, and a Percent Column on each statement. Do not show percentages for the fixed expenses.

2. Refer to the income statements in (1) above. For both present operations and the proposed new operations, compute (a) the degree of operating leverage. (b) the Break-Even Point in dollars, and (c) the margin of safety in both dollars and percentage terms.

3. Refer again, to the data in (1) above. As a manager, what factor would be paramount in your mind in deciding whether to purchase the new equipment? (Assume that ample funds are available to make the purchase).

4. Refer to the original data. Rather than purchase new equipment, the marketing manager argues that the company’s marketing strategy should be changed. Instead of paying sales commissions, which are included in variable expenses, the marketing manager suggests that salespersons be paid fixed salaries and that the company invest heavily in advertising. The marketing manager claims that this new approach would increase unit sales by 50% without any change in selling price; the company’s new monthly fixed expenses would be \$240,000; and its net operating income would increase by 25%. Compute the Break-Event-Point in dollar sales for the company under the new marketing strategy. Do you agree with the marketing manager’s proposal?

Solution 3-26

1. The income statements would be:

Present

Amount Per Unit %

Sales ............................................. \$800,000 \$20 100%

Variable expenses ........................ 560,000 14 70%

Contribution margin .................... 240,000 \$6 30%

Fixed expenses ............................. 192,000

Net operating income .................. \$ 48,000

Proposed

Amount Per Unit %

Sales ............................................. \$800,000 \$20 100%

Variable expenses* ...................... 320,000 8 40%

Contribution margin .................... 480,000 \$12 60%

Fixed expenses ............................. 432,000

Net operating income .................. \$ 48,000

*\$14 – \$6 = \$8

2. a. Degree of operating leverage: Present:

Contribution marginDegree of = operating leverage Net operating income

\$240,000 = = 5

\$48,000

Proposed:

Contribution marginDegree of = operating leverage Net operating income

\$480,000 = = 10

\$48,000

b. Dollar sales to break even: Present:

Fixed expensesDollar sales to = break even CM ratio

\$192,000 = = \$640,000

0.30

Proposed:

Fixed expensesDollar sales to = break even CM ratio

\$432,000 = = \$720,000

0.60

c. Margin of safety: Present:

Margin of safety = Actual sales - Break-even sales

= \$800,000 - \$640,000 = \$160,000

Margin of safety in dollarsMargin of safety = percentage Actual sales

\$160,000 = = 20%

\$800,000

Proposed:

Margin of safety = Actual sales - Break-even sales

= \$800,000 - \$720,000 = \$80,000

Margin of safety in dollarsMargin of safety = percentage Actual sales

\$80,000 = = 10%

\$800,000

3. The major factor would be the sensitivity of the company’s operations to cyclical

movements in the economy. Because the new equipment will increase the CM ratio, in years

of strong economic activity, the company will be better off with the new equipment.

However, the company will be worse off with the new equipment in years in which sales

drop. The fixed costs of the new equipment will result in losses being incurred more quickly

and they will be deeper. Thus, management must decide whether the potential for greater

profits in good years is worth the risk of deeper losses in bad years.

4. No information is given in the problem concerning the new variable expenses or the new

contribution margin ratio. Both of these items must be determined before the new break-

even point can be computed. The computations are: New variable expenses:

Profit = (Sales − Variable expenses) − Fixed expenses

\$60,000** = (\$1,200,000* − Variable expenses) − \$240,000

Variable expenses = \$1,200,000 − \$240,000 − \$60,000

= \$900,000

* New level of sales: \$800,000 × 1.5 = \$1,200,000

** New level of net operating income: \$48,000 × 1.25 = \$60,000 New CM ratio:

Sales ...................................................... \$1,200,000 100%

Variable expenses ................................. 900,000 75%

Contribution margin .............................. \$ 300,000 25%

With the above data, the new break-even point can be computed:

Fixed expenses \$240,000Dollar sales = = =\$960,000

to break even CM ratio 0.25

The greatest risk is that the increases in sales and net operating income predicted by the

marketing manager will not happen and that sales will remain at their present level. Note

that the present level of sales is \$800,000, which is well below the break-even level of sales

under the new marketing strategy. It would be a good idea to compare the new marketing strategy to the current situation

more directly. What level of sales would be needed under the new method to generate at

least the \$48,000 in profits the company is currently earning each month? The

computations are:

Target profit + Fixed expensesDollar sales to attain= target profit CM ratio

\$48,000 + \$240,000 =

0.25

= \$1,152,000 in sales each month

Thus, sales would have to increase by at least 44% (\$1,152,000 is 44% higher than \$800,000)

in order to make the company better off with the new marketing strategy than with the

current approach. This appears to be extremely risky.