Resources: Pastas R Us, Inc. Database & Microsoft Excel, Wk 1: Descriptive Statistics Analysis Assignment
Purpose
This assignment is intended to help you learn how to apply statistical methods when analyzing operational data, evaluating the performance of current marketing strategies, and recommending actionable business decisions. This is an opportunity to build critical-thinking and problem-solving skills within the context of data analysis and interpretation. You'll gain a first-hand understanding of how data analytics supports decision-making and adds value to an organization.
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:
oMedian age between 25 - 45 years old
oHousehold median income above national average
oAt least 15% college educated adult population
Last year, the marketing department rolled out a Loyalty Card strategy to increase sales. Under this program, 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 important 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 of financial performance 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, then it sold $1,667 per sq. ft.
Executive management wants to know whether the current expansion criteria can be improved. They want to evaluate the effectiveness of the Loyalty Card marketing strategy and identify feasible, actionable opportunities for improvement. As a member of the analytics department, you've been assigned the responsibility of conducting a thorough statistical analysis of the company's available database to answer executive management's questions.
Report:
Write a 750 word statistical report that includes the following sections:
oSection 1: Scope and descriptive statistics
oSection 2: Analysis
oSection 3: Recommendations and Implementation
Section 1 - Scope and descriptive statistics
oState the report's objective.
oDiscuss the nature of the current database. What variables were analyzed?
oSummarize your descriptive statistics findings from Excel. Use a table and insert appropriate graphs.
Section 2 - Analysis
oUsing Excel, create scatter plots and display the regression equations for the following pairs of variables:
o"BachDeg%" versus "Sales/SqFt"
o"MedIncome" versus "Sales/SqFt"
o"MedAge" versus "Sales/SqFt"
o"LoyaltyCard(%)" versus "SalesGrowth(%)"
oIn your report, include the scatter plots. For each scatter plot, designate the type of relationship observed (increasing/positive, decreasingegative, or no relationship) and determine what you can conclude from these relationships.
Section 3: Recommendations and implementation
oBased on your findings above, assess which expansion criteria seem to be more effective.Could any expansion criterion be changed or eliminated? If so, which one and why?
oBased on your findings above, does it appear as if the Loyalty Card is positively correlated with sales growth? Would you recommend changing this marketing strategy?
oBased on your previous findings, recommend marketing positioning that targets a specific demographic. (Hint: Are younger people patronizing the restaurants more than older people?)
oIndicate what information should be collected to track and evaluate the effectiveness of your recommendations. How can this data be collected? (Hint: Would you use survey/samples or census?)
Cite references to support your assignment.
Format your citations according to APA guidelines.
#_x000D_# Pastas R Us, Inc. Database (n = 74 restaurants) Per Person Sales Growth Over Loyalty Card % of Annual Sales Median HH Income Square Feet Average Spending Previous Year (%) Net Sales Per Sq Ft (3 Miles) Obs 1 2 3 4 5 6 7 8 SqFt 2354 2604 2453 2340 2500 2806 2250 2400 Sales/Person 6.81 7.57 6.89 7.13 7.04 6.93 7.11 7.13 SalesGrowth% -8.31 -4.01 -3.94 -3.39 -3.30 -1.94 -0.77 -0.37 LoyaltyCard% 2.07 2.54 1.66 2.06 2.48 2.96 2.28 2.34 Sales/SqFt 701.97 209.93 364.92 443.04 399.20 264.64 571.59 642.25 MedIncome 45177 51888 51379 66081 50999 41562 44196 50975 MedAge 34.4 41.2 40.3 35.4 31.5 36.3 35.1 37.6 BachDeg% 31 20 24 29 18 30 14 33 Sales 1652437 546658 895149 1036714 998000 742580 1286078 1541400 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 2709 1990 2392 2408 2627 2500 1986 2500 2668 2517 1251 2998 2625 2300 2761 2764 2430 2154 2400 2430 2549 2500 3653 2440 2600 2160 2800 2757 2450 2400 2270 2800 2520 2487 2629 3200 2335 2500 2449 6.58 6.77 6.66 7.03 7.03 7.00 7.38 7.18 7.35 6.95 7.02 6.85 7.16 6.99 7.28 7.07 7.05 6.54 6.70 6.91 7.58 7.03 6.84 6.94 7.07 7.00 7.08 6.75 6.81 7.64 6.62 6.76 7.11 7.05 6.90 7.17 6.75 7.45 7.00 -0.25 -0.17 0.47 0.55 0.77 1.92 2.05 2.12 2.84 2.88 3.96 4.04 4.05 4.05 4.24 4.58 5.09 5.14 5.48 5.86 5.91 5.98 6.08 6.08 6.13 6.27 6.57 6.90 6.94 7.12 7.39 7.67 7.91 8.08 8.27 8.54 8.58 8.72 8.75 2.20 2.34 2.09 2.47 2.04 2.02 2.01 2.64 2.22 2.07 1.94 2.17 0.72 2.00 1.81 2.13 2.50 2.63 1.95 2.04 1.41 2.05 2.13 2.08 2.73 1.95 2.04 1.62 1.95 1.64 1.78 2.23 2.15 2.83 2.37 3.07 2.19 1.28 1.76 461.45 638.82 484.38 581.09 267.71 572.84 586.48 368.73 351.47 458.24 987.12 357.45 405.77 680.80 368.02 303.95 393.90 562.12 494.88 310.07 373.46 235.81 413.08 625.22 274.30 542.62 178.56 375.33 329.09 297.37 323.17 468.84 352.57 380.34 398.12 312.15 452.16 698.64 367.19 72808 79070 78497 41245 33003 90988 37950 45206 79312 37345 46226 70024 54982 54932 34097 46593 51893 88162 89016 114353 75366 48163 49956 45990 45723 43800 68711 65150 39329 63657 67099 75151 93876 79701 77115 52766 32929 87863 73752 34.9 34.8 36.2 32.2 30.9 37.7 34.3 32.4 32.1 31.4 30.4 33.9 35.6 35.9 33.6 37.9 40.6 37.7 36.4 40.9 35.0 26.4 37.1 30.3 31.3 29.6 32.9 40.7 29.3 37.3 39.8 33.9 35.0 35.0 35.9 33.0 30.9 38.5 40.5 28 29 39 23 22 37 24 17 37 22 36 34 26 20 20 26 21 37 34 34 30 16 28 36 18 36 18 24 22 29 25 28 40 39 30 17 22 29 19 1250068 1271252 1158637 1399265 703274 1432100 1164749 921825 937722 1153390 1234887 1071635 1065146 1565840 1016103 840118 957177 1210806 1187712 753470 951950 589525 1508981 1525537 713180 1172059 499968 1034785 806271 713688 733596 1312752 888476 945906 1046657 998880 1055794 1746600 899248 _x000D_ Saudi Aramco: Company General Use # Median Age % w/ Bachelor's (3 Miles) Degree (3 Miles) Annual Sales Printed 06/12/2020 #_x000D_# Pastas R Us, Inc. Database (n = 74 restaurants) Per Person Sales Growth Over Loyalty Card % of Annual Sales Median HH Income Square Feet Average Spending Previous Year (%) Net Sales Per Sq Ft (3 Miles) Obs 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 SqFt 2625 3150 2625 2741 2500 2450 2986 2967 3000 2500 2600 2800 2986 2223 2300 3799 2700 2650 2500 2994 2718 3700 2000 2400 2450 2575 2400 Sales/Person 6.96 7.30 6.96 6.71 6.82 6.58 7.56 6.98 7.28 6.76 6.92 6.73 6.91 6.77 7.33 7.87 6.95 7.33 6.95 7.21 7.25 7.65 6.93 6.79 7.37 6.76 7.97 SalesGrowth% 8.79 8.90 9.12 9.47 10.17 10.66 10.97 11.34 11.45 11.51 11.73 11.83 11.95 12.47 12.80 13.78 14.09 14.23 14.60 14.88 15.42 16.18 17.23 18.43 20.76 25.54 28.81 SqFt Sales/Person SalesGrowth% LoyaltyCard% 2.51 1.90 1.98 2.41 2.17 2.16 0.29 1.85 1.88 2.19 2.56 2.16 2.10 1.98 0.87 1.07 3.38 1.17 2.14 0.93 2.22 1.68 2.41 2.81 1.09 0.64 1.77 Median Age % w/ Bachelor's (3 Miles) Degree (3 Miles) Annual Sales Sales/SqFt 431.93 367.06 400.53 414.36 481.11 538.06 330.48 249.93 291.87 517.40 551.58 386.81 427.50 453.94 512.46 345.27 234.04 348.33 348.47 294.95 361.14 467.71 403.78 245.74 339.94 400.82 326.54 MedIncome 85366 39180 56077 77449 56822 80470 55584 78001 75307 76375 61857 61312 72040 92414 92602 59599 72453 67925 42631 75652 39650 48033 67403 80597 60928 73762 64225 MedAge 32.1 34.8 38.0 37.0 34.7 36.4 36.8 32.2 34.8 36.7 33.8 34.2 39.0 34.9 39.3 35.6 36.0 41.1 24.7 40.5 32.9 30.3 36.2 32.4 43.5 41.6 31.4 BachDeg% 29 18 19 34 25 30 21 30 30 28 31 16 31 40 33 28 23 16 25 25 18 15 19 27 21 29 15 Sales 1133816 1156239 1051391 1135761 1202775 1318247 986813 741542 875610 1293500 1434108 1083068 1276515 1009109 1178658 1311681 631908 923075 871175 883080 981579 1730527 807560 589776 832853 1032112 783696 Sales/SqFt MedIncome MedAge BachDeg% Sales Statistical Analysis Stat. Mean Standard Deviation Skew Min. Value 1st Quartile Median 3rd Quartile Max. Value IQR LoyaltyCard% 2580.47 7.04 7.41 2.03 420.31 62807.70 35.20 26.31 1059381.31 374.92 0.30 6.62 0.55 137.24 17904.27 3.65 7.00 280423.45 0.527170837189755 0.903631503252 0.4937474710729 -0.75689111371 1.23589655 0.297838011786 -0.1669958 0.14054419559 0.36141333 1251 6.54 -8.31 0.29 178.56 32929 24.7 14 499968 2400 6.825 3.98 1.8575 332.845 46953 32.525 20.25 877477.575 2500 7 7.03 2.075 396.01 62757 35 26.5 1035749.21 2735.25 7.1775 11.4225 2.325 483.5625 76194.25 37.525 30.75 1228866.96 3799 7.97 28.81 3.38 987.12 114353 43.5 40 1746600 335.25 0.3525 7.4425 0.4675 150.7175 29241.25 5 10.5 351389.385 Differences _x000D_ Saudi Aramco: Company General Use # Sales/SqFt Printed 06/12/2020 Sales #_x000D_# Pastas R Us, Inc. Database (n = 74 restaurants) Per Person Sales Growth Over Loyalty Card % of Annual Sales Median HH Income Square Feet Average Spending Previous Year (%) Net Sales Per Sq Ft (3 Miles) Obs SqFt Sales/Person SalesGrowth% LoyaltyCard% Sales/SqFt 1st Q - Min Med - 1st Q 3rd -Med Max - 3rd This box-plot for the annual sales looks that it is normally distributed because the median is almost in the middle of the data and the whiskers are about the same length. Standard Deviation is a better measure of depreciation than IQR because it get all the data in consideration. This chart isn't available in your version of Excel. Editing this shape or saving this workbook into a different file format will permanently break the chart. _x000D_ Saudi Aramco: Company General Use # MedIncome 154.285 63.165 87.5525 503.5575 Printed 06/12/2020 Median Age % w/ Bachelor's (3 Miles) Degree (3 Miles) Annual Sales MedAge BachDeg% 377509.575 158271.63 193117.755 517733.04 Sales #_x000D_# Pastas R Us, Inc. Database (n = 74 restaurants) Per Person Sales Growth Over Loyalty Card % of Annual Sales Median HH Income Square Feet Average Spending Previous Year (%) Net Sales Per Sq Ft (3 Miles) Obs SqFt Sales/Person SalesGrowth% LoyaltyCard% Sales/SqFt MedIncome The histogram is not symmetrical and it has a right skew. I have calculated the Outfitters ranges and the result ranged from 106.8 to 709.6 and any data outside of this range is considered to be outfitters. Examining the data, I found that only one outfitter, 987.12 Sq/Ft. and the SqFt for the outlier is 1251 SqFt. This outlier is larger than the average restaurant database of 420.31 Sq/Ft. This outlier is a problematic in our case and it could lead to wrong results and conclusion. It is caused by the really smaller SqFt that was selected in the data and has big difference. This chart isn't available in your version of Excel. Editing this shape or saving this workbook into a different file format will permanently break the chart. The best measure for central tendency for Sales/SqFt is the median because it is not symmetrical and it has an outlier. The median of Sales/SqFt is 396.01 and when comparing it to mean, we can confirm our results. _x000D_ Saudi Aramco: Company General Use # Printed 06/12/2020 Median Age % w/ Bachelor's (3 Miles) Degree (3 Miles) Annual Sales MedAge BachDeg% Sales Outfitters Range Q1-(1.5IQR) 106.76875 IQR 150.7175 Q3+(1.5IQR) 709.63875 # Sales/Sq Ft 178.56 209.93 234.04 235.81 245.74 249.93 264.64 267.71 274.30 291.87 294.95 297.37 303.95 310.07 312.15 323.17 326.54 329.09 330.48 339.94 345.27 348.33 348.47 351.47 352.57 357.45 361.14 364.92 367.06 367.19 368.02 368.73 373.46 375.33 380.34 386.81 393.90 398.12 399.20 400.53 400.82 403.78 405.77 413.08 414.36 427.50 # Saudi Aramco: Company General Use # 431.93 443.04 452.16 453.94 458.24 461.45 467.71 468.84 481.11 484.38 494.88 512.46 517.40 538.06 542.62 551.58 562.12 571.59 572.84 581.09 586.48 625.22 638.82 642.25 680.80 698.64 701.97 987.12 # Saudi Aramco: Company General Use
