PullSpark Project Outline

Problem

PullSpark currently uses complex spreadsheets to estimate project budgets and track actual expenses for client projects. These spreadsheets include multiple tabs per project (e.g. separate sheets for various event components and a summary “dashboard”) as well as reference rate sheets for PullSpark’s service offerings. While this system works, it has several limitations: maintaining formulas across sheets is cumbersome, summarizing optional vs. sold work is difficult, and updating actuals requires manual effort. PullSpark is seeking to migrate this process to Airtable to improve collaboration, real-time tracking, and ease of use.

Solution

This report outlines a proposed Airtable base structure that meets PullSpark’s needs. The design supports: project budgeting and estimating, ongoing expense tracking (actual costs and revenue), role-based interfaces for different team members (producers, creatives, finance), integration with Harvest for automatic time log imports, and the ability to demonstrate an existing project (e.g. IMC March 2025 or Wiz) in the new system to highlight its benefits. The base is organized into multiple related tables (Projects, Clients, Services/Rate Card, Estimates, Expenses, Time Logs, Invoices, Team Members, etc.), each described below with their fields and usage.

Objectives

1. Provide robust, connected project budgeting.
2. Track actual project expenses and compare to estimates in real time.
3. Offer multi-user, role-based access with user-friendly Airtable Interfaces.
4. Integrate with Harvest for automatic time logging.
5. Replace the current multi-sheet dashboards with a centralized real-time portfolio view.
6. Migrate existing project reports
7. Client facing dashboard

Clients

Stores basic client information so that each Project can link to a Client record.

Usage: Reduces repeated typing of client details. A Project record will link to one Client.

Programs

The Programs table defines higher-level groupings or “programs” under which multiple projects can be organized. For example, “Annual IMC Series”.

Usage: Link each Project to a Program when relevant (many projects can roll up into one program). Summaries (like total revenue, total hours, or overall profit) can be rolled up at the Program level to provide big-picture insights.

Projects

The Projects table is the central hub. Each record is an engagement (e.g. an event or a film production project).

Usage: This table replaces the high-level “dashboard” tab in Excel. All sums/rollups come from child records in Estimate Line Items, Expenses, and Invoices. Quickly see a project’s financial health without juggling multiple sheets.

Estimate Line Items

Each record is a Line Item in the Project (like a row in the budget). It can be summed, conditionally calculated or filtered based on specific date in the line item. It is also be connected to a Services recored to autofill important data. Replaces the line-by-line breakdown in separate tabs.

Usage: The user creates these line items when planning a project. The project’s Budget Total (Sold) sums only those items where Status = In SOW. Items marked Optional don’t affect the sold total unless changed to In SOW. This solves the “sandbox” problem by allowing easy toggling from optional to approved scope.

Services

A reference table for standard roles and items with cost/billable rates. Connected to a Project Line Item in order to pre-fill important data. Replaces the multiple “rate sheets” in Excel.

Usage: Estimate Line Items link here. The line item can then auto-populate the standard rates and category into Project Estimate Line Items.

Actuals

A table to log actual expenses and time entries for the project. Logs hours from Harvest or manual entries, used to track actual labor and non-labor expenses (travel, vendor costs, etc.). Helps compare budgeted hours vs. actual.

Usage: A Harvest integration automatically inserts these. The Projects table can roll up total labor cost or hours. This solves the problem of manually reconciling time data. Producers or finance can log each real expense. The Projects table rollups keep track of total actual cost. Optionally linking to the relevant Estimate Line Item helps track “budget vs. actual” at a granular level.

Invoices

Tracks revenue by listing each invoice or billing milestone.

Usage: The sum of all invoiced amounts for a project is rolled up in Projects as Revenue Invoiced. Helps track how much of the project budget has been billed and whether final payment is still outstanding.

Team Members

Lists PullSpark staff and contractors, storing cost rates for calculations and enabling assignment.

Usage: When a Time Log entry references a Team Member, Airtable can pull that user’s cost rate to calculate cost. Also used in Projects to designate a Project Lead or Stakeholders.

Producer’s Project Dashboard

A single-screen interface showing:

• Project overview (budget vs. actual, margin, etc.).
• An Estimate Line Items grid or form to add/edit budget details.
• A Sandbox total metric (all-in potential).
• Hours overview comparing budgeted vs. actual from Harvest logs.
• Expenses form to record real costs.
• Notes & Attachments area for project documentation.

Creative/Team Member

• Filters tasks or line items assigned to that user.
• Summarizes budgeted hours vs. hours logged (via Harvest integration).
• Possibly shows upcoming deliverables or deadlines.

Finance & Management Interface

• A portfolio dashboard listing all active projects with real-time financials (budget, actual, invoices).
• Invoice tracker by due date or status.
• Possibly overall cash flow or revenue projections (sum of upcoming invoices).
• Quick view of any projects that exceed budget or have overdue invoices.

Phase 1

Blueprint Confirmation

Details

• We’ll begin with a kickoff call to review the project Blueprint including: how data will be structured, which metrics need to be tracked, review useful automations and AI integrations, how different team members will interact with the system, and how they can be onboarded—including mobile shortcuts for quick data entry.. We’ll also test and confirm the Harvest time tracking integration. A second review call will be held to confirm all updates before development begins.

Deliverable(s)

• A detailed Blueprint that includes:
• Finalized table and field structure
• Defined list of all metrics and how each will be calculated
• Harvest time tracking integration plan, including mapping and testing process
• Defined list of system automations and triggers
• Outline of planned AI integrations or enhancements
• Interface structure for each user role, including mobile shortcut workflows
• System rollout and onboarding strategy for the full team

Review

• Finalized project Blueprint

Phase 2

Airtable Base Setup, Core Tables & Relationships

Details

• Create the core system structure and relationships in Airtable. We’ll build out the necessary tables, fields, automations, and workflows based on the approved blueprint from Phase 1.
• Implement key formulas, rollups, alerts, and any other automation needed to handle budget vs. actual tracking, optional items, or custom financial reporting. This includes ensuring that the system can handle core calculations (such as margin, pass-through, or variance) and producing alerts or notifications if projects exceed certain thresholds.

Deliverable(s)

• A working Airtable base with the essential schema and fields based on the Blueprint.
• A fully functional set of calculations and automations that align with the agency’s reporting needs based on the Blueprint.

Review

• Demonstration of example project data in the system to verify that all automations and calculations work as expected.

Phase 3

Harvest Time-Tracking Integration

Details

• Integrate time-tracking data into Airtable from Harvest. This integration ensures that actual hours logged flow seamlessly into the system, allowing accurate comparisons between estimated vs. actual effort. Configuration details (daily sync, on-demand pulls, etc.) will be finalized and tested.

Deliverable(s)

• A working integration pipeline that brings time logs into Airtable in near-real time (or as scheduled), updating relevant projects and metrics automatically.

Review

• A test run confirming that newly logged hours appear in Airtable and properly update the relevant budget/actual fields.

Phase 4

Historical Data Migration

Details

• Create and run an automation script to import data from the existing spreadsheets, ensuring that all historical or in-progress projects are brought into Airtable. This process may involve cleaning or standardizing certain fields to align with the new structure. Once the import is complete, we will verify that the system’s key metrics, calculations, and relationships match those in the original spreadsheets.

Deliverable

• A completed script and executed import for all required project data, confirmed to be accurate and consistent.

Review

• Validation of the imported records against existing data, ensuring calculations and reports in Airtable align with what was captured in the legacy spreadsheets.

Phase 5

Airtable Interfaces for Each Role

Details

• Design and configure role-based interfaces to streamline how different users (such as producers, finance, directors, or creative team members) interact with the system. Each interface will be tailored to display and edit only the fields relevant to that role. We will focus on simplifying data entry, reporting, and approval flows so that each team member has a clear, user-friendly view of their responsibilities.

Deliverable(s)

• Custom interfaces in Airtable for each role, addressing both high-level overviews (e.g., directors’ dashboard) and day-to-day task management (e.g., producers, finance, creatives).

Review

• A session with representatives from each role to confirm that interfaces meet their needs and are easy to navigate.

Phase 6

Client Facing Interfaces

Details

Design and build a simple Airtable Interface that allows PullSpark’s clients to view and interact with specific project information relevant to them. This may include high-level project timelines, status updates, deliverables, or review links—while ensuring no internal financial or sensitive data is visible. We’ll also determine what actions clients should be able to take (e.g., submitting feedback, approving deliverables, or uploading files), and configure the interface accordingly with proper permissions and access controls.

Deliverable(s)

• A client-facing interface tailored to project collaboration needs
• Configured views, permissions, and inputs for client interaction (e.g., file uploads, approvals, status updates)
• Branding elements or instructions for client access

Review

• Internal demo and stakeholder sign-off

Phase 7

Getting Started Documentation

Details

• Provide training and documentation for all relevant team members. This may include embedded tutorials or help text in Airtable, written guides, and live training sessions. We will also conduct a short pilot period where users work with real project data, allowing us to gather final feedback and make any minor improvements before full rollout.

Deliverable(s)

• Training resources (e.g., in-app help or PDF documentation) and a completed round of user acceptance testing with real data.

Review

• Final adjustments based on user feedback to ensure everyone is comfortable adopting the system in day-to-day operations.