# JSU The Coeffecient of Determination and The Variation in Sales Analysis

Description

Phoenix Lumber Company uses the number of construction permits issued to help estimate demand (sales). The firm collected the following data on annual sales and the number of construction permits issued in its market area. (Hint: See worked Regression Example). Upload your Excel File No. of ConstructionSalesYearPermits Issued (000)(1,000,000) 20036.5010.3020046.2010.1020056.6010.5020067.3010.8020077.8011.2020088.2011.4020098.3011.30(a)Graph the data using a scatter plot. Using the “Insert Trendline” function in Excel, determine whether you should use linear regression or log-linear. (b)Using Excel’s Regression Analysis Function, run a regression and answer the following questions about your output. Determine the estimated regression line.(c)Test the hypothesis (at the .05 significance level) that there is no relationship between the variables.(d)Calculate the coefficient of determination. Give an economic interpretation to the value obtained.(e)Discuss the fit and significance of the regression.(f)Suppose that 8,000 construction permits are expected to be issued in 2010. What would be the point estimate of Phoenix Lumber Company’s sales for 2010?What is the elasticity at this point in the demand curve? Are you on the elastic, inelastic, or unit elastic portion of your demand curve? Can you make a recommendation to increase or decrease the price with this information?

1 attachmentsSlide 1 of 1attachment_1attachment_1.slider-slide > img { width: 100%; display: block; }
.slider-slide > img:focus { margin: auto; }

Unformatted Attachment Preview

Return Price
70
70
75
75
80
80
85
80
80
80
90
90
95
95
100
100
90
90
85
80
Returns Completed
932
932
910
920
876
852
811
857
847
865
785
802
789
731
663
709
771
792
831
834
Bret’s Accounting & Tax Services is a small but locally well
completes taxes for individuals. Every year, firms like Bret’s de
an individual tax return. This price determines how many tax re
Suppose you are an office manager for a firm like Bret’s Accoun
what your firm should charge next year for tax returns. Use follo
a) Graph the data using a scatter plot. Using the “Insert Trendlin
use linear regression or log-linear. (Place the graph beneath the d
b) Using Excel’s Regression Analysis Function, run a regression
(Place your regression results beneath the graph from part a.)
i) What is your estimated demand function?
ii) Discuss the fit and significance of the regression.
c) How many returns do you expect to be completed if the firms
point in the demand curve? Are you on the elastic, inelastic, or u
make a recommendation to increase or decrease price with this i
THIS NEXT PART MAY BE BEYOND THE SCOPE OF THIS
CALCULUS KNOWLEDGE I’LL ASSUME THESE CANNOT
d) Suppose the firm has a cost function for individual tax returns
Solver functionality, determine how much the firms should be ch
corresponding total revenue, total cost, and profit. (Use the Tem
SOLUTION
a)
R² = 0.935
Returns Completed
R² = 0.9224
Returns Completed
120
100
80
60
40
20
0
0
200
400
600
Return Price
b)
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.966964654
R Square
0.935020641
0.931410677
800
1000
Standard Error
Observations
19.17588072
20
ANOVA
df
Regression
Residual
Total
Intercept
Return Price
SS
95242.09078
6618.859223
101860.95
MS
95242.09078
367.7144013
Coefficients
Standard Error
1488.898058
41.44617667
-7.851456311
0.487855356
t Stat
35.92365274
-16.09382004
1
18
19
F
Significance F
259.0110435
3.94771E-12
P-value
3.29187E-18
3.94771E-12
Lower 95%
1401.822872
-8.876402381
i) Q = 1488.9 – 7.85 P
ii) Our R-squared is .935; 93.5% of the variation in Returns Completed is explained by our regression using
c)
Intercept
Return Price
Coefficients
Price
1488.898058
-7.851456311
Estimated Returns Completed
Own Price Elasticity
85
821.5242718
-0.8123604 INELASTIC-RAISE PRICE
d)
Intercept
Return Price
Coefficients
Price
1488.898058
-7.851456311
99.81668109
Estimated Returns Completed
Total Revenue
705.1917476
70389.89978
Quantity
Fixed Costs
Variable Costs
5000
10
TOTAL COST
PROFIT
705.1917476
12051.91748
58337.9823
mall but locally well-known accounting firm in Sioux City, IA which
year, firms like Bret’s decide how much they will charge to complete and file
rmines how many tax returns firms complete each year.
firm like Bret’s Accounting and Tax Services and you are trying to determine
for tax returns. Use following data to answer these questions.
sing the “Insert Trendline” function in Excel, determine whether you should
e the graph beneath the data; be sure to label both axes.)
e graph from part a.)
he regression.
e completed if the firms charges \$85 per return? What is the elasticity at this
he elastic, inelastic, or unit elastic portion of your demand curve? Can you
ecrease price with this information?
D THE SCOPE OF THIS CHAPTER; SINCE YOU AREN’T ASSUMING
UME THESE CANNOT BE DONE MANUALLY.
for individual tax returns of TC=5000+10Q. Using functions and Excel’s
ch the firms should be charging for a return to maximize profit and the
and profit. (Use the Template below the answers to other questions).
Upper 95%
1575.973244
-6.82651024
d by our regression using Return Price. Our model is statistically significant as evidenced by the low P-value associated with our F test sta
e associated with our F test statistic and the t Stat associated with Return Price.