Technical
Documentation

The summary panel at the top of the worksheet displays all key figures for the currently active rate scenario, resolved dynamically via LOOKUP against the sensitivity table. At the negotiated rate of $1.66/SF:

The $0.52/SF repair/replace component produces $6,084/month, $73,008/year, covering the full $710,000 renovation loan over 10 years. Loan repayment is embedded in the rent structure rather than treated as a separate obligation.

3.5% annual compounding. 10-year cumulative property tax: approximately $218,391. Embedded in the base rent component as a shared-cost recovery mechanism.

The NOI model was built to answer a specific negotiation question: what is the minimum rate this property needs to generate, and what rate produces a defensible margin? No template existed. The model was constructed from first principles applied to the actual property, renovation scope, and operating cost history.

• Identify all cost categories: renovation amortization, repairs, property taxes, alarm system, optional tenant services
• Set Y/N toggles for renovation line items and tenant-provided services based on the negotiation structure
• The model computes the SF/month contribution for each selected cost automatically

• The break-even engine (row 23) decomposes all cost contributions to SF/month and sums them
• The result ($1.37/SF) is the floor: any rate below this loses money before broker fees
• Adding the 12% profit target produces the negotiation threshold rate ($1.56/SF)

• Enter the proposed rate in B11. The summary panel resolves all figures via LOOKUP against the sensitivity table.
• The negotiated rate of $1.66/SF sits 21 cents above break-even and 10 cents above the profit threshold
• The sensitivity table provides full context: any rate from $0.60 to $10.35 can be evaluated instantly

• Toggle renovation line items in or out to model partial renovation scenarios
• Toggle tenant services to model gross vs. net lease structures
• Change the term length input (B1) to recalculate all 10-year totals and the tax chain automatically

The model produced a break-even of $1.37/SF. The final negotiated rate was $1.66/SF, producing a projected 10-year profit of $406,187 at a 17.4% margin. The efficiency improvements specified during the renovation (LED conversion, RF window tinting, exterior stucco insulation, motion-sensor controls) were designed to drive actual operating costs below the conservative budget, converting the surplus into additional principal prepayment capacity.

The demarcation language defining landlord versus tenant responsibilities was drafted from scratch and accepted by all parties including the tenant's broker. The GC overbilling was caught by physically walking construction draw inspections, not reviewing paperwork. The lease survived a subsequent change in building ownership approximately four years after signing.

• Platform: Microsoft Excel, pre-dynamic-array era
• Structure: Single worksheet with vertically separated functional zones
• Evaluation model: Live - summary panel updates automatically when the active rate input changes
• Scope: 11,700 square feet, 10-year term, $710,000 renovation budget
• Design approach: Input-driven with toggle-controlled cost inclusion, named range lookup engine, full sensitivity table

Real counterparty names, property addresses, and entity-specific financial references have been removed from this documentation.

• All cells in row 3 are LOOKUP-driven against the named range PERSFMO
• Changing B11 (total rate) causes all summary figures to update without manual recalculation
• Row 2 contains column header labels that also serve as the sensitivity table column definitions

• Decomposes total rate into base rent (B9, hardcoded input) and repair/replace amortization (B10, from toggle engine)
• B11 = ROUNDUP(SUM(B9:B10), 2) - serves as the LOOKUP key for the summary panel
• ROUNDUP prevents floating-point precision errors from producing a rate that falls fractionally below cost

• Y/N selector in column J controls inclusion of each renovation line item
• Each selected item follows a four-step chain: total cost input, amortization over term, SF/month conversion, monthly dollar amount
• Aggregated output (R8) feeds the repair/replace rate component (B10)
• Toggling any item recalculates B10, B11, and the entire summary panel simultaneously

• Each cost category decomposed to SF/month contribution using a consistent formula pattern
• A23 = SUM(E23:N23) - the minimum viable rate is the sum of all cost contributions
• Operates independently from the sensitivity table, providing a direct cost-floor calculation

• 999 rate scenarios from $0.60 to $10.35 at $0.01 increments
• Each row is self-contained: monthly rent, annual rent, 10-year rent, all cost columns, profit, margin
• All renovation cost cells reference Q8 (toggle engine output), so changing a toggle updates every row simultaneously
• Property tax column uses a dynamic INDEX into the escalation chain, adjusting automatically if the term input changes

• 3.5% annual compounding from 2015 base ($18,616 actual) through 2048
• Chain formula: each cell = prior cell + (prior cell * 0.035)
• Extended to 2048 to cover potential lease renewal periods beyond the initial term
• Sensitivity table draws cumulative tax using: =SUM($H$20:INDEX($J$20:$AL$20,$B$1))

• Three optional tenant-provided services carry negative cost values
• If toggled Y, the tenant covers the cost and it drops from the landlord cost basis
• All three set to N in the active model - landlord absorbed these costs in the negotiated rate

• Definition: column A of the sensitivity table, rows 25 onward (the per-SF/month rate column)
• Type: lookup vector used by all LOOKUP formulas in the summary panel row 3
• Purpose: enables rate-based lookup without row number arithmetic; decouples the summary display from the physical row structure of the table

• A3: =LOOKUP($B$11, PERSFMO, PERSFMO) - confirms active rate scenario
• B3: =LOOKUP($B$11, PERSFMO, B25:B1000) - resolves monthly rent for active rate
• Columns C through Q follow the identical pattern, each pulling a different cost or profit column

• B11: =ROUNDUP(SUM(B9:B10), 2)

• Q6: =IF(LEFT(J6,1)="Y", L6 * $B$1, 0) - includes 10-year cost if selected
• R6: =IF(LEFT(J6,1)="Y", M6, 0) - contributes SF/month rate if selected
• R8: =ROUNDUP(SUM(R6:R7), 2) - aggregated SF/month rate feeding B10

• E23: =(((E25 / $B$1) / 12) / 11700) - converts 10-year cost to SF/month contribution
• A23: =SUM($E$23:$N$23) - minimum viable rate

• N25: =SUM($H$20:INDEX($J$20:$AL$20, $B$1)) - dynamic range adjusts with term length input

• O25: =D25 - SUM(E25:N25) - 10-year revenue minus all costs
• P25: =O25 / $B$1 - annual profit
• Q25: =O25 / D25 - profit margin as percentage of 10-year revenue

B1 (term length)
  |-- All 10-year cost calculations (* $B$1)
  |-- Tax cumulative sum (INDEX end point)
  |-- Amortization denominators (/ $D$1)

J6:J16 (Y/N toggles)
  |-- Q6:Q7 (10-year renovation costs)
  |-- R6:R7 (SF/month renovation rates)
  |-- R8 (aggregated repair/replace rate)
  |-- B10 (repair/replace component of total rate)
  |-- B11 (total rate = LOOKUP key)
  |-- Row 3 summary panel (all LOOKUP formulas)

E20:AL20 (tax escalation chain)
  |-- N25:N999 (cumulative tax per sensitivity row)

Q8, R8 (renovation aggregation)
  |-- E25:E999 (renovation cost column, all sensitivity rows)
  |-- B10 (repair/replace rate component)

The model was built correctly for its era and remains analytically sound. The following patterns would differ in a modern rebuild:

Monthly compliance data from December 2023 through May 2025 (18 months). The HUBZone resident count held at 30 for 13 consecutive months while active headcount grew from 102 to 114. The compliance percentage declined over the same period. This is not a data anomaly. It reflects a structural condition where the HUBZone resident population had reached a ceiling while contract-driven headcount continued growing.

Current compliance breakdown by work location type, mirroring the SBA Program Examination form structure. Location codes follow the SBA classification: P (Principal Office), OH (Other HUBZone Location), NHR (Non-HUBZone Remote), J (Job Site).

Job site compliance varied significantly by contract: J3 (NASA/GSFC) at 92.3%, J8 (NASA/GSFC WMCLS) at 100%, while J4 (NASA/KSC) at 9.1% and J10 (NASA/GRC) at 0%. The overall percentage was pulled below threshold by the large non-HUBZone job site populations.

During a 2021 ownership reorganization, concerns were raised that the corporate structure between two affiliated entities would not hold up to SBA aggregation scrutiny under HUBZone rules. That recommendation was overruled. When an SBA examination approached two years later, no precise tracking data existed. The model had to be built from scratch from raw HR system exports.

• Each employee record contains hire date, termination date, HUBZone residency start/stop dates, and work location code
• Annual and monthly status columns are formula-driven. Adding a new employee record automatically propagates through all summary sheets
• Status codes update based on whether the employee was active and whether they resided in a HUBZone-qualifying address during each period

• Historical Data computes annual COUNTIF totals directly from the tracking table with no manual input
• 18-Month Data computes rolling monthly totals using the same COUNTIF pattern against numbered monthly columns (1-18)
• The HiddenVariables sheet drives the 18-month date window forward automatically via EDATE chains; no column relabeling required as time advances

• Historical chart shows the long-term trajectory: where the organization has been and where the trend line points
• 18-month chart shows the recent window in detail: month-by-month movement relative to the 35% threshold
• Location breakdown provides the SBA examination format directly, using the same structure the examination response requires

The model produced a conclusion that was not visible from the raw HR data: the HUBZone resident population had effectively reached a ceiling at approximately 30 people, while total headcount continued growing through contract wins. At 30 residents against a growing denominator, the compliance percentage would continue declining regardless of hiring practices, because new hires were concentrated at non-HUBZone job sites.

The forward projection confirmed this: even modest headcount growth would push compliance below 20% within the model's projection window. The 20% floor triggers mandatory decertification. The recommendation was to address the structural problem through the merger before the examination was filed. That recommendation was overruled. SBA aggregated the two entity workforces as predicted, collapsing the safe harbor entirely. The merger originally recommended in 2021 is now executing.

• Platform: Microsoft Excel with structured table references and newer Excel functions (LET, IFERROR, TEXT, EDATE, COUNTIF with wildcards)
• Structure: Multi-sheet workbook with a primary data table, a date calculation engine, two summary sheets, and two chart sheets
• Primary data source: Named Excel table HUBZone_Tracking (232 rows, 55 columns)
• Update model: Adding or modifying employee records automatically propagates through all summary sheets and charts via live formula references
• Date window: 18-month rolling window driven by EDATE chains in HiddenVariables; advances automatically without manual column relabeling

Individual employee records, hire/termination dates, home addresses, and entity-specific information have been removed from this documentation. Summary-level compliance data and formula architecture are presented here.

• Named table: HUBZone_Tracking. Columns A-S contain employee attributes; columns T onward contain period status columns (annual 2010-2027, then monthly 1-18)
• Each period column contains a formula that evaluates whether the employee was active and HUBZone-resident during that period, returning a two-character status code or blank
• Status codes: A | H (Active, HUBZone resident), A | N (Active, non-HUBZone resident), blank (not employed during that period)
• All summary sheets reference this table exclusively. No data is duplicated or manually maintained elsewhere

• Row 1: certification period start dates, annual 2009-2026
• Row 2: certification period end dates via =EDATE(prior, 12) - 1 chain
• Row 3: year labels (2010-2027) via simple increment chain
• Row 4: 18-month rolling window dates formatted as text via =TEXT(EDATE(next_col, -1), "MM/DD/YYYY"), which populates the column headers on the 18-Month Data sheet automatically

• Five rows: Active Employees, Non-HUBZone Residents, Current HUBZone Residents, HUBZone Percentage, 35% Marker
• Each cell is a COUNTIF against the corresponding annual column of the tracking table
• No manual data entry. The entire sheet updates when the tracking table changes

• Same five-row structure as Historical Data, applied to the 18 monthly columns
• Column headers reference HiddenVariables row 4 to display formatted dates
• Additional section: Active Location Data showing current snapshot by work location code
• 12-month average active employee count computed via AVERAGE across the trailing monthly window

• B2: =COUNTIF(HUBZone_Tracking[2010], "*A*")
• Counts any cell in the 2010 column of the tracking table that contains the letter A. The two-character status codes (A | H, A | N) both contain A, so this captures all active employees regardless of HUBZone status.

• B3: =COUNTIF(HUBZone_Tracking[2010], "*N*")
• Counts cells containing N. Only A | N contains N, so this returns active non-HUBZone residents exclusively.

• B4: =COUNTIF(HUBZone_Tracking[2010], "*H*")
• Counts cells containing H. Only A | H contains H, so this returns active HUBZone residents exclusively.

• B5: =IFERROR(B4/B2, 0)
• HUBZone residents divided by active employees. IFERROR suppresses divide-by-zero for future projection years where both counts are zero.

• B3: =COUNTIF(HUBZone_Tracking[1], "*A*")
• Column references use numeric labels (1-18) for the rolling monthly columns rather than year labels. The HiddenVariables sheet determines which calendar month each numeric column represents.

• B1: =HiddenVariables!A4
• Each column header on the 18-Month Data sheet pulls its date label from HiddenVariables row 4. As time advances and the HiddenVariables EDATE chain updates, all column headers relabel automatically.

• A4: =TEXT(EDATE(B4, -1), "MM/DD/YYYY")
• Each cell references the next cell in the chain, offset back one month via EDATE. The rightmost cell references a named range pointing to the current month header. The entire 18-month window of date labels is derived from this single anchor reference.

• Each annual and monthly status cell in the tracking table contains a formula that evaluates three conditions: whether the employee number is populated, whether they were employed during the period (comparing hire/termination dates against the HiddenVariables period boundaries), and whether they held HUBZone-qualifying residency during the period (comparing HUBZone start/stop dates). The formula returns A | H, A | N, or blank accordingly.

HiddenVariables (date engine)
  |-- Row 4 date labels --> 18-Month Data column headers (row 1)
  |-- Rows 1-2 period boundaries --> HUBZone_Tracking period status formulas

HUBZone_Tracking (primary table)
  |-- Annual columns (2010-2027) --> Historical Data COUNTIF formulas
  |-- Monthly columns (1-18) --> 18-Month Data COUNTIF formulas

Historical Data
  |-- Rows 2-5 --> Historical Graph chart series

18-Month Data
  |-- Rows 3-8 --> 18-Month Graph chart series
  |-- Rows 10-37 --> Active Location Data snapshot

All compliance figures in all views derive from a single tracking table via live formulas. There are no manually maintained summary cells. This eliminates the possibility of a summary sheet diverging from the underlying data.

The A | H / A | N encoding packs two pieces of information (active status and HUBZone residency) into a single cell. COUNTIF with wildcard patterns ("*A*", "*H*", "*N*") then extracts each dimension independently. This avoids maintaining separate columns for active status and HUBZone status while still supporting both queries from a single data cell.

The HiddenVariables EDATE chain means the 18-month tracking window advances automatically as months pass. A single anchor reference drives the entire column header system. No manual column relabeling is required to keep the rolling window current.

The location breakdown section of the 18-Month Data sheet uses the same location type codes (P, OH, NHR, J) and the same metrics (locations, employees, HUBZone count, local percentage) as the SBA Program Examination form. The model was designed so the examination response could be populated directly from the model output rather than requiring a separate data compilation step.