Use Google Sheets' AI to Build a Branch Production Dashboard

What This Does

Google Sheets' Gemini AI can help you build formulas and visualizations that transform your raw Encompass pipeline export into a weekly production dashboard — showing pull-through rates, loans per LO, pipeline value by stage, and average days to close. What currently takes 90 minutes of manual work becomes a 15-minute process.

Before You Start

• Google account (free Gmail or Google Workspace)
• Encompass pipeline export saved as a CSV file (from Reports → Pipeline Report)
• Know the column names in your export (typically: Loan Number, LO Name, Stage, Loan Amount, Application Date, Expected Close Date)

Steps

1. Import your Encompass export into Google Sheets

1. Go to sheets.google.com and click + Blank
2. Click File → Import → Upload → select your CSV export
3. Click Import data — your pipeline loads as rows

What you should see: Your pipeline data in columns with headers in row 1.

2. Open the Gemini sidebar

1. Look for the Gemini icon (sparkle symbol) in the right toolbar, or click Extensions → Gemini for Google Workspace
2. A sidebar opens on the right with a text input

Troubleshooting: If Gemini isn't visible, use the formula approach — click any empty cell and start with = then describe your formula need in plain language using the Formulas help feature.

3. Ask Gemini to build your key metrics

Type each request and click Insert to add the formula to your sheet:

1. Pull-through rate per LO: "Write a formula that calculates how many loans in column D (Stage) are 'Closed' divided by all loans for each unique LO name in column B"

2. Pipeline value by stage: "Create a SUMIF formula that totals Loan Amount (column E) for each unique Stage value in column D"

3. Average days in pipeline: "Write a formula that calculates average days between Application Date (column F) and today's date for all loans that aren't yet closed"

4. LO league table: "Summarize total loan count and total loan amount by LO name from this data"

4. Add a summary tab

1. Create a new tab: click the + at the bottom left
2. Name it "Weekly Summary"
3. Ask Gemini: "Create a summary table on this sheet that pulls total pipeline count, total pipeline value, and loans closing this week from the Pipeline tab"

Real Example

Scenario: Monday morning, you have 45 minutes before the team call. You need to know: who has the most loans closing this week, whose pipeline is stalled, and whether you're on track for month-end goal.

What you do: Import Encompass export → ask Gemini for: "Flag all loans in 'Underwriting' stage that have been there for more than 15 days" → Gemini writes the formula → conditional formatting highlights the stalled loans in red.

What you get: In 15 minutes you have a color-coded pipeline with your key metrics — enough to run a sharp 30-minute team call.

Tips

• Save the Google Sheets template and import a fresh export each week — Gemini's formulas recalculate automatically
• Ask Gemini to add a chart: "Create a bar chart showing pipeline value by LO" for a visual you can share with corporate
• If formulas break, ask Gemini to debug: "This formula returns an error — fix it: [paste formula]"

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.