do it less the 1 hour
excel_assn_data_2_.xlsx
excel_assn_quiz_info_2_.docx
Unformatted Attachment Preview
First Choice Travel – Always the Best!
Sales Agent Commission Report
Close Rate Data
Sales agent
Total value oftravel
Number
bookings
ofcruises
Commission
booked
Number of cruises
Close
booked
Rate
Lopez, D.
72185
1
0 to 4
0.0175
El-Kerdi, S.
Fournier, W.
Bhayana, M.
Mennill, S.
Doxtator, V.
Redick, L.
Ashby, W.
De Papp, Q.
O’Donovan, R.
Karamek, F.
Postovic, W.
Bistany, W.
61475
31485
52684
23685
34875
22145
63895
57642
37478
61485
72665
52396
5
0
3
1
7
0
5
3
2
8
10
5
5 and over
0.0225
Total
Average commission
Maximum commission
Minimum commission
Microsoft Excel
1. Merge and Center the title Sales Agent Commission Report from A2:D2 and Close Rate Data
from F2:G2
2. Apply a bottom border below both of the above titles
3. Center align, and apply bold font style to the field headings A3:G3 below the titles
4. Apply the Wrap Text feature to cells, B3, C3, and F3
5. Apply a thick bottom border to the field headings (A3:D3)
6. Apply the Accounting number format to B4:B16, and B18.
7. Apply the Accounting number format to D4:D16, and D18.
8. Center align C4:C16, and C18
9. Apply bold font formatting to the entire row named Total
10. Apply a Top and Double Bottom Border from B18:D18
11. Select all the cells in the Close Rate Data area of the worksheet (F2:G5) and apply the Thick Box
Border to the cells.
12. Apply bold style to all information (F4:G5) in Close Rate Data, center align the information as
well.
13. Change the Close Rate (G4:G5) from a decimal to a Percentage format
14. Use formulas to calculate each agent’s commission in D4:D16. Each agent earns 5% of the total
value of bookings.
15. Use formulas to calculate the totals for Total Value of Travel Bookings, Number of Cruises
Booked, and Commission using the SUM function, your totals should go in the Total line on the
worksheet (Row 18)
16. Calculate the average commission using the AVERAGE function, maximum commission using the
MAX function, and minimum commission using the MIN function in the cells adjacent to the
appropriate label. Apply the Accounting number format to the cells.
17. Apply coloring to the worksheet by choosing a fill color for the Totals in row 18 and a another
different fill color for the titles in row 2.
18. Save file as Excel Quiz (your name) and submit on blackboard.
…
Purchase answer to see full
attachment
Recent Comments