BA 301 Harvard University Business Problems and Excel Sheet Exercises The requirements are attached in the “hw 3 b” file, you will use the file “Excel 33”

BA 301 Harvard University Business Problems and Excel Sheet Exercises The requirements are attached in the “hw 3 b” file, you will use the file “Excel 33” for the data needed and the template is attached if needed for extra help. This needs to be a well done homework so please work on it asap so we can revise it BA 301 – Research & Analysis of Business Problems
Homework Assignment #3 – Fun With Excel
Format/Requirements
•
•
•
Hand this in at the beginning of class, not by email.
Use Times New Roman, 12-point font, 1-inch margins as indicated in the homework
template.
Use the homework template!
.
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 – at the beginning of the assignment, not with the data! 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, or create the chart separately from the Pivot Table.
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 dropdown 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
Document1
1
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 =$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
Document1
3
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 dropdown 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 Pivot Chart and the Pivot Table showing the Southern Region breakdown,
including both Corporate and Foundation donor types.
Question 5: This question does not use the “Fun With Excel” data”, but is specifically intended to help
you practice using the Charting Tool within Excel. You should have already gathered financial
background data for your research company as part of Assignment 1. Find the overall corporate revenue
data for the 2009 through 2013, along with the same data from one primary competitor. Create a single
Excel chart (not a table) that effectively compares that data. This chart should be suitable for presentation
to a management team as part of a formal presentation – so no typos or formatting errors! If it was me,
I’d probably include this chart as part of the final paper.
Document1
4
Donor Type
Corporation
Volunteer
Corporation
Volunteer
Corporation
Corporation
Volunteer
Volunteer
Corporation
Volunteer
Volunteer
Volunteer
Corporation
Foundation
Corporation
Foundation
Corporation
Volunteer
Volunteer
Corporation
Foundation
Foundation
Volunteer
Volunteer
Foundation
Volunteer
Volunteer
Foundation
Corporation
Foundation
Foundation
Volunteer
Volunteer
Volunteer
Corporation
Foundation
Foundation
Corporation
Corporation
Volunteer
Corporation
Foundation
Volunteer
Foundation
Foundation
Foundation
Foundation
Corporation
Corporation
Volunteer
Volunteer
Volunteer
Volunteer
Volunteer
Volunteer
Volunteer
Volunteer
Foundation
Foundation
Volunteer
Corporation
Corporation
Foundation
Foundation
Foundation
Corporation
Volunteer
Corporation
Corporation
Foundation
Volunteer
Foundation
Corporation
Foundation
Volunteer
Corporation
Volunteer
Volunteer
Location
Yrs of Giving
DC Region
6
Northeast
5
DC Region
1
Midwest
1
DC Region
5
DC Region
2
West
5
West
5
DC Region
6
South
6
DC Region
4
Northeast
7
DC Region
5
Northeast
1
Midwest
2
DC Region
6
DC Region
3
DC Region
3
DC Region
4
Northeast
8
West
9
South
3
Midwest
5
DC Region
4
South
5
West
3
DC Region
2
West
6
South
7
DC Region
4
Midwest
7
Northeast
3
South
6
West
4
South
1
West
6
Northeast
3
DC Region
3
DC Region
9
Midwest
7
DC Region
6
DC Region
4
Northeast
3
Midwest
5
DC Region
1
Midwest
5
DC Region
8
West
2
DC Region
4
West
4
Northeast
2
DC Region
9
DC Region
6
Midwest
3
DC Region
6
Northeast
3
West
7
DC Region
5
DC Region
4
DC Region
2
DC Region
5
DC Region
5
West
4
South
4
Midwest
3
Midwest
8
Midwest
4
DC Region
7
Northeast
1
Northeast
3
DC Region
7
DC Region
3
DC Region
4
DC Region
4
South
2
DC Region
3
DC Region
4
West
4
Name
Giving
Columbia Royalty Company
$300,000.00
Ms. Sarah Louise Wood
$305,000.00
EBAA Iron Sales, Inc.
$20,000.00
Mr. Mark Nagel
$5,000.00
Ford Motor Company*
$25,000.00
Caterpillar, Inc.
$9,100.00
Ms. Julie Kerlin
$15,000.00
Ms. Wilma Sauer
$3,489.00
Consolidated Natural Gas
$20,000.00
Mr. David Ackerman
$22,698.00
Ms. Ann Gallus
$155,000.00
Mr. and Mrs. Anthony Cammarota, Jr.
$119,517.31
International Business Machines Corporation***$100,000.00
Norfolk Southern Foundation
$10,000.00
Ingersoll-Rand Company
$10,000.00
NASA Goddard Space Flight Center*
$20,000.00
Beneficial Corporation
$6,200.00
Ms. Hilde Newberry
$210,000.00
Mr. L. Michael Kaas
$20,800.00
Cable News Network, Inc.*
$9,660.00
National Museum of Korea
$20,000.00
Pennzoil Company
$77,000.00
Mr. Mark Edmondson
$50,000.00
Mrs. Susanne Dhyse
$10,000.00
URS Corporation
$16,875.00
Ms. Laura Walko
$300,000.00
Ms. Nora Whalen
$6,000.00
S.C. Johnson & Son, Inc.
$163,649.50
CACI International, Inc.
$6,200.00
McKinsey & Company, Inc.
$5,000.00
Komatsu America International
$25,000.00
Mr. and Mrs.Christopher Callais
$10,000.00
Ms. Priscilla Joslyn
$53,000.00
Dr. Arthur Stern
$15,000.00
Intergraph Corporation*
$163,649.50
National Stone, Sand & Gravel Association
$20,000.00
Transamerica Occidental Life Insurance
$9,000.00
Elle Corporation
$100,000.00
Exxon Corporation
$338,275.00
Mr. Marvin Nakashima
$9,100.00
Ford Division
$111,607.61
Medtronic, Inc.
$500,000.00
Ms. Barbara Dyson
$21,569.00
Tennenbaum & Co., LLC
$3,760.00
Johnson Matthey
$111,000.00
Kerr-McGee Corporation
$5,000.00
Jefferson-Pilot Corporation
$1,325,667.00
GFC Financial Corporation
$21,736.75
Discovery Communications
$338,275.00
Mr. Bill Peters
$5,000.00
Mr. Robert Granick
$130,882.00
Mr. Frank Jaworski
$500,000.00
Ms. Katherine Rogers
$3,000.00
Mrs. Frances Locke
$7,500.00
Ms. Susan Berenson
$100,000.00
Ms. Doralies Berolzheimer
$3,760.00
Ms. Nancy Warder
$20,000.00
SunGuard Data Systems, Inc.
$9,000.00
The Chase Manhattan Bank
$10,000.00
Mr. Dede Den Hartog
$60,000.00
BHP Minerals
$5,050.00
Hewlett-Packard Company*
$6,000.00
Rockland Research Corporation*
$26,250.00
National Ocean Industries Association
$5,000.00
Van American Insurance Company
$50,000.00
American Physical Therapy Association
$5,000.00
Ms. Rosemary Maloney
$2,500.00
Citibank
$38,000.00
Coca-Cola (Company & Foundation)
$100,000.00
Leica, Inc.*
$2,500.00
Mr. David Freeland
$250,000.00
Mobil Corporation
$5,000.00
Aroaima Bauxite Company, Ltd.
$305,000.00
Sun Life Assurance Company of Canada
$26,000.00
Mrs. Mary Nichols
$77,000.00
Chris Mueller
$100,000.00
Ms. Lisa Meyers
$50,000.00
Ms. Mina Eggerton
$305,000.00
Interests
Birds
Marine Hall
Birds
Unrestricted
Birds
Biodiversity
Insect
Unrestricted
Birds
Unrestricted
Unrestricted
Unrestricted
Endowment
Forensic Anthropology
Endowment
Endowment
Biodiversity
GGM
Unrestricted
Biodiversity
Endowment
Forensic Anthropology
Unrestricted
Unrestricted
GGM
Insect
Marine Hall
GGM
Biodiversity
Endowment
Endowment
Unrestricted
Marine Hall
Unrestricted
Endowment
Forensic Anthropology
Endowment
Birds
Birds
Unrestricted
Birds
Endowment
GGM
GGM
Endowment
Endowment
Endowment
Endowment
Birds
Unrestricted
Unrestricted
Unrestricted
Insect
Unrestricted
Marine Hall
GGM
Marine Hall
GGM
GGM
Unrestricted
Biodiversity
Endowment
GGM
Endowment
GGM
Anthropology
Marine Hall
Biodiversity
Biological Dynamics of Forest Fragments
Endowment
Unrestricted
Endowment
Anthropology
GGM
Unrestricted
Biodiversity
Insect
Marine Hall
Volunteer
Volunteer
Foundation
Corporation
Volunteer
Foundation
Corporation
Volunteer
Volunteer
Foundation
Volunteer
Corporation
Volunteer
Foundation
Corporation
Corporation
Foundation
Corporation
Foundation
Foundation
Corporation
Volunteer
Corporation
Corporation
Corporation
Foundation
Volunteer
Volunteer
Corporation
Foundation
Foundation
Corporation
Corporation
Foundation
Foundation
Corporation
Corporation
Volunteer
Foundation
Volunteer
Corporation
Corporation
Corporation
Corporation
Foundation
Corporation
Foundation
Volunteer
Volunteer
Foundation
Corporation
Foundation
Corporation
Foundation
Foundation
Volunteer
Corporation
Corporation
Corporation
Corporation
Corporation
Foundation
Foundation
Foundation
Foundation
Volunteer
Volunteer
Corporation
Volunteer
Foundation
Volunteer
Foundation
Corporation
Corporation
Foundation
Corporation
Foundation
Volunteer
Volunteer
Northeast
West
West
DC Region
DC Region
DC Region
West
DC Region
DC Region
DC Region
Northeast
DC Region
West
Northeast
Midwest
DC Region
West
West
Midwest
DC Region
West
Midwest
DC Region
South
DC Region
South
South
South
DC Region
Midwest
Northeast
DC Region
DC Region
South
Midwest
DC Region
DC Region
Northeast
DC Region
DC Region
DC Region
DC Region
DC Region
Midwest
Midwest
Midwest
DC Region
Midwest
Midwest
DC Region
DC Region
DC Region
DC Region
DC Region
Northeast
West
DC Region
DC Region
DC Region
DC Region
South
DC Region
DC Region
DC Region
DC Region
Northeast
Northeast
DC Region
Northeast
West
DC Region
Northeast
DC Region
DC Region
DC Region
DC Region
Northeast
Northeast
DC Region
6
3
7
4
7
7
2
1
7
3
1
4
6
6
5
5
3
1
4
4
6
8
4
7
4
2
2
3
7
3
5
2
9
5
2
1
2
2
7
4
3
8
6
7
4
3
3
4
3
4
6
6
7
7
2
5
3
2
6
4
1
6
1
5
5
4
4
3
4
2
4
2
3
1
4
3
2
4
9
Mrs. Lynn Braitman
$30,000.00
Lou Carrera
$3,000.00
McDonalds Corporation
$2,500.00
Harry Winston
$3,000.00
Ms. Alida Kane
$50,000.00
Svedala Industries, Inc.
$8,000.00
Battelle Memorial Institute
$5,000.00
Ms. Frances McCaffrey
$5,000.00
Mr. Howard Wilbur
$30,000.00
Parsons Engineering Science, Inc.
$5,000.00
Ms. Cynthia Richardson
$100,000.00
Ecuambiente S.A.
$10,000.00
Mr. Allan Pitcher
$5,000.00
Western Fuels Association, Inc.
$20,000.00
Alumax, Inc.
$2,500.00
CONSOL Inc.
$300,000.00
Sappi, Ltd..
$15,000.00
Coeur d’Alene Mines Corporation
$119,517.31
Resources for the Future, Inc.
$10,000.00
The Aleut Corporation
$10,000.00
Bechtel Foundation
$10,000.00
Ms. Sally Cacheris
$20,000.00
Engelhard Corporation
$5,000.00
Barrick Goldstrike Mines, Inc.
$50,000.00
ASARCO Incorporated
$20,000.00
Orkin Pest Control**
$60,618.00
Mrs. Nancy Springer
$3,000.00
Mr. Phil Mathias
$6,000.00
First Virginia Banks, Inc.
$50,000.00
Quad/Graphics
$5,000.00
Lockheed Martin Corporation
$570,000.00
Corporation for Educational Communications $210,000.00
Cleveland-Cliffs, Inc.
$2,428,500.00
Maritz Travel Company
$50,000.00
Phillips Petroleum Company Norway
$53,000.00
Diebold Incorporated*
$10,000.00
Chemical Bank
$10,000.00
Mr. Walter Howell
$17,500.00
National Association of Secondary School Teachers
$26,250.00
Ms. Mary Weisman
$50,000.00
Elf Aquitaine
$9,660.00
First National Bank of Anchorage
$20,000.00
International Paper Company
$10,000.00
Bristol Bay Native Corporation
$5,000.00
Kennecott Corporation
$9,660.00
Independence Mining Company, Inc.
$3,000.00
Space Biospheres Ventures
$5,000.00
Ms. Lila Motlagh
$250,000.00
Ms. Barbara Lytle
$10,000.00
Jewelers of America
$24,200.00
Degussa Corporation
$10,000.00
Silicon Graphics
$7,500.00
Case, Pomeroy & Company, Inc.
$892,332.00
Janus-Merritt Strategies, LLC
$10,000.00
UNESCO
$10,000.00
Ms. Megan Sullivan
$119,517.31
ARCO Foundation
$55,684.00
Homestake Mining Company
$60,618.00
Friends of the Hunley
$150,000.00
Conoco, Inc.
$20,000.00
ARCO Coal Company
$90,000.00
Motorola, Inc****
$15,000.00
PSEG Services
$10,000.00
Times Mirror Magazines*
$250,000.00
Steve Burnett, Inc.
$10,000.00
Mrs. Alma Hale Paty
$250,000.00
Mr. Samuel Dove
$147,881.50
Harnischfeger Industries, Inc.
$20…
Purchase answer to see full
attachment