Spreadsheet exam

profileabbe

  

This project requires that students take the initiative, make decisions, and question themselves, revising their ideas, adding to their ideas. Students are encouraged to consulting with others – reaching out to people that can help, as well as using Google, librarians, your other professors, this course’s professor, your colleagues sitting next to you.

Requirements:

You can use more than one projects/worksheets/workbooks if you desire.

1. One Word document with a memo introducing and explaining your project (see requirements for that below). 

a. See full requirements below.

b. Provide a clear, practical purpose and decision to be made using evidence provided by the outcome of the analysis, articulated in the memo and in the first documentation worksheet on the spreadsheet.

2. One spreadsheet with a particular name and structure (see below) containing the following work

a. Use at least TWO the following data analysis skills using the appropriate numbers: Count vs. sum vs. average vs. percent of column vs. percent of overall. 

i. Compare categories – percent differences - A, vs. B, vs. C

ii. Look at trends – increasing, decreasing, consistency, variance

iii. Do an analysis using measures of central tendency: Mean or median. Compare the mean/median between different categories.

1. CALCULATE PROPORTIONS WHEN DOING COMPARISONS: (A-B)/Average(A,B)

iv. Do an analysis using measures of distribution: Discuss the shape of the distribution, the skew, as compared to a normal distribution. Use one of the following techniques: 

1. You can also use a histogram or a column chart grouping values in a pivot table.

2. 5 number summary/box and whiskers chart. 

v. Do an analysis with variance, perhaps comparing variance among various categories. 

1. Option 1: Use 5 number summary (min, 1st quartile, median, 3rd quartile, max) or Box and Whiskers chart

2. Option 2: Look at the standard deviation. Relate the standard deviation to the mean (divide the SD by the Mean for the ratio, a high percentage means a lot of variance). Is there a lot of variance in relation to the mean or a little? What might that mean?

3. CALCULATE PROPORTIONS WHEN DOING COMPARISONS:
(A-B)/Average(A,B)

b. You are required to use at least 20 skills. These skills are listed in worksheet in the DataForMidtermSp20.xlsx file, which also has a lot of data that might be used for analysis. 

i. Provide a comment in a cell near where you carried out each skill with what skill was used and briefly why.

ii. You are required to do full formatting, whether they are skills in modules 5 or 8 or not.

iii. Do NOT include among the 20 skills anything covered in modules 1-4, not 5 and 8. 

c. You must include at least one chart and one table, with full formatting as we learned throughout this semester. The chart you choose must be optimal for the purpose, i.e., you should read the document on how to choose a chart posted with the assignment.

d. You can use examples of other projects but you must copy NOTHING directly. If you want to use a template or an idea from an existing worksheet, you must re-create anything you want to emulate MANUALLY in your worksheet. The instructor has ways of figuring out if you cut/paste or if you use another worksheet other than that which you create. DON’T DO IT.

3. You will be evaluated not only on whether these skills are used, but the integrity of your project. That means your project and the skills you use must make practical sense. Skills used arbitrarily and randomly for no apparent reason will not receive credit. 

There is a Canvas survey that is required that will bring you through the logical steps in creating this project.

Step by step instructions:

1. Excel file submitted

a. Create a new Excel file.

b. Save it as “LastNameFirstNameFinalProject"

c. In the first worksheet, put your name and explain the purpose of your spreadsheet – who might want to use it and for what purpose.  This will be very similar to what you write in your second paragraph in your email/memo (see below).  Then list the major elements of your solution – what the reader will see in the following worksheets. Walk them through it, so they understand what they are looking at and the purpose, step by step. Create the following table:

 

  

Worksheet


Table/Chart


Purpose


Example

 

Worksheet label A


Table "title of table 1"


Identify the highest rated x, y or z


"xxxx" is the highest rated 

  

Table "title of table 2"


Compare the average of x, y or z


"xxxx" is the highest averages

 

Worksheet label B


Chart "title of chart"


Line chart showing trends in xxx


"xxx" is trending up recently

d. In the next and following worksheets, provide your solution.

e. Each worksheet should have a meaningful label

f. Each worksheet should replicate that label in row 1, formatted in Title cell format.

2. Word file submitted.

a. Write the text of an email explaining what you have done to someone who might be interest. Note that you are attaching the spreadsheet for them to look at. 

b. Three paragraphs:

i. In the first paragraph, provide the reasons for why you chose your project(s), and why someone else might be interested.

1. Explain the added value of your spreadsheet – what information does it provide that would not otherwise be had.

2. Exactly what decision does the reader want to make, and how is this analysis going to be worth looking at, i.e., what is the added value of your project? Why would they regret not having this information?

ii. In the second paragraph, broadly explain the basic logic of your analysis, process of your use of Excel. 

1. Here, you are providing an overview of all major objects and their purpose. “This spreadsheet provides tools a, b, and c. Tool A provides the ability to analyze x…” 

2. Then, provide a step by step walk through with an example. For example, “If you were interested in (a particular thing), then you would look at the table or chart in this particular way, providing that information.”

iii. In the final paragraph, note that you are willing to meet about this Excel project or answer any questions. 

c. Use full professional formatting

i. Meaningful subject heading

ii. Salutation

iii. Closing 

iv. Single spaced, block paragraphs (space between paragraphs, NOT INDENTED).

d. Use business style:

i. Proper grammar and spelling.

ii. You are not writing in street language to a friend, but formal language to a business colleague. 

1. Use a thesaurus and a dictionary – do not use vague or imprecise terms.

iii. Neutral, 3rd person tone as much as possible – avoid first or second person language, e.g., “I”, and “you”.

iv. Logical hierarchy of writing: 

1. First phrase of every sentence is main point; first sentence in the paragraph is the main point.

2. First sentence in every paragraph introduces and summarizes the entire paragraph.

v. Generally, use short, precise, clearly interrelated sentences. 

vi. Do not submit a first draft – rewrite at least 3 times. 

For example: 

Poor writing: “I've noticed that you were looking for places to live that would best fit your salary. Please look at the attached file in this email that may help you.”

Problems: 

· Contractions are too informal.

· Please re-write minimizing first and second person language. Business communication is neutral, 3rd person, impersonal as much as possible.

· Reflect on how specific and clear you are. What does “best fit for your salary” mean? What is a “fit” for a salary? Use a dictionary and thesaurus to find more specific terms.

Better: 

“This email is written regarding your interest in choosing a place to live after graduation. To that end, I provide attached an Excel file that provides critical information for that decision. The Excel analysis considers two major concerns: The salary you can expect different areas and the cost of living in that area, in terms of housing and the cost of living index.”

Example of formatting: Obviously, you would not be sending a real email with an attachment! You are submitting a Word document in this (email) format.

                   

Ideas on projects 

1. Think of a way to use Excel, or multiple ways of using Excel.

a. Data will be posted to the assignment as well as a list of sources of data.

b. Google – ‘data analytics’, ‘data analysis’ for some ideas.

c. Go through the end of chapter review projects in the textbook.

d. Google your interests and see how people are thinking about these ideas.

e. Gather data from the internet about a topic you are curious about and answer a question. Examples: Data on movies, companies, celebrities, books, albums, works of art, artists, TV shows, cable channels, homes, cars, computers, phones, countries, states, cities, foods, wines, universities, professions, hospitals, airports, airplanes, restaurants…

f. Call people you know, or simply call a professional that might be familiar with your idea and ask them for guidance -most people are willing to help.

g. Ask people who run an organization or business and ask how you can help them solve a problem with Excel.

2. Think through what that might look like in Excel – what information you need, what form it would take in Excel, what functions or skills you might need to use.

3. Find the information you need, and experiment with the best way of utilizing that information, presenting that information in order to address your issue.

4. Consult with colleagues in this class.

5. Finally, CONSULT WITH THE INSTRUCTOR.

    • 4 years ago
    • 15
    Answer(1)

    Purchase the answer to view it

    blurred-text
    NOT RATED
    • attachment
      AnswerDocument.docx