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...

1 answer below »

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.
. 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.
. 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.
. Note, your ri
on 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
ecords. (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 a
anging 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
. 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 Decembe
. 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
old 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.
. 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 co
ectly 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 co
ect order as specified above.
. 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 Fe
uary, 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.
. 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 Ru
ic
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 co
ect order as specified in the document. 5 pts
File named co
ectly
Inco
ect 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
eport filed.
Submitting identical file from another semester -100pts and a formal
eport 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)
    Submitted    Employee ID    Last Name    First Name    Organization    Amount    Donation Sent
    1/29/11    432    Ash    Jamie    Habitat for Humanity    $50    2/1/11
    2/2/11    546    Daniels    Julie    Amnesty International    $200    2/21/11
    2/2/11    657    Rogers    Scott    Animal Shelter    $35    2/15/11
    2/2/11    454    Cross    Roberta    Make a
Answered 1 days AfterApr 04, 2022

Solution

Bikash answered on Apr 06 2022
11 Votes
Tracking Donations
    Submitted    Employee ID    Last Name    First Name    Organization    Amount    Donation Sent
    1/29/11    432    Ash    Jamie    Habitat for Humanity    $50    2/1/11
    2/2/11    454    Cross    Roberta    Make a Wish Foundation    $75    2/4/11
    2/2/11    543    Fisher    Scott    Meals on Wheels    $25    2/26/11
    2/2/11    546    Daniels    Julie    Amnesty International    $200    2/21/11
    2/2/11    657    Rogers    Scott    Animal Shelter    $35    2/15/11
    2/3/11    456    Butler    Amy    Cancer Society    $250    2/10/11
    2/3/11    499    Cross    Roberta    Animal Shelter    $20    2/5/11
    2/3/11    945    Williams    James    Vietnam Veterans    $60    2/13/11
    2/4/11    389    Fisher    Scott    Cancer Society    $25    2/10/11
    2/5/11    243    Bagley    Bob    Cancer Society    $25    2/6/11
    2/5/11    654    Ash    Carlin    Diabetic Association    $100    2/6/11
    2/6/11    21    Smith    James    Diabetic Association    $50    2/19/11
    2/6/11    345    Cross    Roberta    Leukemia Society    $60    2/19/11
    2/6/11    832    Haitz    Todd    Cancer Society    $50    2/14/11
    2/9/11    675    Ash    Scott    Diabetic Association    $100    2/18/11
    2/10/11    334    Kellee    Pete    Police Federation    $180    2/14/11
    2/13/11    366    Smith    James    Habitat for Humanity    $150    2/23/11
    2/14/11    977    Rogers    Scott    Homeless Shelters    $80    2/19/11
    2/16/11    234    Daniels    Julie    Make a Wish Foundation    $225    2/29/2011
    2/16/11    867    Daniels    Julie    Make a Wish Foundation    $130    2/21/11
    2/17/11    987    Haim    Gary    Diabetic Association    $50    2/22/11
    2/20/11    558    Los    Jim    Meals on Wheels    $15    2/22/11
    2/27/11    365    Lee    Sufeing    Homeless Shelters    $25    2/30/2011
    2/27/11    370    Wills    Mary    Habitat for Humanity    $100    2/29/2011
    5/29/11    432    Ash    Jamie    Habitat for Humanity    $50    6/1/11
    6/2/11    454    Cross    Roberta    Make a Wish Foundation    $75    6/4/11
    6/2/11    543    Fisher    Scott    Meals on Wheels    $65    6/26/11
    6/3/11    456    Butler    Amy    Cancer Society    $250    6/10/11
    6/3/11    499    Cross    Roberta    Animal Shelter    $20    6/5/11
    6/3/11    945    Williams    James    Vietnam Veterans    $60    6/13/11
    6/4/11    389    Fisher    Scott    Cancer Society    $65    6/10/11
    6/5/11    243    Bagley    Bob    Cancer Society    $25    6/6/11
    6/5/11    654    Ash    Carlin    Diabetic Association    $100    6/6/11
    6/6/11    25    Smith    James    Diabetic Association    $50    6/19/11
    6/6/11    345    Cross    Roberta    Leukemia Society    $60    6/19/11
    6/6/11    546    Daniels    Julie    Amnesty International    $200    6/21/11
    6/6/11    657    Rogers    Scott    Animal Shelter    $35    6/15/11
    6/6/11    832    Haitz    Todd    Cancer Society    $50    6/14/11
    6/9/11    675    Ash    Scott    Diabetic Association    $100    6/18/11
    6/10/11    334    Kellee    Pete    Police Federation    $180    6/14/11
    6/13/11    366    Smith    James    Habitat for Humanity    $150    6/23/11
    6/14/11    977    Rogers    Scott    Homeless Shelters    $80    6/19/11
    6/16/11    234    Daniels    Julie    Make a Wish Foundation    $225    6/29/11
    6/16/11    867    Daniels    Julie    Make a Wish Foundation    $130    6/21/11
    6/17/11    987    Haim    Gary    Diabetic Association    $50    6/22/11
    6/20/11    558    Los    Jim    Meals on Wheels    $55    6/22/11
    6/27/11    365    Lee    Sufeing    Homeless Shelters    $25    6/30/11
    6/27/11    370    Wills    Mary    Habitat for Humanity    $100    6/29/11
    8/29/11    432    Ash    Jamie    Habitat for Humanity    $80    9/1/11
    9/2/11    484    Cross    Roberta    Make a Wish Foundation    $78    9/4/11
    9/2/11    843    Fisher    Scott    Meals on Wheels    $98    9/29/11
    9/3/11    489    Butler    Amy    Cancer Society    $280    9/10/11
    9/3/11    499    Cross    Roberta    Animal Shelter    $20    9/8/11
    9/3/11    948    Williams    James    Vietnam Veterans    $90    9/13/11
    9/4/11    389    Fisher    Scott    Cancer Society    $98    9/10/11
    9/8/11    243    Bagley    Bob    Cancer Society    $28    9/9/11
    9/8/11    984    Ash    Carlin    Diabetic Association    $100    9/9/11
    9/9/11    28    Smith    James    Diabetic Association    $80    9/19/11
    9/9/11    348    Cross    Roberta    Leukemia Society    $90    9/19/11
    9/9/11    849    Daniels    Julie    Amnesty International    $200    9/21/11
    9/9/11    987    Rogers    Scott    Animal Shelter    $38    9/18/11
    9/9/11    978    Ash    Scott    Diabetic Association    $100    9/18/11
    9/9/11    832    Haitz    Todd    Cancer Society    $80    9/14/11
    9/10/11    334    Kellee    Pete    Police Federation    $180    9/14/11
    9/13/11    399    Smith    James    Habitat for Humanity    $180    9/23/11
    9/14/11    977    Rogers    Scott    Homeless Shelters    $80    9/19/11
    9/17/11    987    Haim    Gary    Diabetic Association    $80    9/22/11
    9/19/11    234    Daniels    Julie    Make a Wish Foundation    $228    9/29/11
    9/19/11    897    Daniels    Julie    Make a Wish Foundation    $130    9/21/11
    9/20/11    888    Los    Jim    Meals on Wheels    $88    9/22/11
    9/27/11    398    Lee    Sufeing    Homeless Shelters    $28    9/30/11
    9/27/11    370    Wills    Mary    Habitat for Humanity    $100    9/29/11
    11/29/11    432    Ash    Jamie    Habitat for Humanity    $50    12/1/11
    12/2/11    454    Cross    Roberta    Make a Wish Foundation    $75    12/4/11
    12/2/11    543    Fisher    Scott    Meals on Wheels    $125    12/26/11
    12/3/11    456    Butler    Amy    Cancer Society    $250    12/10/11
    12/3/11    499    Cross    Roberta    Animal Shelter    $20    12/5/11
    12/3/11    945    Williams    James    Vietnam Veterans    $60    12/13/11
    12/4/11    389    Fisher    Scott    Cancer Society    $125    12/10/11
    12/5/11    243    Bagley    Bob    Cancer Society    $25    12/6/11
    12/5/11    654    Ash    Carlin    Diabetic Association    $100    12/6/11
    12/6/11    211    Smith    James    Diabetic Association    $50    12/19/11
    12/6/11    345    Cross    Roberta    Leukemia Society    $60    12/19/11
    12/6/11    832    Haitz    Todd    Cancer Society    $50    12/14/11
    12/9/11    675    Ash    Scott    Diabetic Association    $100    12/18/11
    12/10/11    334    Kellee    Pete    Police Federation    $180    12/14/11
    12/12/11    546    Daniels    Julie    Amnesty International    $200    12/21/11
    12/12/11    657    Rogers    Scott    Animal Shelter    $35    12/15/11
    12/13/11    366    Smith    James    Habitat for Humanity    $150    12/23/11
    12/14/11    977    Rogers    Scott    Homeless Shelters    $80    12/19/11
    12/16/11    234    Daniels    Julie    Make a Wish Foundation    $225    12/29/11
    12/16/11    867    Daniels    Julie    Make a Wish Foundation    $130    12/21/11
    12/17/11    987    Haim    Gary    Diabetic Association    $50    12/22/11
    12/20/11    558    Los    Jim    Meals on Wheels    $115    12/22/11
    12/27/11    365    Lee    Sufeing    Homeless Shelters    $25    12/30/11
    12/27/11    370    Wills    Mary    Habitat for Humanity    $100    12/29/11
Donations Survey
    Submitted    Employee ID    Last Name    First Name    Organization    Amount    Donation Sent
    1/29/11    432    Ash    Jamie    Habitat for Humanity    $50    2/1/11
    2/2/11    546    Daniels    Julie    Amnesty International    $200    2/21/11
    2/2/11    657    Rogers    Scott    Animal Shelter    $35    2/15/11
    2/2/11    454    Cross    Roberta    Make a Wish Foundation    $75    2/4/11
    2/2/11    543    Fisher    Scott    Meals on Wheels    $25    2/26/11
    2/3/11    499    Cross    Roberta    Animal Shelter    $20    2/5/11
    2/3/11    456    Butler    Amy    Cancer Society    $250    2/10/11
    2/3/11    945    Williams    James    Vietnam Veterans    $60    2/13/11
    2/4/11    389    Fisher    Scott    Cancer Society    $25    2/10/11
    2/5/11    243    Bagley    Bob    Cancer Society    $25    2/6/11
    2/5/11    654    Ash    Carlin    Diabetic Association    $100    2/6/11
    2/6/11    832    Haitz    Todd    Cancer Society    $50    2/14/11
    2/6/11    21    Smith    James    Diabetic Association    $50    2/19/11
    2/6/11    345    Cross    Roberta    Leukemia Society    $60    2/19/11
    2/9/11    675    Ash    Scott    Diabetic Association    $100    2/18/11
    2/10/11    334    Kellee    Pete    Police Federation    $180    2/14/11
    2/13/11    366    Smith    James    Habitat for Humanity    $150    2/23/11
    2/14/11    977    Rogers    Scott    Homeless Shelters    $80    2/19/11
    2/16/11    234    Daniels    Julie    Make a Wish Foundation    $225    2/29/2011
    2/16/11    867    Daniels    Julie    Make a Wish Foundation    $130    2/21/11
    2/17/11    987    Haim    Gary    Diabetic Association    $50    2/22/11
    2/20/11    558    Los    Jim    Meals on Wheels    $15    2/22/11
    2/27/11    370    Wills    Mary    Habitat for Humanity    $100    2/29/2011
    2/27/11    365    Lee    Sufeing    Homeless Shelters    $25    2/30/2011
    5/29/11    432    Ash    Jamie    Habitat for Humanity    $50    6/1/11
    6/2/11    454    Cross    Roberta    Make a Wish Foundation    $75    6/4/11
    6/2/11    543    Fisher    Scott    Meals on Wheels    $65    6/26/11
    6/3/11    499    Cross    Roberta    Animal Shelter    $20    6/5/11
    6/3/11    456    Butler    Amy    Cancer Society    $250    6/10/11
    6/3/11    945    Williams    James    Vietnam...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here