Is it possible to get a step by step of this in Excel spreadsheet showing the formulas used?

Morocco Corporation manufactures disposable thermometers that are sold to hospitals through a network of independent sales agents located in the United States and Canada. These agents sell a variety of products to hospitals in addition to Moroccos disposable thermometer. The sales agents are currently paid an 18% commission on sales, and this commission rate was used when Moroccos management prepared the following budgeted income statement for the coming year.

Morocco Corporation Budgeted Income Statement

Sales. $30,000,000 Cost of Goods Sold: Variable.... $17,800,000 Fixed. 2,400,000 20,200,000 Gross Margin 9,800,000 Selling and Admin. Expenses: Commissions. 5,400,000 Fixed Advertising Exp. 800,000 Fixed Admin. Exp 3,200,000 9,400,000 Net Operating Income.. $ 400,000

Since completion of the above statement, Moroccos management has learned that the independent sales agents are demanding an increase in the commission rate to 20% of sales for the upcoming year. This would be the third increase in commissions demanded by the independent sales agents in five years. As a result, Moroccos management has decided to investigate the possibility of hiring its own sales staff to replace the independent sales agents.

Moroccos controller estimates that the company will have to hire eight salespeople to cover the current market area, and the total annual payroll cost of these employees will be about $700,000, including fringe benefits. The salespeople will also be paid commissions of 10% of sales. Travel and entertainment expenses are expected to total about $400,000 for the year. The company will also have to hire a sales manager and support staff whose salaries and fringe benefits will come to about $300,000 per year. To make up for the promotions that the independent sales agents had been running on behalf of Morocco, management believes that the companys budget for fixed advertising expenses should be increased by $500,000.

Required:

1. (4 points) Assuming sales of $30,000,000, construct a budgeted contribution margin format income statement for the upcoming year with the following alternatives: a. The independent sales agents commission rate stays the same at 18%. b. The independent sales agents commission rate increases to 20%. c. The company employs its own sales force.

2. (2 points) Calculate Moroccos break-even point in sales dollars next year for each of these alternatives: a. The independent sales agents commission rate stays the same at 18%. b. The independent sales agents commission rate increases to 20%. c. The company employs its own sales force.

3. (2 points) Refer to your answer in (1)(b) above. If the company employs its own sales force, what volume of sales would be necessary to generate the same net operating income the company would generate in (1)(b) above?

4. (2 points) Determine the volume of sales at which net operating income would be equal regardless of whether Morocco Corporation sells through agents (at a 20% commission rate) or employs its own sales force.