Excel for Financial Modelling

Learn to boost efficiency in Excel by using keyboard shortcuts for navigation, including control shortcuts to jump between non-empty cells, highlight rows and columns, and move across tabs.

Master speeding up Excel tasks by using the alt key to navigate the ribbon, jumping to home, data, and formula with shortcuts, and quickly applying freeze pane and decimal adjustments.

Balance keyboard and mouse to boost efficiency in Excel tasks, showing when resizing rows or columns, naming ranges, and navigating the ribbon is faster with the mouse.

Master common keyboard shortcuts for efficient spreadsheets, including ctrl+c, ctrl+v, and paste special (ctrl+alt+v), while using shift and ctrl to navigate, highlight, group, and collapse sections.

Learn to write clear, efficient Excel formulas by using spaces after elements, starting with the equal sign, and reading formulas easily for collaboration.

Master populating formulas in Excel for financial modelling by calculating cost of goods sold as revenue times 40% and using F4 to anchor references, with Ctrl+R and Ctrl+D for filling.

Master absolute references to anchor growth rates across revenue streams in Excel, applying year 1 values to subsequent years with F4 anchoring and sense checks.

Use range names in Excel to replace absolute references in financial models by naming a cell (e.g., COGS) and referencing it in formulas via the name manager.

Learn to apply powers for revenue and cost indexation in a financial model, using base years, inflation rates, and proper anchoring with F4 to create dynamic growth across years.

Learn to format Excel cells using the format cells dialog (Ctrl+1), exploring categories like general, number, currency, and date, with custom formats for positive, negative, and zero.

Use format cells (ctrl+1) to append units like 'apartments' to a number, keeping it numeric for calculations in real estate financial models.

Learn to compute totals in excel with the sum function, using revenue and cost of goods sold to find gross margin, while mastering range selection, tab-completion, alt+equal, and control+enter.

Apply the Excel average function by selecting a range and entering the formula, note that empty cells are excluded while zeros are included, and always sense-check the result.

Explore min and max functions in Excel for financial modelling, using typing, F4 anchoring, and Ctrl R and Ctrl D to populate across and down, with max ≥ min check.

Learn how the count function in Excel tallies non-empty numeric cells within a range, showing that strings aren't counted and how this helps identify missing values in a dataset.

Master copy paste in Excel: use control C and control V for all data, and control Alt V for paste special with values, formats, or formulas.

Master date manipulations for financial modeling by tracing the model timeline, marking revenue and cost start dates, and navigating asset life stages like construction, operation, and development.

Master date handling in Excel for financial modelling by using today and now, extracting day, month, year, and weekday, and selecting manual, partial, or automatic calculation modes.

Learn to use the end of month (EO month) function in Excel to calculate end dates across monthly, quarterly, semi-annual, and annual timelines, with start dates and month adjustments.

Learn how the days 360 formula standardizes months to 30 days and years to 360 days to compare two dates for financial modeling.

Apply simple if statements in Excel to drive binary flags in financial models, using a logical test to return true or false and propagate results across years.

Explore alternatives to if statements in Excel by evaluating conditions directly to create shorter, non-nested formulas. Learn to convert booleans to numbers and separate functions for audit-friendly, faster financial models.

Master nested if statements in Excel for financial modelling by using the and and or functions to combine conditions, convert tests to numbers, and create concise, efficient formulas.

Explore how to use averageif and averageifs to compute conditional averages and use countif to tally values meeting criteria, with practical examples from financial modelling.

Demonstrates using sumif to aggregate profits by year and explains maxifs to identify yearly maximum profits, with notes on date-year extraction and version considerations.

Learn to use vlookup to pull information from large data sets and hlookup for horizontal data, including dynamic column references, anchoring, and data validation dropdowns for product profit margin.

Explore how index and match serve as a dynamic alternative to horizontal and vertical lookups, using nested steps to locate a region row and return a value.

Choose XLOOKUP over index match, vlookup, and hlookup for a single, dynamic formula that anchors the lookup and return arrays, eliminating nested lookups and working across newer Excel versions.

Master the offset function to build dynamic references from a reference cell across rows and columns for financial models, enabling multi-scenario analysis with live versions and flexible ranges.

Learn data cleansing for large data sets in Excel, fixing date formats with substitute to extract months and prepare data for analysis.

Use the len function on the order date to count digits and confirm a 10-digit date pattern. Populate the column down with control t and control d across the dataset.

Use left and right functions to extract day and year from 10-digit string. Days are the first two digits; years are the last four; multiply by 1 to convert.

Extract the month from a middle string in Excel using mid, left, and right, stressing data consistency and start positions; use find to locate apostrophes when needed.

Master the round function to format numbers to specific decimal places and use round up or round down in financial modelling, turning decimals into whole totals or units.

Explore Excel's data types, including geography, currencies, and stocks, to convert country names into live datasets with GDP, CPI, population, and stock metrics.

Discover how pivot tables in Excel function as a data analytics tool for financial modelling, enabling forecasting, analysis of actuals and financial statements, and quick creation of pivot tables.

Apply sorting and filtering in pivot tables to refine monthly and yearly data, view each representative's total cost, and sort by name or totals for insights.

Group data in pivot tables by dragging months into columns, then create quarterly groups (January–March, April–June) with a right-click, refining filters for clearer analysis.

Harness pivot tables to summarize and analyze data in Excel, switch between sum and count, review max values, and present percentages for secure external sharing.

Explore pivot charts built from pivot tables, using the design and analyze tabs to customize colors and layout, and see how filters dynamically update the chart and table.

Learn how pivot tables automate quarterly or monthly reports by refreshing the source data and updating dashboards. Explore Power BI basics, drag-and-drop visuals, and using Excel as a data source.

Select the data, insert a simple line chart, and customize axis titles, legend, and chart elements to clearly visualize revenue and year data in Excel.

Explore how to change the graph type in Excel, access the chart design options, and switch between line charts and bar charts with markers to compare yearly data.

Master graph customization in Excel for financial modelling, including changing marker and line colors, using shape fill and outline, and applying dash types via the format data series.

Master advanced chart formatting in Excel for financial modelling by creating a combo chart with stacked areas and lines, then add trend lines and error bars for revenue and costs.

Explore specialized charts in financial modelling, including waterfall, funnel (tornado), and radar, to visualize sensitivities and base-case changes.

Build a dynamic Excel graph that updates with start and end year inputs, using offset and named ranges for revenue and timeline, then tailor the chart as you adjust years.