Question
I need help for a school project that requires me to create and analyze 3 separate pivot tables using particular data. Analysis/Pivot Tables 1. Use
I need help for a school project that requires me to create and analyze 3 separate pivot tables using particular data.
Analysis/Pivot Tables 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)
| Year | Category | Item | Units weekly | Units per Month | Yearly Units | Unit Cost | Total Cost | Sale Price | Sale Total |
| 2020 | Muffins | Blueberry Muffins | 65 | 273 | 3276 | $ 1.75 | $ 477.75 | $ 2.50 | $ 682.50 |
| Muffins | Strawberry Muffins | 30 | 126 | 1512 | $ 1.75 | $ 220.50 | $ 2.50 | $ 315.00 | |
| Muffins | Poppy Seed Muffins | 18 | 75.6 | 907.2 | $ 1.75 | $ 132.30 | $ 2.50 | $ 189.00 | |
| Muffins | Carrot Cake Muffins | 20 | 84 | 1008 | $ 2.25 | $ 189.00 | $ 3.75 | $ 315.00 | |
| Muffins | Banana Nut Muffins | 44 | 184.8 | 2217.6 | $ 2.25 | $ 415.80 | $ 3.75 | $ 693.00 | |
| Bagels | Poppy Seed Bagel | 44 | 184.8 | 2217.6 | $ 1.99 | $ 367.75 | $ 3.50 | $ 646.80 | |
| Bagels | Onion Bagel | 23 | 96.6 | 1159.2 | $ 1.99 | $ 192.23 | $ 3.50 | $ 338.10 | |
| Bagels | All IN Bagel | 60 | 252 | 3024 | $ 1.99 | $ 501.48 | $ 3.50 | $ 882.00 | |
| Bagels | Blueberry Bagel | 32 | 134.4 | 1612.8 | $ 1.99 | $ 267.46 | $ 3.50 | $ 470.40 | |
| Bagels | Cinnamon Raisin Bagel | 45 | 189 | 2268 | $ 1.99 | $ 376.11 | $ 3.50 | $ 661.50 | |
| Donuts | Raspberry Filled donut | 45 | 189 | 2268 | $ 1.00 | $ 189.00 | $ 1.75 | $ 330.75 | |
| Donuts | Glazed Bowtie | 29 | 121.8 | 1461.6 | $ 0.75 | $ 91.35 | $ 1.25 | $ 152.25 | |
| Donuts | Strawberry Glaze Sprinkle | 33 | 138.6 | 1663.2 | $ 1.00 | $ 138.60 | $ 2.50 | $ 346.50 | |
| Donuts | Birthday Cake Donut | 35 | 147 | 1764 | $ 1.50 | $ 220.50 | $ 3.00 | $ 441.00 | |
| Donuts | Chocolate Chunk Donut | 15 | 63 | 756 | $ 1.50 | $ 94.50 | $ 3.00 | $ 189.00 | |
| 2019 | Muffins | Blueberry Muffins | 88 | 369.6 | 4435.2 | $ 1.75 | $ 154.00 | $ 2.50 | $ 220.00 |
| Muffins | Strawberry Muffins | 50 | 210 | 2520 | $ 1.75 | $ 87.50 | $ 2.50 | $ 125.00 | |
| Muffins | Poppy Seed Muffins | 22 | 92.4 | 1108.8 | $ 1.75 | $ 38.50 | $ 2.50 | $ 55.00 | |
| Muffins | Carrot Cake Muffins | 33 | 138.6 | 1663.2 | $ 2.25 | $ 74.25 | $ 3.75 | $ 123.75 | |
| Muffins | Banana Nut Muffins | 40 | 168 | 2016 | $ 2.25 | $ 90.00 | $ 3.75 | $ 150.00 | |
| Bagels | Poppy Seed Bagel | 40 | 168 | 2016 | $ 1.99 | $ 79.60 | $ 3.50 | $ 140.00 | |
| Bagels | Onion Bagel | 23 | 96.6 | 1159.2 | $ 1.99 | $ 45.77 | $ 3.50 | $ 80.50 | |
| Bagels | All IN Bagel | 77 | 323.4 | 3880.8 | $ 1.99 | $ 153.23 | $ 3.50 | $ 269.50 | |
| Bagels | Blueberry Bagel | 32 | 134.4 | 1612.8 | $ 1.99 | $ 63.68 | $ 3.50 | $ 112.00 | |
| Bagels | Cinnamon Raisin Bagel | 45 | 189 | 2268 | $ 1.99 | $ 89.55 | $ 3.50 | $ 157.50 | |
| Donuts | Raspberry Filled donut | 45 | 189 | 2268 | $ 1.00 | $ 45.00 | $ 1.75 | $ 78.75 | |
| Donuts | Glazed Bowtie | 44 | 184.8 | 2217.6 | $ 0.75 | $ 33.00 | $ 1.25 | $ 55.00 | |
| Donuts | Strawberry Glaze Sprinkle | 33 | 138.6 | 1663.2 | $ 1.00 | $ 33.00 | $ 2.50 | $ 82.50 | |
| Donuts | Birthday Cake Donut | 44 | 184.8 | 2217.6 | $ 1.50 | $ 66.00 | $ 2.75 | $ 121.00 | |
| Donuts | Chocolate Chunk Donut | 15 | 63 | 756 | $ 1.50 | $ 22.50 | $ 2.75 | $ 41.25 | |
| 2018 | Muffins | Blueberry Muffins | 125 | 525 | 6300 | $ 1.75 | $ 218.75 | $ 2.50 | $ 312.50 |
| Muffins | Strawberry Muffins | 70 | 294 | 3528 | $ 1.75 | $ 122.50 | $ 2.50 | $ 175.00 | |
| Muffins | Poppy Seed Muffins | 75 | 315 | 3780 | $ 1.75 | $ 131.25 | $ 2.50 | $ 187.50 | |
| Muffins | Carrot Cake Muffins | 33 | 138.6 | 1663.2 | $ 2.25 | $ 74.25 | $ 3.75 | $ 123.75 | |
| Muffins | Banana Nut Muffins | 40 | 168 | 2016 | $ 2.25 | $ 90.00 | $ 3.75 | $ 150.00 | |
| Bagels | Poppy Seed Bagel | 55 | 231 | 2772 | $ 1.50 | $ 82.50 | $ 3.50 | $ 192.50 | |
| Bagels | Onion Bagel | 23 | 96.6 | 1159.2 | $ 1.50 | $ 34.50 | $ 3.50 | $ 80.50 | |
| Bagels | All IN Bagel | 77 | 323.4 | 3880.8 | $ 1.50 | $ 115.50 | $ 3.50 | $ 269.50 | |
| Bagels | Blueberry Bagel | 32 | 134.4 | 1612.8 | $ 1.50 | $ 48.00 | $ 3.50 | $ 112.00 | |
| Bagels | Cinnamon Raisin Bagel | 33 | 138.6 | 1663.2 | $ 1.50 | $ 49.50 | $ 3.50 | $ 115.50 | |
| Donuts | Raspberry Filled donut | 45 | 189 | 2268 | $ 0.75 | $ 33.75 | $ 1.75 | $ 78.75 | |
| Donuts | Glazed Bowtie | 44 | 184.8 | 2217.6 | $ 0.75 | $ 33.00 | $ 1.25 | $ 55.00 | |
| Donuts | Strawberry Glaze Sprinkle | 33 | 138.6 | 1663.2 | $ 0.75 | $ 24.75 | $ 2.50 | $ 82.50 | |
| Donuts | Birthday Cake Donut | 22 | 92.4 | 1108.8 | $ 1.25 | $ 27.50 | $ 2.75 | $ 60.50 | |
| Donuts | Chocolate Chunk Donut | 20 | 84 | 1008 | $ 1.25 | $ 25.00 | $ 2.75 | $ 55.00 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Intermediate Accounting
Authors: J. David Spiceland, James Sepe, Mark Nelson
6th edition
978-0077328894, 71313974, 9780077395810, 77328892, 9780071313971, 77395816, 978-0077400163
