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.
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.
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.
Our Service Charter
-
Excellent Quality / 100% Plagiarism-Free
We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers. -
Free Revisions
We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed. -
Confidentiality / 100% No Disclosure
We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service. -
Money Back Guarantee
If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit. -
24/7 Customer Support
We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.