Question

Suppose overall health care spending rose from $7000 per person in 2006 to $7700 per person in 2007. [1] Calculate both the absolute change and percentage change in health care spending per person from 2006 to 2007. [2] Using 2006 as your starting year (2006 = year 0), determine an exponential equation that calculates the amount of health care spending over time assuming the annual percentage change stays the same. Clearly identify the variable names and symbols in your equation. [3] Using 2006 as your starting year (2006 = year 0), determine a linear equation that calculates the amount of health care spending over time assuming the annual absolute change stays the same. Clearly identify the variable names and symbols in your equation. [4] Create an Excel spreadsheet to compare the two growth models’ predictions for health care spending through the year 2021. Include a chart showing both models. Please clearly indicate the question number on your excel file and attach it to the test. [5] Which model first predicts that U.S. health care spending will reach a level of $10,000 per person? In what year will that occur?

Answer 1

This is a simple problem related to analysis of regression equations based on changes given .

Lets try to solve the problem one by one.

1. To calculate change, absolute change and percentage change

Change = (present value - initial value)

Absolut change =Value of change

Percentage change =Change/ initial value %

Observation No.	Year	Spending	Change (current -previous year value)	Absolute change (magnitude of change)	Percentage change(change/Intial value %)
0	2006	7000
1	2007	7700	700	700	10

2). Derivation of exponential equation given that the percentage change is constant over years

In order to plot the exponential curve for a scenario when the percnetage change is constant ,

We need ot find hte predicted value of spending til 2021.

We shall make use the fact that precentage change is constant across any two consecutive years .

This is equal to 10% or 0.10 (when expressed in fraction )

Let y be predicted value of spending in current year and x be spending in previous year.

Then Percnetage change

=(y-x)/x

=0.10 (as per question)

Thus (y-x)/x=0.10

sloving ofr y ,

y =x*(1+f) (f =fractional chnage =0.10)

Kindly refer to the table below for the derived predicted value

Observation No.	Year	Percentage change(change/Intial value %)	Fractional change(f)(percentage change/100)	Predicted Value for spending current year (Spending in previous year)*(1+f)
0	2006			7000.0
1	2007	10	0.1	7700.0
2	2008	10	0.1	8470.0
3	2009	10	0.1	9317.0
4	2010	10	0.1	10248.7
5	2011	10	0.1	11273.6
6	2012	10	0.1	12400.9
7	2013	10	0.1	13641.0
8	2014	10	0.1	15005.1
9	2015	10	0.1	16505.6
10	2016	10	0.1	18156.2
11	2017	10	0.1	19971.8
12	2018	10	0.1	21969.0
13	2019	10	0.1	24165.9
14	2020	10	0.1	26582.5
15	2021	10	0.1	29240.7

now we plot the scatter plot for the spending vs year  in excel spread sheet and invoke the excel function for  the exponential function of best fit for this scatter plot.

This is given iin hte plot below

The exponential equation is given by

y =7000*e^0.0953x (x =change in the year starting from base year of 2006 which is assumed to be zero value)

3). Linear equation that calculates the amount of health care spending over time assuming the annual absolute change stays the same.

This is simple .

Since the absolute change is constant (its value =700 .,derived from difference of first two years (7700-7000)

the spending value in current year will be topped up by 700 above the spending value in previous year.

This is shown in the table below.

Observation No.	Year	Absolute Change	Spending in current year( spending in previous year+absolute change)
0	2006		7000
1	2007	700	7700
2	2008	700	8400
3	2009	700	9100
4	2010	700	9800
5	2011	700	10500
6	2012	700	11200
7	2013	700	11900
8	2014	700	12600
9	2015	700	13300
10	2016	700	14000
11	2017	700	14700
12	2018	700	15400
13	2019	700	16100
14	2020	700	16800
15	2021	700	17500

These values are fed in excel spreadsheet to get a linear regression equation as shown below.

Thus the linear regression function for the given growth of spending is given by

y =700*x+7000 (x is the change from the base year of 2006)

4). The excel spreadsheet showing the s predicted spending values and its related scatter plot along with related exponential and linear equations is attached in the problem subset 2 and 3 above.

5). The excel spreadsheet combined with both models is attahced below.

Exponential Model						Linear Model
Observation No.	Year	Percentage change(change/Intial value %)	Fractional change(f)(percentage change/100)	Predicted Value for spending current year (Spending in previous year)*(1+f)		Observation No.	Year	Absolute Change	Speding in current year( spending in previous year+absolute change)
0	2006			7000.0		0	2006		7000
1	2007	10	0.1	7700.0		1	2007	700	7700
2	2008	10	0.1	8470.0		2	2008	700	8400
3	2009	10	0.1	9317.0		3	2009	700	9100
4	2010	10	0.1	10248.7		4	2010	700	9800
5	2011	10	0.1	11273.6		5	2011	700	10500
6	2012	10	0.1	12400.9		6	2012	700	11200
7	2013	10	0.1	13641.0		7	2013	700	11900
8	2014	10	0.1	15005.1		8	2014	700	12600
9	2015	10	0.1	16505.6		9	2015	700	13300
10	2016	10	0.1	18156.2		10	2016	700	14000
11	2017	10	0.1	19971.8		11	2017	700	14700
12	2018	10	0.1	21969.0		12	2018	700	15400
13	2019	10	0.1	24165.9		13	2019	700	16100
14	2020	10	0.1	26582.5		14	2020	700	16800
15	2021	10	0.1	29240.7		15	2021	700	17500

We can see that with exponential model ,the predicted value of spending surpasses the $10000 mark in 2010 while it does the same for linear model in 2011.

Hence expontial model predictions predict sooner acheiving of $10000 mark.