DATCB 565 UP Data Analysis And Business Analytics
Revision of papers completed based on professor feedback. *Your Excel was just the original data file. You need to submit your work in Excel as well. Everything looks correct, I just need to be sure that you did the work.* Section 2 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 and 45 years oldHousehold median income above national averageAt 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 1,200 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; 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. Part 1 Conduct the following descriptive statistics analyses using the PastasR Us, Inc. data set in Microsoft Excel. Answer the questions in the spreadsheet or a separate Microsoft Word document. Insert a new column in the database that corresponds to Annual Sales. Annual Sales is the result of multiplying a restaurant’s Sq Ft by Sales/Sq Ft. Calculate the following: meanstandard deviationskew5-number summaryinterquartile range (IQR) for each of the variables Create a box-plot for the Annual Sales variable and answer the following questions: Does it look symmetric?Would you prefer the IQR instead of the standard deviation to describe this variable’s dispersion? Why or why not? Create a histogram for the Sales/Sq Ft variable and answer the following questions: Is the distribution symmetric? If not, what is the skew?Are there any outliers? If so, which one(s)?What is the Sq Ft area of the outlier(s)? Is the outlier(s) smaller or larger than the average restaurant in the database? What can you conclude from this observation?What measure of central tendency is more appropriate to describe Sales/Sq Ft? Why? Cite references to support your assignment. Part 2 Write a 750- to 1,000-word statistical report with the following sections: Section 1: Scope and Descriptive Statistics Section 2: Analysis Section 3: Recommendations and Implementation Section 1: Scope and Descriptive Statistics State the report’s objective. Discuss the nature of the current database. What variables were analyzed? Summarize your descriptive statistics findings from Part 1. Use a table and insert appropriate graphs. Section 2: Analysis Using Microsoft Excel, create scatter plots and display the regression equations for the following pairs of variables: Bach Deg% versus Sales/Sq Ft Med Income versus Sales/Sq Ft Med Age versus Sales/Sq Ft Loyalty Card(%) versus Sales Growth(%) In your report, include the scatter plots. For each scatter plot, designate the type of relationship observed (increasing/positive, decreasing/negative, or no relationship) and determine what can be concluded from these relationships. Section 3: Recommendations and Implementation Based on your findings above, assess which expansion criteria seem to be more effective. Is there any expansion criterion that could be changed or eliminated? If so, which one and why?Based 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?Based on your previous findings, recommend marketing positioning that targets a specific demographic. (Hint: Are younger people patronizing the restaurants more than older people?)Indicate 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 data *Narrowly evaluated if the company should allocate more resources to improve its average TiQ using hypothesis testing results or did not submit an evaluation if the company should allocate more resources to improve its average TiQ 0 Criterion Feedback you need to do a one-sample z test.** *Narrowly assessed if new protocol served its purpose based on a test of hypothesis to determine whether the average ST with service protocol PE is lower than with the PT protocol or did not submit an assessment of new protocol served its purpose. Criterion Feedback This should be a two-sample t test.* Section 4 In Part 1 of this competency assessment, you'll complete a market analysis as a necessary component of the overall business plan that you will prepare and submit in the capstone course, STRCB/581, for your MBA program. Part 2 of the assessment gives you an opportunity to strengthen your skills in gathering and analyzing business-related information. It also provides a deeper understanding of how companies can look at globalization as part of their strategic and operational plans. Part 2B Your organization is evaluating the quality of its call center operations. One of the most important metrics in a call center is Time in Queue (TiQ), which is the time a customer has to wait before he/she is serviced by a Customer Service Representative (CSR). If a customer has to wait for too long, he/she is more likely to get discouraged and hang up. Furthermore, customers who have to wait too long in the queue typically report a negative overall experience with the call. You’ve conducted an exhaustive literature review and found that the average TiQ in your industry is 2.5 minutes (150 seconds). Another important metric is Service Time (ST), also known as Handle Time, which is the time a CSR spends servicing the customer. CSR’s with more experience and deeper knowledge tend to resolve customer calls faster. Companies can improve average ST by providing more training to their CSR’s or even by channeling calls according to area of expertise. Last month your company had an average ST of approximately 3.5 minutes (210 seconds). In an effort to improve this metric, the company has implemented a new protocol that channels calls to CSR’s based on area of expertise. The new protocol (PE) is being tested side-by-side with the traditional (PT) protocol. Download the Call Center Waiting Time data base. Each row in the database corresponds to a different call. Column variables are as follows.Protocol Type: indicates protocol type, either PT or PEQueue Time: Time in Queue, in secondsService Time: Service Time, in seconds Perform a test of hypothesis to determine whether the average TiQ is lower than the industry standard of 2.5 minutes (150 seconds). Use a significance level α=0.05. Evaluate if the company should allocate more resources to improve its average TiQ. Perform a test of hypothesis to determine whether the average ST with service protocol PE is lower than with the PT protocol. Use a significance level α=0.05. Assess if the new protocol served its purpose. (Hint: This should be a test of means for 2 independent groups). Write a 175-word summary of your conclusions. Comp 3 Notes (See attached Notes for revisions)WILL UPLOADZIP FILE AFTER ACCEPTION