# Ford Truck Scenario Regression Excel Exercise – Ford Trucks

One of the best selling vehicles in the world is the Ford F-150. In 2017,

Ford sold 102

Ford Truck Scenario 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?