# Headcount and compensation planning built on your Gusto data

> See every employee by department with pay, tenure and time since their last raise, model increases live, then apply only what you approve back to Gusto.

- Workflow type: app
- Services: Gusto, Google Sheets, Google Docs
- Categories: HR & People, Finance
- Published: 2026-08-24

## What it does

- Builds one table of every employee grouped by department, showing title, hire date, tenure, current pay, annualized cost and the date of their last pay change, with headcount and total compensation rolled up per department
- Lets a planner type a proposed rate or a percent bump on any row and watch the department and company totals move before anything becomes real
- Applies only the rows you approve back into Gusto on the effective date you choose, and exports the full planned versus current table to Google Sheets for the finance model
- Drafts a comp review memo in Google Docs that justifies every increase using each person's job and pay history, ready for the approval meeting

## What you'll need

- A Gusto account for your company, connected with permission to read employees, jobs and pay data, plus permission to make pay changes if you want to apply raises from the app
- A Google account for Google Sheets and Google Docs, and a spreadsheet where the finance export should land
- Pay figures only appear when the Gusto login you connect is allowed to see compensation. Without that access you still get headcount, titles and tenure, and the app tells you what is missing
- A rough idea of your raise threshold, for example flagging anyone more than 18 months since their last pay change

## Prompt

Build me an internal app that my finance and HR leads open every quarter instead of rebuilding a headcount and compensation spreadsheet from Gusto exports. It is a planning workbench: it reads the live roster out of Gusto, lets planners model raises on top of it, and applies only the rows that get approved.

The main view is a table of every employee grouped by department. Each row shows name, job title, hire date, tenure in years and months, current pay rate with its payment unit, annualized cost, the effective date of their last pay change, and months since that change. Handlers build this from Gusto: List Departments for the departments and their assigned members, List Employees for the roster and employment status, Get Employee Jobs for each person's job title, hire date and job history, and List Job Compensations for the pay records on their primary job. Treat the compensation with the most recent effective date on or before today as current, and the one before it as the previous rate, so the app can also show how big their last increase was.

Under each department header show a rollup: headcount, total annualized compensation, average tenure, and how many people are past the raise threshold. Show the same figures for the whole company across the top. Filters for department, employment status (default to active only, with options for onboarding and terminated) and months since last raise, including a one click preset for more than 18 months. Every numeric column sorts.

On top of that sits a planning layer the app owns. A planner types either a proposed rate or a percent bump into any row, and the app computes the new annualized cost, the dollar and percent delta, and immediately reflows the department rollup and the company total so they see the budget impact before anything is real. Planned changes are saved in the app and scoped to a named planning round with a default effective date. Each row carries a status of draft, approved or applied, plus a note explaining the increase. Show current, planned and delta side by side at row, department and company level. Nothing in this layer touches Gusto.

Approving a row still does not write anything. A separate apply action does that. When a planner applies approved rows, the handler must re-read the employee's job and its compensation records from Gusto immediately before writing, because Gusto rejects any write carrying a stale record version and returns a 422 Invalid Version. Use the freshly read version, then call Create Job Compensation with the row's effective date for a genuine pay change so the history stays intact, or Update Compensation when the planner is correcting an existing record rather than raising someone. If Gusto still returns Invalid Version, re-read once and retry, and if it fails again surface Gusto's own error message on that row and leave it approved rather than applied. Store the resulting compensation id and the applied timestamp on the row so it flips to applied and can never be applied twice.

An export action sends the whole planned versus current table to Google Sheets with Append Values: one row per employee with department, title, current rate, current annualized cost, proposed rate, proposed annualized cost, dollar and percent delta, effective date and status, plus the planning round name and a timestamp, so finance can pull it straight into the model. Let the user pick the spreadsheet and the tab.

Add a Draft the comp review memo button that starts a background agent inside the app. The agent takes every approved and applied row in the current planning round, re-reads those people's job and pay history from Gusto with Get Employee Jobs and List Job Compensations, and writes the memo for the approval meeting: total spend added and the new company run rate, a section per department, and a paragraph per person covering title, tenure, time since their last increase, the size of that previous increase and the case for this one drawn from the planner's note. It creates the memo with Google Docs Create Document and fills it in with Batch Update Document, then writes the document title, link and generated timestamp back into the app so a Memos panel lists every memo the team has produced with a link to open it. Show the agent's run status in the app while it works.

Two Gusto behaviors to bake in. First, pay rate fields are only populated when the connected Gusto login has compensation access alongside employee and job access. If rates come back blank, do not silently render zeros or empty cells: show a banner explaining that this connection cannot see compensation, and keep the headcount, title and tenure columns working. Second, Gusto returns money as decimal strings, so parse and total with decimal math rather than floats, and annualize from the payment unit (hourly rate times standard weekly hours times 52, weekly times 52, monthly times 12, yearly as is). Paginate the employee list rather than assuming a single page, since rosters run into the hundreds.

## How to customize

- Change the raise threshold that flags people as overdue, and which statuses show by default (active only, or include people still onboarding)
- Set a default effective date for the whole planning round, or override it per person
- Choose which columns land in the Google Sheets export and which tab the finance model reads from
- Adjust what the memo has to justify and how formal it reads before the approval meeting

## FAQ

### Does this change anything in Gusto on its own?

No. Everything you type stays in the app's own planning layer until you approve a row and press apply. Until then you are modelling numbers, not editing payroll.

### Why are the pay columns empty for me?

The Gusto login connected to the app is not allowed to see compensation. Reconnect with a login that has access to pay data and the rates, annualized costs and totals fill in. Headcount, titles and tenure work either way, and the app shows a banner rather than blank cells.

### What if someone edits a person's pay in Gusto while I am planning?

The app re-reads each record from Gusto immediately before it writes anything. If the record changed since you loaded the screen, it reloads the current numbers and tells you instead of overwriting someone else's edit.

### Can I model a raise without committing to it?

Yes, that is the point of the planning layer. Draft as many proposed rates and percent bumps as you like, compare the department and company totals, then approve only the ones that survive the meeting.

### Will this work for a company with a few hundred employees?

Yes. The roster loads page by page rather than in one request, so large teams work, though a big company will take longer on the first load of the day.

### Do I have to apply changes through the app?

No. You can use it purely as a planning and reporting surface, export the planned versus current table to Google Sheets, and make the actual changes in Gusto yourself.

Use this prompt in General Input: https://www.generalinput.com/prompts/headcount-and-compensation-planning-built-on-your-gusto-data