Quantitative Analysis for Cyber Security
Order Description
Project Description:
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!
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.
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 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.
