please look at the instruction the name of the company is amazon

Part 1

Questions

1) Now that you have written a Situation Analysis (context & background on your company),

you should have a good overall understanding of your chosen company. Now, using that

information, identify symptoms (gaps) of deeper problems that might be facing the company.

Try to find two symptoms (gaps), and write them down as part of this assignment. Be

specific and use quantitative metrics where possible. The symptoms that you list should

show a "gap" as discussed in class. A "gap" is the difference between the desired and the

actual state. 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!

2) 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 previous point, think about why they did not meet their profit goals. Perhaps

a new product was not as successful as planned. Note one symptom will most likely 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?".

3) Develop and include a fishbone diagram for one of the symptoms uncovered in #1 above for

your company. The fishbone diagram should include 3 to 4 main causes of the symptom and

then sub-causes for each main cause (similar to what we did in class). The symptom should

go at the “head” of the fishbone diagram. This will be the visual mapping of one of the root

cause analysis you conducted above in #2.

4) If you’ve uncovered symptoms that are completely unrelated in 2) above, pick one as a focus

for your paper. Ultimately, the goal is to uncover one core problem that you will focus on

solving for your research paper. The last part of this assignment should include a clearly

defined and concise problem statement (consisting of 1 to 3 sentences) as discussed in class

which highlights the one core problem you will be using for your paper.

5) For this assignment, you need to make sure to use proper APA citation format (both for in-

text citation and reference page). This is practice for your final paper. I will be extremely

critical of your formatting for this assignment – so follow the APA guidelines.

Part 2

Format/Requirements

• Submit this assignment on the Canvas submission folder by the due date.

• You must attach a word document which includes your answers to each of the 5

questions. Make sure to follow all instructions when answering each question.

• Use Times New Roman, 12-point font, 1-inch margins

• Answer questions #1 to 5.

.

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 do this assignment in order 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. I

suggest that you don’t wait until the last minute to complete this assignment.

1) Download the file “Fun With Excel Raw Data” the Canvas course website.

2) Open the file with Microsoft Excel and follow the instructions found for each of the

questions 1 to 5.

3) Submit the following on the Canvas submission folder: 1) a word document with your

answers to questions 1 to 5 and 2) the excel files used to answer questions 1 through 5.

In the word document, ensure that you provide specific answers to each question.

For example, for question #1, I want the answer to the question: “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?” In order to answer this question, you must include

the multiple R number and its interpretation, significance of F information and its

interpretation, and the X-Y graph in the word document in your answer to question 1.

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.

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 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. 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 and use the X-Y graph (R multiple and sig of F)

generated in this file to answer this question. Moving forward, as you answer questions 2

through 5, make sure not to make any changes to your saved 301Regression file as this will

change your X-Y graph.

In the word document, provide me with the answer to the above question (ie. Does the

amount of giving tend to increase as years of involvement with the organization increases)

and give me the basic regression stats (showing R multiple and Significance of F) and the

XY chart and their interpretation in order to support your answer.

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

3

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. Save the file as 301AutoSum.

In your word document, give me the answers to question 2 (all the averages). In addition,

provide a screenshot (or copy/paste the table) of the row/rows of subtotals that showed up

when you followed the above instructions.

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.

Provide the answers to this question on your word document. Provide a screenshot from

your excel spreadsheet or copy/paste the table from excel that shows me you have followed

the above instructions.

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.

In your word document, provide the 2 answers as well as the amounts for each of the 2 most

heavily supported interests by corporate donors. Copy and paste the Pivot Chart and the

Pivot Table showing the Southern Region breakdown, including the Corporate donor type.

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.

Include this chart (copy and paste) in your word document as your answer to question 5. Also

attach this excel file on Canvas