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.