Excel Problem
REQUIRED
Use Excel’s regression tools to analyze and forecast future sales.
(Hint: The article “Forecasting with Excel,” by James A. Weisel in the February 2009 issue of the Journal of Accountancy(available at www.aicpa.org) explains how to perform the following tasks using either Excel 2003 or Excel 2007.)*
a. Create a spreadsheet with the following data about targeted e-mails, click ads, and unit sales:
E-MAILS
CLICKS
UNIT SALES
150000
100
12,000
155000
105
12,500
125000
75
10,000
130000
150
14,000
135000
125
12,500
120000
100
10,000
125000
125
10,900
130000
135
11,500
130000
110
12,500
120000
95
10,500
100000
75
10,750
110000
100
10,000
100000
80
9,500
140000
130
13,500
120000
110
11,500
130000
125
13,000
100000
85
12,000
110000
100
9,000
120000
135
10,000
130000
140
13,500
140000
125
13,400
130000
115
12,750
120000
105
12,750
100000
95
10,000
130000
145
9,000
150000
150
15,000
140000
120
12,000
125000
100
13,500
110000
95
11,000
130000
140
13,500
b. Create a scattergraph to illustrate the relationship between targeted e-mails and unit sales. Display the regression equation and the R2 between the two variables on the chart.
c. Create a scattergraph to illustrate the relationship between click ads and unit sales. Display the regression equation and the R2 between the two variables on the chart.
d. Which variable (targeted e-mails or click ads) has the greater influence on unit sales? How do you know?
e. Use the “=Forecast” function to display the forecasted sales for 200,000 targeted e-mails and for 200 click ads.
* Life-long learning opportunity: see p. xxii in preface.
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.