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)

525 anco 60 co 1108.8 $ 1.75 38.50 $ 24 138.6 123.75 30 2,100.00 ou 40 Year Category Item Units weekly Units per Montl Yearly Units Unit Cost Monthly Total Cost Yearly Total Cost 2021 Muffins Blueberry Muffins Sale Price Monthly Sale Total 130 546 6552 $ 1.75 $ 955.50 $ 11,466.00 $ 2.50 $ 1,365.00 Muffins Strawberry Muffins 60 252 3024 $ 1.75 $ 441.00 $ 5,292.00 $ 2.50 $ 630.00 Muffins Poppy Seed Muffins 54 226.8 2721.6 $ 1.75 1.75 $ 396.90 $ 4,762.80 $ 2.50 $ 567.00 Muffins Carrot Cake Muffins 50 M. 210 2520 $ 2.25 2.25 $ 472.50 $ 5,670.00 $ 40 3.75 $ 787.50 Muffins Banana Nut Muffins * 125 6300 $ were 2.25 $ wed 1,181.25 $ 14,175.00 $ 2 Bagels 3.75 $ 1,968.75 e Poppy Seed Bagel 88 ber ce 369.6 4435.2 $ 1.99 1.99 $ 735.50 $ 8,826.05 $ 3.50 $ 1,293.60 Bagels Onion Bagel Sede 23 ber 96.6 1159.2 $ 1.99 1.99 $ . 192.23 $ ***** 2,306.81 $ 3.50 $ Bagels 338.10 * 250 All IN Bagel 20:10 1050 12600 $ 1.99 1.99 $ 2,089.50 $ 1. 25,074.00 $ 3.50 $ 3,675.00 Bagels Blueberry Bagel 120 504 6048 $ 1.99 4. 1.99 $ 1,002.96 $ 12,035.52 $ Bagels 3.50 $ 1,764.00 100 80 Cinnamon Raisin Bagel * 336 will 4032 $ . 1.99 $ * 668.64 $ 8,023.68 $ 3.50 $ 1,176.00 Donuts Raspberry Filled donut 45 189 2268 $ 1.00 $ 189.00 $ Bacon 200 2,268.00 $ 1.75 $ 102 330.75 Donuts Glazed Bowtie 100 252 3024 $ 0.75 $ CO 13 189.00 $ 2,268.00 $ 1.25 $ 315.00 Donuts Strawberry Glaze Sprinkl 66 : 100 277.2 3326.4 $ 1.00 mene 1.00 $ 277.20 $ sos 2.50 $ 3,326.40 $ 693.00 Donuts Birthday Cake Donut 88 TER 4435.2 $ 369.6 1.50 $ FE40 CES 90 554.40 $ 200 100 6,652.80 $ 3.00 $ 1,108.80 AS Donuts Chocolate Chunk Donut 45 189 2268 $ 1.50 $ 189 409001 283.50 $ 200 3,402.00 $ 3.00 $ $ 567.00 2020 Muffins Blueberry Muffins 150 630 7560 $ 1.75 $ 262.50 $ 3,150.00 $ 15000 25 2.50 $ 275 375.00 Muffins Strawberry Muffins 50 2520 210 30 176 2520 $ 1.75 $ 87.50 $ 1,050.00 $ 250 . 125.00 2.50 $ Muffins Poppy Seed Muffins 22 92.4 1.75 $ 462.00 $ 250 2.50 $ > 55.00 Muffins Carrot Cake Muffins 33 22 1663.2 $ 2.25 $ 74.25 $ 891.00 $ 3.75 $ Muffins Banana Nut Muffins 80 336 4032 $ 2.25 $ 180.00 $ $ 3.75 $ 300.00 Bagels Poppy Seed Bagel 40 168 168 2016 S $ 1.99 $ 79.60 $ 19.50 955.20 $ 3.50 $ Bagels 3.50 140.00 Onion Bagel Onion Bagel 23 96.6 1159.2 $ 1.99 1.99 $ $ > 45.1? 45.77 $ 549.24 $ 3.50 $ 549.24 80.50 Bagels All IN Bagel 77 323.4 1.99 $ 153.23 $ > 1,838.76 $ 3.50 $ Bagels 269.50 3.54 Bagels Blueberry Bagel 32 2 134.4 1612.8 $ 1.99 $ 22 63.68 $ 764.16 $ 112.00 3.50 $ Bagels 04.10 Cinnamon Raisin Bagel 3.30 45 Bagers 112.00 189 * 2268 $ 1.99 $ 10 2200 89.55 $ D. 1,074.60 $ 3.50 $ 157.50 Donuts Raspberry Filled donut 45 189 2268 $ 1.00 1.00 $ 20 1.5 45.00 $ 2007 540.00 $ Donuts Glazed Bowtie 1.75 $ . 78.75 44 12 2217.6 $ 0.75 . 184.8 ** -17.00.13? 0.75 $ 33.00 $ 2.00 396.00 $ 1.25$ 55.00 Donuts Strawberry Glaze Sprinkl 33 138.6 1663.2 $ 1.00 $ de 1.2 33.00 $ 396.00 $ 82.50 2.50 $ Donuts Birthday Cake Donut 20.00 44 184.8) 2217.6 $ 1.50 $ 66.00 $ 792.00 $ 2.75 $ 121.00 Donuts Chocolate Chunk Donut 15 63 756 $ 1.50 $ 22.50 $ 270.00 $ 2.75 2.75 $ 41.25 2019 Muffins Blueberry Muffins 120 125 525 525 $ 1.75 $ 210.70 218.75 $ 2,625.00 $ 2.50 $ 42 312.50 Muffins Strawberry Muffins 294 2529 3528 $ 1.75 1.75 $ 122.50 $ 1,470.00 $ 250 2.50 $ 175 175.00 Muffins Poppy Seed Muffins 75 215 315 3780 $ 1.75 3780 75 1.75 $ $ 1,575.00 $ $ 250 2.50 > Muffins Carrot Cake Muffins 187.50 33 $ > 74.25 $ 891.00 $ 3.75 $ 122 75 123.75 Muffins Banana Nut Muffins 40 168 2016 225 2016 $ 2.25 $ Bagels 2.25 $ 90.00 $ 1,080.00 $ 3.75 $ 150.00 Bagels Poppy Seed Bagel 55 231 2772 $ 1.50 $ 82.50 $ 990.00 $ 3.50 $ 192.50 Bagels Onion Bagel 23 96.6 96.6 1159.2 $ 1.50 $ $ $ 34.50 $ 414.00 $ 3.50 $ 414.00 Bagels 3.50 $ 80.50 All IN Bagel 3880.8 $ $ 1.50 $ 115.50 $ 1.386.00 $ 3.50 $ 269.50 Bagels Blueberry Bagel 50 210 2520 $ 1.50 2520 $ 1.50 419 1.50 $ 75.00 $ 900.00 $ 3.50 $ $ 175.00 Bagels 3.30 Cinnamon Raisin Bagel 33 35 138.6 1663.2 $ 1.50 $ 49.50 $ 594.00 $ 3.50 $ 115.50 Donuts Raspberry Filled donut 45 189 2268 $ 0.75 * 40 0.75 $ 2200 0.1 33.75 $ 405.00 $ . ? Donuts Glazed Bowtie 1.75 $ 78.75 10:13 9.00 44 184.8 2217.6 $ 0.75 0.75 $ 0.rs ? 33.00 $ 396.00 $ 1.25 $ 55.00 Donuts Strawberry Glaze Sprinkl 33 138.6 1663.2 $ 0.75 $ 24.75 $ 297.00 $ 2.50 $ 82.50 Donuts Birthday Cake Donut 50 210 2520 $ 1.25 $ 62.50 $ 750.00 $ 2.75 $ 137.50 Donuts Chocolate Chunk Donut 20 84' 1008 $ 1.25 1.25 $ 25.00 $ 300.00 $ 2.75 $ 55.00 3880.8 $ 1.99 Yearly Sales $ 16,380.00 $ 7,560.00 $ 6,804.00 $ 9,450.00 * $ 23,625.00 * $ 15,523.20 20:40 $ 4,057.26 * $ 44,100.00 www.ca $ 21,168.00 wa $ 14,112.00 Daca $ 3,969.0c . $ 3,780.00 * $ 2460 8,316.00 $ 200 13,305.60 s 001 $ 6,804.00 $ 4,500.00 500 or > $ 1,500.00 $ 660 660.00 $ > 1,485.00 $ 3.600.00 > $ 1.680.00 1,680.00 $ $ 966.00 7 $ 3,234.00 3.234.00 $ 1,344.00 $ 1,890.00 4,030.04 $ $ 945.00 $ 660.00 des $ $ 990.00 $ $ 1,452.00 s $ As of 495.00 ZER $ 3,750.00 $ 100 2,100.00 $ $ 2,250.00 > $ 1,485.00 $ > 1 800 or 1,800.00 $ > 2.310.00 2,310.00 $ 2 966.00 $ $ 2,100.00 $ 1.386.00 00.00 $ $ 945.00 29. $ 660.00 $ 990.00 $ $ 1,650.00 $ 660.00 6300 S 170 2 70 2 131 25 138 6 1663.2 $ 2.25 77 323.4 3,234.00 525 anco 60 co 1108.8 $ 1.75 38.50 $ 24 138.6 123.75 30 2,100.00 ou 40 Year Category Item Units weekly Units per Montl Yearly Units Unit Cost Monthly Total Cost Yearly Total Cost 2021 Muffins Blueberry Muffins Sale Price Monthly Sale Total 130 546 6552 $ 1.75 $ 955.50 $ 11,466.00 $ 2.50 $ 1,365.00 Muffins Strawberry Muffins 60 252 3024 $ 1.75 $ 441.00 $ 5,292.00 $ 2.50 $ 630.00 Muffins Poppy Seed Muffins 54 226.8 2721.6 $ 1.75 1.75 $ 396.90 $ 4,762.80 $ 2.50 $ 567.00 Muffins Carrot Cake Muffins 50 M. 210 2520 $ 2.25 2.25 $ 472.50 $ 5,670.00 $ 40 3.75 $ 787.50 Muffins Banana Nut Muffins * 125 6300 $ were 2.25 $ wed 1,181.25 $ 14,175.00 $ 2 Bagels 3.75 $ 1,968.75 e Poppy Seed Bagel 88 ber ce 369.6 4435.2 $ 1.99 1.99 $ 735.50 $ 8,826.05 $ 3.50 $ 1,293.60 Bagels Onion Bagel Sede 23 ber 96.6 1159.2 $ 1.99 1.99 $ . 192.23 $ ***** 2,306.81 $ 3.50 $ Bagels 338.10 * 250 All IN Bagel 20:10 1050 12600 $ 1.99 1.99 $ 2,089.50 $ 1. 25,074.00 $ 3.50 $ 3,675.00 Bagels Blueberry Bagel 120 504 6048 $ 1.99 4. 1.99 $ 1,002.96 $ 12,035.52 $ Bagels 3.50 $ 1,764.00 100 80 Cinnamon Raisin Bagel * 336 will 4032 $ . 1.99 $ * 668.64 $ 8,023.68 $ 3.50 $ 1,176.00 Donuts Raspberry Filled donut 45 189 2268 $ 1.00 $ 189.00 $ Bacon 200 2,268.00 $ 1.75 $ 102 330.75 Donuts Glazed Bowtie 100 252 3024 $ 0.75 $ CO 13 189.00 $ 2,268.00 $ 1.25 $ 315.00 Donuts Strawberry Glaze Sprinkl 66 : 100 277.2 3326.4 $ 1.00 mene 1.00 $ 277.20 $ sos 2.50 $ 3,326.40 $ 693.00 Donuts Birthday Cake Donut 88 TER 4435.2 $ 369.6 1.50 $ FE40 CES 90 554.40 $ 200 100 6,652.80 $ 3.00 $ 1,108.80 AS Donuts Chocolate Chunk Donut 45 189 2268 $ 1.50 $ 189 409001 283.50 $ 200 3,402.00 $ 3.00 $ $ 567.00 2020 Muffins Blueberry Muffins 150 630 7560 $ 1.75 $ 262.50 $ 3,150.00 $ 15000 25 2.50 $ 275 375.00 Muffins Strawberry Muffins 50 2520 210 30 176 2520 $ 1.75 $ 87.50 $ 1,050.00 $ 250 . 125.00 2.50 $ Muffins Poppy Seed Muffins 22 92.4 1.75 $ 462.00 $ 250 2.50 $ > 55.00 Muffins Carrot Cake Muffins 33 22 1663.2 $ 2.25 $ 74.25 $ 891.00 $ 3.75 $ Muffins Banana Nut Muffins 80 336 4032 $ 2.25 $ 180.00 $ $ 3.75 $ 300.00 Bagels Poppy Seed Bagel 40 168 168 2016 S $ 1.99 $ 79.60 $ 19.50 955.20 $ 3.50 $ Bagels 3.50 140.00 Onion Bagel Onion Bagel 23 96.6 1159.2 $ 1.99 1.99 $ $ > 45.1? 45.77 $ 549.24 $ 3.50 $ 549.24 80.50 Bagels All IN Bagel 77 323.4 1.99 $ 153.23 $ > 1,838.76 $ 3.50 $ Bagels 269.50 3.54 Bagels Blueberry Bagel 32 2 134.4 1612.8 $ 1.99 $ 22 63.68 $ 764.16 $ 112.00 3.50 $ Bagels 04.10 Cinnamon Raisin Bagel 3.30 45 Bagers 112.00 189 * 2268 $ 1.99 $ 10 2200 89.55 $ D. 1,074.60 $ 3.50 $ 157.50 Donuts Raspberry Filled donut 45 189 2268 $ 1.00 1.00 $ 20 1.5 45.00 $ 2007 540.00 $ Donuts Glazed Bowtie 1.75 $ . 78.75 44 12 2217.6 $ 0.75 . 184.8 ** -17.00.13? 0.75 $ 33.00 $ 2.00 396.00 $ 1.25$ 55.00 Donuts Strawberry Glaze Sprinkl 33 138.6 1663.2 $ 1.00 $ de 1.2 33.00 $ 396.00 $ 82.50 2.50 $ Donuts Birthday Cake Donut 20.00 44 184.8) 2217.6 $ 1.50 $ 66.00 $ 792.00 $ 2.75 $ 121.00 Donuts Chocolate Chunk Donut 15 63 756 $ 1.50 $ 22.50 $ 270.00 $ 2.75 2.75 $ 41.25 2019 Muffins Blueberry Muffins 120 125 525 525 $ 1.75 $ 210.70 218.75 $ 2,625.00 $ 2.50 $ 42 312.50 Muffins Strawberry Muffins 294 2529 3528 $ 1.75 1.75 $ 122.50 $ 1,470.00 $ 250 2.50 $ 175 175.00 Muffins Poppy Seed Muffins 75 215 315 3780 $ 1.75 3780 75 1.75 $ $ 1,575.00 $ $ 250 2.50 > Muffins Carrot Cake Muffins 187.50 33 $ > 74.25 $ 891.00 $ 3.75 $ 122 75 123.75 Muffins Banana Nut Muffins 40 168 2016 225 2016 $ 2.25 $ Bagels 2.25 $ 90.00 $ 1,080.00 $ 3.75 $ 150.00 Bagels Poppy Seed Bagel 55 231 2772 $ 1.50 $ 82.50 $ 990.00 $ 3.50 $ 192.50 Bagels Onion Bagel 23 96.6 96.6 1159.2 $ 1.50 $ $ $ 34.50 $ 414.00 $ 3.50 $ 414.00 Bagels 3.50 $ 80.50 All IN Bagel 3880.8 $ $ 1.50 $ 115.50 $ 1.386.00 $ 3.50 $ 269.50 Bagels Blueberry Bagel 50 210 2520 $ 1.50 2520 $ 1.50 419 1.50 $ 75.00 $ 900.00 $ 3.50 $ $ 175.00 Bagels 3.30 Cinnamon Raisin Bagel 33 35 138.6 1663.2 $ 1.50 $ 49.50 $ 594.00 $ 3.50 $ 115.50 Donuts Raspberry Filled donut 45 189 2268 $ 0.75 * 40 0.75 $ 2200 0.1 33.75 $ 405.00 $ . ? Donuts Glazed Bowtie 1.75 $ 78.75 10:13 9.00 44 184.8 2217.6 $ 0.75 0.75 $ 0.rs ? 33.00 $ 396.00 $ 1.25 $ 55.00 Donuts Strawberry Glaze Sprinkl 33 138.6 1663.2 $ 0.75 $ 24.75 $ 297.00 $ 2.50 $ 82.50 Donuts Birthday Cake Donut 50 210 2520 $ 1.25 $ 62.50 $ 750.00 $ 2.75 $ 137.50 Donuts Chocolate Chunk Donut 20 84' 1008 $ 1.25 1.25 $ 25.00 $ 300.00 $ 2.75 $ 55.00 3880.8 $ 1.99 Yearly Sales $ 16,380.00 $ 7,560.00 $ 6,804.00 $ 9,450.00 * $ 23,625.00 * $ 15,523.20 20:40 $ 4,057.26 * $ 44,100.00 www.ca $ 21,168.00 wa $ 14,112.00 Daca $ 3,969.0c . $ 3,780.00 * $ 2460 8,316.00 $ 200 13,305.60 s 001 $ 6,804.00 $ 4,500.00 500 or > $ 1,500.00 $ 660 660.00 $ > 1,485.00 $ 3.600.00 > $ 1.680.00 1,680.00 $ $ 966.00 7 $ 3,234.00 3.234.00 $ 1,344.00 $ 1,890.00 4,030.04 $ $ 945.00 $ 660.00 des $ $ 990.00 $ $ 1,452.00 s $ As of 495.00 ZER $ 3,750.00 $ 100 2,100.00 $ $ 2,250.00 > $ 1,485.00 $ > 1 800 or 1,800.00 $ > 2.310.00 2,310.00 $ 2 966.00 $ $ 2,100.00 $ 1.386.00 00.00 $ $ 945.00 29. $ 660.00 $ 990.00 $ $ 1,650.00 $ 660.00 6300 S 170 2 70 2 131 25 138 6 1663.2 $ 2.25 77 323.4 3,234.00