Finance Dashboard with Cash Flow & Category Trends

A finance dashboard shows income, expenses, cash flow, and trends in one view. You build it in Excel and use Copilot and ChatGPT for the cash-flow formulas and the trend narrative.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It records transactions, splits them into income and expense, and shows cash flow and category trends.
• Where it is used: Personal finance and small-business bookkeeping reviews.
• What you learn: SUMIFS by type, running cash flow, charts, and AI insight writing.

Features

• Income vs expense totals
• Monthly cash flow
• Category breakdown
• Trend charts
• AI-written finance summary

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A transaction list with type and category

Folder Structure

finance-dashboard.xlsx
│
├── Sheet: Txns    (Date, Type, Category, Amount)
├── Sheet: Dashboard (KPIs, cash flow, charts)
└── Module: FinMacros (optional VBA)

• Txns: the transaction log.
• Dashboard: the metrics and charts.
• FinMacros: optional helpers.

Step-by-Step Development

Step 1 – Transaction table and totals

What are we building?

A Tx table and income/expense totals.

Why do we need it?

Cash flow is income minus expense.

How do we build it?

=SUMIFS(Tx[Amount], Tx[Type], "Income")
=SUMIFS(Tx[Amount], Tx[Type], "Expense")

• What it does: sums amounts where type is Income or Expense.
• Why needed: the two halves of cash flow.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write SUMIFS that sums Amount where Type is Income, and another for Expense."

Step 2 – Cash flow

What are we building?

A running cash flow column per month or row.

Why do we need it?

Shows whether you are gaining or losing money.

How do we build it?

=SUMIFS(Tx[Amount], Tx[Type], "Income") - SUMIFS(Tx[Amount], Tx[Type], "Expense")

• What it does: income total minus expense total.
• Why needed: the headline finance number.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write cash flow as SUMIFS Income minus SUMIFS Expense."

Step 3 – Category breakdown

What are we building?

Totals per category.

Why do we need it?

Shows where money goes.

How do we build it?

=SUMIFS(Tx[Amount], Tx[Category], A2, Tx[Type], "Expense")

• What it does: sums expense amounts for the category in A2.
• Why needed: each category gets a total.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write SUMIFS that sums Amount where Category equals A2 and Type is Expense."

Step 4 – Charts and AI narrative

What are we building?

Trend charts and a written summary.

Why do we need it?

Managers read charts and short text faster.

How we build it?

Chart income vs expense by month, then paste totals into ChatGPT.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Summarize this monthly cash flow in three sentences and note the riskiest month."

Complete Source Code

Dashboard formulas

=SUMIFS(Tx[Amount], Tx[Type], "Income")
=SUMIFS(Tx[Amount], Tx[Type], "Expense")
=SUMIFS(Tx[Amount], Tx[Type], "Income") - SUMIFS(Tx[Amount], Tx[Type], "Expense")
=SUMIFS(Tx[Amount], Tx[Category], A2, Tx[Type], "Expense")

AI prompts

ChatGPT: "SUMIFS Amount where Type Income / Expense."
Copilot: "Cash flow Income SUMIFS minus Expense SUMIFS."
ChatGPT: "SUMIFS Amount where Category A2 and Type Expense."
ChatGPT: "Summarize monthly cash flow in three sentences."

Running the Project

1. Open finance-dashboard.xlsx.
2. Enter transactions with type and category.
3. Dashboard shows totals, cash flow, and charts.
4. Expected output: a clear financial picture.

Testing

Test Case 1

Input: Income 1000, Expense 600. Expected Output: Cash flow 400. Explanation: 1000 − 600.

Test Case 2

Input: Expense Food 200, Expense Rent 400. Expected Output: Food 200, Rent 400. Explanation: SUMIFS splits by category.

Test Case 3

Input: No transactions. Expected Output: All totals 0. Explanation: SUMIFS finds no matches.

Common Errors

Cash flow negative surprise

Problem: Shows loss. Reason: A transaction typed as Expense by mistake. Solution: Use a Type dropdown for exact text.

Category total wrong

Problem: Off by some amount. Reason: Mixed Income/Expense in same category. Solution: Add the Type condition to SUMIFS.

Chart empty

Problem: No data. Reason: Wrong range selected. Solution: Select the summary cells, not headers only.

Improvements

• Add a savings rate KPI
• Use Power Query to import bank CSV
• Power Automate monthly email
• Ask ChatGPT for a budget from history
• Export to PDF

Challenge Exercises

1. Add a savings rate (cash flow / income).
2. Write a formula for the best month by cash flow.
3. Use ChatGPT to write VBA that imports a bank CSV.
4. Build a 12-month trend chart.
5. Let ChatGPT forecast next month's cash flow.