Technology plays an essential role in supporting decision-making. This major project will focus on supporting decision-making by exploring some of the features within Microsoft Excel, including the basic filter, custom filter, conditional formatting, as well as pivot tables. Understanding these tools can significantly enhance your ability to analyze and interpret data effectively.
Let’s assume that you work for a small business and have been asked to perform the following tasks to aid in various decisions: These tasks are designed to simulate real-world scenarios where data-driven decisions are critical for business success.
Show all information for only customers who live in the North region. This task helps in understanding regional customer distribution.
Show all information for only customers who (a) live in the North region, (b) own their homes, and (c) have only one household member. This exercise demonstrates how to apply multiple filters for precise data segmentation.
Show all information for customers who have at least 4 household members. This task focuses on identifying larger households, which may have different purchasing behaviors.
Show all information for customers who (a) have spent less than
20
20or(b)morethan100. This helps in analyzing customer spending patterns at both ends of the spectrum.
Show all information for all customers highlighting those customers who have spent more than $100. Conditional formatting is a powerful tool to visually emphasize key data points.
Provide a two-dimensional table that counts the number of customers by the categories of REGION and RENT VS. OWN. Pivot tables are essential for summarizing and analyzing large datasets.
Provide a two-dimensional table that both (a) counts the number of customers and (b) sums the TOTAL PURCHASES of customers by the categories of REGION and RENT VS. OWN. This task combines counting and summing functions to provide deeper insights.
Provide a three-dimensional table that counts the number of customers by the categories of REGION, RENT VS. OWN, and NUM HOUSEHOLD. This advanced task demonstrates the use of multi-dimensional data analysis.
First, read the attached Extended Learning Module D: Decision Analysis with Spreadsheet Software document. Then, create the requested worksheet summaries listed above. You will need to use Microsoft Excel to complete this assignment. Familiarizing yourself with the module will ensure a smoother workflow and better results.
Microsoft Excel is available at no cost to Regent students via the Office 365 application. The XLMD_Customer.xlsx data file should be used to complete the assignment. Ensure you have access to the necessary tools and files before starting the project.
Once you have completed each request, please submit one Excel document, including 8 worksheets illustrating each exercise. Proper organization and labeling of worksheets are crucial for clarity and grading purposes.
BUSN 420 Major Project Grading Rubric
Basic Filter Possible Points Actual Points
Show all information for only customers who live in the North region. 5
Show all information for only customers who (a) live in the North region, (b) own their homes, and (c) have only one household member. 10
Comments: 15
Custom Filter Possible Points Actual Points
Show all information for customers who have at least 4 household members. 10
Show all information for customers who (a) have spent less than
20
o
r
(
b
)
m
o
r
e
t
h
a
n
20or(b)morethan100. 15
Comments: 25
Conditional Formatting Possible Points Actual Points
Show all information for all customers highlighting those customers who have spent more than $100. 10
Comments: 10
Pivot Tables Possible Points Actual Points
Provide a two-dimensional table that counts the number of customers by the categories of REGION and RENT vs. OWN. 15
Provide a two-dimensional table that both (a) counts the number of customers and (b) sums the TOTAL PURCHASES of customers by the categories of REGION and RENT vs. OWN. 15
“Enhancing Decision-Making Through Advanced Excel Techniques: A Comprehensive Guide for Business Analysis”
Microsoft Excel, decision-making, data analysis, pivot tables, conditional formatting, business analytics
