P
AI for Credit Analyst

Use Excel's Copilot to Build a Covenant Tracking Dashboard

Tool:Microsoft Excel
AI Feature:Copilot
Time:15-20 minutes
Difficulty:Beginner
Microsoft Excel

What This Does

Copilot builds the compliance tracking formulas and conditional formatting for a covenant monitoring spreadsheet, so you can see at a glance which borrowers are compliant, at risk, or in breach across your portfolio, without writing complex IF/VLOOKUP formulas yourself.

Before You Start

  • You have Excel open with a Microsoft 365 subscription that includes Copilot
  • You have a spreadsheet with borrower names, covenant requirements, and current actuals (or you're setting one up)

Steps

1. Set up your data structure first

Create columns: Borrower Name | Covenant Type | Required Value | Actual Value | Result | Status

2. Open Copilot

Click Copilot in the Home ribbon.

3. Build the compliance logic

Describe the pass/fail logic you need.

4. Add conditional formatting

Ask Copilot to add visual indicators to the Status column.

Real Examples

DSCR compliance check: "In column E (Result), write a formula that calculates DSCR by dividing column D (Actual EBITDA) by column C (Annual Debt Service). In column F (Status), write a formula that returns 'PASS' if E exceeds 1.20, 'WATCH' if E is between 1.10 and 1.20, and 'BREACH' if E is below 1.10. Start in row 2."

Leverage covenant: "In column F, write a formula that returns 'COMPLIANT' if the Debt/EBITDA in column E is less than or equal to 4.0, 'COVENANT WARNING' if between 4.0 and 4.5, and 'COVENANT BREACH' if above 4.5."

Conditional formatting for the whole dashboard: "Add conditional formatting to the Status column (F2:F50): highlight green if the cell says 'PASS' or 'COMPLIANT', yellow if it says 'WATCH' or 'WARNING', and red if it says 'BREACH'. Also highlight the entire row red if column F says 'BREACH'."

Summary count at the top: "In cell B1, write a COUNTIF formula that counts how many cells in column F (rows 2 through 50) contain the word 'BREACH'. Label cell A1 'Breaches Today'. Do the same for 'WATCH' in B2 and 'PASS' in B3."

Tips

  • Start with 5-10 test borrowers before scaling to your full portfolio
  • Ask Copilot: "Add a 'Last Updated' column that reminds me when I last entered actuals. Use data validation to restrict input to dates."
  • Export this sheet as a PDF for your monthly portfolio review meetings

Tool interfaces change. If the button has moved, look for AI/smart options in the Home ribbon.