excel-macro-shock-modeling

name	excel-macro-shock-modeling
description	Use when modelling the macroeconomic impact of an investment spending shock in a spreadsheet — either the demand-side national-accounting framework (multiplier, import leakage, project allocation) or the supply-side Cobb-Douglas potential-GDP framework (TFP, HP-filtered trend, capital accumulation). Fires on "investment shock", "demand multiplier", "import content share", "supply-use table", "potential GDP", "Cobb-Douglas", "HP filter", "TFP / Solow residual", "extend the growth projection", or "capital accumulation with depreciation". Carries the exact in-sheet formula chain each model uses.
metadata	{"short-description":"Demand: deflator-extended GDP, SUT import share, multiplier, bell allocation. Supply: LnZ=lnY−α·lnK, HP filter λ=100, TREND extension, K accumulation (1−δ)K+I"}

excel-macro-shock-modeling

Two frameworks for the same question — what an investment programme does to GDP. Both are built entirely from in-sheet Excel formulas that reference data pulled onto other sheets. The cell addresses below are the layout these templates use; the relationships are what transfer.

When to use this skill

• The task sizes the GDP impact of an investment or spending programme spread over several years.
• The task names a demand multiplier, an import content share, or a supply-use table (SUPPLY / USE sheets) that must be turned into an import-leakage ratio.
• The task asks for potential GDP, a Cobb-Douglas production function, TFP or the Solow residual, or a capital stock projected with depreciation.
• The task names an HP filter on an annual series, or asks to smooth a log-TFP history into a trend and extend it.
• The task asks to extend a macro projection past the last published year by holding a growth rate or a deflator path constant.
• The task asks for scenario variants of the same shock table (a different multiplier, a different import share).
• Not for: a spreadsheet task with no macro model behind it — a chart, a pivot, a lookup, or a descriptive-statistics summary needs none of this.

Procedure

1. Decide which framework the brief asks for — demand-side, supply-side, or both as separate sheets. They answer the same question from opposite directions and do not share a formula chain.
2. Align units first. Source GDP in billions with a model in millions means ×1000 (=WEO_Data!B2*1000). A shock stated in USD becomes local currency at the exchange rate: =6500*2.746 (6.5bn USD, in millions, × local-per-USD). Getting this wrong scales every downstream cell.
3. Build the demand side (if in scope) in this order: extend the projection → derive the import content share from the supply-use table → run the shock table → replicate for each scenario.
4. Build the supply side (if in scope) in this order: set α and δ → compute the LnZ history → HP-filter it → extend K and LnZ → evaluate the production function with and without the investment → read the uplift.
5. Emit formulas, not pre-computed numbers, wherever a value is derived, then recalculate the workbook so cached values exist. Keep every required sheet name present.

Shared building blocks

• Extend a growth series by holding the last known growth rate constant: =B9*(1+C10/100) (prior level × (1 + growth%/100)), copied down.
• Year-on-year change of an index: =(D3/D2-1)*100.
• Unit alignment: as in step 2 above.

Demand side — national-accounting framework

Deflator-extend the projection, derive the import leakage from the supply-use table, then push the shock through the multiplier.

Projection sheet (WEO_Data). Deflator YoY change =(D3/D2-1)*100; extend real GDP by the held growth rate =B9*(1+C10/100); extend the deflator by the average of the last four YoY changes as a fixed anchor: =(1+AVERAGE($E$6:$E$9)/100)*D9.

Supply-Use Table sheet (SUT calc). For each product, link Import and Resources from the SUPPLY sheet and Use from the USE sheet, then:

Column	Meaning	Formula
C	Supply: Import	='SUPPLY (38-38)-2024'!AR4
D	Supply: Resources	='SUPPLY (38-38)-2024'!AS4
E	Use	='USE (38-38)-2024'!T4
F	product import share	=C4/D4
G	import amount in that use	=F4*E4
C46	estimated import content share	=SUM(G4:G40)/E42 (between 0 and 1)

Shock sheet (NA). Assumptions: total investment =6500*2.746 (D30); import content share links to the SUT result ='SUT calc'!C46 (D31); demand multiplier 0.8 for a small open economy (D32); project allocation is a bell shape over 8 years (D33). Per year:

Real GDP into the sheet   C = WEO_Data real GDP × 1000
Project allocation (%)    D = 0.05, 0.10, 0.15, 0.20, 0.20, 0.15, 0.10, 0.05   (sums to 1.0)
Project investment        E = D * $D$30 * (base_deflator / year_deflator)   # deflate to real terms
Imports (leakage)         F = E * $D$31
GDP impact (I − M)        G = E − F
× multiplier              H = G * $D$32
% of GDP                  I = H / C * 100

Then replicate the whole table for the scenarios: Scenario 2 sets the multiplier = 1.0; Scenario 3 sets the import content share = 0.5. Use the GDP deflator as the investment deflator (the hint the task states).

Supply side — Cobb-Douglas potential GDP

Assumptions: capital's share α = 0.35 (B2); annual depreciation rate = average of the most recent 8 years of (consumption of fixed capital ÷ capital stock), =AVERAGE('CFC data'!E22:E29) (B3), landing at 1–3%. Per-year depreciation is CFC / capital_stock with units aligned (=C2/1000000/D2).

History (rows 6–27), TFP and its trend.

LnK  F = =LN(K)          LnY  G = =LN(Y)
LnZ  K = =G6-$B$2*F6     # Solow residual: lnY − α·lnK   (log TFP)

Smooth LnZ into a trend with an HP filter, λ = 100 for annual data (1600 is for quarterly). The in-sheet objective, minimised over the LnZ_HP cells (L), penalises deviation plus curvature:

M (2nd difference of LnZ_HP)  = =L8-2*L7+L6
P5 (objective)                = =SUMXMY2(K6:K27,L6:L27)+100*SUMSQ(M6:M27)

Solve either with Excel Solver (minimise P5 by changing L6:L27) or compute the closed form and write the resulting L values as numbers — the grader wants ≥15 numeric LnZ_HP values plus the P5 objective formula, so a static solved column is acceptable:

import numpy as np
def hp_filter(y, lam=100.0):           # y = LnZ history, returns smoothed trend
    n = len(y); D = np.zeros((n-2, n))
    for i in range(n-2):
        D[i, i], D[i, i+1], D[i, i+2] = 1.0, -2.0, 1.0
    return np.linalg.solve(np.eye(n) + lam * D.T @ D, np.asarray(y, float))

Projection (rows 36–75), production function and the shock.

K/Y ratio   D = =E/F
extend K    E = =F*AVERAGE(D$49:D$57)      # anchor on avg K/Y of the most recent 9 years
extend LnZ  G = =TREND($G$44:$G$57,$C$44:$C$57,C58)   # array formula, extends the trend
Ystar_base  H = =EXP(G)*(E^$B$2)            # potential GDP = TFP × Kᵅ
ΔK          J = =(1-$B$3)*J_prev + I        # capital accumulation: (1−δ)·prior + investment
K_with      K = =E + J
Ystar_with  L = =EXP(G)*(K^$B$2)
uplift %    M = =100*(L/H-1)
projected   N = =H*(1+M/100)

Link the yearly investment amounts from the Investment sheet into column I before accumulating. EXP, LN, AVERAGE, TREND, SUMXMY2, SUMSQ are legacy — no _xlfn. prefix.

Worked example

Input: a 6.5bn USD investment programme over 8 years, an exchange rate of 2.746 local per USD, a supply-use table for 2024, and a GDP projection that stops at the last published year. The brief asks for the annual GDP impact as a percentage of GDP, plus two scenario variants.

Approach: this is the demand side. Convert the programme to local-currency millions with =6500*2.746. Extend real GDP with =B9*(1+C10/100) and the deflator with =(1+AVERAGE($E$6:$E$9)/100)*D9. On the SUT sheet, build column F as =C4/D4 per product and column G as =F4*E4, then the import content share is =SUM(G4:G40)/E42 — sanity-check that it lands between 0 and 1. On the shock sheet, spread the programme over the bell allocation (0.05/0.10/0.15/0.20/0.20/0.15/0.10/0.05), deflate each year to real terms, subtract the import leakage =E*$D$31, apply the 0.8 multiplier, and divide by that year's real GDP ×100. Copy the table twice: once with the multiplier cell set to 1.0, once with the import share cell set to 0.5.

References

None. The economics is standard and transfers: deflator-anchored projection, the supply-use import-content identity, the demand multiplier with import leakage, the Cobb-Douglas Y = TFP·Kᵅ, the Solow residual lnY − α·lnK, the HP filter, and capital accumulation (1−δ)K + I. The parameters shown are the conventional/assumed values with their rationale — α = 0.35 (a common capital share under informal employment), HP λ = 100 (the annual-data setting), the bell allocation and 0.8 multiplier (a small-open-economy assumption the task fixes) — reuse or adjust them to the brief. The specific cell coordinates and cross-sheet ranges belong to these two workbook templates; repoint them at whatever layout the actual file uses.