If you have never used Excel (or barely use it):	Courses and Webinars to take Create your first Excel workbook Basic math in Excel
If you are familiar with Excel, these courses may give you a few new tools:	SUM and SUMIF functions Creating charts Freeze or lock panes Understand and use cell references AVERAGEIF and SUMPRODUCT functions Sort and filter data Conditional formatting VLOOKUP function Advanced IF functions

After taking the courses please complete the following assignment:

Assignment E_1. Download the base file here . This base file has two sheets, one for sales data (for three towns) and the other for commission rates per salesperson (for eight salespersons). Perform the activities described below.

1. Rename the sheet named Sheet1 to Commissions and Sheet2 to Sales.
2. Create a new sheet and copy the contents of the Sales sheet in it. Name this sheet Sales2.
3. Use the SUMIF function to determine the total sales of customers from each town in the Sales worksheet (do not sort the data).
4. Use the AVERAGEIF function to determine the average sales per year and per salesperson in the Sales worksheet (do not sort the data).
5. In the worksheet Sales2 sort the data by town, then per year and then per salesperson.
6. In the worksheet Sales2 create at least three graphs that provide some insight about sales. Include at least one line graph, one pie chart and one bar chart. Graphs should relate to sales per month, per salesperson, per town, percentage of sales per ...
7. Add a column to the worksheet Sales called Comission_rate. In this column implement a VLOOKUP function such that the commission rate from the salesperson in worksheet Commissions shows in the new column of worksheet Sales.
8. In the worksheet Sales use the sumproduct function to calculate all the commissions paid.
9. Add a column to the worksheet Sales called commission paid and calculate the actual commission paid (sales x commission rate).
10. In the worksheet Sales use the conditional formatting function to highlight commissions over $2,100.
11. Add a column to the worksheet Sales called high commissions. In this column create an If function that writes the amount paid when the commission is over $2,100 and leaves it blank otherwise. The format would be something similar to =IF(cell>2100,cell,"").

Name the file Lastname_E_1.

Satisfactory completion requirement: 8 of the 11 activities above must be correctly implemented in the worksheet.

Expected time to complete the unit: 3-5 hours.

 

 

---