Please complete the distribution cost and margin in excel.

AutoSave Off Distribution Costs_blank-1.xlsx - Excel Search (Alt+Q) suraj kumar File Home Insert Page Layout Formulas Data Review View Help Comments Share X Calibri 11 General = = - AP Conditional Formatting ! Insert a AY F e 9X Delete Paste B I U A % , ! 2 Format as Table Cell Styles Styles Sort & Find & Filter Select Analyze Data Sensitivity Format Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity F4 X fx B C D E F H AB Chemicals 1 2 Baseline Scenario 1 Scenario 2 $3.00 $5.00 5.00% 2,00,000 (increase price, distributor support cost (increase price) & Retailer promo cost) $3.000 $3.00 $10.00 $10.00 2.00% 2.00% 5 points each 15% sales revenue 20% sales revenue 3 4 Unit Cost 5 Unit Revenue (price) 6 Share of Market Potential 7 Sales Volume 8 Sales Revenue 9 Gross Margin % 10 Gross Margin $ 11 Distributor Support Costs (10% sales revenue) 12 Retailer Promotion Costs (15% sales revenue) 13 Operating Margin $ (Year 1 Margin) 14 Growth Rate (every year) 15 Year 2 Margin 16 Year 3 Margin 17 18 Wholesaler/Distributor (aggregated) 19 Purchase Coctelia sales revenue of producer Example Ready Type here to search 5.00% 5.00% 5.00% co co col 109% o E d WA | 56 % . 24C 0 0 4x lic ENG IN 01:58 PM 29-11-2021 14 AutoSave Off Distribution Costs_blank-1.xlsx - Excel Search (Alt+Q) suraj kumar File Home Insert Page Layout Formulas Data Review View Help Comments Share X Calibri 11 General - AP = = Conditional Formatting ! Insert a AY F e LO 2 Format as Table 9X Delete Paste B I U A C%) 8 28 Sort & Find & Filter Select Analyze Data Sensitivity Format Cell Styles Styles Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity F4 X fx B D E F H $0 $0 $0 A 16 Year 3 Margin 17 18 Wholesaler/Distributor (aggregated) 19 Purchase Costs (i.e., sales revenue of producer) 20 Wholesaler/Distributor Markup (15%) 21 Sales Revenue 22 Operating Margin (38% of Markup) 23 Distributor support income (cost = 50% of Distributor support costs) 24 Total 25 26 Retailers (aggregated) 27 Purchase Costs (i.e., sales revenue of wholesaler/distributor ) 28 Retailers Markup (25%) 29 Sales Revenue 30 Operating Margin (40% of markup) 31 Retailer promotion income (cost = 30% of Retailer Promotion Costs) 32 Total 15 points each $0 $0 $0 20 points each 33 34 * Hint: When AB Chemicals pay distributor support costs to the Example Ready 109% Type here to search E | 56 % ) . 24C 0 0 4x lic ENG IN 01:58 PM 29-11-2021 714 AutoSave Off Distribution Costs_blank-1.xlsx - Excel Search (Alt+Q) suraj kumar File Home Insert Page Layout Formulas Data Review View Help Comments Share X Calibri 11 - A = = General Conditional Formatting ! Insert a AY F e 2 Format as Table 9X Delete Paste B I U A C%) 8 Analyze Data Sensitivity Format Sort & Find & Filter Select Editing Cell Styles Styles Undo Clipboard Font Alignment Number Cells Analysis Sensitivity F4 fx A B D E F H $0 $0 $0 20 points each 25 26 Retailers (aggregated) 27 Purchase Costs (i.e., sales revenue of wholesaler/distributor) 28 Retailers Markup (25%) 29 Sales Revenue 30 Operating Margin (40% of markup) 31 Retailer promotion income (cost = 30% of Retailer Promotion Costs) 32 Total 33 34 *Hint: When AB Chemicals pay distributor support costs to the "Wholesaler/distributor", this cost translate into an income for the wholesaler and distributor. But the wholesaler/distributor incurs a costs when offering the support so they don't take the full amount. 35 The same applies to Retailer promotion costs. 36 37 38 39 40 Example Ready 109% Type here to search o > | 56%) . 24C O O lic ENG IN 01:59 PM 29-11-2021 714 AutoSave Off Distribution Costs_blank-1.xlsx - Excel Search (Alt+Q) suraj kumar File Home Insert Page Layout Formulas Data Review View Help Comments Share X Calibri 11 General = = - AP Conditional Formatting ! Insert a AY F e 9X Delete Paste B I U A % , ! 2 Format as Table Cell Styles Styles Sort & Find & Filter Select Analyze Data Sensitivity Format Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity F4 X fx B C D E F H AB Chemicals 1 2 Baseline Scenario 1 Scenario 2 $3.00 $5.00 5.00% 2,00,000 (increase price, distributor support cost (increase price) & Retailer promo cost) $3.000 $3.00 $10.00 $10.00 2.00% 2.00% 5 points each 15% sales revenue 20% sales revenue 3 4 Unit Cost 5 Unit Revenue (price) 6 Share of Market Potential 7 Sales Volume 8 Sales Revenue 9 Gross Margin % 10 Gross Margin $ 11 Distributor Support Costs (10% sales revenue) 12 Retailer Promotion Costs (15% sales revenue) 13 Operating Margin $ (Year 1 Margin) 14 Growth Rate (every year) 15 Year 2 Margin 16 Year 3 Margin 17 18 Wholesaler/Distributor (aggregated) 19 Purchase Coctelia sales revenue of producer Example Ready Type here to search 5.00% 5.00% 5.00% co co col 109% o E d WA | 56 % . 24C 0 0 4x lic ENG IN 01:58 PM 29-11-2021 14 AutoSave Off Distribution Costs_blank-1.xlsx - Excel Search (Alt+Q) suraj kumar File Home Insert Page Layout Formulas Data Review View Help Comments Share X Calibri 11 General - AP = = Conditional Formatting ! Insert a AY F e LO 2 Format as Table 9X Delete Paste B I U A C%) 8 28 Sort & Find & Filter Select Analyze Data Sensitivity Format Cell Styles Styles Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity F4 X fx B D E F H $0 $0 $0 A 16 Year 3 Margin 17 18 Wholesaler/Distributor (aggregated) 19 Purchase Costs (i.e., sales revenue of producer) 20 Wholesaler/Distributor Markup (15%) 21 Sales Revenue 22 Operating Margin (38% of Markup) 23 Distributor support income (cost = 50% of Distributor support costs) 24 Total 25 26 Retailers (aggregated) 27 Purchase Costs (i.e., sales revenue of wholesaler/distributor ) 28 Retailers Markup (25%) 29 Sales Revenue 30 Operating Margin (40% of markup) 31 Retailer promotion income (cost = 30% of Retailer Promotion Costs) 32 Total 15 points each $0 $0 $0 20 points each 33 34 * Hint: When AB Chemicals pay distributor support costs to the Example Ready 109% Type here to search E | 56 % ) . 24C 0 0 4x lic ENG IN 01:58 PM 29-11-2021 714 AutoSave Off Distribution Costs_blank-1.xlsx - Excel Search (Alt+Q) suraj kumar File Home Insert Page Layout Formulas Data Review View Help Comments Share X Calibri 11 - A = = General Conditional Formatting ! Insert a AY F e 2 Format as Table 9X Delete Paste B I U A C%) 8 Analyze Data Sensitivity Format Sort & Find & Filter Select Editing Cell Styles Styles Undo Clipboard Font Alignment Number Cells Analysis Sensitivity F4 fx A B D E F H $0 $0 $0 20 points each 25 26 Retailers (aggregated) 27 Purchase Costs (i.e., sales revenue of wholesaler/distributor) 28 Retailers Markup (25%) 29 Sales Revenue 30 Operating Margin (40% of markup) 31 Retailer promotion income (cost = 30% of Retailer Promotion Costs) 32 Total 33 34 *Hint: When AB Chemicals pay distributor support costs to the "Wholesaler/distributor", this cost translate into an income for the wholesaler and distributor. But the wholesaler/distributor incurs a costs when offering the support so they don't take the full amount. 35 The same applies to Retailer promotion costs. 36 37 38 39 40 Example Ready 109% Type here to search o > | 56%) . 24C O O lic ENG IN 01:59 PM 29-11-2021 714