Transcription
In this video, I'm going to show you how to build five finance dashboards with AI that are fully interactive, cleanly structured, and ready to use in real analysis. As a quant and finance automation developer, the goal for me is not just to generate charts, but to build dashboards that are consistent, auditable, and easy to reuse every month without rebuilding anything.
So, what we're going to do in this video is create a full set of finance dashboards step by step, including a P&L dashboard, revenue analysis, cost center tracking, pipeline forecasting, and cash flow monitoring. Each dashboard will be generated as a single self-contained HTML file that you can download, open in any browser, and refresh new data whenever you want. And I'll show you the exact workflow I use to structure the data, define the calculations, and design the prompts so the output stays reliable.
Now, if you want the prompts and the data sets from this video, they're available in my free community. The link is in the description.
But let's get started with the first dashboard. So, the first dashboard we're going to build is a P&L dashboard for financial performance. This dashboard is going to show where profit is coming from and what is affecting margins. It highlights profitability drivers like product margins, cost structure and it supports decisions around identifying your most profitable products, detecting margin compression early and understanding which expense categories are impacting EBIT DA.
The input data is a CSV where each row represents one product in one region in one month. It includes revenue, COGS, gross profit, gross margin, the individual expense categories like marketing, payroll, technology, logistics, and admin plus total, OPEX, EBIT, DA, and EBIT DA margin. All financial fields need to be numeric, and dates need to follow a year-year-month format.
Now, the prompt is structured around a few things that matter if you want the output to be usable. First, it defines the exact CSV schema the dashboard expects, including every column name and what each one means. Now, this forces the AI to build parsing logic that validates the data on upload rather than guessing at column mappings. Second, it specifies the exact calculations. Now, gross margin is gross profit divided by revenue. EBIT DA is gross profit minus total opex. Now these are defined in the prompt. So the dashboard computes them consistently. Now third, the prompt requires interactive filters for month, region and product that update every KPI and chart dynamically. And fourth, it includes a scenario panel where you can adjust a cost category by a percentage and see the estimated EBIT DA impact in real time.
Now the output is a single self-contained HTML file. No server, no dependencies beyond charts.js from a CDN. You download it, you open it in a browser, and the dashboard renders from the CSV you uploaded with the prompt.
Now, once the dashboard loads, the KPI cards show the full picture. Revenue sits at $51.08 million, COGS at $25.43 million, gross profit at $25.65 million with a 50.22% gross margin. After operating expenses of $7.5 million, EBITDA comes in at $18.14 million with a 35.5% EIT DA margin. That is a strong profitability profile and you can see it immediately without opening a spreadsheet.
The profitability trend chart plots revenue, gross profit and EIDA by month. If revenue keeps climbing but EVIT DAR flattens, you know costs are scaling faster than the top line and you can see that developing in real time rather than catching it at the end of the quarter. The product profitability chart shows product A at a 56.2% gross margin on two 23.22 million of revenue while product C generates 8.63 million at only 40.2%.
Clicking product C filters the entire dashboard to that product, so you immediately see where pricing or cost adjustments might be needed. The waterfall chart is where cost decisions get made. Select December and it rebuilds the full P&L flow. Hover over operating expenses and you can see marketing alone represents about 43% of OPEX for that month.
Before handing this to anyone, verify that the KPI totals match the sums in the CSV. Confirm that revenue minus COGS equals gross profit and that gross profit minus OPEX equals Ebit D. Check that the expense categories sum to total OPEX. These reconciliation checks take less than a minute and they are what make the dashboard trustworthy.
Now we can move on to dashboard number two. Dashboard number two is a revenue performance dashboard. Now, the use case I'm going to walk through decomposes year-over-year revenue change into volume effects and price effects. So, you can see exactly how much of the growth came from selling more units versus charging higher prices. You can apply this same dashboard structure to any revenue decomposition use case that you need.
Now the data set contains monthly sales data across two years broken out by region, product and channel with columns for units sold, average order value and revenue for both 2024 and 2025. The critical calculation in this prompt is the year-on-year decomposition. Volume effect equals the sum of the difference in units multiplied by last year's average order value. Price effect equals the sum of this year's units multiplied by the change in average order value. There's also a residual term for cross effects. If you leave these calculations undefined, the AI will estimate them differently every time and the decomposition will not reconcile to the total revenue change.
The prompt also includes a scenario simulation panel with sliders for volume lift, price changes, and channel shift, so you can project revenue impact from hypothetical adjustments.
The KPIs immediately tell the growth story. Total revenue went from 200.17 million in 2024 to 223.66 million in 2025, an 11.7% increase. Average order values sit at $54,327 and the company sold over 411,000 units. North America leads at $88.34 million, roughly a third of total revenue.
The growth decomposition waterfall is where the real insight sits. Of the 23.49 million in additional revenue, 22.73 million came from volume and only 756,000 came from price. That means nearly all growth is volume driven. Now, if you're in a strategy meeting and someone asks whether the company should invest in distribution or pricing power, this chart gives you the answer with actual numbers behind it.
Now the revenue trend chart with the 3-month moving average surfaces something worth investigating. Q2 revenue totals 58.98 million but Q3 drops to 50.6 million, a 14.2% decline. Now that seasonal pattern or channel disruption needs to be understood before building any forward forecast off the trend. Product B dominates at $117.95 million, over half of total sales. Its higher average order value is what drives that, which means revenue concentration risk sits with a single product line.
Make sure to validate that the total revenue equals the sum of the revenue column in the CSV. Confirm that the decomposition equation balances. Total revenue change should equal volume effect plus price effect plus residual. And verify that revenue always equals units multiplied by average order value for any filtered slice.
Now once you have verified everything, we can move on to dashboard number three. For the third dashboard, we are building a cost center dashboard. Now this one tracks which departments and cost categories are driving spending increases year-over-year with drill down all the way to individual GL entries. Now you can adapt this to whatever cost reporting structure your organization uses.
The data set is a GL level expense file with each row representing a cost allocation by department, category, GL account, and month. It includes costs for both 2025 and 2024, plus a monthly budget column, which allows the dashboard to show actual versus budget variance alongside year-over-year cost movement.
Now the prompt structure here requires the dashboard to compute department level and category level aggregations, year-over-year variance by department, and the variance waterfall that bridges from 2024 total opex to 2025 total opex through each department's contribution.
Total operating cost for 2025 comes in at 10.65 million, up 229,000 from 2024, which is a 2.2% increase. Sales is the top spending department at 3.27 million, representing almost a third of total opex.
The variance waterfall breaks down where that 229,000 euro increase came from. Sales contributed 142,000, marketing 88,000, operations 37,000, while it decreased by €71,000. That tells leadership exactly which departments are driving cost growth.
Now the category breakdown reveals that payroll dominates at 4.29 million. But since payroll reductions are operationally difficult, the more actionable targets are consulting at 1.62 million and travel at 1.48 million.
Clicking consulting filters the dashboard to show which departments generate those vendor costs. The drill-down table lets you search by department and category to find specific GL entries, filtering to IT and software returns the line items that make up the €491,000 in software spending. And that figure matches the aggregated chart totals. That reconciliation between detail and summary is what makes the dashboard auditable.
Check the total opex equals the sum of the cost_2025 column. Verify that department totals match the aggregated data set. Confirm that variance calculations equal cost_2025 minus cost_2024 for each row and validate that category totals sum to total operating cost.
So that is three dashboards done. And let me show you the fourth one which moves to sales pipeline dashboard for revenue forecasting. And what we're doing here is we're applying probability weights to each funnel stage so that you can see how likely the current pipeline is to convert into actual closed revenue.
The data set has each row representing a group of deals at a specific stage broken out by month, region, and sales rep. The stages run from MQL through SQL, opportunity, and negotiation, closed one. And each row includes deal count, average deal size and total value.
The most important detail in this prompt is the stage probability weighting. Now the prompt defines default probabilities: 5% for MQL, 20% for SQL, 50% for opportunity, 70% for negotiation, and 100% for closed one. So forecast revenue is computed by multiplying each deal's value by its stage probability. So these probabilities are editable in a setting panel. So teams can adjust forecast conservatism based on their own historical close rates.
Now, the prompt also specifies conversion rate calculations between stages, a scenario simulator for volume and win rate changes, and a rep-level performance view with individual pipeline distribution and close rates.
The KPI summary shows a total pipeline of $69.14 million, but once you apply the probability weighting, forecast revenue drops to $28.73 million. That gap between pipeline value and probability weighted forecast is exactly why raw pipeline numbers are misleading without stage adjustment. The team has 1,27 active deals and a 58.1% win rate from opportunity to closed one.
The funnel chart reveals that the largest drop-off happens between MQL and SQL, which means lead qualification needs improvement. If the team is spending on top of the funnel marketing, but leads are not converting past qualification and the pipeline value is inflated with deals that will never close. The rep performance chart shows rep C controlling 20.73 million of pipeline value, which is about 22% of the total. Filtering to the rep shows their stage distribution, win rate, and average deal size, which is exactly what sales leaders need for coaching and quot reviews.
Verify that the total pipeline value equals the sum of total_value in the CSV. Confirm that forecast revenue equals the sum of total_value multiplied by the stage probability for each row. Check that win rate equals closed deals divided by opportunity deals and validate conversion rates against the stage counts.
Now let me show you how the final dashboard, which covers cash flow dashboard for liquidity monitoring. The last dashboard we're building is a cash flow dashboard. Now this one covers liquidity monitoring, receivables aging and forward cash projections with scenario sensitivity.
The data set is a monthly cash flow file with operating, investing and financing cash flows, free cash flow, end-of-month cash balance, accounts receivable with aging buckets at 0 to 30, 30 to 60, 60 to 90 and 90+ days, accounts payable and inventory.
Now the prompt requires a dashboard to compute a cash runway metric. If average monthly free cash flow is positive, it displays a healthy status. If negative, it calculates how many months of cash remain by dividing the current balance by the average monthly burn. It also defines a six-month forecast projection that carries forward current cash flow patterns and lets you adjust revenue growth, payment delays, and expense increases through scenario sliders.
One important validation the prompt includes is a check that the receivables aging buckets sum to total accounts receivable with a warning flag if they diverge by more than 0.5%.
The December 2025 snapshot shows a cash balance of 14.6 million, year-to-date operating cash flow of 7.81 million and a free cash flow of 5.66 million. Average monthly free cash flow is positive at roughly €471,000, which means the company is generating cash rather than burning it. So there's no immediate liquidity risk.
The receivables aging breakdown surfaces a specific collection opportunity. Of the 3.53 million in total AR, about 544,000 is more than 90 days overdue, which represents roughly 15% of receivables. That concentration in the 90-day plus bucket is a clear signal for the collections team. The working capital chart shows receivables exceed payables by about 1.3 million euros, meaning cash is tied up in unpaid invoices. Improving collection speed would directly increase available liquidity without any change in operating performance.
The scenario tool projects what happens if payment delays increase AR by 15%. Cash balance would decline by 14.6 million to roughly 14.07 million. And even with that deterioration, the company maintains a comfortable buffer. But the exercise quantifies the sensitivity so the treasury knows how much slack exists.
Verify the total operating cash flow in the KPI cards equals the sum of the operating_cash_flow column. Confirm that the aging buckets sum to total accounts receivable. Validate that net working capital equals AR minus AP minus inventory and check that the forecast projections correctly apply the scenario adjustments you defined.
Now, if you want to use the prompts and the data sets from this video, they're all available in the free community, the link is in the description below. Now, every one of these dashboards follows the same workflow. Clean CSV structured prompt with defined calculations and validation rules and a self-contained HTML file you can refresh with new data every month.
Always verify the outputs before presenting them. Confirm the KPI totals reconcile to the source data, that waterfall charts balance, and that filtered views match the underlying rows. And that takes under a minute per dashboard.
Thank you for watching. If you got any questions or any comments, leave them below and I will try and get to them. And I look forward to seeing you next time.