1
00:00:00,620 --> 00:00:05,530
So we know that this spreadsheet has
column B which is really important

2
00:00:05,530 --> 00:00:08,350
because it is the sum of
all the other columns.

3
00:00:08,350 --> 00:00:12,971
So I'm actually going to change
the background color on this and

4
00:00:12,971 --> 00:00:20,725
maybe even actually even
change the freeze pane.

5
00:00:20,725 --> 00:00:25,301
What I want to do is start
to do some analysis here, so

6
00:00:25,301 --> 00:00:30,605
I am going to say these are actuals,
this is the actual data,

7
00:00:30,605 --> 00:00:34,874
and I am actually going
to merge all those cells.

8
00:00:41,303 --> 00:00:42,340
Shrink that down.

9
00:00:44,340 --> 00:00:50,634
And then what I'm gonna do is I'm going to
copy it so we can do some analysis on it.

10
00:00:54,181 --> 00:00:58,749
Let's insert Columns to the right.

11
00:00:58,749 --> 00:01:00,501
Insert 8 right.

12
00:01:05,160 --> 00:01:05,969
Copy.

13
00:01:09,245 --> 00:01:13,682
I wanna do one little divider row here,
column here, so insert one left.

14
00:01:13,682 --> 00:01:16,090
I'm gonna make it smaller.

15
00:01:16,090 --> 00:01:17,780
And I'm just gonna color it black so

16
00:01:17,780 --> 00:01:22,120
that it's easy to recognize when I'm going
to a new section across the spreadsheet.

17
00:01:23,170 --> 00:01:26,882
And this isn't actuals anymore,
let's do this % of total.

18
00:01:33,741 --> 00:01:39,253
Okay, let's start inputting some formulas.

19
00:01:39,253 --> 00:01:43,833
Equals C3,
we want it to be divided by the total

20
00:01:43,833 --> 00:01:48,880
personal consumption expenditures for
that year.

21
00:01:51,710 --> 00:01:55,007
If we copy this over, uh-oh.

22
00:01:55,007 --> 00:01:58,740
It's not counting the percentage total of
personal consumption expenditure any more.

23
00:01:58,740 --> 00:02:01,790
It's calculating durable household
equipment as a percent of motor

24
00:02:01,790 --> 00:02:02,559
vehicle parts.

25
00:02:03,600 --> 00:02:10,250
Which is not what we want so we're gonna
use a absolute reference to cell B3.

26
00:02:10,250 --> 00:02:13,140
But we don't wanna do both column and

27
00:02:13,140 --> 00:02:18,700
row absolute references we want
to just do colon and this is why.

28
00:02:18,700 --> 00:02:20,740
We can copy this formula.

29
00:02:20,740 --> 00:02:23,565
Left to right, and
it's accurate still, but

30
00:02:23,565 --> 00:02:27,709
we can also copy this formula across
here and it's still accurate.

31
00:02:27,709 --> 00:02:32,373
Click here, and it's updating
now motor vehicles in 1930 for

32
00:02:32,373 --> 00:02:37,047
the total PCE or
Personal Consumption Expenditures in 1930.

33
00:02:37,047 --> 00:02:41,719
Which is great,
because now I can take this formula and

34
00:02:41,719 --> 00:02:47,520
copy it across all the different
categories we're interested in.

35
00:02:51,370 --> 00:02:52,350
Don't want to go all the way down.

36
00:02:53,570 --> 00:02:56,900
So here's a nice little shortcut
trick that you can use.

37
00:02:56,900 --> 00:02:59,124
I'm going to jump over to this cell and

38
00:02:59,124 --> 00:03:02,738
then because we know if we use
the command or control button and

39
00:03:02,738 --> 00:03:07,067
skips to the next cell with something
in it or the edge of our spreadsheet.

40
00:03:07,067 --> 00:03:13,865
I'm going to just put data in these cells,
so that when I copy this formula,

41
00:03:20,149 --> 00:03:25,280
It stops at the 2015 row,
because now there's data to stop it there.

42
00:03:27,120 --> 00:03:31,470
Those are a lot of numbers, let's make it
easier by formatting it as a percentage.

43
00:03:34,070 --> 00:03:38,154
We've got the formats of
percent of total now,

44
00:03:38,154 --> 00:03:41,944
let's look at what this
data is telling us.

45
00:03:41,944 --> 00:03:49,915
In 1995, Americans spent 5% of their
total PCE on motor vehicle parts.

46
00:03:49,915 --> 00:03:56,395
Compared to 4.26% in 1929.

47
00:03:56,395 --> 00:03:57,422
Well that's interesting.

48
00:03:57,422 --> 00:03:58,440
And look at this.

49
00:03:59,850 --> 00:04:03,650
During World War II the percentage of PCE

50
00:04:03,650 --> 00:04:08,140
that Americans spent on motor vehicles and
parts went down significantly.

51
00:04:09,490 --> 00:04:12,540
But after the war it started
to bounce back again.

52
00:04:15,450 --> 00:04:19,023
Let's calculate what
the average percentages are for

53
00:04:19,023 --> 00:04:21,902
each of these categories across the years.

54
00:04:21,902 --> 00:04:28,537
So =average, I'm going to sum.

55
00:04:28,537 --> 00:04:31,960
So I held down Cmd+Shift there.

56
00:04:31,960 --> 00:04:36,740
And it took me all the way to the top, and
because I have merged cells here in row

57
00:04:36,740 --> 00:04:42,940
one, it selected everything that has a
column that's shared with this merged row.

58
00:04:42,940 --> 00:04:43,690
That's not what we want.

59
00:04:43,690 --> 00:04:49,135
So we wanna go down,
down while still holding shift and

60
00:04:49,135 --> 00:04:50,895
left to get back to the original column.

61
00:04:51,945 --> 00:04:55,626
Close the parentheses and there we are.

62
00:04:55,626 --> 00:04:59,809
So we see that from 1929
to 2015 on average,

63
00:04:59,809 --> 00:05:05,435
Americans spent 4.79% of their
PCE on motor vehicles and parts.

64
00:05:06,940 --> 00:05:07,620
Earlier in the course,

65
00:05:07,620 --> 00:05:11,800
we learned that average is
sometimes a misleading statistic.

66
00:05:13,220 --> 00:05:13,853
Let's look at median.

67
00:05:21,027 --> 00:05:23,920
Let's see how this fluctuates across
all the different categories.

68
00:05:29,831 --> 00:05:34,280
The average and
median is actually sometimes very close.

69
00:05:34,280 --> 00:05:41,760
And, For financial services, it seems
to be a little bit wider of a gap.

70
00:05:41,760 --> 00:05:43,860
For transportation services,
it's very close.

71
00:05:48,520 --> 00:05:52,342
So let's take a look at some
more of the columns and

72
00:05:52,342 --> 00:05:55,264
see which ones are changing the most.

73
00:05:55,264 --> 00:06:00,688
In 1929 Americans for example spent
21% of their income on food and

74
00:06:00,688 --> 00:06:05,588
beverage for consumption off premise
of where it is purchased like

75
00:06:05,588 --> 00:06:07,790
a grocery store for example.

76
00:06:07,790 --> 00:06:14,261
And 4.39% of their gasoline and
other energy goods.

77
00:06:14,261 --> 00:06:15,052
How about health care?

78
00:06:15,052 --> 00:06:21,320
Health care, 2.9%, so
I'm actually gonna freeze this at the top.

79
00:06:21,320 --> 00:06:24,422
So we can see 1929 the whole way.

80
00:06:24,422 --> 00:06:28,817
Let's go down to 2015 and
see how much this has changed.

81
00:06:30,704 --> 00:06:35,520
Americans are spending a lot of less as
a percent of their total expenditures

82
00:06:35,520 --> 00:06:36,960
on food and beverage.

83
00:06:38,975 --> 00:06:42,696
They're spending less on gasoline and
energy.

84
00:06:45,217 --> 00:06:47,220
But they're spending a lot
more on health care.

85
00:06:47,220 --> 00:06:48,259
Look at that.

86
00:06:48,259 --> 00:06:52,842
16.8 % compared to 3% in 1929.

87
00:06:52,842 --> 00:06:58,530
What other categories have gone up
since 1925, there's healthcare.

88
00:06:58,530 --> 00:07:03,327
So I'm just gonna call this
out by highlighting that

89
00:07:03,327 --> 00:07:06,821
cell in a yellow so I don't forget it.

90
00:07:06,821 --> 00:07:09,711
What else, recreation.

91
00:07:09,711 --> 00:07:12,890
It's gone up a bit,
I guess we're able to take more free time.

92
00:07:14,218 --> 00:07:17,410
Financial services and
insurance has also gone up a lot and

93
00:07:17,410 --> 00:07:20,200
same with food,
services and accommodation.

94
00:07:21,915 --> 00:07:23,195
So I'm going to highlight those.

95
00:07:31,285 --> 00:07:34,415
Housing and utilities we're also
spending more on in the US.

96
00:07:37,825 --> 00:07:41,533
Clothing and footwear has gone way down.

97
00:07:41,533 --> 00:07:46,742
So I'm going to highlight

98
00:07:46,742 --> 00:07:51,950
that as a orange color just

99
00:07:51,950 --> 00:07:58,600
to be different than the yellow.

100
00:07:58,600 --> 00:08:02,480
As you can see, just with what you've
learned, you can build a very powerful

101
00:08:02,480 --> 00:08:06,150
spreadsheet that can start to give
you some pretty interesting insights.