Question

с D NOTES: No elaboration necessary. Please enter your answer here. B 1 Data Analytics Assignment 2 2 QUESTIONS: ANSWERS: 1) Add a new column for Commissions on the DAA2 Data worksheet. Note that commissions are only earned on sales transactions where the COGS/Sales % is greater than or equal to 50%. In that new column, calculate Commissions as 2% of sales for only those transactions (and $0 for all transactions that fail to 3 meet the threshold). 2) Use a formula to determine the number of transactions in the DAAZ Data worksheet where Product1 4 was sold. 3) Analyzing data from the DAA2 Data worksheet, use reference/lookup formulas to find the following information about the first sale by SalesRep6. Note that reference/lookup formulas will automatically pull 5 the first return value in the event that there are duplicates. 6 3a) In which region did this transaction take place? 7 3b) On what date this transaction occur? 8 3c) Which product was sold? 9 4) Analyzing the data from the DAA2 Data worksheet, use SUMIFS to calculate the following: 10 4a) Total Sales for sales to Cust16 11 4b) Total Sales for sales of Product2 in Region3 in August 2019 5) Use SUMIFS to populate the B2:E6 array in the cross-sectional table on the DAA2 Table worksheet with 12 total sales by region and product, analzying the data from the DAA2 Data worksheet. 13 6) Use Pivot Tables to answer the following questions: 14 6a) What is total sales made by SalesRep5 in Region1 during Quarter 2? Please enter your answers here. Please enter your answers here. No elaboration necessary. Please enter your answers here and also use separate worksheets entitled 6a and 6b respectively to show your work. 15 6b) What percentage of total sales of the entire year were sales of Product2 in Region4 during Quarter 3?

Answer 1

1)	Commission
	Commission	Region1	Region2	Region3	Region4	Grand Total
	Product 1	0	0	0	0	0
	Product 2	0	0	0	0	0
	Product 3	0	0	0	0	0
	Product 4	0	0	0	0	0
	Product 5	75.6	24.84	68.58	0	169.02
	Grand Total	75.6	24.84	68.58	0	169.02

2)	No of Transaction sold for product -1 is 11.COGS/Sales percentage of all transactions of product-1 is 48% so no need to caluculate Commision
	Date	Product	Region	Sales Rep	Customer	Sales	COGS
	1-1-19	Product 1	Region1	Sales Rep 2	Cust 23	529	253
	1-3-19	Product 1	Region1	Sales Rep 4	Cust 17	1495	715
	1-3-19	Product 1	Region3	Sales Rep 6	Cust 30	230	110
	1-3-19	Product 1	Region4	Sales Rep 3	Cust 29	1288	616
	1-4-19	Product 1	Region4	Sales Rep 2	Cust 33	989	473
	1-4-19	Product 1	Region4	Sales Rep 1	Cust 3	1150	550
	1-4-19	Product 1	Region2	Sales Rep 2	Cust 20	920	440
	1-7-19	Product 1	Region1	Sales Rep 1	Cust 10	1081	517
	1-7-19	Product 1	Region3	Sales Rep 5	Cust 23	483	231
	1-7-19	Product 1	Region4	Sales Rep 5	Cust 10	506	242
	1-12-19	Product 1	Region1	Sales Rep 3	Cust 8	552	264

3)	As per sales rep 6 First sale made as on 1-1-19 for sale 891
	Date	Product	Region	Sales Rep	Customer	Sales	COGS	COGS/Sales	Commission Rate	Commission-2% On sale
	1-1-19	Product 5	Region2	Sales Rep 6	Cust 31	891	478.5	54%	2%	17.82
	1-2-19	Product 2	Region3	Sales Rep 6	Cust 11	1364	620	45%	0%	0
	1-2-19	Product 3	Region4	Sales Rep 6	Cust 10	1134	499.5	44%	0%	0
	1-3-19	Product 1	Region3	Sales Rep 6	Cust 30	230	110	48%	0%	0
	1-7-19	Product 3	Region4	Sales Rep 6	Cust 14	819	360.75	44%	0%	0
	1-9-19	Product 3	Region1	Sales Rep 6	Cust 19	1134	499.5	44%	0%	0
	1-9-19	Product 2	Region2	Sales Rep 6	Cust 29	946	430	45%	0%	0
	1-10-19	Product 5	Region3	Sales Rep 6	Cust 19	297	159.5	54%	2%	5.94
	1-10-19	Product 2	Region4	Sales Rep 6	Cust 11	616	280	45%	0%	0

3a) In Region 2 transaction takes place
3b)The above transaction occurs as on 1-1-19
3c)Product was sold

4)	4a) Total sales for Customer 16=486
	Date	Product	Region	Sales Rep	Customer	Sales	COGS
	1-6-19	Product 5	Region1	Sales Rep 3	Cust 16	486	261
	4b) Total sales of product 2 in Region 3 in August 2019=858
	Date	Product	Region	Sales Rep	Customer	Sales	COGS	COGS/Sales
	1-8-19	Product 2	Region3	Sales Rep 3	Cust 18	858	390	45%

5)	Sales	Region1	Region2	Region3	Region4	Grand Total
	Product 1	529				529
	Product 2			1364		1364
	Product 3		1281		1134	2415
	Product 5		891			891
	Grand Total	529	2172	1364	1134	5199

6)	6a) No Sales made by sales Rep5 in Region 1
	6b) Total sales for Year =34139,Sales of Product 2 in region 4 for Q3=616
	Date	Product	Region	Sales Rep	Customer	Sales	COGS	COGS/Sales
	1-10-19	Product 2	Region4	Sales Rep 6	Cust 11	616	280	45%
	Percentage of sales of Product 2 in Region 4 for 3=Sales of product 2 in region 4 for Q3/Total sales for year
				=	616/34139
				=	1.80%

Total sales For year
Sales	Region1	Region2	Region3	Region4	Grand Total
Product 1	3657	920	713	3933	9223
Product 2	748	1628	2728	616	5720
Product 3	1134	3192	1638	3318	9282
Product 4	0	114	969	380	1463
Product 5	3780	1242	3429	0	8451
Grand Total	9319	7096	9477	8247	34139