Create an Excel® document in which you include the following:

Paste from "Median and Average Sale Price of Houses Sold", Price Ann sheet, the Period and Median Price columns (column A and B respectively), starting from year 1963.

On a third column, calculate Index, taking into assumption that for year 1984 it is 100. Format it to 1 decimal place. Name it "House Price Index."

For example, for year 1983, the index will be calculated based on median price of 1983 vs. 1984 (e.g. 94.2).

As a second example, for year 1985, the index will be calculated based on median price of 1985 vs. 1986 (e.g. 105.5).

On a fourth column, paste from "Annual U.S. CPI (includes CPI-U and CPI-W)", the Index column (column B) starting from 1963. Name it "Consumer Price Index."

Create a line chart, plotting "House Price Index" and "Consumer Price Index" for each year, starting from 1963. Year will be the X Axis. Price Index will be the Y Axis.

Please assist me with the solution to this troubling problem, Thank you for your time and effort

U.S. CPI - All items (1982-84=100)

Updated 2-6-18

U.S. CPI-U

U.S. CPI-W

(U = all urban consumers)

(W = urban wage earners and clerical workers)

Index:

Annual

Index:

Annual

1982-84=100

growth rate

1982-84=100

growth rate

1960

29.6

1.7%

29.8

1.7%

1961

29.9

1.0%

30.1

1.0%

1962

30.2

1.0%

30.4

1.0%

1963

30.6

1.3%

30.8

1.3%

1964

31.0

1.3%

31.2

1.3%

1965

31.5

1.6%

31.7

1.6%

1966

32.4

2.9%

32.6

2.8%

1967

33.4

3.1%

33.6

3.1%

1968

34.8

4.2%

35.0

4.2%

1969

36.7

5.5%

36.9

5.4%

1970

38.8

5.7%

39.0

5.7%

1971

40.5

4.4%

40.7

4.4%

1972

41.8

3.2%

42.1

3.4%

1973

44.4

6.2%

44.7

6.2%

1974

49.3

11.0%

49.6

11.0%

1975

53.8

9.1%

54.1

9.1%

1976

56.9

5.8%

57.2

5.7%

1977

60.6

6.5%

60.9

6.5%

1978

65.2

7.6%

65.6

7.7%

1979

72.6

11.3%

73.1

11.4%

1980

82.4

13.5%

82.9

13.4%

1981

90.9

10.3%

91.4

10.3%

1982

96.5

6.2%

96.9

6.0%

1983

99.6

3.2%

99.8

3.0%

1984

103.9

4.3%

103.3

3.5%

1985

107.6

3.6%

106.9

3.5%

1986

109.6

1.9%

108.6

1.6%

1987

113.6

3.6%

112.5

3.6%

1988

118.3

4.1%

117.0

4.0%

1989

124.0

4.8%

122.6

4.8%

1990

130.7

5.4%

129.0

5.2%

1991

136.2

4.2%

134.3

4.1%

1992

140.3

3.0%

138.2

2.9%

1993

144.5

3.0%

142.1

2.8%

1994

148.2

2.6%

145.6

2.5%

1995

152.4

2.8%

149.8

2.9%

1996

156.9

3.0%

154.1

2.9%

1997

160.5

2.3%

157.6

2.3%

1998

163.0

1.6%

159.7

1.3%

1999

166.6

2.2%

163.2

2.2%

2000

172.2

3.4%

168.9

3.5%

2001

177.1

2.8%

173.5

2.7%

2002

179.9

1.6%

175.9

1.4%

2003

184.0

2.3%

179.8

2.2%

2004

188.9

2.7%

184.5

2.6%

2005

195.3

3.4%

191.0

3.5%

2006

201.6

3.2%

197.1

3.2%

2007

207.342

2.8%

202.767

2.9%

2008

215.303

3.8%

211.053

4.1%

2009

214.537

-0.4%

209.630

-0.7%

2010

218.056

1.6%

213.967

2.1%

2011

224.939

3.2%

221.575

3.6%

2012

229.594

2.1%

226.229

2.1%

2013

232.957

1.5%

229.324

1.4%

2014

236.736

1.6%

232.771

1.5%

2015

237.017

0.1%

231.810

-0.4%

2016

240.007

1.3%

234.076

1.0%

2017

245.120

2.1%

239.051

2.1%

Source: U.S. Dept. of Labor, Bureau of Labor Statistics.

Median and Average Sales Prices of New Homes Sold in United States
Annual Data
Period	Median	Average
1963	$18,000	$19,300
1964	$18,900	$20,500
1965	$20,000	$21,500
1966	$21,400	$23,300
1967	$22,700	$24,600
1968	$24,700	$26,600
1969	$25,600	$27,900
1970	$23,400	$26,600
1971	$25,200	$28,300
1972	$27,600	$30,500
1973	$32,500	$35,500
1974	$35,900	$38,900
1975	$39,300	$42,600
1976	$44,200	$48,000
1977	$48,800	$54,200
1978	$55,700	$62,500
1979	$62,900	$71,800
1980	$64,600	$76,400
1981	$68,900	$83,000
1982	$69,300	$83,900
1983	$75,300	$89,800
1984	$79,900	$97,600
1985	$84,300	$100,800
1986	$92,000	$111,900
1987	$104,500	$127,200
1988	$112,500	$138,300
1989	$120,000	$148,800
1990	$122,900	$149,800
1991	$120,000	$147,200
1992	$121,500	$144,100
1993	$126,500	$147,700
1994	$130,000	$154,500
1995	$133,900	$158,700
1996	$140,000	$166,400
1997	$146,000	$176,200
1998	$152,500	$181,900
1999	$161,000	$195,600
2000	$169,000	$207,000
2001	$175,200	$213,200
2002	$187,600	$228,700
2003	$195,000	$246,300
2004	$221,000	$274,500
2005	$240,900	$297,000
2006	$246,500	$305,900
2007	$247,900	$313,600
2008	$232,100	$292,600
2009	$216,700	$270,900
2010	$221,800	$272,900
2011	$227,200	$267,900
2012	$245,200	$292,200
2013	$268,900	$324,500
2014	$288,500	$347,700
2015	$294,200	$352,700
2016	$307,800	$360,900
2017	$323,200	$383,200
Note: The sales price includes the land.

Answer rating: 100% (QA)

House price index is calculated by the fo... View the full answer