Project Assignment: Scholarships
Documentation worksheet:
1. Add the relevant information as usual.
Switch to the Studentsworksheet: This data represents scholarship information for students in the Westside High School marching band. Complete the following requirements:
2. Create an Excel table named StudentData, in which the contents are sorted by Last Name, First Name. Choose a table style that uses light blue banded rows. Turn off filter arrows.
3. Use conditional formatting to highlight all GPA valuesthat are greater than 3.5with a light green fill with dark green text.
4. Add a new column to the table named Band Stipend. Calculate the band stipend with a formula by using the Basic_Stipend lookup table on the Reference worksheet to determine theBand Stipend value that each student will be given.
5. Students who are in the marching band for more than 1 year receive a loyalty bonus. Add a new column named Loyalty Bonus. Create a formula that will determine the dollar amount of the loyalty bonus the student will receive, using the lookup table named Loyalty on the Reference worksheet which contains the percentage to be applied to the student's band stipend amount.
6. Students who have been with the band more than 2 years may be eligible for an additional scholarship based on their GPA values. Add a new column named GPA Scholarship. Create a formula to determine the scholarship amount using the lookup table named GPA_Valueson the Reference worksheet to get the GPA Scholarship amount.
7. Add a new column named Total. Create a formula to calculate the total amount of money that the student will receive.
8. Add a Total row using Excel table features, and show the average values for Band Stipend, Loyalty Bonus, and GPA Scholarship. Show the sum of the Total column.
9. Format all money values with Accounting style.
10. Prepare the worksheet for printing. Use Landscape orientation and Narrow margins. All columns should show on each page, and the top 3 lines should repeat on each page. Add a page footer with your name in the left section and the date you completed this worksheet in the right section.
11. Create a new, empty pivottablebased on the StudentData table on a new worksheet. Name the worksheet Analysis.
Switch to the Analysis worksheet:
12. Configure the pivottable to show total paid to students by Gender and Class. Format all money values with Accounting style. Change the row and column headings to indicate what they reference in the pivottable.
13. Create a clustered columnpivotchart based on your pivottable.
14. Add a slicer for Class. Set it to only display only Freshman and Sophomore.
Switch to the Summary worksheet:
15. Complete the summary table, using conditional aggregate functions for rows 5 through 8. You must use table and column names.
16. In row 9 of the summary table, determine overall count of students, average total paid and total amount paidbased on years in band.(Hint: Do not average a set of averages - calculate the total average on the original data.)Format all money values in the Summary area with Accounting style
17. Create a 3-D pie chart showing Total Paid by Years in Band. Add an appropriate chart title. Show percentages in the data labels with 2 decimal places. Include a legend at the bottom. Explode theslice of the pie chart that has the highest percentage. Position the chart below the summary area.
18. Prepare this worksheet for printing. The summary area and the pie chart should show on a single page. Use Portrait orientation and Narrow margins. Add a page footer with your name in the left sectionand the date you completed this worksheet in the right section.
19. Arrange the worksheets in this order: Documentation, Students, Reference, Analysis, Summary.
20. Save your file, and close Excel. Submit your Excel file into Canvas.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Project-Scholarships-Student.rar