Question

Pittman Company is a small but growing manufacturer of telecommunications equipment. The company has no sales force of its own; rather, it relies completely on independent sales agents to market its products. These agents are paid a sales commission of 15% for all items sold. Barbara Cheney, Pittman's controller, has just prepared the company's budgeted income statement for next year as follows: $17,000,000 10,030,000 6,970,000 Pittman Company Budgeted Income Statement For the Year Ended December 31 Sales Manufacturing expenses : Variable $7,650,000 Fixed overhead 2,380,000 Gross margin Selling and administrative expenses: Commissions to agents 2,550,000 Fixed marketing expenses 119,000* Fixed administrative expenses 1,840,000 Net operating income Fixed interest expenses Income before income taxes Income taxes (30%) Net income 4,509,000 2,461,000 595,000 1,866,000 559,800 $ 1,306,200 *Primarily depreciation on storage facilities. As Barbara handed the statement to Karl Vecci, Pittman's president, she commented, “I went ahead and used the agents' 15% commission rate in completing these statements, but we've just learned that they refuse to handle our products next year unless we increase the commission rate to 20%." "That's the last straw," Karl replied angrily. “Those agents have been demanding more and more, and this time they've gone too far. How can they possibly defend a 20% commission rate?" "They claim that after paying for advertising, travel, and the other costs of promotion, there's nothing left over for profit," replied Barbara. "I say it's just plain robbery," retorted Karl. “And I also say it's time we dumped those guys and got our own sales force. Can you get your people to work up some cost figures for us to look at?" "We've already worked them up," said Barbara. “Several companies we know about pay a 7.5% commission to their own salespeople, along with a small salary. Of course, we would have to handle all promotion costs, too. We figure our fixed expenses would increase by $2,550,000 per year, but that would be more than offset by the $3,400,000 (20% * $17,000,000) that we would avoid on agents' commissions."

Please show the excel work for the question.

Thank you

Answer 1

Solution 1:
	15% commission	20% commission	own sales force
Sales	17000000	17000000	17000000
Variable expenses:
Manufacturing	7650000	7650000	7650000
Commission	2550000	3400000	1275000
Total variable expenses	10200000	11050000	8925000
Contribution margin	6800000	5950000	8075000
Variable expenses ratio	60.00%	65.00%	52.50%
Contribution margin ratio	40.000%	35.000%	47.500%
Fixed expenses:
Manufacturing overhead	2380000	2380000	2380000
Marketing	119000	119000	2669000
Administrative	1840000	1840000	1761800
Interest	595000	595000	595000
Total fixed expenses	4934000	4934000	7405800
Income before income taxes	1866000	1016000	669200
Income taxes(30%)	559800	304800	200760
Net income	1306200	711200	468440
Break Even Point in dollars (Total Fixed Expenses/Controbution margin ratio)	12335000	14097143	15591158

Solution 2:
Income before tax as per income statement	1866000
Total Fixed Expenses (when 20% commission)	4934000
Total	6800000
/ Contribution margin ratio (When 20% commission)	35.000%
Sales required to generate target profit	19428571

Solution 3:

Let Sales Volume be "X"

Total Cost when commission 20% = Total Cost for Own sales Force

0.6500*X +4934000 = 0.5250*X +7405800

0.125*X = 2471800

X = 2471800/0.125

X = 19774400

Hence, Sales volume required is $19,774,400

Solution 4:
	15% commission	20% commission	own sales force
Contribution margin	6800000	5950000	8075000
Income before taxes	1866000	1016000	669200
Degree of Operating Leverage (Contribution margin/Income before taxes)	3.64	5.86	12.07