dee

 

In this assignment, you will use Microsoft Excel  to work with a real-world administrative dataset. The purpose of this  assignment is to help you build practical spreadsheet skills that are  commonly used in organizational settings. In many public and nonprofit  organizations, Excel is used to organize records, review trends,  summarize performance, and support routine decision-making.

For this assignment, you will analyze a dataset related to code violations and complaints in Fort Worth, Texas. You will review the data, prepare it for analysis, create basic summaries, and write a short memo explaining what you found.

This assignment is designed to help you practice applied administrative analysis rather than advanced technical features.

Learning Objectives

By completing this assignment, you should be able to:

1. organize and review an administrative dataset in Excel;
    
2. clean and prepare data for analysis;
    
3. use basic Excel tools such as sorting, filtering, formulas, and PivotTables;
    
4. create a chart that communicates a meaningful finding;
    
5. explain your findings in clear professional language.
    

Instructions

Using the provided dataset, complete the following tasks:

Part 1: Review and Prepare the Data

Open the dataset in Excel and review its structure.

You should:

• examine the variables in the dataset;
   
• identify important fields such as complaint type, case status, date, officer, and address;
   
• check whether any important values are missing;
   
• format the data so that it is readable and usable;
   
• use sorting and filtering to explore the file.
   

Part 2: Clean and Organize the Data

Prepare a working version of the dataset.

At minimum, you should:

• make sure date fields are formatted correctly;
   
• check for missing or blank values in key variables;
   
• examine whether categories appear to be entered consistently;
   
• create at least one new variable that helps your analysis.
   

Examples of useful new variables include:

• year of case creation;
   
• month of case creation;
   
• case age based on dates;
   
• a simple open/closed indicator.
   

Part 3: Conduct Basic Analysis

Use Excel to answer the following questions:

1. What are the five most common complaint types?
    
2. How many cases are currently listed as open and how many are closed?
    
3. Which code officer appears most frequently?
    
4. In which year were the most cases created?
    
5. What pattern stands out most in the data?
    

You should use Excel tools such as formulas, filters, or PivotTables to support your answers.

Part 4: Create a PivotTable and a Chart

Create:

• one PivotTable
   
• one chart
   

Your PivotTable should summarize an important pattern in the data.

Your chart should clearly present one finding from your analysis.

A bar chart or column chart will usually work best.

Part 5: Write a Short Memo

Write a 300–500 word memo explaining your findings.

Your memo should address:

• what you found in the dataset;
   
• which issue or pattern appears most important;
   
• what your PivotTable or chart shows;
   
• what a manager or supervisor should pay attention to;
   
• one practical recommendation or takeaway.
   

Deliverables

Submit the following:

1. One Excel file that includes:
    
   • your working dataset,
      
   • at least one new variable,
      
   • one PivotTable,
      
   • one chart.
      
2. One short memo in Word or PDF format.