What is the average amount of giving and the average number of years of giving for corporations, foundations, and volunteers and the grand average for all three types of donors?

Format/Requirements

• Hand this in at the beginning of class, not by email.
• Use Times New Roman, 12-point font.
• Make sure you show your name on each page.
• Number the answers, and place them in the correct order (make it easy for me to find your answers).
• Make sure to answer the questions that are asked. Questions 1, 4A, and 4B require an explicit text answer to the question in addition to printing your Excel output.
.
Overview

Read each of the assignment questions and the instructions for each question carefully. These instructions are based on Excel 2016 and, if you are using a previous version of Excel, you may need to “hunt around” a little or use the help function to locate the necessary Excel command.

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 which might be helpful for management decision making.

This exercise requires that you have the Data Analysis package installed for Excel. If you don’t, you may need your original Microsoft discs or you may need to go to the SBA computer lab. I suggest that you don’t wait until the last minute to complete this assignment.

Be aware, these instructions assume that you will be using a PC (not a Mac) computer. If you are experienced using Excel on a Mac, you may not have difficulty. If you have limited experience using Excel, I recommend you use a computer in the library or computer lab to complete this assignment.

Download the file “Excel Data Analysis 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 provideexplicit answers to the questions for Questions 1, 4A, and 4B.

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? What do the values of R-squared, Significance of F, Intercept, and X-variable tell you tell you that is useful in interpreting the results of this analysis?
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, then select column E for Giving by choosing that in the Sort By drop-down menu. 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 (typically on the far right of the Data ribbon). If not, you’ll have to add the Data Analysis Add-In by selecting the Filetab 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, follow the instructions below to run a regression with years as the independent (x) variable, and giving as the dependent variable (y).
Select Data thenData Analysis and then choose Regression from the options. For Input Y Range, highlight the giving amounts of 50,000 and over ($E$217:$E$326); for Input X Range, highlight the years associated with these amounts ($C$217:$C$326). Next, under Output Options, select New Worksheet. Finally, 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 meaningful such as “Yrs of Giving” and “Amount of Giving” and resize the regression chart to make it legible.
The regression results will consist of 4 tables along with the regression chart and should appear on a new worksheet. Change the column widths so that you can actually see the numbers in the cells. What information do the values for Significance of F, R-square, and X-variable provide?
Save the file under the name 301Regression.
Print the XY chart on one page, and the basic regression stats on another page, showing R-Squared ,Significance of F, and Intercept and X-variable. Do not include all of the other data included in the 4th table labelled Residual Output – just the regression chart and the tables showing the key items above.

Question 2: What is the average amount of giving and the average number of years of giving for corporations, foundations, and volunteers and the grand average for all three types of donors?
Features: AutoSum (and Outline).
Instructions: Return to the Raw Data sheet. Click on any cell containing data and select Data and thenSort. Sort by column A, “Donor Type”. Nextautomatically insert subtotals by selecting Data, Outline, Subtotal, and check the “Years of Giving” and “Giving” columns, and uncheck other columns. Now,in the “Use Function” dropdown list, select “Average” so that Excel uses the correct mathematical function to answer the question.
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. If necessary, resize or 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. Do not turn in extraneous Excel output which does not answer the question asked.

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 a dollar amount (cell E2) and select Home, Styles, then Conditional Formatting. Choose Highlight Cell Rules, then Between…, and indicate that the cell value is between 49,999 and 499,999 and choose a color for display. Then click OK. Use the same process to indicate that if the cell value is greater than 499,999.99and 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, then Filter. To practice using the Filter function, click on the pull-down menu in the in the “Giving” column and select Top 10 (under Number Filter). Next, experiment by changing 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 in your table, 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 50,000. Now imagine that you’re going to have an invitation-only party of insect enthusiasts in the DC area and use the “Interests” pull-down to find onlythose DC-areadonors who are 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. Again, do not print additional Excel output except those lines required to answer the question asked (there should not be several dozen lines of information).

Question 4a: In the Southern region, which are the two most heavily supported Interests by corporate donors and what are the Amounts of Giving for each of those Interests? (Read the question carefully.)
Features: Pivot Table and Pivot Chart.
Instructions:Re-open your original data file: Excel Data Analysis Raw Data, and save it as 301Pivot. Click on a cell containing data and select Insert, Charts, then PivotChart in the dropdown list you get.Click on PivotChart & PivotTable. (If you are using a different version of Excel, you may have to search some for the PivotChart function but I am looking for both a Pivot Chart and Pivot Table in your answer.)
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. On the left side of the table, you should see a drop down menu labeled “Donor Type”. Click on that menu and choose Corporate and Foundation. Also near the left side of the table 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 (South, Corporate and Foundation).
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 on the Home tab just above “Number” near the center of the 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 as necessary. Re-save your file as 301Pivot.
Print the both Pivot Chart and the Pivot Table showing the Southern Region breakdown, including both Corporate and Foundation donor types but also make sure that you answer the question in text. Resize and move the Pivot Chart and Pivot Table as necessary to print them.

Question 4b: In the West Region, which Donor Type (Corporation, Foundation, or Volunteer) donated the greatest amount and how much was it? How much did this Donor Type contribute to Insect causes?

Print both the Pivot Chart and the Pivot Table showing the West Region and all donor types but also make sure that you answer the questions.

Question 5:
Assume you have chosen Kellogg Company (the cereal people) as the subject of your research paper. As part of your background research, you have reviewed annual corporate revenue (net sales) numbers for Kellogg and their key competitor – General Mills. Create one Excel chart (one chart/graph, please, not a table) showing net sales for both companies for the most recent five years reported by each company* that you might include in your final paper. Along with your chart, write a single text sentence describing the main conclusion you would draw from looking at the chart you have prepared (this is in addition to the title of our chart). Use actual Kellogg’s and General Mills data, not data from your term paper company. You can access both company’s recent 10K reports on their respective web sites in the Investors section.

Order from us and get better grades. We are the service you have been looking for.