Reference no: EM13208423
A senior financial analyst with Ace Gadgets (AG) is attempting to get a better grasp on sales forecasting for AG's new franchises. She has obtained various details for 27 existing franchises including (see associated spreadsheet):
Sales (Annual)
Square feet of the store
Inventory
Advertising $ (annual)
Number of competing stores in the district
From her recollection of her undergraduate course in statistics, she thought of regression analysis as a possibility in modeling new franchise sales. She has enlisted your help in this modeling task and has provided you with this list of questions.
1. What is the correlation between the above variables and sales?
2. Which variable appears to have the strongest relationship with sales? Why do you suggest this variable?
3. Create a scatterplot between the variable that you selected in requirement 2 and sales. Properly label your chart.
4. Add a trend line to the requirement 3 chart along with the regression equation and R
5. Interpret (in layman's language) what the equation means and what the R2 means. Remember that the senior analyst (senior ˜ old) hasn't had a course in statistics in several years and needs an interpretation that is understandable. Be sure to include all elements of the equation.
6. Using the analysis toolpak add-in, run regression analysis using the variable that you selected in requirement 2.
7. Using the output from requirement 6, is this variable statistically significant in predicting sales? What specifically on the output allows you to reach this conclusion1?
8. Which variables from the above list are useful in predicting sales? Why?
9. Using an appropriate Excel function, if a new franchise decided to carry $300,000 in inventory, what can be the expected annual sales for this franchise? Are you 100% confident in your answer? Why or why not?
| sales |
sqft |
inventory |
advertising |
competing |
|
| 231,000 |
3,000 |
294,000 |
8,200 |
11 |
|
| 156,000 |
2,200 |
232,000 |
6,900 |
12 |
|
| 10,000 |
500 |
149,000 |
3,000 |
15 |
|
| 519,000 |
5,500 |
600,000 |
12,000 |
1 |
|
| 437,000 |
4,400 |
567,000 |
10,600 |
5 |
|
| 487,000 |
4,800 |
571,000 |
11,800 |
4 |
|
| 299,000 |
3,100 |
512,000 |
8,100 |
10 |
|
| 195,000 |
2,500 |
347,000 |
7,700 |
12 |
|
| 20,000 |
1,200 |
212,000 |
3,300 |
15 |
|
| 68,000 |
600 |
102,000 |
4,900 |
8 |
|
| 570,000 |
5,400 |
788,000 |
17,400 |
1 |
|
| 428,000 |
4,200 |
577,000 |
10,500 |
7 |
|
| 464,000 |
4,700 |
535,000 |
11,300 |
3 |
|
| 15,000 |
600 |
163,000 |
2,500 |
14 |
|
| 65,000 |
1,200 |
168,000 |
4,700 |
11 |
|
| 98,000 |
1,600 |
151,000 |
4,600 |
10 |
|
| 398,000 |
4,300 |
342,000 |
5,500 |
4 |
|
| 161,000 |
2,600 |
196,000 |
7,200 |
13 |
|
| 397,000 |
3,800 |
453,000 |
10,400 |
7 |
|
| 497,000 |
5,300 |
518,000 |
11,500 |
1 |
|
| 528,000 |
5,600 |
615,000 |
12,300 |
0 |
|
| 99,000 |
800 |
278,000 |
2,800 |
14 |
|
| 500 |
1,100 |
142,000 |
3,100 |
12 |
|
| 347,000 |
3,600 |
461,000 |
9,600 |
6 |
|
| 341,000 |
3,500 |
382,000 |
9,800 |
5 |
|
| 507,000 |
5,100 |
590,000 |
12,000 |
0 |
|
| 400,000 |
8,600 |
517,000 |
7,000 |
8 |
|
How many strings will be read
: You should read a list of strings from a data file (using scanf), and find the minimum and maxium values of the string data. The input file format will start with a single integer that indicates how many strings will be read, and then lists those ..
|
|
Explain how to prepare a buffer solution
: A student is asked to prepare a buffer solution at pH=8.60 using one of the following weak acids HA (kA=2.7*10^-3), HB (Ka = 4.4*10^-6) and HC (Ka = 2.6 *10^-9).
|
|
How to use supply and demand analysis for manufactures
: Using supply and demand analysis for manufactures and assuming Canada is a high priced producer compared to the US, draw the supply and demand curves for Canada and the US as well as the import demand and supply curves assuming free trade
|
|
Predicting phenomena-mathematical physics
: On a scale of 1 to 10 measuring the sophistication of a scientific discipline in predicting phenomena, mathematical physics would probably be a 10. Where do you think OB would fall on the scale? Why?
|
|
Correlation between the variables and sales
: What is the correlation between the variables and sales and which variable appears to have the strongest relationship with sales? Why do you suggest this variable?
|
|
How much labor and land are allocated to manufacture
: Suppose at current factor prices a country's manufactures use 60 hours of labor for each acre of land and food is produced using only fifteen hours of labor per acre of land. If the economy's total resources are 1800 hours of labor and 180 acres o..
|
|
Define molarity of the sodium acetate and the acetic acid
: finds the antilog ratio of acid and base and determines that for every 1ml of acid she needs 2.29 ml of acid. what is the final concentration in molarity of the sodium acetate and the acetic acid?
|
|
What are the factors responsible for skin color
: what are the factors responsible for skin color? What are the two pigments responsible for skin color? What are the functions of those pigments?
|
|
Designing a custom chip that has a few dense arithmetic unit
: you are designing a custom chip that has a few dense arithmetic units that demand very high current density from the power supply (500mA/mm2 peak) and a large number of units with modest current demand
|