You should now have Tab 2 complete: Excel Summary Stats. Next, you’ll create charts and a histogram for Tabs 3 and 4.
Step 7: Create Charts and a Histogram
Where would we be without the ability to view data in charts? It is sometimes easier to grasp context of data if we can see it captured in an image. In this step, you will work with data to create charts, adding a tab for charts, and another for a histogram
It is often helpful to view and interpret analytical results when they are presented visually. Graphs and charts help readers digest and interpret information more quickly, consistent with the familiar adage “a picture is worth a thousand words.” Let’s see what we can see in your data analysis.
Create the following graphs in your workbook on a separate tab named Graphs_Charts:
Create separate pie charts that show the percentage of employees by a) gender, b) education level, and c) marital status. Explore pie chart formats.
Create separate bar charts that show the a) number of employees by race, and b) the number of employee per state.
Create a line graph for the sales summary provided.
Create a histogram that shows the number of employees in incremental salary ranges of $10,000. Here, you want to show how many employees are making 0-$10,000, $10,999-$20,000, up to $210,000. This involves counting how many for each “salary bucket,” creating what is called a frequency distribution table and histogram. Histograms seem hard, but mastering how to visualize the frequency of events is so helpful in analysis!
In this step, you will build Tab 3: Graphs—Charts and Tab 4: Histogram. After you complete these tabs, you’ll be ready to sort the data
Step 8: Copy and Sort the Data
You’ve accomplished a lot with your data set, summary stats, charts, and histograms. Another skill you’ll need to be able to do is sort data in an Excel worksheet for reporting purposes. You’ll copy and sort the data.. This is a good skill that applies to any Excel application.
Copy and Sort Data
Many times we want to sort data on an Excel worksheet for reporting purposes. Let’s see what other perspectives the functions of sorting and subtotaling yield.
Begin by following the steps in the “How to Copy Excel 2010 Sheet to Another Sheet” provided below. This will allow you to retain your work for Steps 2 through 7. Place the sheet at the end of the workbook and title the tab “Sorted Data.”
Delete all rows containing Section 2 and Section 3 work. Be sure to leave the section in cells F437:I422, as this section is referenced for the Vlookup function populating the region; otherwise, you will get a #N/A or #REF! Error in the column for region.
Apply the ability to sort data on each column of the spreadsheet, so that you can sort by employee #, hire date, role, etc.
Experiment with the filter funnel, sorting the data by various columns. For example, try sorting by employee number from smallest to largest. Try sorting by role in ascending order (A-Z).
Sort the spreadsheet by region.
Employ the subtotal feature to subtotal the salary for each region, with a grand total for the company.
Format the entire spreadsheet to print, so that the columns fit on the pages, and Row 1 repeats on each page.
Step 9: Conduct Quantitative Analysis
In this step, your hard work bears fruit. What does it all mean? Think back to your boss’s reasons for tasking you with this project. Bring your powers of analysis to bear to determine what the data may be telling you. Apply your quantitative reasoning skills by answering the questions provided in the resource and writing a short essay.
After you answer the questions, your short essay should include:
a one-paragraph narrative summary of your findings, describing patterns of interest
an explanation of the potential relevance of such patterns
a description of how you would investigate further to determine if your results could be perceived as good or bad for the company.
Prepare your response in this workbook. Create a tab for Quantitative Analysis, create a text box, and paste your answers to above questions and your essay in it. Move the tab to the first tab position.
Apply Quantitative Reasoning
Now that you have completed your analysis, think about the patterns you’ve seen in the workforce and answer these questions.
1. From the created histogram (Tab 4), it appears that a large share of employees have a salary between $31,000 $40,000 or $51,000 $60,000. This may indicate reasonable promotion rate for new employees. Is this distribution unimodal or bimodal? Explain.
2. The line chart, as detailed in your “Graph Charts” Excel spreadsheet (Tab 3), shows sales increasing over the years from 1999 to 2014. In 2015, sales slightly decreased to under $4,000,000. Investors want to know what the projected sales will be in 2017. One method for determining this value is to calculate the average annual growth rate per year, based on historical trends. For example, if sales were $200 last year and $500 this year, the average annual growth rate would be calculated as ($200+$500)/2. Calculate the average annual growth rate for 1999
– 2015 and projected sales for 2017.
3. The standard deviation provides insight into the distribution of values around the mean. If the standard deviation is small, the more narrow the range between the lowest and highest value. That is, values will cluster close to the mean. From your descriptive statistics, describe your standard deviations. What does this tell you about the variables?
4. The company has a keen interest in the educational, race, and gender makeup of its workforce. Its emphasis is on a diverse, dynamic workforce. From your “Graph Charts” spreadsheet, describe your pie chart findings for these characteristics of the workforce. Describe how you would determine how the company was meeting expectations on these characteristics.
5. The company is conducting an analysis on how many positions to create to keep up with demand. Specifically, it wants to identify a rough estimate of the number of positions per job title. From your Excel chart, identify the mode of the job title distribution. Describe your findings.
Step 10: Submit Your Completed Workbook and Analysis
You’re now ready to submit your workbook and analysis. Review the requirements for the final deliverable to be sure you have:
Excel Workbook with Six Tabs
Tab 1: Data—completed data sheet (Steps 1–6 above)
Tab 2: Excel Summary Stats (Step 6)
Tab 3: Graphs—Charts (Step 7)
Tab 4: Histogram (Step 7)
Tab 5: Sorted Data (Step 8)
Quantitative Analysis (Step 9; see detail below and move to first position upon completion.)
Answers to Questions and Short Essay
Prepare your response in this workbook. Create a tab for Quantitative Analysis, create a text box, and paste your answers to the questions and your essay in it. Move the Quantitative Analysis tab to the first tab position.
Make sure the following tabs are included in your final workbook:
Excel Summary Stats
Format to Be Printed