Computer Assignment 1the Data For This Problem Is Located In the Exc
Computer Assignment # 1 The data for this problem is located in the Excel file on Isidore under Computer Assignment #1 and it corresponds to Case Problem 1 (Pelican Stores) on p. in your text. Please answer the following questions within your Excel file. Please label your work so that it may be easily found within your file.
1. Using the pivot table tool, create a crosstabulation of type of customer (regular or promotional) versus net sales. Place this pivot table in a new worksheet labeled “Pivot Table.” Use good formatting practices when constructing this table as shown in class.
2. Based upon the summarized information from the pivot table you created in question 1, which type of customer tends to spend larger amounts on their purchases?
3. Based upon the summarized information from the pivot table you created in question 1, which type of customer comprises most of the sales for this sample?
4. Create a scatter diagram to explore the relationship between net sales and customer age. Put this scatter diagram in a new worksheet labeled “Scatter Diagram.” Before creating your scatter diagram, carefully consider which variable is most appropriate to put on the y-axis of your graph. Be sure to label your axes appropriately and create a meaningful title for your graph.
5. Based upon your scatter diagram from question 4, does there appear to be a relationship between the customer’s age and the net sales?
Paper For Above instruction
Analysis of Customer Data for Pelican Stores
Computer Assignment 1the Data For This Problem Is Located In the Exc
This assignment involves analyzing customer data from Pelican Stores using Excel tools such as pivot tables and scatter plots to uncover patterns related to customer spending behavior. The goal is to generate insights into customer purchase types and their relationship to net sales and age, providing a comprehensive understanding of the sales dynamics within the dataset.
Creating a Crosstabulation Using Pivot Tables
The initial step involves creating a pivot table to analyze the relationship between customer type—either
regular or promotional—and net sales. Using the data provided, the pivot table should be placed in a new worksheet labeled “Pivot Table” for clear organization. Good formatting practices should be employed to ensure the table is easily readable and professional. This involves proper titling, aligning data, and using consistent number formats. The pivot table should summarize total net sales for each customer type, enabling comparison of spending patterns between regular and promotional customers.
Analyzing Customer Spending Patterns
Based on the pivot table, the first question is to identify which customer type tends to spend larger amounts on their purchases. Typically, this can be determined by observing the total net sales figures for each customer type. It is expected that promotional customers might show different spending behaviors compared to regular customers, potentially indicating higher or lower average purchases. Analyzing the summarized data allows us to conclude which segment tends to contribute more significantly to overall sales based on total or average net sales.
Determining the Customer Segment Comprising Most Sales
The next analysis question aims to find out which customer group makes up the majority of sales in the sample. This involves looking at the total net sales figures for each customer type in the pivot table. The group with the higher total sales provides insights into which customer segment is more dominant in the revenue generation within this sample. Understanding this distribution helps the business identify key customer segments to target for marketing and sales strategies.
Exploring the Relationship Between Net Sales and Customer Age
The fourth step is to create a scatter diagram to investigate potential relationships between customer age and net sales. This scatter plot should be formulated carefully, with the most appropriate variable assigned to the y-axis—likely net sales—since it directly measures purchase amount, and age on the x-axis. Prior to plotting, consider the nature of the data to ensure meaningful insights. For example, placing age on the x-axis and net sales on the y-axis can reveal if older or younger customers tend to spend more.
In the scatter plot, axes should be clearly labeled, including units where applicable. The plot should also have a descriptive title that summarizes the relationship being examined, such as “Customer Age vs. Net Sales.” Good formatting practices are essential to make the chart clear and interpretable.
Interpreting the Relationship from the Scatter Diagram
The final question involves interpreting the scatter diagram to assess whether a relationship exists between customer age and net sales. Visual inspection can reveal patterns such as a positive or negative trend, clusters, or the absence of any discernible pattern. For example, if data points generally slope upward from left to right, this suggests a positive correlation—older customers tend to spend more. Conversely, a lack of pattern suggests no significant relationship.
Understanding this relationship assists the business in tailoring marketing strategies, such as targeting specific age groups to maximize sales or developing age-specific promotions.
Conclusion
This analysis leverages Excel’s pivot table and charting tools to provide valuable insights into customer behavior at Pelican Stores. By identifying the spending patterns of different customer types and examining how age influences purchase amounts, the business can better inform its marketing and sales strategies. Accurate and well-formatted data visualization and interpretation are key to extracting actionable insights from the dataset.
References
Chickering, D. M., & Hesse-Biber, S. N. (2016). The Practice of Qualitative Research. Sage Publications.
Evergreen, S. (2018). Data Visualization: A Practical Introduction. Harvard Business Review Press.
Few, S. (2012). Show Me the Numbers: Designing Tables and Graphs to Enlighten. Analytics Press.
Kirk, A. (2016). Data Visualization: A Handbook for Data Driven Design. Sage Publications.
Kirk, A. (2012). Data Visualisation: A Handbook for Data Driven Design. Sage Publications.
Roberts, J. (2017). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. Wiley.
Wainer, H. (2013). Visual Revelations: Graphical Tales of Discovery in Science and Law. Johns Hopkins University Press.
Yau, N. (2011). Data Points: Visualization That Means Something. Wiley.
Few, S. (2012). Show Me the Numbers: Designing Tables and Graphs to Enlighten. Analytics Press.
Hadley Wickham. (2016). ggplot2: Elegant Graphics for Data Analysis. Springer-Verlag.