Question

Using the Excel file Forecasting Sales Using Ad Dollars Data use Excel to create a forecast of Sales $ using Ad $.

Analyze the data using simple linear regression in Excel to answer the following questions.

  1. Does more ad spending increase, decrease, or have no effect on sales $?
  2. What is the regression equation? Write it and its numerical values.
  3. Using the regression equation calculate predicted sales and error values and enter them into the Excel spreadsheet in the appropriate spaces.

Home Insert Draw Page Layout View Share Comments P vm A - , % Formulas Data Review Conditional Formatting v ting Format as Ta

0 0
Add a comment Improve this question Transcribed image text
Answer #1
X Y Y1=828.13+10.79X E=Y1-Y
Ad $ (thousands) Sales ($ million) Predicted Sales Error 1
41                      1,250                           1,270                                20
54                      1,380                           1,411                                31
63                      1,425                           1,508                                83
54                      1,425                           1,411                             (14)
48                      1,450                           1,346                           (104)
46                      1,300                           1,324                                24
62                      1,400                           1,497                                97
61                      1,510                           1,486                             (24)
64                      1,575                           1,518                             (57)
71                      1,650                           1,594                             (56)
Click "Data"
Click "Data Analysis"
Select "Regression" tool
Click"OK"
Input y_Range (Sales)
Input x_Range (Rental Ad $)
Click "OK"
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.847950033
R Square 0.719019259
Adjusted R Square 0.683896667
Standard Error 67.19447214
Observations 10
ANOVA
df SS MS F Significance F
Regression 1 92431.72331 92431.72331 20.47170228 0.0019382
Residual 8 36120.77669 4515.097086
Total 9 128552.5
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 828.1268882 136.1285978 6.083415987 0.000294814 514.2137788 1142.04 514.2138 1142.04
X Variable 1 10.7867573 2.384042146 4.52456653 0.0019382 5.289146253 16.28437 5.289146 16.28437
Regression Equation :
Y=a+bX
a=Intercept= 828.13
b=X Variable1= 10.79
Y=828.13+10.79X
R Square value = 0.719019259
This indicates reasonably good correlation between Ad$ and Sales
Add a comment
Know the answer?
Add Answer to:
Using the Excel file Forecasting Sales Using Ad Dollars Data use Excel to create a forecast...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • Week 3 Simple Regression Forecasting Sales Problem Homework Assignment Using the Excel file Forecasting Sales Using...

    Week 3 Simple Regression Forecasting Sales Problem Homework Assignment Using the Excel file Forecasting Sales Using Ad Dollars Data use Excel to create a forecast of Sales $ using Ad $. Analyze the data using simple linear regression in Excel to answer the following questions. 1. Does more ad spending increase, decrease, or have no effect on sales $? 2. What is the regression equation? Write it and its numerical values. 3. Using the regression equation calculate predicted sales and...

  • please help ! Week 3 Simple Regression Forecasting Sales Problem Homework Assignment Using the Excel file...

    please help ! Week 3 Simple Regression Forecasting Sales Problem Homework Assignment Using the Excel file Forecasting Sales Using Ad Dollars Data use Excel to create a forecast of Sales $ using Ad $. Analyze the data using simple linear regression in Excel to answer the following questions. 1. Does more ad spending increase, decrease, or have no effect on sales $? 2. What is the regression equation? Write it and its numerical values. 3. Using the regression equation calculate...

  • х New Tab Week 3 Forecasting Sales Problem ws/kelly/Downloads/Week%203%20Forecasting%20Sales%20Problem.pdf x | + Week 3 Simple Regression...

    х New Tab Week 3 Forecasting Sales Problem ws/kelly/Downloads/Week%203%20Forecasting%20Sales%20Problem.pdf x | + Week 3 Simple Regression Forecasting Sales Problem Homework Assignment Using the Excel file Forecasting Sales Using Ad Dollars Data use Excel to create a forecast of Sales $ using Ad $. Analyze the data using simple linear regression in Excel to answer the following questions. 1. Does more ad spending increase, decrease, or have no effect on sales $? 2. What is the regression equation? Write it and...

  • Multiple Linear Regression 20. The Excel file Concert Sales provides data on sales dollars and the...

    Multiple Linear Regression 20. The Excel file Concert Sales provides data on sales dollars and the number of radio, TV, and newspa-per ads promoting the concerts for a group of cities. Develop simple linear regression models for predict-ing sales as a function of the number of each type of ad. Compare these results to a multiple linear regres-sion model using both independent variables. State each model and explain R-Square, Significance F, and p-values from Business Analytics Methods, Models, and Decisions...

  • 5. Using regression analysis to forecast assets The AFN equation and the financial statement-forecasting approach both...

    5. Using regression analysis to forecast assets The AFN equation and the financial statement-forecasting approach both assume that assets grow at relatively the same rate as sales. However, the relationship between assets and sales is often a little more difficult than that. In particular, some firms use regression analysis to predict the required assets needed to support a given level of sales. Mainway Toys Co. has used its historical sales and asset data to estimate the following regression equations: Accounts...

  • 5. Using regression analysis to forecast assets The AFN equation and the financial statement–forecasting approach both...

    5. Using regression analysis to forecast assets The AFN equation and the financial statement–forecasting approach both assume that assets grow at relatively the same rate as sales. However, the relationship between assets and sales is often a little more difficult than that. In particular, some firms use regression analysis to predict the required assets needed to support a given level of sales. Mile Brewing Co. has used its historical sales and asset data to estimate the following regression equations: Accounts...

  • Instructions for Excel Assignment 2 a) Using the data in the file (i.e., the values of...

    Instructions for Excel Assignment 2 a) Using the data in the file (i.e., the values of Y and values of X), create a scatter plot with the variable Y plotted vertically and the variable X plotted horizontally. You are to add a trendline, the equation of the line and R to this scatter plot. You do not need to add a title to this plot. b) Run the 'Regression' analysis tool (found under DATA/Data Analysis). The output using this tool...

  • Instructions for Excel Assignment 2 a) Using the data in the file (i.e., the values of...

    Instructions for Excel Assignment 2 a) Using the data in the file (i.e., the values of Y and values of X), create a scatter plot with the variable Y plotted vertically and the variable X plotted horizontally. You are to add a trendline, the equation of the line and R to this scatter plot. You do not need to add a title to this plot. b) Run the 'Regression' analysis tool (found under DATA/Data Analysis). The output using this tool...

  • Instructions for Excel Assignment a) Using the data in the file (i.e., the values of Y...

    Instructions for Excel Assignment a) Using the data in the file (i.e., the values of Y and values of X), create a scatter plot with the variable Y plotted vertically and the variable X plotted horizontally. You are to add a trendline, the equation of the line and R2 to this scatter plot. You do not need to add a title to this plot. b) Run the ‘Regression’ analysis tool (found under DATA/Data Analysis). The output using this tool should...

  • Please use Excel Solver. Consider the following set of time series sales data for a growing...

    Please use Excel Solver. Consider the following set of time series sales data for a growing company over the past 8 months: Month Sales 1 15 2 13 18 4 22 20 6 23 7 22 8 21 1. Construct a time series plot. What type of pattern exists? 2. Develop a forecast for the next month using the averaging method. 3. Develop a forecast for the next month using the naïve last-value method. 4. Develop a forecast for the...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT