Question

1 Approved Answer

Posted on Jul 18, 2024

K A B D 1 Year Category Item Units weekly 2. 2020 Muffins Blueberry Muffins 65 3 Muffins Strawberry Muffins 30 Muffins Poppy Seed Muffins

image text in transcribed

K A B D 1 Year Category Item Units weekly 2. 2020 Muffins Blueberry Muffins 65 3 Muffins Strawberry Muffins 30 Muffins Poppy Seed Muffins 18 5 Muffins Carrot Cake Muffins 20 6 Muffins Banana Nut Muffins 44 7 Bagels Poppy Seed Bagel 44 8 Bagels Onion Bagel 23 9 Bagels All IN Bagel 60 10 Bagels Blueberry Bagel 32 11 Bagels Cinnamon Raisin Bagel 45 12 Donuts Raspberry Filled donut 45 13 Donuts Glazed Bowtie 29 14 Donuts Strawberry Glaze Sprinkle 33 15 Donuts Birthday Cake Donut 35 16 Donuts Chocolate Chunk Donut 15 17 2019 Muffins Blueberry Muffins 88 18 Muffins Strawberry Muffins 50 19 Muffins Poppy Seed Muffins 22 20 Muffins Carrot Cake Muffins 33 21 Muffins Banana Nut Muffins 40 22 Bagels Poppy Seed Bagel 40 23 Bagels Onion Bagel 23 24 Bagels All IN Bagel 77 25 Bagels Blueberry Bagel 32 26 Bagels Cinnamon Raisin Bagel 45 27 Donuts Raspberry Filled donut 45 28 Donuts Glazed Bowtie 44 29 Donuts Strawberry Glaze Sprinkle 33 30 Donuts Birthday Cake Donut 44 31 Donuts Chocolate Chunk Donut 15 32 2018 Muffins Blueberry Muffins 125 33 Muffins Strawberry Muffins 70 34 Muffins Poppy Seed Muffins 75 35 Muffins Carrot Cake Muffins 33 36 Muffins Banana Nut Muffins 40 37 Poppy Seed Bagel 55 38 Bagels Onion Bagel 23 39 Bagels All IN Bagel 77 40 Bagels Blueberry Bagel 32 41 Bagels Cinnamon Raisin Bagel 33 42 Donuts Raspberry Filled donut 45 43 Donuts Glazed Bowtie 44 44 Donuts Strawberry Glaze Sprinkle 33 45 Donuts Birthday Cake Donut 22 46 Donuts Chocolate Chunk Donut 20 47 48 E F G H J Units per Month Yearly Units Unit Cost Total Cost Sale Price Sale Total 273 3276 $ 1.75 $ 477.75 $ 2.50 $ 682.50 126 1512 $ 1.75 $ 220.50 $ 2.50 $ 315.00 75.6 907.2 $ 1.75 $ 132.30 $ 2.50 $ 189.00 84 1008 $ 2.25 $ 189.00 $ 3.75 $ 315.00 184.8 2217.6 $ 2.25 $ 415.80 $ 3.75 $ 693.00 184.8 2217.6 $ 1.99 $ 367.75 $ 3.50 $ 646.80 96.6 1159.2 $ 1.99 $ 192.23 $ 3.50 $ 338.10 252 3024 $ 1.99 $ 501.48 $ 3.50 $ 882.00 134.4 1612.8 $ 1.99 $ 267.46 $ 3.50 $ 470.40 189 2268 $ 1.99 $ 376.11 $ 3.50 $ 661.50 189 2268 $ 1.00 $ 189.00 $ 1.75 $ 330.75 121.8 1461.6 $ 0.75 $ 91.35 $ 1.25 $ 152.25 138.6 1663.2 $ 1.00 $ 138.60 $ 2.50 $ 346.50 147 1764 $ 1.50 $ 220.50 $ 3.00 $ 441.00 63 756 $ 1.50 $ 94.50 $ 3.00 $ 189.00 369.6 4435.2 $ 1.75 $ 154.00 $ 2.50 $ 220.00 210 2520 $ 1.75 $ 87.50 $ 2.50 $ 125.00 92.4 1108.8 $ 1.75 $ 38.50 $ 2.50 $ 55.00 138.6 1663.2 $ 2.25 $ 74.25 $ 3.75 $ 123.75 168 2016 $ 2.25 $ 90.00 $ 3.75 $ 150.00 168 2016 $ 1.99 $ 79.60 $ 3.50 $ 140.00 96.6 1159.2 $ 1.99 $ 45.77 $ 3.50 $ 80.50 323.4 3880.8 $ 1.99 $ 153.23 $ 3.50 $ 269.50 1612.8 $ 1.99 $ 63.68 $ 3.50 $ 112.00 189 2268 $ 1.99 $ 89.55 $ 3.50 $ 157.50 189 2268 $ 1.00 $ 45.00 $ 1.75 $ 78.75 184.8 2217.6 $ 0.75 $ 33.00 $ 1.25 $ 55.00 138.6 1663.2 $ 1.00 $ 33.00 $ 2.50 $ 82.50 184.8 2217.6 $ 1.50 $ 66.00 $ 2.75 $ 121.00 63 756 $ 1.50 $ 22.50 $ 2.75 $ 41.25 525 6300 $ 1.75 $ 218.75 $ 2.50 $ 312.50 294 3528 $ 1.75 $ 122.50 $ 2.50 $ 175.00 315 3780 $ 1.75 $ 131.25 $ 2.50 $ 187.50 138.6 1663.2 $ 2.25 $ 74.25 $ 3.75 $ 123.75 168 2016 $ 2.25 $ 90.00 $ 3.75 $ 150.00 231 2772 $ 1.50 $ 82.50 $ 3.50 $ 192.50 96.6 1159.2 $ 1.50 $ 34.50 $ 3.50 $ 80.50 323.4 3880.8 $ 1.50 $ 115.50 $ 3.50 $ 269.50 134.4 1612.8 $ 1.50 $ 48.00 $ 3.50 $ 112.00 138.6 1663.2 $ 1.50 $ 49.50 $ 3.50 $ 115.50 189 2268 $ 0.75 $ 33.75 $ 1.75 $ 78.75 184.8 2217.6 $ 0.75 $ 33.00 $ 1.25 S 55.00 138.6 1663.2 $ 0.75 $ 24.75 $ 2.50 $ 82.50 92.4 1108.8 $ 1.25 $ 27.50 $ 2.75 $ 60.50 84 1008 $ 1.25 $ 25.00 $ 2.75 $ 55.00 134.4 Bagels Content: What to do: Analysis/Pivot Tables 1. la. Use the attached Excel data file to analyze and create the appropriate pivot tables needed to provide the appropriate information for the following: 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) Which products are our best based on total sales per year? Which products are our most profitable per year? (Hint: This requires a calculation for each sale) Analyze and create a pivot table for each of the scenarios. Name file yournamebakeitsales.xlsx. 2. . K A B D 1 Year Category Item Units weekly 2. 2020 Muffins Blueberry Muffins 65 3 Muffins Strawberry Muffins 30 Muffins Poppy Seed Muffins 18 5 Muffins Carrot Cake Muffins 20 6 Muffins Banana Nut Muffins 44 7 Bagels Poppy Seed Bagel 44 8 Bagels Onion Bagel 23 9 Bagels All IN Bagel 60 10 Bagels Blueberry Bagel 32 11 Bagels Cinnamon Raisin Bagel 45 12 Donuts Raspberry Filled donut 45 13 Donuts Glazed Bowtie 29 14 Donuts Strawberry Glaze Sprinkle 33 15 Donuts Birthday Cake Donut 35 16 Donuts Chocolate Chunk Donut 15 17 2019 Muffins Blueberry Muffins 88 18 Muffins Strawberry Muffins 50 19 Muffins Poppy Seed Muffins 22 20 Muffins Carrot Cake Muffins 33 21 Muffins Banana Nut Muffins 40 22 Bagels Poppy Seed Bagel 40 23 Bagels Onion Bagel 23 24 Bagels All IN Bagel 77 25 Bagels Blueberry Bagel 32 26 Bagels Cinnamon Raisin Bagel 45 27 Donuts Raspberry Filled donut 45 28 Donuts Glazed Bowtie 44 29 Donuts Strawberry Glaze Sprinkle 33 30 Donuts Birthday Cake Donut 44 31 Donuts Chocolate Chunk Donut 15 32 2018 Muffins Blueberry Muffins 125 33 Muffins Strawberry Muffins 70 34 Muffins Poppy Seed Muffins 75 35 Muffins Carrot Cake Muffins 33 36 Muffins Banana Nut Muffins 40 37 Poppy Seed Bagel 55 38 Bagels Onion Bagel 23 39 Bagels All IN Bagel 77 40 Bagels Blueberry Bagel 32 41 Bagels Cinnamon Raisin Bagel 33 42 Donuts Raspberry Filled donut 45 43 Donuts Glazed Bowtie 44 44 Donuts Strawberry Glaze Sprinkle 33 45 Donuts Birthday Cake Donut 22 46 Donuts Chocolate Chunk Donut 20 47 48 E F G H J Units per Month Yearly Units Unit Cost Total Cost Sale Price Sale Total 273 3276 $ 1.75 $ 477.75 $ 2.50 $ 682.50 126 1512 $ 1.75 $ 220.50 $ 2.50 $ 315.00 75.6 907.2 $ 1.75 $ 132.30 $ 2.50 $ 189.00 84 1008 $ 2.25 $ 189.00 $ 3.75 $ 315.00 184.8 2217.6 $ 2.25 $ 415.80 $ 3.75 $ 693.00 184.8 2217.6 $ 1.99 $ 367.75 $ 3.50 $ 646.80 96.6 1159.2 $ 1.99 $ 192.23 $ 3.50 $ 338.10 252 3024 $ 1.99 $ 501.48 $ 3.50 $ 882.00 134.4 1612.8 $ 1.99 $ 267.46 $ 3.50 $ 470.40 189 2268 $ 1.99 $ 376.11 $ 3.50 $ 661.50 189 2268 $ 1.00 $ 189.00 $ 1.75 $ 330.75 121.8 1461.6 $ 0.75 $ 91.35 $ 1.25 $ 152.25 138.6 1663.2 $ 1.00 $ 138.60 $ 2.50 $ 346.50 147 1764 $ 1.50 $ 220.50 $ 3.00 $ 441.00 63 756 $ 1.50 $ 94.50 $ 3.00 $ 189.00 369.6 4435.2 $ 1.75 $ 154.00 $ 2.50 $ 220.00 210 2520 $ 1.75 $ 87.50 $ 2.50 $ 125.00 92.4 1108.8 $ 1.75 $ 38.50 $ 2.50 $ 55.00 138.6 1663.2 $ 2.25 $ 74.25 $ 3.75 $ 123.75 168 2016 $ 2.25 $ 90.00 $ 3.75 $ 150.00 168 2016 $ 1.99 $ 79.60 $ 3.50 $ 140.00 96.6 1159.2 $ 1.99 $ 45.77 $ 3.50 $ 80.50 323.4 3880.8 $ 1.99 $ 153.23 $ 3.50 $ 269.50 1612.8 $ 1.99 $ 63.68 $ 3.50 $ 112.00 189 2268 $ 1.99 $ 89.55 $ 3.50 $ 157.50 189 2268 $ 1.00 $ 45.00 $ 1.75 $ 78.75 184.8 2217.6 $ 0.75 $ 33.00 $ 1.25 $ 55.00 138.6 1663.2 $ 1.00 $ 33.00 $ 2.50 $ 82.50 184.8 2217.6 $ 1.50 $ 66.00 $ 2.75 $ 121.00 63 756 $ 1.50 $ 22.50 $ 2.75 $ 41.25 525 6300 $ 1.75 $ 218.75 $ 2.50 $ 312.50 294 3528 $ 1.75 $ 122.50 $ 2.50 $ 175.00 315 3780 $ 1.75 $ 131.25 $ 2.50 $ 187.50 138.6 1663.2 $ 2.25 $ 74.25 $ 3.75 $ 123.75 168 2016 $ 2.25 $ 90.00 $ 3.75 $ 150.00 231 2772 $ 1.50 $ 82.50 $ 3.50 $ 192.50 96.6 1159.2 $ 1.50 $ 34.50 $ 3.50 $ 80.50 323.4 3880.8 $ 1.50 $ 115.50 $ 3.50 $ 269.50 134.4 1612.8 $ 1.50 $ 48.00 $ 3.50 $ 112.00 138.6 1663.2 $ 1.50 $ 49.50 $ 3.50 $ 115.50 189 2268 $ 0.75 $ 33.75 $ 1.75 $ 78.75 184.8 2217.6 $ 0.75 $ 33.00 $ 1.25 S 55.00 138.6 1663.2 $ 0.75 $ 24.75 $ 2.50 $ 82.50 92.4 1108.8 $ 1.25 $ 27.50 $ 2.75 $ 60.50 84 1008 $ 1.25 $ 25.00 $ 2.75 $ 55.00 134.4 Bagels Content: What to do: Analysis/Pivot Tables 1. la. Use the attached Excel data file to analyze and create the appropriate pivot tables needed to provide the appropriate information for the following: 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) Which products are our best based on total sales per year? Which products are our most profitable per year? (Hint: This requires a calculation for each sale) Analyze and create a pivot table for each of the scenarios. Name file yournamebakeitsales.xlsx. 2