1 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Project 9: 

Generating Weighted Average 

Annual Calendar Year Education 

Expenditure
2 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov

Project 9

Calculate the calendar year education mean by 

the number of children in the household for the 

US population in 2016

In this project you will:

Learn about calendar year versus collection year 

in CE

Calculate calendar year population estimates 

using expenditure data from MTBI

2
3 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov

Calendar Year Estimates

Two Main Differences:

 Use 5 quarters of data, but only select months 

that fall in the calendar year (Numerator)

 Population weights are adjusted based on the 

number of months in the calendar year the CU 

could report (Denominator)

3
4 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov 4

Population Weights

 Need another adjustment to FINLWT21

Adjust weights based on the number of months that could have 

been included

MO_SCOPE: “Months in Scope”
5 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov 5

MO_SCOPE

Quarter1 (FMLI161x)

Oct 

2015

Nov

2015

Dec 

2015

Jan

2016

Feb 

2016

March 

2016

0

X 1

X X 2

Quarter5 (FMLI171)

Oct 

2016

Nov

2016

Dec 

2016

Jan

2017

Feb 

2017

March 

2017

X X X 3

X X 2

X 1
6 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov 6

Quarter2-4 (FMLI162-164)

Jan 

2016

Feb

2016

March 

2016

April

2016

May

2016

June 

2016

X X X 3

X X X 3

X X X 3

Quarters 3 and 4 are analogous.

MO_SCOPE MO_SCOPE
7 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov 7

Population Weights

 Multiply FINLWT21 by MO_SCOPE / 3

 Still need to adjust to account for quarterly weights, so divide by 4. 

 ...wait – did you say 4?? But I’m using 5 quarters?!

Yes...but you’re really only using 1/3 of the first quarter and 2/3 of 

the fifth quarter. So, dividing by 4 is easier then saying divide by:

(1/3)*1 + 1 + 1 + 1 + (2/3)*1 = 4
8 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov 8

Quick Guide to Adjusting Population Weights

FMLI161x POPWEIGHT = FINLWT21 [(QINTRVMO-1)/3]/4 

FMLI162 POPWEIGHT = FINLWT21 (3/3) / 4

FMLI163 POPWEIGHT = FINLWT21 (3/3) / 4

FMLI164 POPWEIGHT = FINLWT21 (3/3) / 4

FMLI171 POPWEIGHT = FINLWT21 [(4-QINTRVMO)/3]/4
9 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov 9

Expenditures in Scope

 REF_YR

Identifies the reference year of the expenditure

 REF_MO

Identifies the reference year of the expenditure
10 — U.S. BUREAU OF LABOR STATISTICS TATISTICS • bl s.gov bl s.gov 10

Project 9 Steps

1. Append all five quarters of MTBI data.

2. Create calendar year education expenditures:

 For each NEWID, create an EDUCA variable by summing the following UCC’s, if 

REF_YR = 2016:

• Tuition: 670110, 670210, 670410, 670901

• Test: 670903

• Books: 660110, 660210, 660410, 660901, 660902

• Other: 67092

3. Append all five quarters of FMLI data

4. Merge FMLI and MTBI

5. Create weighted expenditures by multiplying EDUCA by FINLWT21

6. Create population weights using months in scope (MO_SCOPE)

7. Aggregates: Sum the weighted expenditure by number of children

8. Populations: Sum the population weights by number of children

9. Means: Calculate annual means for each of the group by dividing the 

aggregates by the population weights by number of children
11 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov 11

Weighting Calendar Mean

Project 9 Results

Count # of Children Education 

Expenditure

19,518 0 Children $877.46

5,516 1 Child $1,493.60

4,169 2 Children $1,695.83

1,666 3 Children $1,300.35

780 More than 3 Children $1,314.50