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

Adjusted R Square

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.)

unction, run a regression and answer the following questions about your output.

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.

Purchase answer to see full

attachment

Tags:

construction permits

Estimate Demand

Permits Issued

User generated content is uploaded by users for the purposes of learning and should be used following FENTYESSAYS.COM ESSAY’s honor code & terms of service.