Portland State University
School of Business Administration
BA 301: Research and Analysis of Business Problems
Term: Winter 2010
Instructions – Fun With Excel (version 2007)
Excel is a terrific tool for data 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 2007. If you use Excel 2003 or earlier, use the other set of instructions for that version.
Go to the course web site and open and save the spreadsheet Fun With Excel. Use the data to answer the questions below. Before proceeding, save a couple of extra copies of the file in case you wish to start one section over.
Question 1: Among large donors (>=$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?).
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 is active in your Excel package by selecting the Office button in the upper left hand corner of the screen. Select Excel Options at the bottom of the menu, choose Add-Ins, select Analysis Toolpak-VBA. Then run a regression with years as the independent (x) variable, and giving as the dependent variable (y).
Select Data, Analysis, 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. You might wish to change the style of the chart to X Y (Scatter) by selecting the data on the chart and right-clicking.
The regression results should appear on a new worksheet ply (Sheet 4). 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 $110,552 for every additional year the donor has a relationship with the organization.
Save the file under the name 301Regression.
Print the XY chart and the basic regression stats, 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 Sort, and sort by column A, Donor Type. To automatically insert subtotals, select Data, Outline, Subtotal, then checkmark 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.
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 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? And which of these donors are 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 that you wish to use Green Fill with Light Green Text. Then click OK. Use the same process to indicate that if the cell value is greater than or equal to 500000, you wish the cell to be Light Red Fill with Dark Red Text.
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. 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 Region 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 fitting the donation requirements.
Question 4: In the Southern region, which interests are most heavily supported by corporate and foundation donors?
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 the current worksheet by selecting one of the Sheet Tabs at the bottom of the page and clicking on the top left cell. Click OK.
Select Donor Type, Location, Giving and Interests in the Pivot Table Field List by placing checkmarks in the appropriate boxes. In the PivotChart Filter Pane, under Donor Type select Corporate and Foundation. Under Location, select South. Now, in order to improve the chart appearance, rearrange the fields at the bottom of the PivotTable Field List. Move Interests to the Legend Fields, and move Location above Donor Type. Now you’ll see that you can answer the question.
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.
Print the chart and/or table showing the Southern Region breakdown.
Question 5: How can I access all of this information quickly and easily?
Features: Menu using hyperlinks.
Instructions: Open a new blank worksheet using Office button, New. Type in the following information:
A2: CUSTOMER ANALYSIS MENU
A4: Relationship between tenure and giving
A6: Average tenure and giving
A8: Large and very large DC insect donors
A10: Giving details for Southern organizations
Save the file as 301Menu.
Format your menu in some visually-appealing manner. For example, center the data, change the font size to 28, change the cell color of all cells to blue, then change the color of the cells containing data to yellow.
Once you have a format you like, click on cell A4 and select Insert, Links, Hyperlink, then browse until you find the file 301Regression, and select it. You will notice that some of your formatting has disappeared. To change it back to the desired look, do not click on the cell. Use arrow keys to move to the appropriate cell, and re-format it. Now click on the cell to link to the regression file. Click the blue back arrow button to return to your menu. Insert hyperlinks to the other files you have created and print out your menu.
Portland State University