Share
Explore BrainMass

Regression Analysis and Sales Forecasting

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), and 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 R2.
5. Interpret (in layman's language) what the equation means and what the R2 means. Remember that the senior analyst 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

Attachments

Solution Preview

Regression Analysis: Ace Gadgets (AG)

Dear Student:

All calculations have been done in the attached Excel spreadsheet.

Q1. Correlation between sales and variables: n = 27
Sales Sqft Inventory Advertising Competing
Correlation (r ) 89.41% 94.55% 91.40% -91.22%

Q2. The correlation between Sales and Inventory is the strongest = 94.55% (highest value)

Q3. Scatterplot between Sales and Inventory in Excel worksheet regression, labeled Sales and Inventory

Q4. Trend line y = 0.9499x - 81504 and r^2 = 0.894

Q5. The equation tells us that the amount of inventory is positively ...

Solution Summary

This solution shows a regression analysis and sales forecasting of ACE Gadgets using Excel functions. The solution includes the calculation and interpretation of correlation and R2, the graphic presentation of a scatterplot, and the comparison of the variables with each other.

$2.19