Lab 1 Homework Assignment

🎉 Congratulations! You've just been hired as the newest Data Analyst at UMD Bulldog LLC, a thriving local electronics retailer in Duluth, Minnesota.

It's Monday morning, and you're walking into the modern office building on Superior Street for your first day. The receptionist greets you warmly and directs you to the second floor where the data analytics team works. As you settle into your new workspace - a standing desk with dual monitors overlooking Lake Superior - you can't help but feel excited about the opportunities ahead.

Your manager, Sarah Chen, approaches with a coffee and a welcoming smile. "Ready for your first challenge?" she asks. "I've got a critical assignment that will not only test your analytical skills but also determine how quickly you can make a real impact here at UMD Bulldog LLC."

Your mission, should you choose to accept it, is to: Become a data detective and uncover the critical quality issues that are costing the company thousands of dollars monthly. You'll analyze real customer, employee, and inventory data to identify inconsistencies, evaluate the financial impact of data problems, compare our current Excel limitations with professional database solutions, and apply your detective skills to uncover hidden patterns in our messy data.

Sarah continues: "This isn't just an academic exercise - your findings will directly influence major business decisions, potentially save jobs by improving our operations, and help us serve our 1,000+ loyal customers better. Are you ready to dive in?"

As you settle into your new workspace, Sarah Chen, your manager, gives you the company background. UMD Bulldog LLC is a beloved local electronics retailer that has been serving the Duluth community since 2015. What started as owner Mike Bulldog's dream of bringing quality tech to northern Minnesota has grown into your new workplace - a thriving business with 3 bustling locations across Duluth and Superior, 45+ dedicated employees (including you!), 500+ cutting-edge products, 1,000+ loyal customers, and over $2M in annual revenue.

"We've been using a shared Excel workbook system since our founding," Sarah explains. "It worked perfectly when Mike started the company, but now it's creating serious operational challenges as we've expanded rapidly across northern Minnesota."

Sarah leans forward with a concerned expression:

"Here's the situation we're facing. Our Excel-based system worked fine when we were small, but now it's costing us big time. 23% of our customer orders contain data errors causing shipping delays. We're losing $15,000 every month to inventory tracking failures. Our staff wastes 8 hours every week just fixing data mistakes, and customer satisfaction has dropped 15% due to order fulfillment errors."

She continues: "I need you to dig into our historical data and find out exactly what's going wrong. The CEO is breathing down my neck about these issues. We have duplicate customer records causing billing chaos, inventory discrepancies leading to stockouts and overordering, and our systems just can't handle our growth anymore."

Sarah turns to her laptop and opens three Excel files, explaining your mission as she clicks through each one:

"These are exports from our main systems - Sales, HR, and Inventory data from the past year. I know there are problems in here, but I need you to find them, quantify them, and help me build a case for upgrading our systems. The board meeting is next week, and I need concrete evidence of what these data issues are costing us."

"Your job is to be a data detective," Sarah emphasizes as she scrolls through the messy data. "Look for inconsistencies, duplicates, missing information, and anything else that doesn't look right. Document everything you find - we need proof that our current approach isn't working. Your analysis will directly influence a major technology investment decision that could transform how we operate."

As Sarah clicks through each file, you can see the frustration in her eyes. Each Excel file represents a different department's data management nightmare, and the problems are immediately apparent.

Sales_Data.xlsx
"This file contains customer orders from the past year," Sarah explains. "But look at this mess - we have duplicate customer entries, inconsistent pricing, and missing information scattered throughout. Our shipping department spends hours every week trying to sort out which orders belong to which customers."

HR_Data.xlsx
"Our HR system is even worse," she continues, opening the second file. "We have employee records with spelling variations, inconsistent date formats, and ID conflicts across systems. Payroll processing has become a nightmare, and we can't even get accurate headcount reports."

Inventory_Data.xlsx
"And this inventory file," Sarah says with a sigh, "shows products with multiple price points, items that don't exist in our system being sold, and stock levels that never match reality. We've had to do emergency inventory counts multiple times this quarter because our numbers were so far off."

Sarah hands you a USB drive with the data files:

"Here are the actual files you'll need for your investigation. Each file is available in both Excel (.xlsx) and CSV (.csv) formats - use whichever format works best with your analysis tools."

Sarah's final reminder as she closes the laptop:

"Remember, this is real company data affecting real people's jobs and livelihoods. Treat this information with the same confidentiality and care you would in any professional data analyst role. Your findings will directly influence major business decisions and potentially save jobs by improving our operations."

Sarah gives you a quick overview of the key Excel tools you'll need:

"Document the exact cell references and row numbers where you find problems - we'll need specific locations for validation and the board presentation," Sarah reminds you.

Sarah emphasizes the importance of your investigation:

"I need concrete evidence for the board meeting next week. For each question you answer, document your findings with specific cell references and row numbers. Your analysis will determine whether UMD Bulldog LLC invests in a new database system or continues struggling with our current Excel approach. The future of our data management depends on what you discover today."

Before you begin your investigation, Sarah offers one final tip:

"Use Excel's Find function (Ctrl+F) to search for exact matches, but make sure 'Match entire cell contents' is unchecked to find partial matches. This will help you discover variations in names and spellings. When you find problems, note the exact cell reference (like A15 or C42) and row number for your documentation. Remember - every error you find represents real money lost by UMD Bulldog LLC, so be thorough in your detective work."

As you wrap up your investigation, Sarah explains what she needs for the board meeting:

"Your analysis will directly influence a major technology investment decision that could save UMD Bulldog LLC thousands of dollars monthly. I need you to prepare a comprehensive professional report that I can present to the board with confidence."

Your deliverables must include:

• Complete Investigation Report - Answer all 20 questions with concrete evidence and business impact analysis that clearly demonstrates the scope of our data problems.

• Evidence Documentation - Provide specific cell references and row numbers for at least 5 data problems you discover (e.g., "Row 15, Column B contains duplicate customer 'John Smith'"). Include the file name, exact location, and description of each issue for validation purposes.

• Executive Summary - Write a 200-word summary using your own words and voice to help you prepare in case you are asked to speak your opinion during the board meeting. This should reflect your personal analysis and recommendations based on what you discovered. (You should not use AI for writing this part.)

Critical Deadline: Submit your report as LastName_FirstName_UMDBulldog_DataAnalysis.pdf by [Insert Date]. The board meeting depends on your findings, and late submissions will impact important company decisions about our future technology infrastructure.

Sarah explains how your performance will be evaluated:

"Your work will be evaluated using the same criteria I use for real data analyst performance reviews here at UMD Bulldog LLC. This isn't just an academic exercise - I'm assessing you as if you were a permanent member of our analytics team."

Important Note: If you are selected to speak about your findings during the board meeting simulation, 75% of your grade will be based on your demonstrated understanding of the materials and your ability to articulate your analysis professionally.

As Sarah walks you to your desk, she offers some final guidance:

"Don't hesitate to reach out if you need help. This investigation is critical to our company's future, and I want you to succeed in your first major assignment. Remember, you're not just completing homework - you're solving real business problems that affect real people."

She then shares some practical Excel shortcuts that will save you time:

Use Ctrl+Home to jump to the beginning of your data, Ctrl+End to jump to the last data cell, F2 to edit cells directly, and Alt+Enter to create line breaks within cells. Most importantly, remember that accuracy is more important than speed in data analysis - take your time to get it right.

Before leaving you to begin, Sarah emphasizes the key principles of professional data analysis:

Never modify the original data files - always work with copies. Don't assume the first occurrence of data is correct - duplicates and errors can appear anywhere. Pay close attention to subtle spelling differences that might indicate data entry problems. Cross-reference information across all three files to find inconsistencies. Document your methodology as you work, and most importantly, think like a detective - every anomaly in the data tells a story about what's going wrong in our business operations.