# Excel Macro Program by USCensus

VIEWS: 99 PAGES: 2

• pg 1
Steps to Deriving ACS Adjusted Migration Rates in VP07
1. Compute state veteran populations at the end of FY using old migration rate (Mig ) from VP04 (prior VetPop model). Input and Output are the number of veterans at the state level at the beginning and end of the Fiscal Year, respectively.
1

Inputi , j  Migi1, j  Output 1,,T i, j  1 to 54 i j

  I 1,1  0   0   0 

0

I

2, 2

0 0

0 0 . . 0

1 0   1   m1,1 m1, 0   1    m2,1 . 0  . .   1 . I 54,54   m54,1 

2. The sum of each column in the Output matrix from Step 1 represents the state veteran population at the end of Fiscal Year. Adjust (by raking) the Output matrix from above (Step 1) so that the state distributions in Output and ACS are the same.
1  1 o1,1 o1,2  1  o .   2,1 . .  1   54,1 . o

Output 1 j i,

  . .  ,  . .  1 . o 54,54  .

o

1

1,54

o

1 1,1



o

1,T 1,1



ACS o
54 i1

St1

1,T i,1



3. Calculate the 2nd migration rate (Mig ) from Output matrix of Step 2 by dividing each cell by the sum of its rows.

2

1

 2  m1,1 2 m 2,1 2 Migi, j    2  m 54,1

m
. . .

2

1,2

  . .  , . .   2 . m 54,54   .

m

2

1,54

m

2

1,1



o

1 1,1



o
j1

54

1 1, j



1

4. Repeat steps 1-3 above to calculate next Migration rate until the Output

n,T

matrix is close to ACS.
100

An Excel Macro program iterates the above calculation for 100 times and computes Mig 100 the output using Mig is very close to ACS

where

I  1,1  0  0   0

0

I

2,2

0 0

0 0 . 0

      I 54,54 0 0 0

 100 m1,1 100 m 2,1   100  m 54,1

m

100 1,2

. . .

  . .   . .   100 . m 54,54  .

m

100

1,54

 100,T o1,1 100,T o 2,1  .  100,T  o 54,1

o

100,T 1,2

. . .

  . .  , . .   100,T . o 54,54   .

o

100,T 1,54



54 100,T   o i,1   i1  54 100,T   Such that  o i,2  i1  54 .    100,T   o i,54  i1 



 ACS St1     ACS St 2    .   ACS St 54 



2

To top