Developer Handover Pack

Everything needed to build the automation from scratch: current state, full agent specs, data flow, and the build stack.

4 pagesPDF · Technical

FS-DOC-04Technical

Developer Handover Pack

Budget vs Actuals Reporting

[YourCompany.com] · Finance Department · Prepared by FullSpec · [Today's Date]

This document gives the FullSpec build team everything needed to construct, configure, and validate the three-agent Budget vs Actuals Reporting automation. It covers the current-state step map with bottleneck flags, full agent specifications with IO contracts, the end-to-end data flow trace, and the recommended build stack. All build decisions, credential requirements, and error-handling rules are documented here so nothing requires a back-channel conversation before work begins.

01Process snapshot

Step

Name

Description

Confirm Period Close in Accounting System

Bookkeeper checks QuickBooks to confirm all transactions for the period are posted and reconciled before exporting. Owner: Bookkeeper. Time cost: 20 min.

Export Profit and Loss Report

A P&L report is exported as CSV for the closed period, covering all accounts and cost centres. Owner: Bookkeeper. Time cost: 15 min.

Paste Actuals into Budget Spreadsheet

Exported figures are copied into the master budget spreadsheet, mapping each account line to the corresponding budget row by hand. BOTTLENECK: blocks all downstream variance work and introduces copy-paste error risk. Owner: Bookkeeper. Time cost: 45 min.

Repair Broken Formulas and Row Alignment

New account codes or renamed cost centres break lookup formulas, which must be found and fixed before variance calculations are reliable. BOTTLENECK: unpredictable fix time, delays variance step. Owner: Bookkeeper. Time cost: 30 min.

Calculate Variances and Flag Outliers

Dollar and percentage variances are calculated for each line; any line exceeding tolerance is manually highlighted. Owner: Bookkeeper. Time cost: 25 min.

Draft Variance Commentary

Finance Manager writes a brief explanation for each flagged variance, often requiring follow-up with department heads. BOTTLENECK: highest-skill manual task in the cycle. Owner: Finance Manager. Time cost: 50 min.

Chase Department Heads for Context

Where variance reasons are unclear, the Finance Manager contacts the relevant owner and waits for a response before the report can be completed. BOTTLENECK: uncontrolled wait time pushes delivery 4 to 7 days. Owner: Finance Manager. Time cost: 40 min.

Format and Polish the Final Report

Completed spreadsheet is formatted for presentation with a summary page, charts updated, and file saved to shared drive. Owner: Finance Manager. Time cost: 35 min.

Distribute Report to Stakeholders

Report is shared via Google Drive to the CEO, department heads, and board members. Owner: Finance Manager. Time cost: 15 min.

Time cost summary: Total manual time per cycle is 275 minutes (4 hours 35 min) of direct execution time, across two roles. At 1 run per month this equates to approximately 275 minutes/month of direct touch time, with the blended 6 hours/week benchmark figure accounting for upstream reconciliation activity and context-switching overhead. Steps 1 through 4 (Steps 1, 2, 3, 4) are fully replaced by the Actuals Sync Agent. Steps 5, 6, and 7 are replaced by the Variance Analysis Agent. Steps 8 and 9 are replaced by the Report Distribution Agent. The only remaining human step is the Finance Manager commentary review, estimated at under 20 minutes per cycle.

Developer Handover PackPage 1 of 4

FS-DOC-04Technical

02Agent specifications

Actuals Sync Agent

Connects to QuickBooks via OAuth 2.0, pulls the closed-period P&L report on the scheduled period-close date, parses account-level figures across all cost centres, and writes them into the designated actuals columns of the master Google Sheets budget template. The agent validates each account code against the maintained mapping table before writing, flags unmapped codes into a separate error tab rather than skipping them silently, and under no circumstances overwrites formula cells or header rows. All writes use row-keyed matching against the account code column, not positional index.

Trigger

Scheduled date trigger: fires on the configured period-close date each month (e.g. the 1st business day after month-end). Trigger must be configurable per calendar month.

Tools

QuickBooks (P&L report API), Google Sheets (Sheets API v4, service account auth)

Replaces steps

Steps 1, 2, 3, 4 — period confirmation, CSV export, actuals paste, formula repair

Estimated build

16 hours — Complex

// Input
trigger.period_close_date         // ISO 8601 date, e.g. '2025-02-01'
trigger.entity_id                 // QuickBooks realm ID / Xero org ID
config.mapping_table_sheet_id     // Google Sheets ID of mapping reference tab
config.actuals_sheet_tab_name     // e.g. 'Feb 2025 Actuals'
config.actuals_column_index       // Column letter where actuals are written, e.g. 'D'

// QuickBooks API response (ProfitAndLoss report)
qb.report.Rows[].ColData[].value  // Account name string
qb.report.Rows[].ColData[].id     // QuickBooks account ID
qb.report.Rows[].ColData[].Amount // Period actuals figure, decimal

// Output
sheets.write_result.updated_rows   // Count of rows successfully written
sheets.write_result.unmapped_codes // Array of account IDs not found in mapping table
sheets.write_result.skipped_rows   // Rows skipped due to formula or header protection
agent.status                       // 'complete' | 'partial' | 'failed'
agent.timestamp                    // ISO 8601 completion timestamp

QuickBooks API requires OAuth 2.0 with scopes com.intuit.quickbooks.accounting (read-only). Token expiry is 1 hour for access tokens; refresh tokens expire after 101 days of non-use. A token-refresh mechanism and an alert to support@gofullspec.com must be implemented before go-live.
Google Sheets access must use a dedicated service account (not a personal Google account). The service account email must be granted Editor access on the budget spreadsheet.
The account-to-budget-line mapping table must exist as a dedicated named tab in the budget spreadsheet with columns: qb_account_id, qb_account_name, budget_row_key, budget_line_label. This structure must be confirmed with the Finance Manager before build starts.
Write operations must target cells by matching the value in the account-code column (column A by default), not by row number. Row-number targeting breaks if rows are inserted.
If qb.report.Rows[].ColData[].id is absent from the mapping table, write the account to a dedicated 'Unmapped Accounts' tab and surface it in the Variance Analysis Agent's flagged list.
Actuals column must be cleared before each write to avoid stale data from a prior partial run. Use a batch clear-then-write, not a cell-by-cell update.
Confirm QuickBooks API rate limit: 500 requests per minute per realm. A report with 50 cost lines will not approach this, but confirm if multi-entity runs are added later.
Confirm whether Xero fallback is required. The mapping table schema and API response structure differ between QuickBooks and Xero. Build for QuickBooks first; Xero adapter to be a separate branch if needed.

Variance Analysis Agent

Reads the fully populated budget template immediately after the Actuals Sync Agent confirms a 'complete' or 'partial' status. Calculates dollar variance (actuals minus budget) and percentage variance ((actuals minus budget) divided by budget) for every mapped line. Compares each variance against the configured tolerance threshold (default: flag if absolute percentage variance exceeds 10%, or if dollar variance exceeds $500 on any single line). For flagged lines, the agent drafts plain-English commentary drawing on the variance direction, magnitude, and the same line's prior three periods if available. It then posts a targeted Slack message to each department head listing only the flagged lines relevant to their cost centre, with a 24-hour response window. Replies received within the window are appended to the commentary draft column before the Finance Manager review step.

Trigger

Fires immediately when Actuals Sync Agent emits agent.status = 'complete' or 'partial'. Does not fire on 'failed'.

Tools

Google Sheets (Sheets API v4, read and write), Slack (Web API, chat.postMessage)

Replaces steps

Steps 5, 6, 7 — variance calculation, commentary drafting, chasing department heads

Estimated build

12 hours — Moderate

// Input
upstream.agent.status              // Must be 'complete' or 'partial' to proceed
sheets.budget_column               // Column letter for approved budget figures, e.g. 'C'
sheets.actuals_column              // Column letter written by Actuals Sync Agent, e.g. 'D'
sheets.variance_dollar_column      // Target column for dollar variance output, e.g. 'E'
sheets.variance_pct_column         // Target column for percentage variance output, e.g. 'F'
sheets.commentary_column           // Target column for drafted commentary, e.g. 'G'
config.threshold_pct               // Percentage threshold to flag, e.g. 0.10 (10%)
config.threshold_dollar            // Dollar threshold to flag, e.g. 500
config.dept_head_slack_map         // JSON object: { cost_centre_id: slack_user_id }
config.response_window_hours       // e.g. 24
sheets.prior_periods_tab           // Tab name holding prior 3-period actuals for trend

// Output
sheets.variance_rows               // Array of { row_key, budget, actuals, var_dollar, var_pct, flagged }
sheets.commentary_draft            // Array of { row_key, draft_text, source: 'agent' | 'slack_reply' }
slack.messages_sent                // Array of { dept_head_slack_id, message_ts, lines_flagged }
slack.replies_received             // Array of { message_ts, reply_text, received_at }
agent.flagged_line_count           // Integer
agent.pending_commentary_count     // Lines with no Slack reply after window closes
agent.status                       // 'complete' | 'partial_pending' | 'failed'

Slack integration requires a bot token with scopes: chat:write, channels:read, users:read. The bot must be invited to any private channel used by department heads before the first run.
The dept_head_slack_map config object must be populated before build. Each cost_centre_id must match the budget_row_key values in the mapping table. Confirm these with the Finance Manager.
If a department head does not reply within the configured response window, the commentary column for that line must be set to a standardised pending string (e.g. 'No response received within 24 hours. Finance Manager to review.') and the report must not be held. The Report Distribution Agent fires on approval regardless.
Prior-period trend data is optional but improves commentary quality. If the prior_periods_tab does not exist on first build, the agent falls back to commentary based on variance magnitude only.
Percentage variance formula must handle zero-budget lines (division-by-zero). If budget = 0 and actuals > 0, flag the line and set var_pct to null with a label of 'No budget set'.
Threshold values (threshold_pct, threshold_dollar) must be stored in a config tab on the spreadsheet so the Finance Manager can adjust them without a code change.
Slack message format must list: cost centre name, budget figure, actuals figure, variance dollar and percentage, and a plain-English request for a one-line explanation. Do not send raw account codes to department heads.

Report Distribution Agent

Fires after the Finance Manager explicitly approves the commentary in the budget sheet. Approval is detected by the presence of a defined approval marker (a specific cell value or a checkbox state) in the budget spreadsheet. On approval, the agent formats the finalised Google Sheet into a presentation-ready state, saves a snapshot copy to Google Drive under a date-stamped filename following the agreed naming convention, sets sharing permissions automatically, and posts a Slack notification to the configured finance channel with a direct link to the published file.

Trigger

Approval marker detected in the budget spreadsheet (e.g. cell B2 on the Summary tab changes to 'APPROVED' or a named checkbox is checked by the Finance Manager).

Tools

Google Sheets (Sheets API v4), Google Drive (Drive API v3), Slack (Web API, chat.postMessage)

Replaces steps

Steps 8, 9 — formatting and polishing the final report, distributing to stakeholders

Estimated build

10 hours — Moderate

// Input
sheets.approval_cell_ref           // e.g. 'Summary!B2', must contain string 'APPROVED'
sheets.source_spreadsheet_id       // Google Sheets ID of the master budget template
config.drive_folder_id             // Google Drive folder ID for published reports
config.file_naming_pattern         // e.g. 'BudgetVsActuals_{YYYY}_{MM}_v{version}'
config.slack_channel_id            // Finance channel ID for stakeholder notification
config.stakeholder_slack_ids       // Array of Slack user IDs to mention in notification

// On approval
sheets.approval_timestamp          // ISO 8601 timestamp when approval marker was set
sheets.approver_name               // Value read from the approval row, e.g. 'Rachel Tung'

// Output
drive.published_file_id            // Google Drive file ID of the saved snapshot
drive.published_file_url           // Shareable link
drive.file_name                    // Final filename applied, e.g. 'BudgetVsActuals_2025_02_v1'
slack.notification_ts              // Slack message timestamp of the stakeholder notification
slack.notification_text            // Full message text posted to finance channel
agent.status                       // 'complete' | 'failed'

The approval detection mechanism must be confirmed with the Finance Manager before build. A named checkbox in a fixed cell is the lowest-friction option. An explicit string value ('APPROVED') is more robust for polling-based triggers. Decide and document this before the Report Distribution Agent build begins.
Google Drive snapshot must be a copy of the sheet, not a shortcut or a link to the live document. Use the Drive API files.copy method to create a static snapshot.
File naming convention must be agreed and stored in config. The version increment (v1, v2) must increment if the agent is triggered more than once in a single period, to avoid overwriting an already-published file.
Sharing permissions on the Drive snapshot must be set at publish time using Drive API permissions.create. Default: anyone-with-link can view. Do not set edit access on the snapshot.
The Slack notification must include: the period name (e.g. 'February 2025'), total variance summary (net dollar variance), count of flagged lines, count of lines still marked pending, and a direct URL to the Drive file.
Google Drive API scopes required: https://www.googleapis.com/auth/drive.file (restrict to files created by the app). Do not request broader drive scope.
If the approval marker is detected but the actuals column is empty or the Variance Analysis Agent status is 'failed', the Report Distribution Agent must abort and post an error alert to the finance Slack channel rather than publishing an incomplete report.

Developer Handover PackPage 2 of 4

FS-DOC-04Technical

03End-to-end data flow

Full trigger-to-output data flow — Budget vs Actuals Reporting automation

// ─────────────────────────────────────────────────────────────
// TRIGGER: Scheduled period-close date fires
// ─────────────────────────────────────────────────────────────
trigger.period_close_date          = '2025-02-01'          // ISO 8601
trigger.entity_id                  = 'qb_realm_id_xyz'     // QuickBooks realm

// ─────��───────────────────────────────────────────────────────
// AGENT 1: Actuals Sync Agent
// ─────────────────────────────────────────────────────────────

// Step 1 replacement: QuickBooks API call — fetch P&L report
REQUEST  GET /v3/company/{realmId}/reports/ProfitAndLoss
         ?start_date=2025-01-01&end_date=2025-01-31
         &accounting_method=Accrual
         &summarize_column_by=Total
         Authorization: Bearer {access_token}

RESPONSE qb.report.Rows[].ColData[0].value  // Account name, e.g. 'Payroll Expenses'
         qb.report.Rows[].ColData[0].id      // Account ID, e.g. '60'
         qb.report.Rows[].ColData[1].value   // Amount, e.g. '42500.00'

// Step 2 replacement: account-to-budget-line lookup
mapping_table[qb_account_id]       -> budget_row_key, budget_line_label
unmapped_accounts[]                -> write to 'Unmapped Accounts' tab

// Step 3 replacement: write actuals to Google Sheets
REQUEST  sheets.spreadsheets.values.batchUpdate
         spreadsheetId: config.source_spreadsheet_id
         range: '{actuals_sheet_tab_name}!D{row}' per budget_row_key match
         valueInputOption: RAW
         values: [[qb.actuals_amount]]

// Step 4 replacement: formula integrity confirmed via row-key matching
sheets.write_result.updated_rows   // e.g. 47
sheets.write_result.unmapped_codes // e.g. ['account_id_88', 'account_id_91']
agent1.status                      = 'complete'            // or 'partial'
agent1.timestamp                   = '2025-02-01T07:14:22Z'

// ─────────────────────────────────────────────────────────────
// HANDOFF 1: Actuals Sync Agent -> Variance Analysis Agent
// Condition: agent1.status IN ('complete', 'partial')
// ─────────────────────────────────────────────────────────────

// ─────────────────────────────────────────────────────────────
// AGENT 2: Variance Analysis Agent
// ─────────────────────────────────────────────────────────────

// Step 5 replacement: read budget and actuals columns, compute variances
for each mapped row in sheets.budget_tab:
  var_dollar  = actuals_value - budget_value
  var_pct     = (actuals_value - budget_value) / budget_value
  if budget_value == 0: var_pct = null, flag = true, label = 'No budget set'
  if abs(var_pct) > config.threshold_pct OR abs(var_dollar) > config.threshold_dollar:
    flagged = true
  write var_dollar -> sheets.variance_dollar_column (e.g. col E)
  write var_pct    -> sheets.variance_pct_column    (e.g. col F)

// Step 6 replacement: draft commentary per flagged line
for each flagged_row:
  prior_3_periods = read sheets.prior_periods_tab for budget_row_key
  draft_text = agent.generateCommentary(
    budget_line_label, var_dollar, var_pct, prior_3_periods
  )
  write draft_text -> sheets.commentary_column (e.g. col G), source='agent'

// Step 7 replacement: Slack prompts to department heads
for each dept_head in config.dept_head_slack_map:
  dept_flagged_lines = filter flagged_rows where cost_centre_id == dept_head.cost_centre_id
  if dept_flagged_lines.length > 0:
    REQUEST POST https://slack.com/api/chat.postMessage
            channel: dept_head.slack_user_id
            text: formatted_prompt(dept_flagged_lines)
    slack.messages_sent[] = { dept_head_slack_id, message_ts, lines_flagged }

// Collect Slack replies within config.response_window_hours (e.g. 24h)
for each slack.reply received:
  match reply to message_ts -> budget_row_key
  update sheets.commentary_column value = reply.text, source='slack_reply'
// After window expires, unmatched flagged lines:
  write 'No response received within 24 hours. Finance Manager to review.'

agent2.flagged_line_count          // e.g. 8
agent2.pending_commentary_count    // e.g. 2
agent2.status                      = 'complete'

// ─────────────────────────────────────────────────────────────
// HUMAN GATE: Finance Manager reviews and approves commentary
// Approval marker: sheets cell 'Summary!B2' set to 'APPROVED'
// Estimated time: under 20 minutes
// ─────────────────────────────────────────────────────────────

// ─────────────────────────────────────────────────────────────
// HANDOFF 2: Approval marker detected -> Report Distribution Agent
// Condition: sheets['Summary!B2'] == 'APPROVED'
//            AND agent2.status == 'complete'
//            AND sheets.actuals_column NOT empty
// ─────────────────────────────────────────────────────────────

// ─────────────────────────────────────────────────────────────
// AGENT 3: Report Distribution Agent
// ─────────────────────────────────────────────────────────────

// Step 8 replacement: snapshot and save to Google Drive
REQUEST  POST https://www.googleapis.com/drive/v3/files/{fileId}/copy
         name: 'BudgetVsActuals_2025_02_v1'
         parents: [config.drive_folder_id]
RESPONSE drive.published_file_id    // e.g. '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs'
         drive.published_file_url   // shareable link

// Set sharing permissions — view-only for anyone with link
REQUEST  POST https://www.googleapis.com/drive/v3/files/{fileId}/permissions
         role: 'reader', type: 'anyone'

// Step 9 replacement: Slack notification to stakeholders
REQUEST  POST https://slack.com/api/chat.postMessage
         channel: config.slack_channel_id
         text: 'Budget vs Actuals report for February 2025 is ready.'
               + ' Net variance: $-3,200.'
               + ' Flagged lines: 8 (2 pending Finance Manager review).'
               + ' View report: {drive.published_file_url}'
         mentions: config.stakeholder_slack_ids

agent3.status                      = 'complete'
agent3.timestamp                   = '2025-02-01T09:41:05Z'
// ─────────────────────────────────────────────────────────────
// END: Report distributed. Cycle complete for period Feb 2025.
// ─────────────────────────────────────────────────────────────

Developer Handover PackPage 3 of 4

FS-DOC-04Technical

04Recommended build stack

Orchestration layer

A workflow automation platform with native HTTP request nodes, scheduled triggers, and a shared credential store. Build one workflow per agent (three workflows total): 'BVA-01 Actuals Sync', 'BVA-02 Variance Analysis', 'BVA-03 Report Distribution'. Store all OAuth tokens and API keys in the platform's credential store, not in workflow nodes. Workflows chain via a shared status flag written to the budget spreadsheet so each agent can be triggered and debugged independently.

Webhook configuration

No inbound webhooks are required for the core trigger flow. The Actuals Sync Agent fires on a cron schedule (configured per calendar month-end). The Variance Analysis Agent polls for agent1.status via a Sheets read node on a 5-minute interval after the Actuals Sync completes, with a 2-hour timeout. The Report Distribution Agent polls for the approval marker cell ('Summary!B2') on a 10-minute interval, with a 72-hour timeout before sending an overdue alert to the Finance Manager via Slack.

Templating approach

Variance commentary drafts use a structured prompt template with variable substitution: budget_line_label, var_dollar (formatted as currency), var_pct (formatted as percentage), and prior_period_trend (e.g. 'up for 2 consecutive periods'). Slack message text uses a Handlebars-style template stored in the config tab of the budget spreadsheet so the Finance Manager can adjust message wording without a code change. Google Drive file names follow the pattern: BudgetVsActuals_{YYYY}_{MM}_v{n}, where {n} increments if the agent runs more than once in a period.

Error logging

All agent errors are written to a dedicated 'Automation Log' tab in the budget spreadsheet with columns: timestamp, agent_name, error_code, error_message, row_key (if applicable), resolved (boolean). Separately, a critical-error alert is posted to the Slack finance channel if: (1) the QuickBooks OAuth token fails to refresh, (2) the Actuals Sync Agent returns status 'failed', (3) the Report Distribution Agent detects an empty actuals column at approval time, or (4) the approval polling timeout of 72 hours is reached. Error alerts are also sent to support@gofullspec.com for build-period monitoring.

Testing approach

All three agents are built and tested against a prior closed period (e.g. December 2024) in a sandbox copy of the budget spreadsheet before any run against the live template. The sandbox spreadsheet ID is stored as a separate credential set. A parallel run is conducted alongside the manual February 2025 close: actuals figures written by the automation are compared row-by-row against figures produced by the Bookkeeper to confirm zero discrepancy. Sign-off requires all mapped rows to match and zero formula cells overwritten.

Estimated total build time

Actuals Sync Agent: 16 hours. Variance Analysis Agent: 12 hours. Report Distribution Agent: 10 hours. Subtotal agent builds: 38 hours. End-to-end integration testing and parallel-run validation: included in the 38-hour estimate per the project snapshot. Total build effort: 38 hours across a 4 to 5 week delivery window.

Before any build work begins, the following must be confirmed: (1) QuickBooks OAuth credentials and realm ID are available and the service account has been granted read access to P&L reports. (2) The Google Sheets service account email has been granted Editor access on the master budget spreadsheet. (3) The account-to-budget-line mapping table exists in the spreadsheet and has been signed off by the Finance Manager. (4) The Slack bot has been added to all relevant channels and the dept_head_slack_map has been populated. (5) The approval marker cell reference and format have been agreed. None of these are build decisions; they are pre-build dependencies. Raise blockers to support@gofullspec.com immediately.

Developer Handover PackPage 4 of 4

Developer Handover Pack