Hello I\'m having trouble with how to lay this out in excel. Professor says ther
ID: 2519356 • Letter: H
Question
Hello I'm having trouble with how to lay this out in excel. Professor says there should be 4 tabs for the 4 questions in excel. Any help would be much appreciated.
CVP Project Data Accounting 1B Trump Company is a small but growing manufacturer of telecommunications equipment. The company does not have its own sales force; rather, it relies completely on inde sales agents to market its products. These agents are paid a commission of 15% of seling price for all items sold pendent Paul Ryan, Trump' s controller has just prepared the company's budgeted income statement for next year. The statement follows TRUMP COMPANY BUDGETED INCOME STATEMENT FOR THE YEAR ENDEO DECEMBER 31 SALES MANUFACTURING COSTS VARIABLE FIXED GROSS MARGIN $16,000,000 $7,200,000 2,340,000 6,460,000 COMMISSION TO AGENTS FIXED MARKETING COSTS FIXED ADMIN COSTS 2,400,000 120,000 1 800,000 4,320,000 2,140,000 NET OPERATING INCOME primarily depreciation on storage facilities As Ryan handed the statement to Donald Trump. Trump's president, he 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% Mr. Trump replied angrily. Those agents have been demanding more and more and this time they have gone too far. How can they possibly defend a 20% commission rate?" say it's time we dumped those guys and got our own sales force. Ryan replied "We've already worked them up. Several companies we know pay about 7.5% commission to ther own salespeople, along with a small salary Of course, we would have to handle all promotion costs too We figure our fixed costs would increase by $2,400,000 per year, but we do not have to pay $3,200,000 (20% x $16,000,000) commissions to the agents. The breakdown of the $2,400,000 cost figure is as foilows: Salaries Sales manager Salesperson Travel and Entertainment Advertising $ 100,000 800,000 400,000 Total Super," replied Mr. Trump. 'and I note that the $2,400,000 is just what we are pa the agents under the old 15% commission rate." it's even better than that," explained Ryan. "We can actually save $75,000 a year because that's what we are paying the auditing firm now to check out the agents' reports. So our overall administrative costs would be less." Required: in Execel 1. Compute Trump Company's break-even point in sales dollars for next year assuming if: a, the agents commission rate remains unchanged at 15% b the agents commission rate is increased to 20% c. the company employs its own sales force 2. Assume that Trump decides to continue selling through the agents and pays the 20% commission rate. Determine the volume of sales that would be required to generate the same net income as contained in the budgeted income statement for next year which is $2,140,000Explanation / Answer
Answer 1. BEP (In $) = Fixed Cost / Contribution Margin Ratio Agents' Commission - 15% Agents' Commission - 20% Own Sales Force 7.50% Comm. Sales 16,000,000.00 16,000,000.00 16,000,000.00 Variable Costs Variable Manufacturing Expenses 7,200,000.00 7,200,000.00 7,200,000.00 Commission to agents 2,400,000.00 3,200,000.00 1,200,000.00 Total Variable Costs 9,600,000.00 10,400,000.00 8,400,000.00 Contribution 6,400,000.00 5,600,000.00 7,600,000.00 Fixed Costs Fixed Manufacturing Expenses 2,340,000.00 2,340,000.00 2,340,000.00 Fixed Marketing Expenses 120,000.00 120,000.00 2,520,000.00 Fixed Administration Expenses 1,800,000.00 1,800,000.00 1,725,000.00 Total Fixed Costs 4,260,000.00 4,260,000.00 6,585,000.00 Net Operating Income 2,140,000.00 1,340,000.00 1,015,000.00 Contribution Margin Ratio 40.00% 35.00% 47.50% BEP (In $) 10,650,000 12,171,429 13,863,158 Answer 2. BEP (With Target Profit) = (Fixed Cost + Target Profit) / Contribution Margin Ratio BEP (With Target Profit) = ($4,260,000 + 2,140,000) / 35% BEP (With Target Profit) = $18,285,714.29 or say $18,285,714 (Approx.)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.