SQL Masterclass for Financial Analysis & Financial Reporting

Explore how a general ledger data model uses a data table linked to dimension tables through foreign and primary keys, with a calendar table to support time-based analysis.

Master the basics of sql by using select star from the tables gl, chart of accounts, territory, and calendar in Finn DB, then export the results to Excel.

Master selecting specific columns and filtering rows in SQL to retrieve just the data you need from the general ledger, using where clauses for account key, territory key, and amount.

Master sql filtering in select statements by using where with equals, dates, and text, and apply greater than, less than, not equal, and between for account keys and dates.

Learn to extract year and month from dates in the general ledger using SQL Server, alias them, and filter by a specific month across years while noting quarter function limitations.

Leverage SQL Server's datepart to extract year, quarter, month, day of year, week number, and weekday from a calendar table, enabling time-based filtering for financial analysis.

Learn how to analyze sales data across weeks using time intelligence in SQL, filtering by account 210 and week 51, extracting date parts, and preparing for totals and aggregates.

Learn to aggregate financial data in SQL using sum and group by to compute totals by territory key and account key, with where and order by to filter results.

Learn to create and use subqueries to aggregate data by year, extracting year from dates, summing amounts, and aliasing subqueries as tables for clean, year-level results.

Learn how to pivot data from rows to columns using the pivot function, turning yearly amounts into columns for 2018, 2019, and 2020 with summed totals.

Pivot the data to produce one row per account key with three year columns (2018–2020) by summarizing the GL in a subquery and summing amounts.

Create a view named my_summary to save a common query for reporting. Use select star from my_summary and treat it like a reporting table, not a separate one.

Learn how to join general ledger with chart of accounts and territory data to create richer reports, enabling balance sheet and profit or loss insights.

Learn how to join two tables by selecting specific columns from each table, such as date and amount from GL and country from territory.

Explore how inner joins return only matching records across two tables, while left, right, and full joins show how unmatched rows from left, right, or both sides are included.

Practice joining four tables in SQL to build a table with GL data, country from territory, account from chart of accounts, and calendar dates, preparing a profit and loss statement.

Format numbers in SQL by using the format function on the amount column to control decimals and create comma separated values for financial reporting.

Learn to build a profit and loss statement step by step using sql, joining gl with chart of accounts, aggregating amounts, and applying filters to show multi-year columns.

Pivot the profit and loss data to display years as columns, sum amounts by year, format values, and validate the final report against an Excel reference.

update the profit and loss statement to generate country by country results by joining the territory table, adding a country column, and applying grouping and ordering in the subquery.

Learn why balance sheets differ from profit and loss statements. Apply cumulative balances to report assets like cash across periods.

Learn to calculate a cumulative sum in SQL using sum(amount) over order by date on the GL table, and view distinct dates with carried balances to prepare a balance sheet.

Learn to compute a cumulative sum over dates using the over clause, partitioned by territory key and account key, with order by date to produce accurate, distinct balances.

Wrap the date in the year function to compile data by year, apply it to order by, and rename the column to year to reveal 2018–2020 values.

Build a balance sheet query by joining GL and territory data to pull year, country, class, accounts, and amounts, then compute a cumulative sum with a window function.

Pivot the balance sheet query to three year columns (2018, 2019, 2020), verify values by country, then produce a consolidated company balance sheet and sort results.

Learn to pivot a single amount column into multiple year-based columns using case when and sum, then group by account key and territory key for organized results.

Learn to calculate financial ratios with SQL by aggregating sales and gross profit, storing values in columns, and joining GL with chart of accounts to filter by subclass sales.

Learn to calculate gross profit and net profit using case when and filters from the chart of accounts, with class and report based criteria.

Master ebitda, operating profit, and pbit calculations in sql using case when filters on sales, cost of sales, and operating expenses from the chart of accounts.

Calculate gross profit margin by dividing gross profit by sales and optionally multiplying by 100. Use a subquery and a view to simplify calculations and prepare for country-level analysis.

Break down financial values by country to enable country-level analytics using joins between general ledger and territory data, group by country, sort, and save as a reusable view.

Learn to calculate gross, operating, and net profit margins from sales using sum functions, validate results with Excel, and apply country slicers to compare performance.

Develop a balance sheet query by joining general ledger with chart of accounts, extracting year, and calculating running sums for assets, liabilities, and equity, including inventory and country breakdown options.

Compile balance sheet values and PL data into a view by joining on country and year, creating a unified table for assets, sales, EBITDA, and net profit to calculate ratios.

Learn to calculate key financial ratios from the profit and loss and balance sheet data, including asset turnover, roce, roe, gearing, and current and quick ratios.

Learn to calculate interest cover and turnover periods for receivables, payables, and inventory by updating the PL values and balance sheet views with cost of sales and interest expense.

Merge updated pl values and balance sheet values into the fin values view, then drop and recreate it to include receivables, payables, cost of sales, and interest expense.

calculate key financial ratios in sql, including interest cover, inventory turnover period, receivables and payables turnover periods, and validate results before preparing the cash flow statement.

Filter financial ratios by country with a simple query, selecting where country equals Germany, and verify results with an Excel slicer for ratios like return on capital employed and gearing.

Learn to build a cash flow statement in SQL with opening balances, period values, and sign changes, guided by a detailed structure contrasting P&L and balance sheet logic.

Upload cash flow table (CF) to the database using the import flat file wizard, preview and import data, then start writing cash flow statement preparation logic in a new query.

Construct a cash flow statement in SQL by joining GL and CF, aggregating by year, and pivoting 2018–2020 columns with case-based sign logic and value types.

Calculate the start-of-year cash and cash equivalents by applying a lag of the prior year's closing balance, then pivot and merge data to finalize the cash flow statement.

Merge the closing and opening balance queries with the union command to compile a cash flow statement, showing cash and cash equivalents at the start and end of the year.