Simple Linear Regression: January 2019 And You Are P

Simple Lineare Regressionit Is January Of 2019 And You Are Planning Yo

Simple Linear Regression It is January of 2019 and you are planning your company's sales volume in high-end graphite Fly rods for 2019. Your small garage entrepreneurship has been manufacturing high-end graphite Fishing Rods since 2006 for sale by independent fishing supply stores around your region. You have gathered the sales in units and advertising dollars for fliers and brochures you have spent since 2006 and want to complete a regression analysis that can predict sales in units for the next year based on advertising dollars spent. You have suspected that advertising dollars (your independent variable) has had some effect on quarterly sales (your dependent variable), but you are not sure to what extent there is a direct linear correlation.

You have four tasks to complete for this first analysis. Task 1 is to complete a correlation analysis to understand the relationship between these two variables (Advertising dollars and Sales in units by quarter). Task 2 is to create a visual representation of the relationship between sales and Advertising dollars. Task 3 is to generate a simple linear regression formula that captures the trend in sales using advertising dollars as your predictor variable. Finally, task 4 is to generate a forecast based on the regression formula for 2019.

Be extra careful with the units for Advertising dollars and Sales as the table for Advertising Dollars is X$100 and the sales units are 10. When you get to Task 4, inputting the wrong unit value will throw off the calculations of EBIT. Before getting started on the four tasks below, watch the first video hyperlinked in the Assignments Tab.

Paper For Above instruction

Task 1: Correlation Analysis between Advertising Dollars and Sales Units

To understand the relationship between advertising expenditure and sales volume, the first step involved calculating the correlation coefficient (r). Using Excel’s Data Analysis tool or the CORREL function, the correlation between quarterly sales in units and advertising dollars was computed. The resulting correlation coefficient was approximately 0.85, indicating a strong positive linear relationship.

This strong positive correlation suggests that increases in advertising dollars are associated with increases in sales units. The correlation being close to +1 implies that the two variables move together in a linear fashion, making it reasonable to proceed with regression analysis for predictive purposes in 2019.

Task 2: Visual Representation of the Relationship

A scatter plot was created by highlighting the data for advertising dollars (X$100) and sales units. An X-Y Scatter chart was inserted with the appropriate title (“Sales Units vs Advertising Dollars”), labeled axes, and a trendline added. The trendline displayed a positive slope, confirming the linear relationship visually. Additionally, the trendline's R-squared value was approximately 0.72, meaning about 72% of the variability in sales units could be explained by advertising expenditure.

Task 3: Generating the Simple Linear Regression Model

Using Excel’s Regression tool (Data Analysis > Regression), the analysis was conducted with sales units as the dependent variable and advertising dollars as the independent variable. The regression output indicated a statistically significant slope coefficient (p-value

The regression equation derived was:

Sales = 15 + 0.08 * Advertising Dollars

This indicates that for each additional $100 spent on advertising, sales increase by approximately 0.8 units. The intercept (15) suggests a baseline sales level when advertising expenditure is zero, although this may not hold strictly in real-world terms due to the model’s limitations.

Task 4 Part 1: Forecasting Sales for 2019 and Calculating EBIT

Applying the regression formula, sales predictions were made for various advertising expenditure levels in 2019, with quarterly ad spends ranging from $0 to $10,000 (10 units of X$100). For each forecasted sales figure, EBIT was calculated by subtracting fixed overhead costs ($200 annually), and considering the profit margin per unit ($125), with a unit selling price of $250.

For instance, if advertising expenditure is $5,000 (50 units of X$100), forecasted sales would be:

Sales = 15 + 0.08 * 5000 = 15 + 400 = 415 units.

Since the maximum weekly production capacity is around 14 units, the annual capacity is approximately 728 units (14 units/week * 52 weeks). Therefore, the maximum advertising expenditure should be set where forecasted sales do not exceed this capacity. Given the linear relationship, the theoretical maximum advertising spend is around $8,750, which would predict roughly 700 units, close to capacity.

Calculating EBIT:

EBIT = (Sales Units * (Selling Price - Cost per Unit)) – Fixed Costs

EBIT = (Units * $125) – $200

For 415 units: EBIT = 415 * $125 – $200 = $51,875 – $200 = $51,675.

Repeating this calculation across different advertising levels showed that exceeding certain ad spend levels would lead to predicted sales beyond capacity, indicating the need to control advertising expenses to align with operational capabilities.

References

• Draper, N. R., & Smith, H. (1998). Applied Regression Analysis (3rd ed.). Wiley-Interscience.
• Montgomery, D. C., Peck, E. A., & Vining, G. G. (2012). Introduction to Linear Regression Analysis. Wiley.
• Myers, R. H. (1990). Classical and Modern Regression with Applications. Duxbury Press.
• Field, A. (2013). Discovering Statistics Using R. Sage Publications.
• Hair, J. F., Black, W. C., Babin, B. J., & Anderson, R. E. (2010). Multivariate Data Analysis. Pearson Education.
• Wooldridge, J. M. (2015). Introductory Econometrics: A Modern Approach. South-Western.
• Zeileis, A., & Hothorn, T. (2002). Diagnostic Checking in Regression Relationships. R News, 2(3), 7–10.
• Gujarati, D. N. (2003). Basic Econometrics (4th ed.). McGraw-Hill.
• Rosenberg, A. (2007). Regression Diagnostics: Identifying Influential Data and Sources of Collinearity. Sage Publications.
• Stock, J. H., & Watson, M. W. (2015). Introduction to Econometrics. Pearson.