I need this assignment completed. I made it about half-way through and got stuck. Not sure if the work that was completed is even correct.
Analyzing Statistics Files Needed: Ch5Analysis_LastName.xlsx Lazarus Consulting is a large computer consulting company in New York. Pete, the CEO and founder, is well known for his philanthropic efforts. Pete knows that most of his employees contribute to nonprofit organizations and wants to reward them for their efforts, while encouraging others to also contribute to charities. The only stipulations are that the charity must be nonprofit and the company will match only up to $2,000 per year per employee, with an award for anyone whose donations are over $1000. Pete also wants to complete an analysis on the donations and organizations frequented by his employees. He has provided 1 tab for information and asks you to complete 4 more, for a total of 5 tabs in the order specified below: • Tracking Donations • Donations Survey • Donations Lists • Donations Subtotals • Donations Analysis Pete requests that you do not deviate from the order specified above so their analysis software will be able to read in the values as needed without additional work on their part. Note: All styles should be in shades of green and blue. Use this to check your selection. FOR MAC USERS: Chart filters may not be supported in Excel 2016 for Mac. The charts will display, but not the filters. Slicers are supported, so you might get around this problem using slicers. Example images: The example images are EXAMPLES ONLY!! You may or may not need the same number of rows, the images are for placement examples only. Follow the directions and include information as specified. Donations Survey Worksheet 1) Start Excel and open Ch5Analysis_LastName.xlsx. 2) Save the spreadsheet as Ch5Analysis_LastName .xlsx. (Replace “Last Name” with your last name.) 3) Create a copy of the Tracking Donations tab and name new tab Donations Survey 4) Convert the range A1:G97 to an Excel table. 5) Name the table DonationResults. 6) Apply Table Style shown below to the table. a. If you do not have the style, use a light green style AND make a note under the table with the style you used. No documentation, no points. 7) Sort the data in ascending order by three sort fields in the following order: Submitted, Organization, and Last Name. Lists Worksheet 1) Create a new tab and name it ‘Donations Lists’. 2) You will need 4 lists with headers. 3) If you do not have the styles/colors listed below, use a similar style AND make a note under the table with the style you used. No documentation, no points. 4) A header should look like this before color changes: 5) Formatting for main header: a. Bold, arial, 14. b. Merge and center over columns A – G c. Dark Green, Accent 3 Lighter 60% 6) Formatting information for the column headers: a. 20 width for the Organization, 12 width for the rest. b. Same names as seen above and bolded. c. Dark Blue, Text 2, lighter 60% 7) Filter the records in the Tracking Donations tab using the filter button shown above. Show only the Cancer Society organization and then copy the filtered list as follows: a. Take a screenshot of your filter criteria and save it. Make sure to crop it close. No images of your entire screen or overly large images with too much information will be accepted. Only the filter dialog popup. b. Note, your ribbon can look different depending upon your version of excel. c. Example image: (NOTE THIS CAN LOOK DIFFERENT DEPENDING UPON YOUR VERSION OF EXCEL!) d. Select all the rows in the filtered list, and then click the Copy button to copy the filtered records. (don’t copy the column headers, you already added them) e. Show the Lists worksheet. f. Click cell A4, and then click the Paste button. g. Add the screenshot to the spreadsheet starting at cell I:2 h. Example final layout: (Note: this is only an EXAMPLE of arranging your data.) 8) Repeat Step 7 to copy the following filtered lists to the Lists worksheet using the Filters. Line the screenshots up with the main header similar to the image above. 9) Leave at least 1 empty cell row between the bottom of the previous table and the next main header. Do this for all of the rest. 10) See image on next page as an EXAMPLE on how placement should look depending upon the number of results you have etc. a. The image shows what it looks like when your results are less than the size of the screenshot b. It also shows what it looks like when your results are more than the size of the screenshot. c. Keep this information in mind when completing this tab. Points will be deducted for improper formatting. When a client specifies a format, you are required to give them exactly what they want without taking liberties. They are the ones paying you for the job. Trust me, been there, done that, got the t-shirt. a. Donations submitted in November and December b. Donations with amounts more than $150 c. Donations whose last name is either Ash or Cross Donations Subtotals Worksheet 1) Clear all filters from the SurveyResults table in the Donations Survey tab. 2) Copy this tab and create a new tab named Donations Subtotals 3) Sort the data in ascending order by three sort fields in the following order: Submitted Date, Last Name, and First Name. 4) Use the Subtotal feature to sum all the values in the Amount column at each change in the Submitted Date column. See Subtotal Tutorial on the class website. https://edu.gcfglobal.org/en/excel2013/groups-and-subtotals/1/ 5) Click the Outline buttons to show only subtotals and the grand total. This will show only bold rows and all should be now. Donations Survey Worksheet Revisit 1) Clear all filters from the SurveyResults table in the Donations Survey tab. 2) Use the Slicer feature as follows: a. Create a Slicer from organization and position the top left corner of the slicer in cell H2. b. Change the height of the slicer to 2.4 and the width to 1. c. Apply Slicer Style Light 3 to the State slicer. If you do not have the slicer style Light 3, use a light green style AND make a note under the slicer with the style you used. No documentation, no points. d. Format the slicer’s size to the following, so that you can see all of the choices. e. Use the State slicer to show only the records for the Make a Wish Foundation and the Leukemia Society. You know you did this correctly if your table only shows records associated with those organizations. 3) Add a Total row to count the number of records at the bottom of the Donation Sent column. This should update every time you click a different organization on the slicer. Donations Analysis Worksheet 1) Clear all filters from the SurveyResults table in the Donations Survey tab. 2) In Donations Survey, create a PivotTable that will show the total (sum) amount of donations organized by Submitted and Organization. 3) Follow these steps: a. In the Create PivotTable dialog box, select New Worksheet (the default), and then change the name of the new worksheet to Donations Analysis. Move it to the end of the tabs so its in the correct order as specified above. b. In the PivotTable Fields pane, Organization should be in the columns; months and submitted in the rows, and sum of amount in values. 4) Apply the Pivot Style Medium 11 style to the PivotTable. (or use a style with shades of green if you do not have 11, make sure to document it below the table. No documentation, no points.) 5) Create a Slicer from the Month field so that only the months February, June, September, and December are shown. https://edu.gcfglobal.org/en/excel2013/groups-and-subtotals/1/ 6) Change the style of the Slicer to: Slicer Style Light 3, and then move the slicer to that its upper- left corner appears in cell J11. (or use a style with shades of green if you do not have 3, make sure to document it below the table. No documentation, no points.) 7) Create a PivotChart from the data in the filtered PivotTable: a. Select the Clustered Column chart type. b. Apply Chart Style 14 to the chart. (or use a style with shades of green if you do not have 14, make sure to document it below the table. No documentation, no points.) c. Under Design, use the quick layout option and select Layout 9. This will give you a title, and chart X and Y axis. Use “Donations” for the title, “Amount” for the Y axis title, and “Months” for the X axis title. d. Resize the chart as seen below: e. Position the PivotChart so its upper-left corner appears in cell A12. 8) Show the Survey worksheet again. 9) Save your file and submit it to your instructor. Grading Rubric Grade Item: Points Donations Survey Tab 22.5pts Donations Lists Tab 22.5pts Donations Subtotals Tab 22.5pts Donations Analysis Tab 22.5pts All tabs are in the correct order as specified in the document. 5 pts File named correctly Incorrect examples including but not limited to: Ch5Analysis_LastName (1) or Ch5Analysis_LastName.xlsx.xlsx 5 pts Common Additional Deductions: Submitting identical files as someone else in the class -100pts and a formal report filed. Submitting identical file from another semester -100pts and a formal report filed. Taking liberties and making changes you want to make and not ones that are in the instructions. Ie formatting etc. -10~100pts depending upon infraction Using your own excel document instead of the one provided -100pts Donations Survey (2) SubmittedEmployee IDLast NameFirst NameOrganizationAmountDonation Sent 1/29/11432AshJamieHabitat for Humanity$502/1/11 2/2/11546DanielsJulieAmnesty International$2002/21/11 2/2/11657RogersScottAnimal Shelter$352/15/11 2/2/11454CrossRobertaMake a