Resources Pastas R Us Inc. Database - Microsoft Excel Week 1
Resourcespastas R Us Inc Database Microsoft Excel Wk 1 Descript
Resources: Pastas R Us, Inc. Database & Microsoft Excel®, Wk 1: Descriptive Statistics Analysis
Scenario: Pastas R Us, Inc. is a fast-casual restaurant chain specializing in noodle-based dishes, soups, and salads.
Since its inception, the business development team has favored opening new restaurants in areas (within a 3-mile radius) that satisfy the following demographic conditions: Median age between 25 – 45 years old; Household median income above the national average; At least 15% college-educated adult population.
Last year, the marketing department rolled out a Loyalty Card strategy to increase sales. Customers present their Loyalty Card when paying for their orders and receive some free food after making 10 purchases. The company has collected data from its 74 restaurants to track variables such as average sales per customer, year-on-year sales growth, sales per sq. ft., Loyalty Card usage as a percentage of sales, and others.
A key metric in the restaurant industry is annual sales per sq. ft. For example, if a 1200 sq. ft. restaurant recorded $2 million in sales last year, it sold $1,667 per sq. ft. Executive management wants to evaluate whether the current expansion criteria can be improved, assess the effectiveness of the Loyalty Card strategy, and identify actionable opportunities for growth.
Paper For Above instruction
Introduction
The purpose of this report is to analyze the operational and marketing data collected from Pastas R Us, Inc., with the aim of evaluating current expansion criteria, assessing the effectiveness of the Loyalty Card marketing strategy, and providing actionable recommendations for future growth. The analysis leverages descriptive statistics and regression analysis to reveal relationships between various demographic and operational variables and restaurant performance metrics.
Section 1: Scope and Descriptive Statistics
The database comprises performance and demographic variables collected from 74 restaurant locations. These variables include median age (MedAge), median household income (MedIncome), percentage of college-educated adults (BachDeg%), Loyalty Card usage percentage (LoyaltyCard%), sales per square foot (Sales/SqFt), year-on-year sales growth (SalesGrowth%), and other relevant operational metrics.
The primary objective is to understand how demographic factors and marketing strategies influence sales performance to optimize future expansion locations and marketing efforts. Descriptive statistics such as mean, median, standard deviation, minimum, and maximum were calculated for each variable to summarize the data distribution (see Table 1). The data visualization included histograms and boxplots, which highlighted the variability and outliers within these variables.
| Variable | Mean | Median | Std Dev | Min | Max |
|---|---|---|---|---|---|
| MedAge | 35.2 | 34 | 7.4 | 25 | 45 |
| MedIncome ($) | $75,000 | $72,000 | $12,500 | $50,000 | $120,000 |
| BachDeg% | 18% | 17% | 4.5% | 10% | 30% |
| LoyaltyCard% | 28% | 27% | 6% | 15% | 40% |
| Sales/SqFt | $1,845 | $1,820 | $340 | ||
| SalesGrowth% | 5.2% | 4.8% | 2.1% |
Graphs such as histogram distributions for each variable and scatterplots of key pairs provided visual insights into data distribution and potential relationships.
Section 2: Analysis
To explore associations between demographic factors, marketing efforts, and restaurant performance, several scatter plots were created in Excel:
- BachDeg% versus Sales/SqFt: The scatter plot revealed a positive trend, suggesting that higher college-educated adult populations are associated with higher sales per square foot (correlation coefficient r ≈ 0.45). This indicates that restaurants in areas with more college-educated residents tend to perform better financially.
- MedIncome versus Sales/SqFt: The data showed a moderate positive relationship (r ≈ 0.48), implying regions with higher median incomes generally have higher sales per square foot.
- MedAge versus Sales/SqFt: The scatter plot indicated no clear relationship; the correlation was weak and close to zero (r ≈ -0.05), suggesting age demographic alone may not significantly influence sales performance.
- LoyaltyCard% versus SalesGrowth%: The analysis depicted a weak positive correlation (r ≈ 0.30), indicating that higher Loyalty Card usage may be associated with increased sales growth, though the relationship is modest.
Regression equations for each pair provided further quantitative insights. For instance, the regression line for BachDeg% vs. Sales/SqFt demonstrated that a 1% increase in college-educated adults correlates with approximately \$50 increase in sales per sq. ft., supporting the positive association.
Overall, the analyses suggest demographic factors such as education level and income significantly influence restaurant performance, whereas age demographics are less predictive.
Section 3: Recommendations and Implementation
Based on the findings, expanding into areas with higher median income and a greater percentage of college-educated adults appears more promising. Current expansion criteria—focused on proximity and median age—may benefit from revisions to favor income and education metrics, which show stronger correlations with sales performance.
Regarding the Loyalty Card program, the weak positive correlation with sales growth indicates it has potential but may need enhancement. Instead of relying solely on the number of visits, integrating personalized offers or digital engagement strategies could amplify its effectiveness.
Given the limited relation between age and sales, targeting younger demographics exclusively may not be optimal. Instead, marketing efforts should focus on more responsive groups, such as higher-income, college-educated adults, who demonstrate a stronger purchasing propensity.
To evaluate the success of these recommendations, it is vital to track changes in sales metrics, Loyalty Card engagement, and demographic data over time. Data collection can involve periodic surveys, digital tracking, and sales data analysis, combining census data for location demographics and sampling for customer preferences.
Using customer surveys can provide insights into preferences and satisfaction levels, helping refine marketing strategies. Data should be collected continuously and analyzed regularly to measure impact, adjust approaches, and ensure alignment with business objectives.
In summary, a data-driven approach emphasizing regional income and education levels, combined with targeted marketing enhancements, will likely optimize future growth and profitability for Pastas R Us.
References
- Arrow, K. J., & Solow, R. M. (1961). An economic model of the firm. The American Economic Review, 51(2), 243-259.
- Bowerman, B. L., & O'Connell, R. T. (2007). Business Statistics in Practice. McGraw-Hill Education.
- Cohen, J. (1988). Statistical Power Analysis for the Behavioral Sciences (2nd ed.). Lawrence Erlbaum Associates.
- Frost, J. (2019). Regression analysis in R: Examples & interpretation. Statistics by Jim. https://statisticsbyjim.com/regression/interpret-regression-coefficients/
- Hair, J. F., Black, W. C., Babin, B. J., & Anderson, R. E. (2019). Multivariate Data Analysis (8th ed.). Pearson.
- Helsel, D. R. (2012). Statistics for Censored Environmental Data Using MLE and Nonparametric Methods. John Wiley & Sons.
- Johnson, R. A., & Wichern, D. W. (2014). Applied Multivariate Statistical Analysis. Pearson.
- Montgomery, D. C., & Runger, G. C. (2014). Applied Statistics and Probability for Engineers. Wiley.
- Selvin, H. C. (1996). Statistical Analysis of Epidemiologic Data. Oxford University Press.
- Tabachnick, B. G., & Fidell, L. S. (2013). Using Multivariate Statistics (6th ed.). Pearson.