A Excel Assignment 1 Questions 1 2 INSTRUCTIONS Sort the data in EA1 Data by Date [from oldest to newest] and then by Product [from

A\\nExcel Assignment 1 Questions\\n1\\n2\\nINSTRUCTIONS\\nSort the data in EA1 Data by Date [from oldest to newest] and then by Product [from PROD-001 to PROD-\\n3 200]. The data should be sorted by both simultaneously using Add Levels as we did in class.\\n4 2) Use SUMIFS to find and calculate the following. All number formatting should be in Currency.\\n2a) Find sales made to CUST-101 only.\\n6 2b) Find sales of PROD-055 during the months of April and May. (You may add both months together.)\\nUse Pivot Tables to calculate the following. Be sure to label all your Pivot Tables accordingly so I can see how\\n7 you arrived at each answer. All number formatting should be in Accounting.\\n8 3a) Find sales made in MSA-44 only.\\n9 3b) Find sales that SP-13 sold during, Quarter 1 (January, February, March) only.\\n3c) Find sales where PROD-091 was sold to CUST-126 only. Then divide by sales of all transactions. Your final 10 answer should be expressed as a percentage of all sales.\\nUsing IF, AND, or other logical formulas, calculate Commissions. In order to calculate it, you must first find Collections divided by Sales as a percentage for each transaction. There are three different calculations for Commissions depending on the Collections divided by Sales result. If Collection divided by Sales is greater than or equal to

70%

, Commissions is

3%\\\\times

Sales. If Collection divided by Sales is greater than or equal to

40%

but less\\n11 than

70%

, Commissions is

2%

x Sales. For all other transactions, Commissions is

$0

.\\n12 5) Set up the following spreadsheet security options:\\n5a) Protect the EA1 Data worksheet with the option to allow users to only select locked and unlocked cells.\\n13 Please use the password assignment1 (no upper-case letters).\\n5b) Protect your file so that a password must be entered to open and view the file. Please use the password assignment1 (no upper-case letters).

Show transcribed image text
 

1 2 INSTRUCTIONS Excel Assignment 1 Questions ANSWE 1) Sort the data in EA1 Data by Date [from oldest to newest] and then by Product [from PROD-001 to PROD- 3 200]. The data should be sorted by both simultaneously using Add Levels as we did in class. 4 2) Use SUMIFS to find and calculate the following. All number formatting should be in Currency. 5 2a) Find sales made to CUST-101 only. 6 2b) Find sales of PROD-055 during the months of April and May. (You may add both months together.) 3) Use Pivot Tables to calculate the following. Be sure to label all your Pivot Tables accordingly so I can see how 7 you arrived at each answer. All number formatting should be in Accounting. 8 3a) Find sales made in MSA-44 only. 9 3b) Find sales that SP-13 sold during Quarter 1 (January, February, March) only. 3c) Find sales where PROD-091 was sold to CUST-126 only. Then divide by sales of all transactions. Your final 10 answer should be expressed as a percentage of all sales. 4) Using IF, AND, or other logical formulas, calculate Commissions. In order to calculate it, you must first find Collections divided by Sales as a percentage for each transaction. There are three different calculations for Commissions depending on the Collections divided by Sales result. If Collection divided by Sales is greater than or equal to 70%, Commissions is 3% x Sales. If Collection divided by Sales is greater than or equal to 40% but less 11 than 70%, Commissions is 2% x Sales. For all other transactions, Commissions is $0. 12 5) Set up the following spreadsheet security options: 5a) Protect the EA1 Data worksheet with the option to allow users to only select locked and unlocked cells. 13 Please use the password assignment1 (no upper-case letters). 5b) Protect your file so that a password must be entered to open and view the file. Please use the password 14 assignment1 (no upper-case letters). 15 16 17