1. Use the attached Excel data file to analyze and create the appropriate pivot tables needed to provide the appropriate information for the following: a. Which products are the best selling products in each category (Donuts, Muffins, and Bagels per year? (Hint: this will require a breakdown for both the units sold and the dollar value sold) b. Which products are our best based on total sales per year? c. Which products are our most profitable per year? (Hint: This requires a calculation for each sale) 2. Analyze and create a pivot table for each of the scenarios. 60" 54" 226.8 210 210" 4435.2 Blueberry Bagel 504 203 0,032.00 130 - 92.47 Carrot Cake Muffins 138.6 200 A B C D E F G H J K L M M 1 Year Category Item Units weekly Units per Month Yearly Units Unit Cost Monthly Total Cost Yearly Total Cost Sale Price Monthly Sale Total Yearly Sales 2. 2021 Muffins Blueberry Muffins 130 546 6552 $ 1.75 $ 955.50 $ 11,466.00 $ 2.50 $ 1,365.00 3 $ 16,380.00 Muffins Strawberry Muffins 252' 3024 $ 1.75 $ 441.00 $ 5,292.00 $ 2.50 $ 630.00 $ 7,560.00 4 Muffins Poppy Seed Muffins 2721.6$ 1.75 $ 396.90 $ 4,762.80 $ 2.50 $ 567.00 5 $ 6,804.00 Muffins Carrot Cake Muffins 50 50 2520 $ 2.25 $ 472.50 $ 5,670.00 $ 3.75 $ 787.50 S 9.450.00 9,430.00 Muffins Banana Nut Muffins 125 525 6300 S D300 2.22 2.25 $ 1,181.25 $ 14,175.00 $ $ 23.625.00 3.75 $1,968.75 Bagels 3.13 1.300:1? 88 23.02. Poppy Seed Bagel 369.6 $ 1.99 $ 735.50 $ 8,826.05 $ 3.50 $ 1.293.60 3.30 1.295.00 $15,523.20 Bagels Onion Bage! 23 2.-23.ZU 20 96.6 1159.2 $ 1.99 $ *. 192.23 $ 2,306.81 $ 3.50 $ 338.10 2.1.2 $ 4,057.20 Bagels All IN Bagel 250 1050 12600 $ 12000 1.99 $ *. 2,089.50 $ 25,074.00 $ 3.50 3,675.00 3.50 3.675.00 $ 44,100.00 Bagels 120 6048 $ 1.99 $ 10. *. 1.002.96 090 $ 12,035.52 $ 3.50 $1,764.00 11 Bagels 12.10:22 $21,168.00 Cinnamon Raisin Bagel $ 80 Bagels ou 336 4032 $ 1.99 $ w 668.64 $ 8,023.68 $ $14.112.00 3.50 $ 1,176.00 Donuts 0.025.00 Donuts Raspberry Filled donut 45 189 217.112. S 2268 $ 1.00 $ 189.00 $ 2200 2,268.00 $ 1.75 $ 330.75 13 4,200.00 $ 3,969.00 Donuts Donuts Glazed Bowtie 60 9.209.00 252 3024 $ 0.75 $ 22 189.00 $ 2,268.00 $ 109.00 1.25 $ 315.00 14 S $ 3,780.00 Donuts Strawberry Glaze Sprink 66 277.2 13.00 3326.4 5 > 3.100.00 1.00 $ 277.20 $ 3,326.40 $ 250 S 693.00 $ S 8.316.00 15 Birthday Cake Donuts 20. 0.0 0,310.00 Domus birthday Cake Donut 88 369.6 4435.2 $ 1.50 $ 554.40 $ 6,652.80 $ 3.00 . $ 1,108.80 16 $13,305.60 Donuts 310.03.09 Chocolate Chunk Donut Donde 45 189 2268 $ 1.50 109 S $ SAVO 283.50 $ 17 3,402.00 $ 203:34 3.00 $ 567.00 2020 Muffins 0.007307.00 $ 6,804.00 Blueberry Muffins ws 150 630 30 7560 $ 1.75 $ 262.50 $ 3,150.00 $ $ 2.50 $ 375.00 202.5 18 $ 4,500.00 Muffins 230 375.00 Strawberry Muffins wurtins 50 210 2520 S 25205 1.75 $ 87.50 $ 1,050.00 $ 01.30 2.50 $ $ 125.00 S 1.500.00 19 Muffins 123.0 Poppy Seed Muffins wurtins 31,300.00 22 1108.8 $ 92.4 1.75 $ 38.50 $ 462.00 $ 2.50 $ 55.00 20 S 660.00 UL. Muffins 33.00 33 carrot cake 138.6 1663.2 $ S 2.25 $ 74.25 $ 891.00 $ S 1.485.00 14.20 3.75 $ 123.75 21 D. Banana Nut Muffins Muffins 80 336 11 21.02.0 4032 $ 2.25 $ 24 we -2 180.00 $ 400.00 22 2,160.00 2.100.00 S 3.75 $ 300.00 wa Bagels Poppy Seed Bagel $ 3,600.00 33.000. 40 2016 S www. 168 2010 1.99 4 $ 79.60 $ 955.20 . $ 3.50 $ 140.00 23 323.20 $ 1,680.00 Onion Bagel 140.00 Bagels 23 1,000.0 96.6 1159.2 $ 1.99 $ $ *. , 45.77 $ 3.1 549.24 $ 3.50 $ 349.29 80.50 Bagels S 24 . 966.00 All IN Bagel Bagels 77 323.4 300.00 3880.8 $ 1.99 $ *. 153.23 $ 155.25 1,838.76 $ 350 $ 269 50 1,058.76 25 3.50 269.30 $ 3,234.00 Bagels Blueberry Bage! 32 134.4) 1612.8 $ 199 $ 1.99 $ 63.68 $ 764.16 $ 350 $112.00 704.16 26 $ 3.50 112.00 Bagels $ 1,344.00 Bagels Cinnamon Raisin Bagel 45 189 2268 $ 1.99 $ 89.55$ 09.35 $ 1,074.60 3.50 $ 157.50 3.30 $ 1,890.00 Raspberry Filled donut 45 189 2268 $ 1.00 $ 1.09 45.00 $ 540.00 $ 1.75 $ 78.75 $ 28 945.00 Donuts Glazed Bowtie 44 184.8 2217.6 $ 0.75 $ 33.00 $ 396.00 $ 1.25 $ 55.00 29 $ 660.00 Donuts Strawberry Glaze Sprink 33 138.6 1663.2 $ ce 1.00 $ 33.00 $ 396.00 $ 2.50 $ 82.50 S $ 990.00 30 Donuts Birthday Cake Donut 44 184.8 2217.6 $ 1.50 $ 66.00 $ 792.00 $ 2.75 $ 121.00 31 $ 1,452.00 Donuts Chocolate Chunk Donut 15 63 756 $ 1.50 $ 22.50 $ 14 ene 270.00 $ 2.75 $ 41.25 S 32 $ 495.00 2019 Muffins Blueberry Muffins 125 6300 $ *** 1.75 S ... 218.75 $ 2,625.00 $ 33 2.50 $ 312.50 $ 3,750.00 Muffins Strawberry Muffins 70 - 294 3528 $ 1.75 $ 122.50 $ 1 1,470.00 $ . 2.50 $ 175.00 34 $ 2,100.00 ** Muffins Poppy Seed Muffins 75 315 3780 $ 1.75 $ 12 131.25 $ 20 1,575.00 $ 2.50 $ 187.50 $ 2,250.00 Muffins Carrot Cake Muffins 33 138.6 1663.2 $ 2.25 $ 74.25 $ 2010 we 891.00 $ 3.75 $ 123.75 $ 1,485.00 Muffins Banana Nut Muffins * 168 2016 $ 2.25 $ 90.00 $ 1,080.00 $ 3.75 $ 150.00 $ 1,800.00 Bagels www.ce Poppy Seed Bagel 55 " 2772 $ 1.50 S 82.50 $ 990.00 $ 3.50 $ 192.50 $ 2,310.00 . Bagels Onion Bagel 23 96.6 1159.2 $ 1.50 $ 34.50 $ 414.00 $ 3.50 $ 80.50 $ 966.00 Bagels All IN Bagel 77 323.4 3880.8 $ 1.50 S 115.50 $ 1,386.00 $ 3.50 $ 269.50 con $ 3,234.00 40 Bagels Blueberry Bagel e 50 210 2520 $ 1.50 $ 75.00 $ 900.00 $ 3.50 $ 175.00 $ 2,100.00 Bagels Cinnamon Raisin Bagel 138.6 1663.2 $ 1.50 $ 49.50 $ 21 Save 594.00 $ 3.50 $ 115.50 $ 1,386.00 42 Donuts Raspberry Filled donut 189 2268 $ 0.75 $ *** 33.75 $ 405.00 $ 1.75 $ 78.75 $ 945.00 43 Donuts Glazed Bowtie 184.8 2217.6 $ 0.75 $ 33.00 $ 396.00 $ 1.25 $ 55.00 $ 660.00 44 Donuts Strawberry Glaze Sprink 1663.2 S 0.75 $ 24.75 $ 297.00 $ 2.50 $ 82.50 $ 990.00 45 Donuts Birthday Cake Donut 210 2520 $ 1.25 $ 62.50 $ 750.00 $ 2.75 $ 137.50 $ 1,650.00 46 Donuts Chocolate Chunk Donut 1008 $ 1.25 $ 25.00 $ 300.00 S 2.75 $ 55.00 $ 660.00 F 134.4 109 189 Donuts 525 401 231 88*** 138.6 841 1. Use the attached Excel data file to analyze and create the appropriate pivot tables needed to provide the appropriate information for the following: a. Which products are the best selling products in each category (Donuts, Muffins, and Bagels per year? (Hint: this will require a breakdown for both the units sold and the dollar value sold) b. Which products are our best based on total sales per year? c. Which products are our most profitable per year? (Hint: This requires a calculation for each sale) 2. Analyze and create a pivot table for each of the scenarios. 60" 54" 226.8 210 210" 4435.2 Blueberry Bagel 504 203 0,032.00 130 - 92.47 Carrot Cake Muffins 138.6 200 A B C D E F G H J K L M M 1 Year Category Item Units weekly Units per Month Yearly Units Unit Cost Monthly Total Cost Yearly Total Cost Sale Price Monthly Sale Total Yearly Sales 2. 2021 Muffins Blueberry Muffins 130 546 6552 $ 1.75 $ 955.50 $ 11,466.00 $ 2.50 $ 1,365.00 3 $ 16,380.00 Muffins Strawberry Muffins 252' 3024 $ 1.75 $ 441.00 $ 5,292.00 $ 2.50 $ 630.00 $ 7,560.00 4 Muffins Poppy Seed Muffins 2721.6$ 1.75 $ 396.90 $ 4,762.80 $ 2.50 $ 567.00 5 $ 6,804.00 Muffins Carrot Cake Muffins 50 50 2520 $ 2.25 $ 472.50 $ 5,670.00 $ 3.75 $ 787.50 S 9.450.00 9,430.00 Muffins Banana Nut Muffins 125 525 6300 S D300 2.22 2.25 $ 1,181.25 $ 14,175.00 $ $ 23.625.00 3.75 $1,968.75 Bagels 3.13 1.300:1? 88 23.02. Poppy Seed Bagel 369.6 $ 1.99 $ 735.50 $ 8,826.05 $ 3.50 $ 1.293.60 3.30 1.295.00 $15,523.20 Bagels Onion Bage! 23 2.-23.ZU 20 96.6 1159.2 $ 1.99 $ *. 192.23 $ 2,306.81 $ 3.50 $ 338.10 2.1.2 $ 4,057.20 Bagels All IN Bagel 250 1050 12600 $ 12000 1.99 $ *. 2,089.50 $ 25,074.00 $ 3.50 3,675.00 3.50 3.675.00 $ 44,100.00 Bagels 120 6048 $ 1.99 $ 10. *. 1.002.96 090 $ 12,035.52 $ 3.50 $1,764.00 11 Bagels 12.10:22 $21,168.00 Cinnamon Raisin Bagel $ 80 Bagels ou 336 4032 $ 1.99 $ w 668.64 $ 8,023.68 $ $14.112.00 3.50 $ 1,176.00 Donuts 0.025.00 Donuts Raspberry Filled donut 45 189 217.112. S 2268 $ 1.00 $ 189.00 $ 2200 2,268.00 $ 1.75 $ 330.75 13 4,200.00 $ 3,969.00 Donuts Donuts Glazed Bowtie 60 9.209.00 252 3024 $ 0.75 $ 22 189.00 $ 2,268.00 $ 109.00 1.25 $ 315.00 14 S $ 3,780.00 Donuts Strawberry Glaze Sprink 66 277.2 13.00 3326.4 5 > 3.100.00 1.00 $ 277.20 $ 3,326.40 $ 250 S 693.00 $ S 8.316.00 15 Birthday Cake Donuts 20. 0.0 0,310.00 Domus birthday Cake Donut 88 369.6 4435.2 $ 1.50 $ 554.40 $ 6,652.80 $ 3.00 . $ 1,108.80 16 $13,305.60 Donuts 310.03.09 Chocolate Chunk Donut Donde 45 189 2268 $ 1.50 109 S $ SAVO 283.50 $ 17 3,402.00 $ 203:34 3.00 $ 567.00 2020 Muffins 0.007307.00 $ 6,804.00 Blueberry Muffins ws 150 630 30 7560 $ 1.75 $ 262.50 $ 3,150.00 $ $ 2.50 $ 375.00 202.5 18 $ 4,500.00 Muffins 230 375.00 Strawberry Muffins wurtins 50 210 2520 S 25205 1.75 $ 87.50 $ 1,050.00 $ 01.30 2.50 $ $ 125.00 S 1.500.00 19 Muffins 123.0 Poppy Seed Muffins wurtins 31,300.00 22 1108.8 $ 92.4 1.75 $ 38.50 $ 462.00 $ 2.50 $ 55.00 20 S 660.00 UL. Muffins 33.00 33 carrot cake 138.6 1663.2 $ S 2.25 $ 74.25 $ 891.00 $ S 1.485.00 14.20 3.75 $ 123.75 21 D. Banana Nut Muffins Muffins 80 336 11 21.02.0 4032 $ 2.25 $ 24 we -2 180.00 $ 400.00 22 2,160.00 2.100.00 S 3.75 $ 300.00 wa Bagels Poppy Seed Bagel $ 3,600.00 33.000. 40 2016 S www. 168 2010 1.99 4 $ 79.60 $ 955.20 . $ 3.50 $ 140.00 23 323.20 $ 1,680.00 Onion Bagel 140.00 Bagels 23 1,000.0 96.6 1159.2 $ 1.99 $ $ *. , 45.77 $ 3.1 549.24 $ 3.50 $ 349.29 80.50 Bagels S 24 . 966.00 All IN Bagel Bagels 77 323.4 300.00 3880.8 $ 1.99 $ *. 153.23 $ 155.25 1,838.76 $ 350 $ 269 50 1,058.76 25 3.50 269.30 $ 3,234.00 Bagels Blueberry Bage! 32 134.4) 1612.8 $ 199 $ 1.99 $ 63.68 $ 764.16 $ 350 $112.00 704.16 26 $ 3.50 112.00 Bagels $ 1,344.00 Bagels Cinnamon Raisin Bagel 45 189 2268 $ 1.99 $ 89.55$ 09.35 $ 1,074.60 3.50 $ 157.50 3.30 $ 1,890.00 Raspberry Filled donut 45 189 2268 $ 1.00 $ 1.09 45.00 $ 540.00 $ 1.75 $ 78.75 $ 28 945.00 Donuts Glazed Bowtie 44 184.8 2217.6 $ 0.75 $ 33.00 $ 396.00 $ 1.25 $ 55.00 29 $ 660.00 Donuts Strawberry Glaze Sprink 33 138.6 1663.2 $ ce 1.00 $ 33.00 $ 396.00 $ 2.50 $ 82.50 S $ 990.00 30 Donuts Birthday Cake Donut 44 184.8 2217.6 $ 1.50 $ 66.00 $ 792.00 $ 2.75 $ 121.00 31 $ 1,452.00 Donuts Chocolate Chunk Donut 15 63 756 $ 1.50 $ 22.50 $ 14 ene 270.00 $ 2.75 $ 41.25 S 32 $ 495.00 2019 Muffins Blueberry Muffins 125 6300 $ *** 1.75 S ... 218.75 $ 2,625.00 $ 33 2.50 $ 312.50 $ 3,750.00 Muffins Strawberry Muffins 70 - 294 3528 $ 1.75 $ 122.50 $ 1 1,470.00 $ . 2.50 $ 175.00 34 $ 2,100.00 ** Muffins Poppy Seed Muffins 75 315 3780 $ 1.75 $ 12 131.25 $ 20 1,575.00 $ 2.50 $ 187.50 $ 2,250.00 Muffins Carrot Cake Muffins 33 138.6 1663.2 $ 2.25 $ 74.25 $ 2010 we 891.00 $ 3.75 $ 123.75 $ 1,485.00 Muffins Banana Nut Muffins * 168 2016 $ 2.25 $ 90.00 $ 1,080.00 $ 3.75 $ 150.00 $ 1,800.00 Bagels www.ce Poppy Seed Bagel 55 " 2772 $ 1.50 S 82.50 $ 990.00 $ 3.50 $ 192.50 $ 2,310.00 . Bagels Onion Bagel 23 96.6 1159.2 $ 1.50 $ 34.50 $ 414.00 $ 3.50 $ 80.50 $ 966.00 Bagels All IN Bagel 77 323.4 3880.8 $ 1.50 S 115.50 $ 1,386.00 $ 3.50 $ 269.50 con $ 3,234.00 40 Bagels Blueberry Bagel e 50 210 2520 $ 1.50 $ 75.00 $ 900.00 $ 3.50 $ 175.00 $ 2,100.00 Bagels Cinnamon Raisin Bagel 138.6 1663.2 $ 1.50 $ 49.50 $ 21 Save 594.00 $ 3.50 $ 115.50 $ 1,386.00 42 Donuts Raspberry Filled donut 189 2268 $ 0.75 $ *** 33.75 $ 405.00 $ 1.75 $ 78.75 $ 945.00 43 Donuts Glazed Bowtie 184.8 2217.6 $ 0.75 $ 33.00 $ 396.00 $ 1.25 $ 55.00 $ 660.00 44 Donuts Strawberry Glaze Sprink 1663.2 S 0.75 $ 24.75 $ 297.00 $ 2.50 $ 82.50 $ 990.00 45 Donuts Birthday Cake Donut 210 2520 $ 1.25 $ 62.50 $ 750.00 $ 2.75 $ 137.50 $ 1,650.00 46 Donuts Chocolate Chunk Donut 1008 $ 1.25 $ 25.00 $ 300.00 S 2.75 $ 55.00 $ 660.00 F 134.4 109 189 Donuts 525 401 231 88*** 138.6 841
