Overview
In some cases, a user might want to look at multipliers (Sales, Jobs, or Earnings) for groups of less-detailed NAICS, i.e. 2-digit, or even create total aggregate multipliers for all industries together.
While Analyst and Developer do not support such operations, users can produce aggregate multipliers by combining two Analyst reports and using Excel operations. At a high level, aggregate multipliers are weighted averages of the multipliers of the group to be aggregated, weighted with respect to the correct multiplier-to-sales ratio multiplied by exports.
For instance, an aggregate jobs multiplier is the weighted average of the group's jobs multipliers, weighted with respect to the jobs-to-sales ratio times exports. This article will walk through two examples. The first will begin with 6-digit NAICS and create total aggregate jobs, earnings, and sales multipliers for all industries together. The second will begin with 6-digit NAICS and create jobs, earnings, and sales multipliers for each 2-digit NAICS. Since Analyst supports region aggregation, this article will not discuss aggregating with regard to region. This Excel workbook contains a sheet for each example
Example 1: Creating Total Aggregate Multipliers - See sheet "example1_totals"
Create and export to CSV* a Regional Multipliers report, using the desired region(s).
Create and export to CSV an Exports report, using the same region(s) as used in the Regional Multipliers report. In Analyst, Exports can be found in the Exports Table report in the Input-Output section. In Developer, use the Industry Table and select the column "Exported Sales."
Cut and paste the three columns (NAICS, Industry, Exports/Exported Sales) from the Exports CSV onto the end of the Regional Multipliers export. Ensure that the industries line up.
All multipliers are calculated with regard to exports and with regard to sales. Exports are in terms of sales and are given in the exports CSV in the column "Exports"/"Exported Sales", so exported jobs and exported earnings are the only other columns that need to be calculated.
Calculate an Exported Jobs column using the product of the Exports column from the Exports table you created, and the Jobs to Sales column from the Regional Multipliers table. For the example, the formula is Column X * Column P.
Calculate an Exported Earnings column using the product of the Exports/Exported Earnings column from the Exports table, and the Earnings to Sales column from the Regional Multipliers table. For the example, the formula is Column X * Column Q.
In a fresh cell, calculate the total aggregate sales multiplier, which is the weighted average of total sales weighted with respect to exports, divided by the sum of all exports. For the example, the formula is SUMPRODUCT(Column R,Column X) / SUM(Column X)
In a fresh cell, calculate the total aggregate jobs multiplier, which is the weighted average of total jobs weighted with respect to exported jobs, divided by the sum of all exported jobs. For the example, the formula is SUMPRODUCT(Column S,Column Y) / SUM(Column Y)
In a fresh cell, calculate the total aggregate earnings multiplier, which is the weighted average of total earnings weighted with respect to exported earnings, divided by the sum of all exported earnings. For the example, the formula is SUMPRODUCT (Column T,Column Z) / SUM(Column Z)
Example 2: Creating 2-Digit Multipliers - See sheet "example2_2digit"
Create and export to CSV a Regional Multipliers report, using the desired region(s).
Create and export to CSV an Exports report, using the same region(s) as used in the Regional Multipliers report. In Analyst, Exports can be found in the Exports Table report in the Input-Output section. In Developer, use the Industry Table and select the column "Exported Sales."
Cut and paste the three columns from the Exports CSV onto the end of the Regional Multipliers export. Ensure that the industries line up.
All multipliers are calculated with regard to exports and with regard to sales. Exports are in terms of sales and are given in the exports CSV in the column "Exports"/"Exported Sales", so exported jobs and exported earnings are the only other columns that need to be calculated.
Calculate an Exported Jobs column using the product of the Exports column from the Exports table you created, and the Jobs to Sales column from the Regional Multipliers table. For the example, the formula is Column X * Column P.
Calculate an Exported Earnings column using the product of the Exports column from the Exports table, and the Earnings to Sales column from the Regional Multipliers table. For the example, the formula is Column X * Column Q.
In a fresh column, create a list of 2-digit NAICS codes.
For each group of rows containing identical 2-digit codes, calculate jobs, earnings, and sales multipliers.
In a fresh column, calculate the 2-digit sales multipliers, which are the weighted average of total sales in the 2-digit group weighted with respect to exports, divided by the sum of all exports in the 2-digit group. The formula for NAICS 11 in the example is SUMPRODUCT(R2:R18,X2:X18)/SUM(X2:X18), run on just the rows in the NAICS 11 group. Repeat for each 2-digit group using the relevant cell references.
In a fresh column, calculate the 2-digit jobs multipliers, which are the weighted average of total jobs in the 2-digit group weighted with respect to exported jobs, divided by the sum of all exported jobs in the 2-digit group. The formula for NAICS 11 in the example is SUMPRODUCT(S2:S18,Y2:Y18)/SUM(Y2:Y18), run on just the rows in the NAICS 11 group. Repeat for each 2-digit group using the relevant cell references.
In a fresh column, calculate the 2-digit earnings multipliers, which are the weighted average of total earnings in the 2-digit group weighted with respect to exported earnings, divided by the sum of all exported earnings in the 2-digit group. The formula for NAICS 11 in the example is SUMPRODUCT(T2:T18,Z2:Z18)/SUM(Z2:Z18), run on just the rows in the NAICS 11 group. Repeat for each 2-digit group using the relevant cell references.
* Lightcast's Excel exports round to the nearest whole number, whereas CSV exports provide detail out to several decimal places. For the sake of precision in calculating multipliers, these examples use CSV exports.