Question
In the following problems, you will import and analyze data from the Foodmart.accdb file. This is an Access database file with five related tables: Customers,
In the following problems, you will import and analyze data from the Foodmart.accdb file. This is an Access database file with five related tables: Customers, Dates, Facts, Products, and Stores. The Facts table, with over 250,000 rows, has a row for each sale of a product during part of 1997 and 1998. Four of its fields are foreign keys relating the sale to the other four tables. Its other two fields, the facts, are Revenue and UnitsSold from the sale. C:\Users\19106\Downloads\Foodmart.mdb
1. Use Power Query to create a Data Model consisting of relevant fields from the Facts, Products, and Customers tables. The latter should include at least the Country and City fields. In the Power Pivot window, create a hierarchy called Country-City consisting of the Country and City fields. Then create a pivot table based on the Data Model to find the three cities that have generated the most revenue in the Beverages product department in their respective countries.
2. Use Power Query to create a Data Model consisting of relevant fields from the Facts, Products, Stores, and Dates tables. Then create two hierarchies, Product Structure and Store Location. The first should contain the fields ProductFamily, ProductDepartment, ProductCategory, and ProductSubcategory. The second should contain the fields Country, Region, StateProvince, and City. Use this to create a pivot table and accompanying pivot chart that breaks down total revenue by the two hierarchies, the first in the Rows area and the second in the Columns area. Also, include a slicer for the day of the week (Monday, Tuesday, etc.). Do whatever it takes to produce a pivot chart that shows total revenue on the weekends: PLEASE EXPLAIN EVERY SINGLE STEP
| ProductID | DateID | CustomerID | StoreID | Revenue | UnitsSold |
|---|---|---|---|---|---|
| 1318 | 483 | 3 | 15 | $2.79 | 3 |
| 1534 | 483 | 3 | 15 | $5.56 | 2 |
| 885 | 483 | 3 | 15 | $2.76 | 2 |
| 130 | 483 | 3 | 15 | $4.46 | 2 |
| 1252 | 483 | 3 | 15 | $6.84 | 3 |
| 176 | 483 | 3 | 15 | $6.81 | 3 |
| 286 | 483 | 3 | 15 | $4.26 | 3 |
| 1279 | 661 | 3 | 15 | $8.88 | 3 |
| 736 | 661 | 3 | 15 | $6.39 | 3 |
| 393 | 661 | 3 | 15 | $3.36 | 3 |
| DateID | TheDate | TheDay | TheMonth | TheYear | DayOfMonth | WeekOfYear | MonthOfYear | Quarter |
|---|---|---|---|---|---|---|---|---|
| 367 | 1/1/1997 | Wednesday | January | 1997 | 1 | 2 | 1 | Q1 |
| 368 | 1/2/1997 | Thursday | January | 1997 | 2 | 2 | 1 | Q1 |
| 369 | 1/3/1997 | Friday | January | 1997 | 3 | 2 | 1 | Q1 |
| 370 | 1/4/1997 | Saturday | January | 1997 | 4 | 2 | 1 | Q1 |
| 371 | 1/5/1997 | Sunday | January | 1997 | 5 | 3 | 1 | Q1 |
| 372 | 1/6/1997 | Monday | January | 1997 | 6 | 3 | 1 | Q1 |
| 373 | 1/7/1997 | Tuesday | January | 1997 | 7 | 3 | 1 | Q1 |
| 374 | 1/8/1997 | Wednesday | January | 1997 | 8 | 3 | 1 | Q1 |
| 375 | 1/9/1997 | Thursday | January | 1997 | 9 | 3 | 1 | Q1 |
| 376 | 1/10/1997 | Friday | January | 1997 | 10 | 3 | 1 | Q1 |
| ProductID | ProductName | BrandName | ProductSubcategory | ProductCategory | ProductDepartment | ProductFamily |
|---|---|---|---|---|---|---|
| 1 | Washington Berry Juice | Washington | Juice | Pure Juice Beverages | Beverages | Drink |
| 2 | Washington Mango Drink | Washington | Flavored Drinks | Drinks | Beverages | Drink |
| 3 | Washington Strawberry Drink | Washington | Flavored Drinks | Drinks | Beverages | Drink |
| 4 | Washington Cream Soda | Washington | Soda | Carbonated Beverages | Beverages | Drink |
| 5 | Washington Diet Soda | Washington | Soda | Carbonated Beverages | Beverages | Drink |
| 6 | Washington Cola | Washington | Soda | Carbonated Beverages | Beverages | Drink |
| 7 | Washington Diet Cola | Washington | Soda | Carbonated Beverages | Beverages | Drink |
| 8 | Washington Orange Juice | Washington | Juice | Pure Juice Beverages | Beverages | Drink |
| 9 | Washington Cranberry Juice | Washington | Juice | Pure Juice Beverages | Beverages | Drink |
| 10 | Washington Apple Juice | Washington | Juice | Pure Juice Beverages | Beverages | Drink |
| StoreID | Name | Address | City | StateProvince | PostalCode | Region | Country | Manager | Phone | Fax | TotalSqft | GrocerySqft | FrozenSqft | MeatSqft |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Store 1 | 2853 Bailey Rd | Acapulco | Guerrero | 55555 | Mexico West | Mexico | Jones | 262-555-5124 | 262-555-5121 | 23593 | 17474.9897485736 | 3670.80615085587 | 2447.20410057058 |
| 2 | Store 2 | 5203 Catanzaro Way | Bellingham | WA | 55555 | North West | USA | Smith | 605-555-8203 | 605-555-8201 | 28206 | 22271.1505749192 | 3560.90965504848 | 2373.93977003232 |
| 3 | Store 3 | 1501 Ramsey Circle | Bremerton | WA | 55555 | North West | USA | Davis | 509-555-1596 | 509-555-1591 | 39696 | 24389.9213919746 | 9183.64716481525 | 6122.43144321017 |
| 4 | Store 4 | 433 St George Dr | Camacho | Zacatecas | 55555 | Mexico Central | Mexico | Johnson | 304-555-1474 | 304-555-1471 | 23759 | 16844.4408361103 | 4148.73549833383 | 2765.82366555589 |
| 5 | Store 5 | 1250 Coggins Drive | Guadalajara | Jalisco | 55555 | Mexico West | Mexico | Green | 801-555-4324 | 801-555-4321 | 24597 | 15011.5121830164 | 5751.29269019015 | 3834.19512679343 |
| 6 | Store 6 | 5495 Mitchell Canyon Road | Beverly Hills | CA | 55555 | South West | USA | Maris | 958-555-5002 | 958-555-5001 | 23688 | 15336.7531698218 | 5010.74809810693 | 3340.49873207129 |
| 7 | Store 7 | 1077 Wharf Drive | Los Angeles | CA | 55555 | South West | USA | White | 477-555-7967 | 477-555-7961 | 23598 | 14210.3780255912 | 5632.57318464529 | 3755.04878976353 |
| 8 | Store 8 | 3173 Buena Vista Ave | Merida | Yucatan | 55555 | Mexico South | Mexico | Williams | 797-555-3417 | 797-555-3411 | 30797 | 20141.2029634172 | 6393.47822194967 | 4262.31881463312 |
| 9 | Store 9 | 1872 El Pintado Road | Mexico City | DF | 55555 | Mexico Central | Mexico | Stuber | 439-555-3524 | 439-555-3521 | 36509 | 22450.0263025592 | 8435.38421846446 | 5623.58947897631 |
| 10 | Store 10 | 7894 Rotherham Dr | Orizaba | Veracruz | 55555 | Mexico Central | Mexico | Merz | 212-555-4774 | 212-555-4771 | 34791 | 26354.0175160877 | 5062.18949034737 | 3374.79299356491 |
| CustomerID | LastName | FirstName | MiddleInitial | Address | City | StateProvince | PostalCode | Region | Country | Phone1 | Phone2 | Birthdate | MaritalStatus | AnnualIncome | Gender | TotalChildren | Occupation | Houseowner |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Nowmer | Sheri | A. | 2433 Bailey Road | Tlaxiaco | Oaxaca | 15057 | Mexico South | Mexico | 271-555-9715 | 119-555-1969 | 8/26/1961 | M | $30K - $50K | F | 4 | Skilled Manual | Y |
| 2 | Whelply | Derrick | I. | 2219 Dewing Avenue | Sooke | BC | 17172 | Canada West | Canada | 211-555-7669 | 807-555-9033 | 7/3/1915 | S | $70K - $90K | M | 1 | Professional | N |
| 3 | Derry | Jeanne | 7640 First Ave. | Issaquah | WA | 73980 | North West | USA | 656-555-2272 | 221-555-2493 | 6/21/1910 | M | $50K - $70K | F | 1 | Professional | Y | |
| 4 | Spence | Michael | J. | 337 Tosca Way | Burnaby | BC | 74674 | Canada West | Canada | 929-555-7279 | 272-555-2844 | 6/20/1969 | M | $10K - $30K | M | 4 | Skilled Manual | N |
| 5 | Gutierrez | Maya | 8668 Via Neruda | Novato | CA | 57355 | Central West | USA | 387-555-7172 | 260-555-6936 | 5/10/1951 | S | $30K - $50K | F | 3 | Manual | N | |
| 6 | Damstra | Robert | F. | 1619 Stillman Court | Lynnwood | WA | 90792 | North West | USA | 922-555-5465 | 333-555-5915 | 10/8/1942 | S | $70K - $90K | F | 3 | Professional | Y |
| 7 | Kanagaki | Rebecca | 2860 D Mt. Hood Circle | Tlaxiaco | Oaxaca | 13343 | Mexico South | Mexico | 515-555-6247 | 934-555-9211 | 3/27/1949 | M | $30K - $50K | F | 2 | Manual | Y | |
| 8 | Brunner | Kim | H. | 6064 Brodia Court | San Andres | DF | 12942 | Mexico Central | Mexico | 411-555-6825 | 130-555-6818 | 8/10/1922 | M | $50K - $70K | M | 2 | Professional | Y |
| 9 | Blumberg | Brenda | C. | 7560 Trees Drive | Richmond | BC | 17256 | Canada West | Canada | 815-555-3975 | 642-555-6483 | 6/23/1979 | M | $10K - $30K | M | 5 | Skilled Manual | Y |
| 10 | Stanz | Darren | M. | 1019 Kenwal Rd. | Lake Oswego | OR | 82017 | North West | USA | 847-555-5443 | 212-555-8635 | 8/26/1949 | S | $30K - $50K | M | 4 | Management | N |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered 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
Fundamental Accounting Principles
Authors: John Wild, Ken Shaw, Barbara Chiappett
23rd edition
1259536351, 978-1259536359
