Question
Use a scatter plot to recreate the graph on the previous slide. Use Excel's regression tool to estimate the demand schedule. Identify the
Use a scatter plot to recreate the graph on the previous slide.
• Use Excel's regression tool to estimate the demand schedule.
• Identify the following:
• Estimated slope coefficient
• Standard Error and 95% confidence interval
• t and p values
• R2
• Is the estimated slope coefficient statistically significant?
The average tax (T) is $1.92 and the average percent smokers (R) is
16.19. Calculate the tax elasticity: % Change in R divided by %
Change in T.
| State | Cigarette Tax | tax | smokingRate |
| Alabama | $0.675 / 20-pack | 0.675 | 20.2 |
| Alaska | $2.00 / 20-pack | 2 | 17.4 |
| Arizona | $2.00 / 20-pack | 2 | 14.9 |
| Arkansas | $1.15 / 20-pack | 1.15 | 20.2 |
| California | $2.87 / 20-pack | 2.87 | 10 |
| Colorado | $1.94 / 20-pack | 1.94 | 13.5 |
| Connecticut | $4.35 / 20-pack | 4.35 | 12.1 |
| Delaware | $2.10 / 20-pack | 2.1 | 15.9 |
| District of Columbia | $5.02 / 20-pack | 5.02 | 12.7 |
| Florida | $1.339 / 20-pack | 1.339 | 14.8 |
| Georgia | $0.37 / 20-pack | 0.37 | 16.3 |
| Hawaii | $3.20 / 20-pack | 3.2 | 12.3 |
| Idaho | $0.57 / 20-pack | 0.57 | 15.3 |
| Illinois | $2.98 / 20-pack | 2.98 | 14.5 |
| Indiana | $0.995 / 20-pack | 0.995 | 19.2 |
| Iowa | $1.36 / 20-pack | 1.36 | 16.4 |
| Kansas | $1.29 / 20-pack | 1.29 | 16.2 |
| Kentucky | $1.10 / 20-pack | 1.1 | 23.6 |
| Louisiana | $1.08 / 20-pack | 1.08 | 21.9 |
| Maine | $2.00 / 20-pack | 2 | 17.6 |
| Maryland | $3.75 / 20-pack | 3.75 | 12.7 |
| Massachusetts | $3.51 / 20-pack | 3.51 | 12 |
| Michigan | $2.00 / 20-pack | 2 | 18.7 |
| Minnesota | $3.04 / 20-pack | 3.04 | 14.6 |
| Mississippi | $0.68 / 20-pack | 0.68 | 20.4 |
| Missouri | $0.17 / 20-pack | 0.17 | 19.6 |
| Montana | $1.70 / 20-pack | 1.7 | 16.6 |
| Nebraska | $0.64 / 20-pack | 0.64 | 14.7 |
| Nevada | $1.80 / 20-pack | 1.8 | 15.7 |
| New Hampshire | $1.78 / 20-pack | 1.78 | 15.9 |
| New Jersey | $2.70 / 20-pack | 2.7 | 13.1 |
| New Mexico | $2.00 / 20-pack | 2 | 16 |
| New York | $4.35 / 20-pack | 4.35 | 12.7 |
| North Carolina | $0.45 / 20-pack | 0.45 | 18.5 |
| North Dakota | $0.44 / 20-pack | 0.44 | 17 |
| Ohio | $1.60 / 20-pack | 1.6 | 20.8 |
| Oklahoma | $2.03 / 20-pack | 2.03 | 18.9 |
| Oregon | $3.33 / 20-pack | 3.33 | 14.5 |
| Pennsylvania | $2.60 / 20-pack | 2.6 | 17.3 |
| Rhode Island | $4.25 / 20-pack | 4.25 | 13.3 |
| South Carolina | $0.57 / 20-pack | 0.57 | 17.5 |
| South Dakota | $1.53 / 20-pack | 1.53 | 18.3 |
| Tennessee | $0.62 / 20-pack | 0.62 | 19.9 |
| Texas | $1.41 / 20-pack | 1.41 | 14.7 |
| Utah | $1.70 / 20-pack | 1.7 | 7.9 |
| Vermont | $3.08 / 20-pack | 3.08 | 15.1 |
| Virginia | $0.60 / 20-pack | 0.6 | 14 |
| Washington | $3.025 / 20-pack | 3.025 | 12.6 |
| West Virginia | $1.20 / 20-pack | 1.2 | 23.8 |
| Wisconsin | $2.52 / 20-pack | 2.52 | 15.4 |
| Wyoming | $0.60 / 20-pack | 0.6 | 18.4 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
To recreate the graph and perform the regression analysis well follow these steps in Excel 1 Create ...
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Financial Analysis with Microsoft Excel
Authors: Timothy R. Mayes, Todd M. Shank
7th edition
1285432274, 978-1305535596, 1305535596, 978-1285432274
