(Excel Add-Ins:Power Pivot = 20 Points)

Create Data models with the following spreadsheets:

Cust_ID	Cust_Name	Cust_City	Preferred Customer		Order_ID	Cust_ID	Item_Name	Item_Price	Final_Price
1	Bob	Boston	Y		10	6	Pants	$ 92.00
2	Francis	Albuquerque	Y		11	5	Hat	$ 66.00
3	Jim	Toledo	N		12	4	Hat	$ 37.00
4	Polly	Boston	N		13	2	Shirt	$ 21.00
5	Gene	Toledo	Y		14	2	Hat	$ 55.00
6	Juanita	Albuquerque	N		15	5	Pants	$ 47.00
					16	4	Pants	$ 21.00
					17	1	Pants	$ 95.00
					18	6	Hat	$ 45.00
					19	5	Shirt	$ 83.00
					20	2	Shirt	$ 38.00
					21	6	Hat	$ 44.00
					22	3	Gloves	$ 49.00
					23	2	Pants	$ 46.00
					24	6	Shirt	$ 62.00
					25	5	Pants	$ 81.00
					26	2	Pants	$ 32.00
					27	4	Shirt	$ 92.00
					28	5	Hat	$ 76.00
					29	5	Pants	$ 33.00
					30	3	Hat	$ 87.00
					31	3	Hat	$ 40.00
					32	4	Shirt	$ 74.00
					33	4	Hat	$ 95.00
					34	1	Hat	$ 39.00
					35	3	Gloves	$ 44.00
					36	2	Shirt	$ 20.00
					37	1	Gloves	$ 23.00
					38	1	Hat	$ 29.00

2. Using a Power Pivot, create a relationship between the spreadsheets using Cust_ID as the key.

3. Create a Pivot Table from the PowerPivot and do one of the following and paste the visualization to your exam document:

a. Create a 3D map using the Total spent by customer by city

b. Create a clustered bar chart of Total spent by customer

c. Create a total spent by each customer (where they get a 10% discount if they are a preferred customer). There are at least three good ways of annotating the discount.