Michelle has three stores named Michelles Teas which sell different flavors of teas. Michelle has been keeping
Question:
Michelle has three stores named Michelle’s Teas which sell different flavors of teas. Michelle has been keeping the sales figures manually. She has decided that a spreadsheet application can help her keep better tabs on sales and allow her to analyze how well the stores are selling each tea.
1. Michelle needs a worksheet for each store. Name the worksheets Store1. Store 2 and Store 3. Michelle needs to know the annual sales, cost of goods sold, annual gross profit, profit margin, and markup percentage for each item for each store.
2. Michelle wants to know the sum, average, maximum, and minimum for annual sales, cost of goods sold, and annual gross profit for each store.
3. Michelle wants the worksheet to flag any item that has a markup of 25 or less so she can decide if the price needs readjustment for each store. The formula for Markup is =(Price-Cost)/Cost
4. Michelle wants to identify the top two best-selling items for each store.
5. Michelle wants to see a pie chart showing the percentages each tea makes up of the gross profit margin for each store.
6. Michelle wants a line chart comparing the annual sales of the teas for each store.
7. Michelle needs a bar chart that compares Cost of Goods Sold with Annual Sales for each store.
8. Michelle wants a consolidated worksheet that calculates the total number of units sold for each tea, total sales for each tea, the total cost of goods for each tea, the total profit for each tea, and the average profit margin for each tea. The worksheet should highlight the two bestselling teas.
9. Michelle wants a bar chart comparing total sales to total costs for each tea.
10. Create a PivotTable, PivotChart, and slicer by tea name for the consolidated worksheet.
11. Create a dashboard that shows the KPI (Key Performance Indicators for each store) and the business.
12. Michelle wants a menu system that will allow her to move to any of the worksheets in the workbook.
Product | Price | Cost |
Green Breaker | 2.40 | 1.20 |
Grizzly Berry | 3.00 | 2.00 |
Memphis Heat | 3.50 | 2.25 |
Purple Hills | 4.00 | 2.75 |
Blueberry Spaceship | 2.00 | 0.50 |
Shinning Ocean | 5.00 | 4.00 |
Store 1
Product - Units Sold
Green Breaker - 3000
Grizzly Berry - 2000
Memphis Heat - 1000
Purple Hills - 200
Blueberry Spaceship - 4000
Shining Oceans – 500
Store 2
Product - Units Sold
Green Breaker - 3575
Grizzly Berry - 1181
Memphis Heat - 149
Purple Hills - 1862
Blueberry Spaceship - 915
Shining Oceans – 1176
Store 3
Product - Units Sold
Green Breaker - 1795
Grizzly Berry - 3128
Memphis Heat - 2364
Purple Hills - 3086
Blueberry Spaceship - 3791
Shining Oceans – 1891
South-Western Federal Taxation 2020 Comprehensive
ISBN: 9780357109144
43rd Edition
Authors: David M. Maloney, William A. Raabe, James C. Young, Annette Nellen, William H. Hoffman