Assignment - Fun With Excel
Format/Requirements
• Hand this in at the beginning of class, not by email.
• Use Times New Roman, 12-point font and the format provided in the Homework template.
• Make sure you show your name, course number, and section number on the page.
• Number the answers, and place them in the correct order.
Overview
Excel is a terrific tool for data and statistical analysis. This assignment involves working with a set of data containing information about different charity donors, which might be used to manage fundraising direct mail or promotional campaigns. You will learn a few simple tricks for analyzing this data such that you can extract some useful information and answer some questions. These instructions are written for Excel 2013. Excel 2010 should be quite similar. This exercise requires that you have the Data Analysis package installed for Excel. If you don't, you may need your original Microsoft discs. Let me know if you need help with this installation. I suggest that you don't wait until the last minute to complete this assignment.
Download the file "Fun With Excel Raw Data" found on the D2L course website. Open the file with Microsoft Excel and follow the instructions found with each of the following questions. Print out the results/data, and ensure that you separately provide specific answers to the questions. DO NOT print out all of the regression data for Question 1, only the basic r-squared and Sig of F information, and the X-Y graph. Be aware, the Mac version of Excel does not allow you to do Pivot Charts, only Pivot tables. So, you will need to use a Windows PC for Question 4.
Question 1: Among large donors (greater than or equal to $50,000), does the amount of giving tend to increase as the years of involvement with the organization increases? (i.e. is there a correlation between giving and years?). What number do you look at to determine this correlation?
Features: Data Sort, Regression
Instructions: Sort the data by amount of giving in ascending order by clicking on any cell in the table and selecting Data, Sort, select column E for Giving by choosing that in the Sort By drop-down menu, and sort in Smallest to Largest in the Order drop-down menu.
Make sure the regression feature (part of the Data Analysis package) is active in your Excel software. Data Analysis should show up as one of the menu items in the top window bar under the Data heading. If not, you'll have to add it by selecting the File tab in the upper left hand corner of the screen. Select Options at the bottom of the menu, choose Add-Ins, select Analysis Toolpak. Now, at the bottom of the screen, select Go... next to Excel Add-ins. Make sure that Analysis ToolPak is selected and click OK. Once installed, run a regression with years as the independent (x) variable, and giving as the dependent variable (y).
Select Data, Data Analysis and then choose Regression from the options. For the y range, highlight the giving amounts of 50,000 and over ($E$217:$E$326); for the x range, highlight the years associated with these amounts ($C$217:$C$326), click on Line fit plots to see a graphic representation of the data, and select OK. Change the style of the chart to X Y (Scatter) by selecting the data on the chart and right-clicking, if not already in this format. Clean up the chart format by changing the labels on the axes to something more informative.
The regression results should appear on a new worksheet ply (Sheet 4). Change the column widths so that you can actually see the numbers in the cells. If Significance F is <.05, it is unlikely these results happened purely by chance. The R-square provides an estimate of how much of the variation in giving can be explained by the length of the relationship. The x variable is the slope of the line, and can be interpreted to mean that giving increases by approximately $108,026 for every additional year the donor has a relationship with the organization.
Save the file under the name 301Regression.
Print the XY chart on one page, and the basic regression stats on another, showing R-Squared and Significance of F. Do not include all of the datapoints (i.e., the lists of data).
Question 2: What is the average amount of giving and the average number of years of giving for corporations, foundations, and volunteers?
Features: AutoSum (and outline).
Instructions: Return to Sheet 1, click on any cell containing data and select Data and Sort, and sort by column A, Donor Type. To automatically insert subtotals, select Data, Outline, Subtotal, and check off the years of giving and giving columns, and uncheck other columns. You can see the averages for each donor type by selecting Use Function: Average (above the column checkboxes.)
To view subtotals only (which is essentially an outline of your data), you can click on the small 2 in the upper left corner of your spreadsheet. To expand a particular section of your outline, such as volunteers, click on the + sign next to that subcategory.
To return to the outline view, click on the - sign next to the subtotal for that category. Return to level 2 outline view. Modify the spreadsheet so that your data will print on a single page. Save the file as 301AutoSum.
Print your spreadsheet on one page showing only the data that answers the question.
Return to the original data by selecting Data, Subtotals, Remove All, and save the file as 301InsectLovers.
Question 3: Which are the large (>=$50,000) and very large (>=$500,000) donors in the DC region, who are also insect enthusiasts?
Features: Conditional formatting; Format Painter, Auto Filter
Instructions: Click on the first cell in the giving column containing an amount (E2) and select Home, Styles, Conditional formatting. Choose Highlight Cell Rules, Between..., and indicate that the cell value is between 49999 and 499999 and choose a color for display. Then click OK. Use the same process to indicate that if the cell value is greater than or equal to 500000, and choose a different color for this display.
Now, copy (paint) this format to the remaining cells in the column. Click on the cell you have just formatted (E2) and click on the Format Painter icon-the small paintbrush located in Home, Clipboard. When the paintbrush is active, click on the first cell in your format range (E2) and drag your cursor to the end of the format range (E326). The cells with values meeting the criteria should have changed color.
Now you can use Auto Filter to view selected records. Click on a cell containing data and select Data, Sort & Filter, Auto Filter. To practice using Auto Filter, click on the pull-down menu in the in the Giving column and select Top 10 (under Number Filter). Change the selection to 20 and press return. The remaining records are the top 20 largest donors.
Now return to your original view by clicking on the Giving pull-down menu again, and placing a checkmark in (Select All). Now click on the pull down menu next to Location and select DC Region. By scrolling down, you can see all donors in the DC region only.
Now return to the giving column and select Number Filters, Custom Filter. Indicate that you wish to see records for which the cell value is greater than or equal to 50000. Now imagine that you're going to have an invitation-only party of insect enthusiasts in the DC area. Use the Interests pull-down to find donors interested in Insects. Save this file as 301InsectLovers.
Print your spreadsheet on one page showing the answers to the question - only Insect Lovers in the DC Region donating $50,000 or more. Don't worry if you don't have a color printer. It'll show up shaded.
Question 4: In the Southern region, which are the two most heavily supported interests by corporate donors, and what are the amounts?
Features: Pivot table / pivot chart.
Instructions: Re-open your original data file: FunWithExcel, and save it as 301Pivot. Click on a cell containing data and select Insert, Pivot Chart.
Indicate that your data is in the A1 to F326 range of your existing Excel list, and indicate that you would like to see your results in a new worksheet. Click OK. Notice that you now have a blank chart in the middle and a blank table on the left. You should see a list of PivotChart fields in the upper right that are the same as the fields in the upper row of your spreadsheet.
Choose Donor Type, Location, Giving and Interests in the PivotChart Fields list. The chart should now be really messy and hard to understand. It's time to clean it up and make it more useful. First, click and move Interests from the Axis box on the lower right of the screen to the Legend box. The chart should become a bit simpler, but still not good. In the same way, move Location from the Axis box to the Filters box. Now the chart should be much better. In the lower left of the chart, you should see a drop down menu labeled Donor Type. Click on that menu and choose Corporate and Foundation. In the upper left of the chart you should see a drop down menu labeled Location. Use that menu to choose South only. Now you'll see that you can answer the question, either by looking at the chart or the Pivot Table.
Notice that the Pivot Table in the upper left allows you to make changes using drop down menus. For example, change the location to west, then change the donor type to volunteer only. You can see that the graph and/or table adjusts itself to represent the data you have selected. Use the drop-down menus to return to the original data.
Format the data in the table to Comma Style by highlighting the entire sheet (Ctrl A) and clicking on the comma icon in the formatting toolbar. Reduce the number of decimal places to 0 by using the decimals formatting icon (.00 to .0). Now find the data needed to answer the question by using the pull-down menus. Re-save your file as 301Pivot.