Question 7 (15 points):

Data used: Inventory sheet

7a. What is the Total Sales for all books (ID contain word "book") for the whole table? (5 points)

7b. What is the Total Sales for all records that have sales less than 5,000 and inventory more than 650? (5 points)

7c. How many records that have Inventory less than 500 and belong to Asian or Africa markets? (5 points)

Time Period	ID	Market	Sales	Inventory
1	001_book_326c	Asian	3088	211
1	101_book_a009	Asian	8332	292
1	501_book_12	Asian	7537	443
1	001_book_acx	Asian	8825	831
1	001_book_a442	Asian	9063	272
1	book_acx	Europe	8053	586
1	501_shoes_12	Europe	6722	99
1	001_shoes_acx	Europe	7088	595
1	001_shoes_a442	Europe	369	575
1	shoes_acx	Africa	2481	741
1	shoes_acx23	Africa	2672	514
1	xz_shoes_acx	Africa	8287	537
1	111_shoes_acx24	Africa	2164	400
1	301_shoes_acx	Africa	652	785
1	001g_shoes_a442	Africa	4353	746
1	601_tables_326c	Asian	7431	894
1	101_tables_a009	Asian	4862	208
1	tables_acx23	Asian	5695	955
1	xz_tables_acx	Asian	4129	969
1	111_tables_acx24	Asian	8740	689
1	301_tables_acx	Europe	9037	778
1	001g_tables_a442	Europe	7669	430
2	001_book_326c	Europe	6707	562
2	001_book_acx	Europe	5615	727
2	001_book_a442	Africa	1027	322
2	book_acx	Africa	591	319
2	book_acx23	Africa	218	142
2	601_shoes_326c	Africa	8007	474
2	101_shoes_a009	Africa	3984	997
2	501_shoes_12	Africa	648	455
2	001_shoes_acx	Asian	7942	329
2	001_shoes_a442	Asian	5831	435
2	shoes_acx	Asian	6858	95
2	shoes_acx23	Asian	5602	783
2	xz_shoes_acx	Asian	523	58
2	111_shoes_acx24	Europe	7129	53
2	301_shoes_acx	Europe	8565	360
2	001g_shoes_a442	Europe	8673	968
2	601_tables_326c	Europe	142	177
2	101_tables_a009	Africa	8031	522
2	001_tables_a442	Africa	4994	660
2	tables_acx	Africa	1976	258
2	tables_acx23	Africa	3292	755
2	xz_tables_acx	Africa	5545	376
2	111_tables_acx24	Africa	1953	924
2	301_tables_acx	Asian	9887	457
2	001g_tables_a442	Asian	5632	416
3	001_book_326c	Asian	8853	658
3	101_book_a009	Asian	9728	759
3	501_book_12	Asian	8935	844
3	001_shoes_acx	Europe	2641	362
3	001_shoes_a442	Europe	4942	17
3	shoes_acx	Europe	6393	620
3	shoes_acx23	Europe	1675	959
3	xz_shoes_acx	Africa	706	883
3	111_shoes_acx24	Africa	1341	306
3	301_shoes_acx	Africa	5550	325
3	001g_shoes_a442	Africa	5892	338
3	601_tables_326c	Africa	1841	886
3	101_tables_a009	Africa	6448	46
3	501_tables_12	Asian	4994	58
3	001_tables_acx	Asian	791	390
3	001_tables_a442	Asian	3084	564
3	tables_acx	Asian	6825	524
3	tables_acx23	Asian	3890	971
3	xz_tables_acx	Europe	3454	954
3	111_tables_acx24	Europe	5825	280
3	301_tables_acx	Europe	3226	188
3	001g_tables_a442	Europe	6045	491
4	001_book_326c	Africa	7620	879
4	101_book_a009	Africa	6814	234
4	501_book_12	Africa	6788	995
4	001_book_acx	Africa	6297	101
4	001_book_a442	Africa	932	979
4	book_acx	Africa	9994	179
4	shoes_acx	Asian	8434	233
4	shoes_acx23	Asian	9992	769
4	xz_shoes_acx	Asian	4592	126
4	111_shoes_acx24	Asian	4415	331
4	301_shoes_acx	Asian	7459	955
4	001g_shoes_a442	Europe	8823	560
4	601_tables_326c	Europe	33	652
4	101_tables_a009	Europe	5835	952
4	501_tables_12	Europe	9111	814
4	001_tables_acx	Africa	9237	22
4	001_tables_a442	Africa	8350	937
4	tables_acx	Africa	6044	539
4	001g_tables_a442	Africa	9941	487

Question 8 (15 points):

Data used: Real Estate sheet

This table shows the purchased and sold price for a portfolio of properties

8a. Create a new column called Profit_Loss.

Profit_Loss = Sold Price Purchase Price

Then create a Level column

If a property results in a loss, then give value -15 (negative 15) in column Level.

If not, if a property results in a profit, give value 20 (positive 20) in column Level.

Find the sum of Level column over the whole portfolio. (5 points)

8b. Calculate the average Purchase Price for the whole portfolio. So you have only 1 average number.

Create a new column named Purchased Price Level.

If purchased price of a property is more than the average number above, then give Purchased Price Level value 40.

If not, give the Purchase Price Level value 60.

Report the sum of the Purchase Price Level for the whole portfolio. (5 points)

8c. Create a new column named Both Price Level.

If both purchased price and sold price of a property are greater than 550 then give Both Price Level value 2. If not give value 0.

Report the sum of this Price Level column. (5 points

Property ID	Purchase price (cost)	Sold Price (revenue)
1	364	669
2	766	384
3	288	492
4	555	397
5	582	280
6	644	928
7	662	555
8	175	250
9	58	80
10	572	669
11	847	526
12	757	210
13	377	254
14	900	666
15	648	932
16	298	66
17	203	243
18	737	566
19	258	333
20	872	500
21	263	737
22	608	189
23	322	384
24	521	279
25	306	203
26	260	295
27	560	613
28	329	493
29	508	915
30	471	316
31	418	398
32	872	520
33	133	222
34	747	701
35	219	500
36	514	178
37	996	860
38	189	627
39	991	442
40	420	458
41	576	560
42	871	724
43	176	114
44	532	703
45	659	317
46	957	783
47	894	470
48	594	243
49	962	792
50	885	407
51	653	500
52	598	202
53	564	674
54	495	172
55	202	438
56	388	333
57	475	115
58	316	714
59	206	402
60	900	585
61	901	590
62	316	560
63	560	865
64	350	244
65	483	758
66	50	41
67	520	450
68	479	200
69	570	770
70	762	681
71	560	918
72	179	67
73	531	434
74	333	284
75	480	482
76	538	384
77	758	725
78	603	660
79	561	605
80	654	696
81	858	666
82	672	635
83	976	967
84	124	315
85	624	213
86	315	522
87	975	950
88	723	923
89	374	685
90	888	830
91	286	284
92	361	666
93	226	111
94	973	475
95	299	434
96	909	888
97	38	63
98	888	845
99	747	676
100	530	528
101	436	802
102	919	460
103	345	590
104	666	476
105	698	336
106	772	1,113
107	794	666
108	210	300
109	69	96
110	686	802
111	1,016	631
112	908	252
113	452	304
114	1,080	799
115	777	1,118
116	357	79
117	243	291
118	884	679
119	309	399
120	1,046	600
121	315	884
122	729	226
123	386	460
124	625	334
125	367	243
126	312	354
127	672	735
128	394	591
129	609	1,098
130	565	379
131	501	477
132	1,046	624
133	159	266
134	896	841
135	262	600
136	616	213
137	1,195	1,032
138	226	752
139	1,189	530
140	504	549
141	691	672
142	1,045	868
143	211	136
144	638	843
145	790	380
146	1,148	939
147	1,072	564
148	712	291
149	1,154	950
150	1,062	488