Research & Analysis of Business Problems

BA 301 – Research & Analysis of Business Problems
Homework Assignment #2
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.
? In contrast to the paper – this should be 2-sided and single-spaced.
? Use the Homework Template.
.
Questions
1) Now that you have written a Situation Analysis, you should have a good overall
perspective on your chosen company. Now, using that information, continue
identifying symptoms (signs and indicators) of deeper problems that might be facing
the company. Try to find as many as you can, but ensure that you have found at least
two or three, and write them down as part of this assignment. Be specific and use
quantitative metrics where possible. All of the symptoms that you list should show a
“gap” as discussed in class. A “gap” is the difference between the desired and the
actual. For example, if you find that the company had a profit of $5 million dollars in
the previous year, and their goal was $10 million – this is a gap, or a symptom of an
underlying problem!
Once you have identified these symptoms, continue your research and begin a root
cause analysis. In other words – ask why the symptoms occurred. Referring to the
“profit” example in the first paragraph, think about why they did not meet their profit
goals. Perhaps a new product was not as successful as planned. Note that it’s
possible that many symptoms may lead back to the same root cause. Or one symptom
may have many possible causes. This analysis is the core of your problem
description and is a crucial component in your research paper, so I would recommend
that you spend some time on it. You won’t be able to develop solutions if you have
not answered the question “why did these symptoms happen?” If you uncover
symptoms that are completely unrelated, pick one as a focus for your paper. This
“focus” problem is what you will work on for the remainder of the paper.
Try to keep this to a one-page (one-side) summary, single-spaced!
2) Pick one of the reputable research sources used in your work to date, and create and
include with this assignment a properly formatted MLA Bibliography entry as shown
and explained in the research class lecture. This is practice for what is required on
the Bibliography page of your final paper. I will be extremely critical on formatting
for this assignment – so follow the guidelines.

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!
? Answer the questions.
.
Overview
Excel is a good tool for basic statistical analysis. This assignment uses a dataset of
charitable giving history, which might be used to manage fundraising direct mail or
promotional campaigns. You will learn a few simple tricks for analyzing this data so that
you can extract some useful information and answer some questions, as discussed in class.
These instructions were written specifically for Excel 2013. Your version may be slightly
different, so beware. Excel for the Apple iOS is quite a bit different, and I can’t guarantee
that all of the features work the same way. You might wish to use one of the school
computers to avoid any problems
This exercise also requires that you have the Data Analysis package (for histograms and
regression analysis) for Excel. If you don’t see Data Analysis at the far right on your
toolbar under the Data dropdown, you will need to install this from Options/Add-Ins. It’s
quite straightforward, and you might need it for other classes – so why not do it now. You
may need your original Microsoft discs, although I haven’t seen that happen for many terms.
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” 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. Again, Apple users beware, your Mac version of Excel may 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.
S17BA301Assignment3.docx 2
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
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 RSquared
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
S17BA301Assignment3.docx 3
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.
S17BA301Assignment3.docx 4
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 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 chosen company as part of Assignment 1. Find the overall corporate revenue
(sales) data for the previous five full years, 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!
Include labels and titles, and adjust the format of the numbers if needed for comprehension. If it was
me, I’d probably include this chart as part of the final paper.

find the cost of your paper