# Headcount plan cost modeler with live market pay checks

> Open one screen to see every planned hire next to real market pay, with quarterly and annual cost totals that update as you edit.

- Workflow type: app
- Services: Job Salary Data, Google Sheets
- Categories: Finance, HR & People
- Published: 2026-08-24

## What it does

- Lists every planned hire from the hiring plan spreadsheet you already keep, showing role, level, city, start quarter, whether they are full time or contract, and the base you have budgeted.
- Puts a live market estimate beside each row, so you can see your planned base against the going minimum, median and maximum for that title and city, with a flag when you are budgeting under the market.
- Keeps rolling quarterly and annual cost totals in the header that add taxes and benefits on top of base and only count the part of the year each person is actually on payroll.
- Lets you fix a row or add a new hire right on the page and saves it back to the spreadsheet, and gives every row a Test another city button that has an assistant price the same role in a few alternative cities and write a short recommendation under the row.

## What you'll need

- A Google account with the hiring plan spreadsheet you already use, with one row per planned hire
- Columns in that sheet for role title, level, city, start quarter, full time or contract, and planned base, plus a notes column the app is allowed to write into
- A Job Salary Data account for the market pay lookups (the free tier covers a small plan, bigger plans need a paid tier)
- Agreement between finance and people on the loading percentage you use for taxes and benefits

## Prompt

Build me an app that my finance lead and my people lead open together during hiring planning: a headcount plan cost modeler backed by a Google Sheets planning tab, with a live market pay estimate on every planned hire.

The plan sheet is the source of truth. On load, read the planning tab with the Google Sheets Get Values operation over a configured spreadsheet ID and range. Treat the first row as headers and map columns by header name rather than by fixed position, so my sheet layout does not have to match yours exactly. Expect one row per planned hire with: role title, level, city, start quarter, employment type (FTE or contractor), planned base, and a notes column. Also expect (and create in the sheet if missing) a small block of benchmark cache columns the app owns: market minimum, market median, market maximum, currency, confidence, and benchmark fetched date. If required columns are missing, show a clear setup screen naming exactly which headers to add rather than failing silently.

The main board is a dense table, one row per planned hire, grouped or filterable by start quarter and by department if the sheet has one. Each row shows the plan side (role title, level, city, start quarter, FTE or contractor, planned base) next to the market side (min, median, max, confidence, and the date the benchmark was fetched). Market numbers come from the Job Salary Data Get Job Salary Estimate operation, called with the row's role title and city. That call returns up to three publisher estimates, so iterate the whole data array rather than taking the first one, and roll them into a single min, median and max for display; keep the lowest confidence value seen so the row is not presented as more reliable than the weakest input. Flag rows where the planned base is below market: a soft warning under the median, a stronger warning under the minimum, and show the gap in both currency and percentage terms.

Editing happens in place. A row expands into an inline editor for city, level, start quarter and planned base, and Save writes just that row back with the Google Sheets Update Values operation targeting the row's A1 range (use Batch Update Values if a save touches more than one range). Adding a planned hire opens the same form empty and writes with the Append Values operation so the new row lands under the existing table. After any edit that changes role title or city, mark the row's cached benchmark as stale rather than silently refetching, and show a small Refresh estimate control on the row instead.

The header carries the numbers we are actually deciding on: rolling quarterly cost totals across the plan year, and a full annual total, both recomputed live as rows are edited. Cost per hire is planned base multiplied by a loading multiplier for employer taxes and benefits, configurable in the app and defaulting to 1.2x base, then prorated by start month. Derive the start month from the start quarter (Q1 starts in January, Q2 in April, Q3 in July, Q4 in October) so a Q3 hire only carries half a year of cost, and show the proration factor on the row so nobody has to reverse engineer the total. Apply the loading multiplier only to FTE rows: contractors do not carry employer taxes and benefits, so they roll up at their raw annualized cost. Let the user override the multiplier and see every total move immediately.

Normalize before you roll up. Salary estimates come back as annual figures in the response currency with a salary period field, so read salary_currency and salary_period rather than assuming annual USD, and convert to the plan currency basis before comparing to planned base. Contractor rows in the plan sheet are frequently entered as an hourly or day rate, so let each row declare its rate basis and annualize it (a standard 2,080 hours or 260 days per year, both configurable) before it enters any total or any market comparison. If a market estimate comes back in a different currency than the plan, show the currency clearly on the row and exclude it from the mixed-currency totals rather than quietly summing across currencies.

Be disciplined about the salary API quota. It is a metered RapidAPI plan with a monthly request allowance, so refetching every row on every page load would burn the allowance in a couple of sessions. Never fetch on page load. Read the cached benchmark and its fetched date out of the sheet and render the date inline as an as-of stamp. Estimates older than a configurable staleness window (default 30 days) render as stale with a Refresh control, and there is a Refresh stale estimates action in the header that fetches only the stale rows, shows how many calls that will take before it runs, and writes each refreshed estimate plus today's date back to the sheet with Update Values. Surface the remaining quota when the API reports it, and degrade gracefully on a rate limit response by keeping the cached numbers on screen with a warning instead of blanking the row.

Every row also gets a Test another city button that kicks off a background agent. The agent takes that row's role title, level and planned base, picks three or four sensible alternative cities (from a configurable list, defaulting to a mix of lower-cost metros in the same country), and calls Get Job Salary Estimate once per city. It compares each city's median against the row's current city and planned base, weighs the savings against the confidence value the API returns so a large saving on a low-confidence sample is not presented as a win, and writes a short plain-language recommendation (two or three sentences, naming the best alternative, the annualized saving, and the confidence caveat) into that row's notes column using Update Values. It also writes the date it ran. The app renders that recommendation inline underneath the row, with the run date, and shows an in-progress state on the button while the agent is working so two people do not kick off the same comparison twice.

Round it out with a settings panel for the spreadsheet ID and range, the loading multiplier, the plan year, the staleness window, the contractor annualization basis, the alternative-city list and the below-market flag threshold. Keep the table readable at a glance: money formatted consistently, below-market flags visually obvious without color alone, stale dates muted, and the quarterly totals pinned so they stay visible while scrolling a long plan.

## How to customize

- Change the loading multiplier used for taxes and benefits, which starts at 1.2 times base
- Set how old a saved market estimate can get before the app marks it stale and offers to refresh it
- Choose which alternative cities the Test another city check compares against, and how far below market counts as a flag

## FAQ

### Do I have to move our hiring plan out of Google Sheets?

No. The spreadsheet stays the source of truth. The app reads the plan tab, and every edit you make on screen, every new hire you add and every recommendation the assistant writes goes straight back into the same sheet, so anyone who opens the file still sees current numbers.

### Where do the market salary numbers come from?

They are live pay estimates looked up by job title and city, and each one comes back with a minimum, a median, a maximum and a confidence rating. The app shows all of it, including the confidence, so you can tell a well sampled role from a thin one.

### Will this burn through my salary data allowance?

No. Market estimates are saved into the sheet with the date they were fetched and reused after that, so opening the app does not trigger new lookups. Fresh numbers are only pulled when you refresh a row or run a city comparison.

### How does it handle contractors?

Contractor rows are converted to a comparable annual figure before anything is totalled, and the taxes and benefits loading is applied only to full time rows, so you are not padding a day rate with benefits the person does not receive.

### Can finance and recruiting both work in it at once?

Yes. Both leads open the same screen and see the same spreadsheet backed numbers, so the plan review happens in one place instead of over two versions of the same file.

### What if a role is budgeted below market?

The row is flagged, with a stronger warning when the planned base falls under the market minimum rather than just under the median. You can then edit the base inline and watch the quarterly and annual totals move before you commit.

Use this prompt in General Input: https://www.generalinput.com/prompts/headcount-plan-cost-modeler-with-live-market-pay-checks