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 specied 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 2017. 7. Make 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.