1) Complete the income projection in columns I and J, using SUMPRODUCT. 2) Create appropriate range...

 

Transcribed Image Text:

1) Complete the income projection in columns I and J, using SUMPRODUCT. 2) Create appropriate range names for the cells: C16, D16, E16, C22, D22, E22. 3) Create four scenarios, using the scenario information located to the right of the income statement. 4) Create a scenario report for the four scenarios, showing Sales, Contribution Margin, Income after Taxes for each. Edit the report so the appearance is professional. 5) Starting in column L build a two-dimensional data table that shows Income after tax at a variety of Casual Selling prices and marginal tax rates. No need to change the units of other products. Assumptions: Revenues Units sold Unit sales price > Marginal tax rate Team 2,500 $33.99 Variable expenses (on a per unit basis) Variable Manufa $12.50 Distribution $2.00 Fixed expenses Marketing $17,500 Payroll (not grouped by category) Estimated overhead (not grouped by category) Other items TheZone - Apparel Division - Swim Accessories Product Category Projected Marginal Income Statement For the Year Ended Dec 31, 2022 Swim Accessories Categories Competition Swim Accessories + 3,500 $55.99 $12.50 $3.50 $30,000 Casual 3,000 $44.99 $15.00 $3.00 $22,000 $70,000 $25,000 40% Projections: Sales Variable Costs: Manufacturing Distribution expense Total Variable Costs Contribution margin Fixed expenses: Marketing Payroll Overhead Total fixed expenses Marginal income before taxes $ 69,500 70,000 25,000 Ś 164,500 (164,500) Scenario Information Team Marketing Team Units Competition Marketing Competition Units Casual Marketing Casual Units I 4 Base $17,500 2,500 $30,000 3,500 $22,000 3,000 Optimistic Most Likely $35,000 6,000 $100,000 12,500 $40,000 4,500 $100,000 13,000 $50,000 5,000 $40,000 4,000 Pessimistic $35,000 6,000 $50,000 6,500 $45,000 5,000 1) Complete the income projection in columns I and J, using SUMPRODUCT. 2) Create appropriate range names for the cells: C16, D16, E16, C22, D22, E22. 3) Create four scenarios, using the scenario information located to the right of the income statement. 4) Create a scenario report for the four scenarios, showing Sales, Contribution Margin, Income after Taxes for each. Edit the report so the appearance is professional. 5) Starting in column L build a two-dimensional data table that shows Income after tax at a variety of Casual Selling prices and marginal tax rates. No need to change the units of other products. Assumptions: Revenues Units sold Unit sales price > Marginal tax rate Team 2,500 $33.99 Variable expenses (on a per unit basis) Variable Manufa $12.50 Distribution $2.00 Fixed expenses Marketing $17,500 Payroll (not grouped by category) Estimated overhead (not grouped by category) Other items TheZone - Apparel Division - Swim Accessories Product Category Projected Marginal Income Statement For the Year Ended Dec 31, 2022 Swim Accessories Categories Competition Swim Accessories + 3,500 $55.99 $12.50 $3.50 $30,000 Casual 3,000 $44.99 $15.00 $3.00 $22,000 $70,000 $25,000 40% Projections: Sales Variable Costs: Manufacturing Distribution expense Total Variable Costs Contribution margin Fixed expenses: Marketing Payroll Overhead Total fixed expenses Marginal income before taxes $ 69,500 70,000 25,000 Ś 164,500 (164,500) Scenario Information Team Marketing Team Units Competition Marketing Competition Units Casual Marketing Casual Units I 4 Base $17,500 2,500 $30,000 3,500 $22,000 3,000 Optimistic Most Likely $35,000 6,000 $100,000 12,500 $40,000 4,500 $100,000 13,000 $50,000 5,000 $40,000 4,000 Pessimistic $35,000 6,000 $50,000 6,500 $45,000 5,000

Answer rating: 100% (QA)

To complete the given tasks lets go step by step 1 Complete the income projection in columns 1 and J using SUMPRODUCT In column 1 we will calculate the total revenue by multiplying the units sold with ... View the full answer