Excel File Needed: The Prescott Mini Stores Franchise Problem - Regression 2014
The Prescott Mini Stores Franchise Problem
Introduction: Mack Stewart president and CEO of the Star Bright Stores, headquartered in Prescott Florida, is worried about his franchise operation. Mack knows that his company's future depends on growing his business and by attracting more potential franchisees to purchase stores. In order to attract more individuals he has kept statistics on 27 franchise owners spread out over several states, each with different characteristics. While he somewhat understands the data, he has contracted with you, a local "expert" in statistics to evaluate the data and to recommend to him the type of franchisee he should pursue.
Your Tasks
Using the Excel data file that has been provided you are to completely answer the following questions:
1. What is the current status of the 27 franchises?
1) To do this you will create a one-variable summary using StatTools and analyze such data as:
i. The net sales over the past year;
ii. Sizes of the franchisee's store;
iii. The inventory that the various stores have on hand during the year;
iv. The amount the stores spent on advertising during the year;
v. How many "families" live in the districts where the stores are located;
vi. How many competing stores are in those districts and what does this tell you about how successful the Star Bright franchises are; and,
vii. What does the skewness and kurtosis show (if anything) you about these data?
2. Doing two Q-Q plots, do you consider the data for net sales and advertising to be normal or not, and why?
3. Doing a correlation in StatTools and using all six of the variables , how are each of these variables correlated to each other. Which ones are important or unimportant in the case study to net sales and why? Again, be specific.
4. Doing a scatterplot of net sales versus number of competitors in the district?
1. Adding a trend line along with the equation and the R-squared value to the plot, what do these tell you about the data?
2. What does the equation tell you about the impact of these two variables, and what does the r-squared value tell you?
5. Next do a multiple regression using net sales as the dependent variable, and all other variables as independent variables:
1. Do any of the variables have a t-value that is greater than the alpha (.05) for this assignment? If so, delete them and rerun the regression and compare and contrast the old regression versus the new regression without one or more of the variables.
2. Is the F-ratio for this/these regressions significant? Why?
3. Is the r-squared values for this/these regressions appear to be valid? Does it show that it explained a sufficient amount of the total variation?
6. Using the coefficients from this regression estimate the net income for the following typical franchise operations.
NOTE: If you have deleted any of the variables because they exceed alpha then only use those variables that apply to the new regression. If you have not deleted any variables than use all of the data found below. Explain in the report how you came up with the projected values for these homes. The completed Table One below must be included in your mini report.
Table 1. Typical Franchise Holders of Star Bright Mini Stores
Franchise Number Square footage in 1,000s Inventory in $1,000 Advertising annual budget in $1,000s Size of the district in thousands of families Number of competing stores Projected Net Income for this franchise in dollars
1 .55 105 2.6 1.5 2
2 2.2 298 3.1 3.3 3
3 3.5 359 4.4 6.8 8
4 5.5 540 9.6 9.7 9
5 7.5 689 15.9 16.0 14
• Square footage in thousands = the amount of square footage for this store.
• Inventory - the annual amount of inventory held by this store.
• Advertising = the annual amount of advertising spent by this store.
• Size of the district in thousands of families = how many families live in this district (from the US Census).
• Number of competing stores in the district = how many similar stores are found in this district (exclusive franchise area).
• Projected net income for this franchise = using the coefficients from the regression and the values found in this table, what is the projected annual net income for this store?
7. Based on the projected net income of these franchise stores, what types of franchisees should Mack be seeking. Again, be specific, take each variable into consideration when you provide this answer. Your recommendation should be no more than 300 words.
Submit your mini report by the date indicated in the weekly/monthly calendar for your section.
Adding a Trendline to a Graph in Word
First, create the XY scatterplot graph in Excel as shown by your professor during the chats
Next right click on any data point in the graph you just created. Notice that one option available is to Add Trendline. Select that option (1).
Next you are presented with several options, select linear (2) for the type of trendline you want and also check to display the equation and the r-squared value (3) on the graph and select Close (4).
You can now right click on the graph and paste it into your Word document.
Be sure to explain to the reader how to read and understand the graph, what the trendline means, and what the equation and r-squared values each mean.