Question

1 Approved Answer

Posted on Sep 30, 2024

Question Three (30 marks) You are required to extract the data recorded in the following table and place them on a spreadsheet and to perform

image text in transcribed

Question Three (30 marks) You are required to extract the data recorded in the following table and place them on a spreadsheet and to perform the following analysis and properly tabulate your answers, (i) ) The total amount of net sales by month and by year (ii) The total sales amount grouped by each product category (iii) The total profit amount by each product category (iv) The total sales value to each country (v) The average sales price for each product type Discounts Sales COGS Profit Date 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 D 352100 0 0 D 0 0 0 Segment Country Discount Product Band Government Canada Carretera None Government Germany Carretera None Midmarket France Carretera None Midmarket Germany Carretera None Midmarket Mexico Carretera None Government Germany Carretera None Midmarket Germany Montana None Channel Partners Canada Montana None Government France Montana None Channel Partners Germany Montana None Midmarket Mexico Montana None Enterprise Canada Montana None Small Business Mexico Montana None Government Germany Montana None Enterprise Canada Montana None Midmarket USA Montana None Government Canada Paseo None Midmarket Mexico Paseo None Channel Partners Canada Paseo None Government Germany Paseo None Channel Partners Germany Paseo None Government Mexiko Paseo None Midmarket France Paseo None Small Business Mexiko Paseo None Midmarket Mexico Paseo None Government USA Paseo None Government Canada Paseo None Channel Partners USA Paseo None Midmarket Canada Paseo None Government Canada Paseo None Government Germany Paseo None Government Mexico Velo None Enterprise France Velo None Channel Partners Germany Velo None Government Germany Velo None Channel Partners Germany Velo None Enterprise USA Velo None Enterprise Canada Velo None Small Business Canada VIT None Channel Partners Germany VIT None Midmarket France VTT None Midmarket Germany VIT None Government France VIT None Small Business France VTT None Government Canada VTT Nond Government France Amarilla None Channel Partners USA Amarilla None Enterprise Germany Amarilla None Government France Amarill None Government Germany Amarilla None Channel Partners USA Amarilla None Government USA Amanila None Midmarket USA Amarills None Government France Paseo Low Midmarket France Paseo Low Government France Paseo Low Government France Velo Low Government Canada VIT Low Channel Partners USA Carretera Low Government Mexico Carretera Low Units Manufacturing Sold Price Sale Pricel Gross Sales 16185 3 3 20 32370 1321 3 3 20 26420 2178 3 15 32670 888 3 15 13320 2470 3 15 37050 1513 3 350 529550 921 5 15 13815 2518 5 12 30216 1899 5 20 37980 1545 5 12 18540 2470 5 15 37050 2665 S S 125 333187.5 958 5 5 300 287400 2146 5 7 15022 345 5 125 43125 615 5 15 9225 292 10 20 5840 974 10 15 14610 2518 10 12 30216 1006 10 350 367 10 12 4404 883 10 7 6181 549 10 15 8235 788 10 300 236400 2472 10 15 37080 1143 10 7 8001 1725 10 350 603750 912 10 12 10944 2152 10 15 32280 1817 10 20 36340 1513 10 350 529550 1493 120 7 10451 1804 125 225500 2161 120 12 29932 1006 120 350 352100 1545 120 12 18540 2821 120 125 352625 345 120 125 43125 2001 250 300 600300 2838 250 12 34056 2178 250 15 32670 888 250 15 13320 1527 250 350 534450 2151 250 300 645300 1817 250 20 36340 2750 260 350 962500 1953 260 12 23436 4219.5 260 125 527437.5 1899 260 20 37980 1686 260 7 11802 2141 260 12 1143 260 7 8001 615 260 15 9225 3945 10 7 27615 2296 10 15 34440 1030 10 7 7210 120 7 4473 1326 250 7 7 9282 1858 3 12 22296 1210 3 350 423500 0 0 0 32370 26420 32670 13320 37050 529550 13815 30216 37980 18540 37050 333187.5 287400 15022 43125 9225 5840 14610 30216 352100 4404 6181 8235 236000 37080 8001 603750 10944 32280 36340 529550 10451 225500 25932 352100 18540 352625 43125 600300 34056 32670 13320 534450 645300 36340 962500 23436 527437.5 37980 11802 25692 8001 9225 27338.85 34095.6 71379 4428.27 9189.18 22073.04 419265 16185 13210 21780 8880 24700 393380 9210 7554 18990 4635 24700 319860 239500 10730 41400 6150 2920 9740 7554 261560 1101 4415 5490 197000 24720 5715 448500 2736 21520 18170 393380 7465 216480 6483 261560 4635 338520 41400 500250 8514 21780 8880 397020 537750 18170 715000 5859 506340 18990 8430 6423 5715 6150 19725 22960 5150 3195 6630 5574 314600 16189 Jan-14 13210 Jan 14 10890 Jun-14 4440 Jun-14 12350 Jun-14 136170 Dec. 14 4605 Mar 14 22662 Jun-14 18990 Jun 14 13905 Jun-14 12350 Jun-14 13327.5 Jul 14 47900 Aug 14 4292 Sep-14 1725 Oct-13 3075 Dec-14 2920 Feb 14 4870 Feb-14 22662 Jun-14 90540 Jun-14 3303 Jul 14 1766 Aug 14 2745 Sep-13 39400 Sep 13 12360 Sep-14 2286 Oct-14 155250 Nov 13 8208 Nov.13 10760 Dec 13 18170 Dec 14 136170 Dec 14 2986 Jan-14 9020 Feb-14 19449 Mar 14 90540 Jun-14 13905 Jun-14 14105 Aug-14 1725 Oct-13 100050 Feb-14 25542 Apr 14 10890 Jun-14 4440 Jun-14 137430 Sep-13 107550 Sep-14 18170 Dec 14 247500 Feb-14 17577 Apr 14 21097,5 Apr 14 18990 Jun 14 3372 Jul 14 19269 Aug 14 2286 Oct-14 3075 Dec-14 7613.85 Jan-14 11135,6 Feb-14 1987.9 May 14 1233.27 Nov 14 2559.18 Mar-14 16499.04 Feb 14 - 104665 Mar 14 120 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 25692 0 0 0 276.15 344.4 72.1 44.73 92 82 222.96 4235 639 Question Three (30 marks) You are required to extract the data recorded in the following table and place them on a spreadsheet and to perform the following analysis and properly tabulate your answers, (i) ) The total amount of net sales by month and by year (ii) The total sales amount grouped by each product category (iii) The total profit amount by each product category (iv) The total sales value to each country (v) The average sales price for each product type Discounts Sales COGS Profit Date 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 D 352100 0 0 D 0 0 0 Segment Country Discount Product Band Government Canada Carretera None Government Germany Carretera None Midmarket France Carretera None Midmarket Germany Carretera None Midmarket Mexico Carretera None Government Germany Carretera None Midmarket Germany Montana None Channel Partners Canada Montana None Government France Montana None Channel Partners Germany Montana None Midmarket Mexico Montana None Enterprise Canada Montana None Small Business Mexico Montana None Government Germany Montana None Enterprise Canada Montana None Midmarket USA Montana None Government Canada Paseo None Midmarket Mexico Paseo None Channel Partners Canada Paseo None Government Germany Paseo None Channel Partners Germany Paseo None Government Mexiko Paseo None Midmarket France Paseo None Small Business Mexiko Paseo None Midmarket Mexico Paseo None Government USA Paseo None Government Canada Paseo None Channel Partners USA Paseo None Midmarket Canada Paseo None Government Canada Paseo None Government Germany Paseo None Government Mexico Velo None Enterprise France Velo None Channel Partners Germany Velo None Government Germany Velo None Channel Partners Germany Velo None Enterprise USA Velo None Enterprise Canada Velo None Small Business Canada VIT None Channel Partners Germany VIT None Midmarket France VTT None Midmarket Germany VIT None Government France VIT None Small Business France VTT None Government Canada VTT Nond Government France Amarilla None Channel Partners USA Amarilla None Enterprise Germany Amarilla None Government France Amarill None Government Germany Amarilla None Channel Partners USA Amarilla None Government USA Amanila None Midmarket USA Amarills None Government France Paseo Low Midmarket France Paseo Low Government France Paseo Low Government France Velo Low Government Canada VIT Low Channel Partners USA Carretera Low Government Mexico Carretera Low Units Manufacturing Sold Price Sale Pricel Gross Sales 16185 3 3 20 32370 1321 3 3 20 26420 2178 3 15 32670 888 3 15 13320 2470 3 15 37050 1513 3 350 529550 921 5 15 13815 2518 5 12 30216 1899 5 20 37980 1545 5 12 18540 2470 5 15 37050 2665 S S 125 333187.5 958 5 5 300 287400 2146 5 7 15022 345 5 125 43125 615 5 15 9225 292 10 20 5840 974 10 15 14610 2518 10 12 30216 1006 10 350 367 10 12 4404 883 10 7 6181 549 10 15 8235 788 10 300 236400 2472 10 15 37080 1143 10 7 8001 1725 10 350 603750 912 10 12 10944 2152 10 15 32280 1817 10 20 36340 1513 10 350 529550 1493 120 7 10451 1804 125 225500 2161 120 12 29932 1006 120 350 352100 1545 120 12 18540 2821 120 125 352625 345 120 125 43125 2001 250 300 600300 2838 250 12 34056 2178 250 15 32670 888 250 15 13320 1527 250 350 534450 2151 250 300 645300 1817 250 20 36340 2750 260 350 962500 1953 260 12 23436 4219.5 260 125 527437.5 1899 260 20 37980 1686 260 7 11802 2141 260 12 1143 260 7 8001 615 260 15 9225 3945 10 7 27615 2296 10 15 34440 1030 10 7 7210 120 7 4473 1326 250 7 7 9282 1858 3 12 22296 1210 3 350 423500 0 0 0 32370 26420 32670 13320 37050 529550 13815 30216 37980 18540 37050 333187.5 287400 15022 43125 9225 5840 14610 30216 352100 4404 6181 8235 236000 37080 8001 603750 10944 32280 36340 529550 10451 225500 25932 352100 18540 352625 43125 600300 34056 32670 13320 534450 645300 36340 962500 23436 527437.5 37980 11802 25692 8001 9225 27338.85 34095.6 71379 4428.27 9189.18 22073.04 419265 16185 13210 21780 8880 24700 393380 9210 7554 18990 4635 24700 319860 239500 10730 41400 6150 2920 9740 7554 261560 1101 4415 5490 197000 24720 5715 448500 2736 21520 18170 393380 7465 216480 6483 261560 4635 338520 41400 500250 8514 21780 8880 397020 537750 18170 715000 5859 506340 18990 8430 6423 5715 6150 19725 22960 5150 3195 6630 5574 314600 16189 Jan-14 13210 Jan 14 10890 Jun-14 4440 Jun-14 12350 Jun-14 136170 Dec. 14 4605 Mar 14 22662 Jun-14 18990 Jun 14 13905 Jun-14 12350 Jun-14 13327.5 Jul 14 47900 Aug 14 4292 Sep-14 1725 Oct-13 3075 Dec-14 2920 Feb 14 4870 Feb-14 22662 Jun-14 90540 Jun-14 3303 Jul 14 1766 Aug 14 2745 Sep-13 39400 Sep 13 12360 Sep-14 2286 Oct-14 155250 Nov 13 8208 Nov.13 10760 Dec 13 18170 Dec 14 136170 Dec 14 2986 Jan-14 9020 Feb-14 19449 Mar 14 90540 Jun-14 13905 Jun-14 14105 Aug-14 1725 Oct-13 100050 Feb-14 25542 Apr 14 10890 Jun-14 4440 Jun-14 137430 Sep-13 107550 Sep-14 18170 Dec 14 247500 Feb-14 17577 Apr 14 21097,5 Apr 14 18990 Jun 14 3372 Jul 14 19269 Aug 14 2286 Oct-14 3075 Dec-14 7613.85 Jan-14 11135,6 Feb-14 1987.9 May 14 1233.27 Nov 14 2559.18 Mar-14 16499.04 Feb 14 - 104665 Mar 14 120 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 25692 0 0 0 276.15 344.4 72.1 44.73 92 82 222.96 4235 639