Food for All Samuel Hamilton started Food for All in Lake
Charles, Louisiana, three years ago in response to a growing number of
residents who encountered unexpected challenges with being able to feed
themselves and their families. The food bank has been very successful providing
healthy food for the town residents. Samuel is considering expanding the food
bank’s reach to include several other towns in the area and needs to analyze
current donations to see whether it can support the expansion. Samuel tracks
donations in Excel. He has entered donation data for the Ârst quarter of the
year in a worksheet and wants you to analyze the data. Complete the following:
1. Open the Food workbook located in the Excel5 > Case3
folder included with your Data Files, and then save the workbook as Food Bank
in the location speciÂed by your instructor.
2. In the Documentation sheet, enter your name and the date.
3. Samuel wants to view donations with values that are
either less than $10 or greater than $100. He tried Âltering the donations in
the Donation Amount Filter worksheet, but it’s not working as expected. Review
the custom Number Âlter in the worksheet, and Âx the problems.
4. In the Donations worksheet, create an Excel table, and
then rename the table as DonationsTbl. Format the DonationsTbl table using the
table style of your choice.
5. In the DonationsTbl table, format the Value column so
that it is clear that this Âeld contains dollars.
6. Find the record that has a year of 3018. Correct the year
so that it is XXXXXXXXXXMake a copy of the Donations worksheet, and then rename
the copied worksheet as Sorted Donations. (Hint: Press the Ctrl key and drag
the sheet tab to the right of the current sheet tab to make a copy of the
worksheet.) In the Sorted Donations worksheet, sort the data in ascending order
by Zip and then in ascending order by Date.
8. Using conditional formatting, highlight all of the
records in the sorted table that are the type Food with the format of your
choice.
9. Make a copy of the Donations worksheet, and then rename
the copied worksheet as Filtered Donations. Filter the DonationsTbl table to
display records that have not been sent a receipt. Sort the data by Zip in
ascending order and then by Value in descending order.
10. Insert a Total row that calculates the total of the
Value column for the Âltered data and the count of the Receipt column. Remove
any totals that appear for other columns. Make sure that the columns are wide
enough to display the values.
11. In the Donation Type Subtotal worksheet, Samuel is
trying to include subtotals that show the total Value for each donation Type.
However, the subtotal for each type appears more than once. Fix this report so
it shows only one subtotal for each type.
12. Based on the DonationsTbl table in the Donations
worksheet, create a PivotTable in a new worksheet that displays the Count of
Value and the average Value of the donations by Type. Place the Type Âeld in
the ROWS area of the PivotTable. Apply the PivotTable style that matches the
DonationsTbl table style. Format the Average values using the Accounting
format. Change the labels above the average donations to Average, and change
the label above the count of donations to Number.
13. Insert a slicer to Âlter the PivotTable by Type, and
then use the slicer to Âlter Food from the PivotTable. Format the slicer to
match the PivotTable style. Resize and position the slicer appropriately.
Rename the worksheet as PivotTable by Type.
14. Based on the DonationsTbl table in the Donations
worksheet, create a PivotTable in a new worksheet that shows the Total Value by
Zip. Format the Sum of Value so that it is more readable. Apply a PivotTable
style to match the style of the DonationsTbl table. Rename the worksheet as
PivotTable Value by Zip.
15. Based on the PivotTable in the PivotTable Value by Zip
worksheet, create a PivotChart using the Clustered column chart type. Move the
PivotChart to row 3. Change the chart title to Donations by Zip. Change the Âll
color of the bars to a color that matches the style in the PivotTable. Remove
the legend.
16. Filter the PivotChart to hide the donations in the ZIP
code 70611.
17. Save the workbook, and then close it.