Fluent Essays

Regression Excel Exercise – Ford Trucks One of the best selling vehicles in the world is the Ford F-150. In 2017, Ford sold 102 of these vehicles every hour, outselling all its competitors. Each year Ford introduces a new model of the F-150 and sets a price for the base model. The price determines how many trucks they will sell. Suppose that you work for Ford and are tasked with determining what price to set for the 2022 F- 150. Use the following data to answer the questions. Data Trucks Sold Price of Truck 830,730 $45,000 1,129,089 $25,000 1,224,136 $25,000 929,367 $30,000 986,164 $30,000 914,698 $37,500 918,426 $35,000 880,492 $40,000 906,364 $37,500 922,627 $35,000 921,632 $35,000 836,038 $45,000 997,857 $30,000 1,004,017 $30,000 1,088,790 $27,500 1,114,224 $25,000 846,520 $40,000 1,004,111 $27,500 1,004,122 $27,500 919,836 $35,000 1. Excel or another statistical program, a. Graph the data using a scatter plot b. Insert a trendline c. Label graph completely 2. Using Excel's Data Analysis Regression function, run a regression and answer the following questions about your output. a. Write your estimated demand function. b. List the confidence interval for the coefficient on price and write a sentence interpreting this confidence interval. c. List the P-value for price and write one sentence interpreting this P-value. d. List the R2 and write one sentence interpreting this R2. e. Evaluate if the linear regression is appropriate (refer back to your scatterplot). f. List 3 potential missing variables and explain why they may influence the Qd. 3. How many trucks do you expect to sell if the price is $30,000 per truck? 4. Calculate the price elasticity of demand at this point in the demand curve. a. Based on this elasticity result, if the goal is to maximize total revenue, should you increase or decrease the $30,000 per truck price? b. At what price do you maximize revenues? Return Price Returns Completed 70 932 70 932 75 910 75 920 80 876 80 852 80 857 80 847 80 865 80 834 85 811 85 831 90 785 90 802 90 771 90 792 95 789 95 731 100 663 100 709 SUMMARY OUTPUT Regression Statistics Multiple R 0.97 R Square 0.94 Adjusted R Square 0.93 Standard Error 19.18 Observations 20 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 1488.90 41.45 35.92 0.00 1401.82 1575.97 1401.82 1575.97 Price -7.85 0.49 -16.09 0.00 -8.88 -6.83 -8.88 -6.83 2. Using Excel's Data Analysis Regression function, run a regression and answer the following questions about your output. a. Write your estimated demand function. Qd = a - bP Qd = 1488.90 - 7.85P b. List the confidence interval for the coefficient on price and write a sentence interpreting this confidence interval. The confidence interval for the price coefficient is -6.83 to -8.88. This means that I am d 95% confident that the true coefficient for P falls within this range. c. List the P-value for price and write one sentence interpreting this P-value. The P-value is 0.00. This means that the coefficient for price is statically significant (P<0.01). Combined with negative sign on the coefficient, I can conclude that consumers decrease their quantity demanded when price increases. d. List the R2 and write one sentence interpreting this R2. The R2 is 0.94. This means that 94% of the variation in the observed data is explained by the regression. e. Evaluate if the linear regression is appropriate (refer back to your scatterplot). When visibly examining the scatterplot, the data appear to be linear (not curved) so this linear regression is appropriate. f. List 3 potential missing variables and explain why they may influence the Qd. Price of related goods. Add a ton of detail. Income - normal good. Add a ton of detail. Financial market conditions. Add a ton of detail. 3. How many returns do you expect to be completed if the firms charges $85 per return? Qd Intercept Coefficient P 821.52 1488.90 -7.85 85 4. Calculate the price elasticity of demand at this point in the demand curve. e = b * (p/Q) e b p q 0.81 -7.85 85 821.52 a. Based on this elasticity result, if the goal is to maximize total revenue, should you increase or decrease the $85 per return price? At the price of $85, the price elasticity of demand is 0.81. This is an inelastic demand response. If the demand for the good is inelastic, then the response to a price increase will be a relatively smaller decrease in the quantity demanded, so that the total revenue increases I would recommend increasing the price to increase revenue. b. At what price do you maximize revenues? Predicted total revenuP Q 0 20 40 60 80 100 120 0 100 200 300 400 500 600 700 800 900 1000 Pr ic e ($ ) Quantity Demanded Tax Returns 40 50 60 70 80 90 100 110 500 550 600 650 700 750 800 850 900 950 1000 Pr ic e ($ ) Quantity Demanded Tax Returns 69829.2 85 821.52 69975.62 86 813.67 70404.3 90 782.27 70580.84 94 750.86 70585.95 95 743.01 Total Revenue Maximized 70572.48 96 735.13 Sheet1