Tel-Data is a small and growing manufacturer of telecommunications equipment. The company currently outsources its sales department to SalezProNet a network of independent sales agents. These outsourced sales agents are paid a sales commission of 15% for all Tel-Data system packages sold. Nancy Hern, the Controller has prepared next year's budgeted income statement for her meeting with the President Scott Tracy. Tel-Data Budgeted Income Statement for 2022 (also attached in Excel spreadsheet) Tel-Data, Inc. Budgeted Income Statement For the Year Ended December 31, 2022 $16,000,000 Sales Manufacturing Expenses Variable $7,200,000 Fixed Overhead 2,340,000 9,540,000 6,460,000 Gross Margin Selling and Administrative expenses: Commissions to agents Fixed Marketing expenses $2,400,000 120,000 Fixed Administrative expenses 1,800,000 4,320,000 Net operating Income Fixed interest expenses 2,140,000 540,000 Income before income taxes Income taxes Net income 1,600,000 480,000 $1,120,000 Nancy: I used 15% for the sales agents' commissions in this report, however I learned this morning that SalezProNet will be charging us a 20% commission next year. Scott: Do you know what their justification for such a hefty increase in one year is? Nancy: They said their advertising, travel, and other promotional costs had increased and their profits were shrinking. Scott: That seems excessive. Maybe it is time we looked into hiring our own salespeople in-house rather than continue to use SalezProNet. Can you work out those figures for our next meeting? Nancy: I thought you might suggest that, so I made some quick estimates on what it might cost us. Tel-Data, Inc. Estimates for hiring sales department in-house For the Year Ended December 31, 2022 Projected Sales Salaries: Sales Manager Salespeople Travel and other promotional expenses Advertising Total projected sales expenses $100,000 600,000 400,000 1,300,000 $2,400,000 Scott: So, the amount is the same, $2,400,000 whether we continue with the outside sales agency or hire our own salespeople? Nancy: Not really because SalezProNet would require 20% commission next year, and the $2,400,000 in the budgeted income statement is shown at the 15% rate, which is $16 million sales x 15%. Also, we currently pay $75,000 to an auditing firm to audit the SalezProNet agents' reports, so we would be able to save that amount, which is included in fixed administrative expenses, if we hired our own salespeople. Scott: Great! Put this information together in a report that I can take to the executive committee tomorrow morning and present the idea of hiring our own salespeople. Required: Download the accompanying Excel spreadsheet and use the data to perform the following: 1. Restructure the budgeted income statement into contribution margin format in three columns: one column with 15% commission, one column with 20% commission, and one column with own Sales Department. 2. Calculate the breakeven in sales dollars with the sales agents' 15% commission rate. 3. Calculate the breakeven in sales dollars with the sales agents' 20% commission rate. 4. Calculate the breakeven in sales dollars with hiring the salespeople in-house. 5. Calculate the sales dollars needed if Tel-Data continues to use SalezProNet at the 20% rate in 2022 (hint-do target profit calculation with the budgeted net income figure as the target profit) 6. Determine the sales dollars at the point of indifference, the point at which net income is the same whether they hire their own salespeople or continue to use SalezProNet. (Hint - set up an equation that makes the 20% commission column's CM ratio times "X" + that column's fixed costs = to the Own Sales department's CM ratio times "X" + that column's fixed costs, then solve for "X" 7. Using income before taxes, calculate the Degree of Operating Leverage with the SalezProNet commission at 15% 8. Using income before taxes, calculate the Degree of Operating Leverage with the SalezProNet at 20% commission 9. Using income before taxes, calculate the Degree of Operating Leverage if they employ their own salespeople. 10. Analyze a recommendation based on the calculations made in 1-8. Prepare an executive summary bullet pointing your calculations and recommendation. Deliverables: Each item is worth 5 points for a total of 50 points. Upload into Sakai your spreadsheet that clearly shows your calculations. Use colors and fonts and boxes to make it presentation worthy to the executive team. Your executive summary bullet points may be included on the spreadsheet (on the same sheet or a different sheet in the workbook) or may be included on a separate Word doc. Remember, Excel does not automatically spell check so be sure to run spell check on your spreadsheet