Financial Modelling in MS Excel

Explore how changing loan amount, interest rate, and duration affects monthly payments using what-if analysis, data tables, goal seek, and conditional formatting.

Learn to format Excel sheets professionally using format painter and macros to apply consistent styles, remove gridlines, and reuse formatting across sheets in this workbook.

Explore data filters in tables to analyze student records, freeze header rows for easy scrolling, wrap text without widening columns, and navigate large Excel datasets efficiently.

Explore conditional logic in Excel by applying the if function, the and function, and countif to classify pass/fail, mark absences, and allocate scholarships.

Learn to create and customize pivot tables in Excel by dragging fields to rows, columns, and values, using filters and slicers to summarize four thousand rows into clear, actionable insights.

Learn how to format as a table in Excel to enable filters, pivot table analysis, dynamic ranges, and automatic totals, counts, and averages on sales data across shops.

Learn to use DSum and DAverage in Excel to create a dynamic database, define criteria, and compute totals or average values for specific shops such as Metro.

Excel's error checking flags inconsistencies in formulas with a green triangle, such as unexpected price entries; disable this by turning off error checking options under formulas.

Explore time value of money, learn to compound and discount cash flows, derive future value and present value, and understand NPV and IRR.

Use discounted cash flow to evaluate a 100,000 investment with three 45,000 inflows, at a 10 percent discount rate. Compute present value and net present value to see wealth impact.

Compute present value and net present value using a discount rate, illustrated by 10% yielding a net present value of 11,908, showing the project’s value over the best alternative.

Explore how net present value relates to discount rate and learn to calculate the internal rate of return (IRR) using a two-rate method with positive and negative NPVs.

Develop a simple, flexible financial model to forecast profit and loss statements from historical data, ensure consistent columns and checks, and use scenario analysis for best, worst, and likely outcomes.

Forecast a profit and loss in Excel by projecting revenue with best, most likely, and worst case scenarios using the choose function, and calculating EBITA, depreciation, and cost of sales.

Learn to separate fixed and variable costs in cost of sales, model them as a percentage of revenue, and use scenario analysis in Excel to forecast profit and loss.

Finalize a forecasted profit and loss in Excel by modeling fixed and variable admin costs as a percentage of revenue, plus scenario analysis and macro formatting.

Build a forecasted profit and loss in Excel by linking historical data to a final PNL sheet, using assumptions, revenue-based depreciation, and a three-scenario forecast for net income.

Explore the balance sheet structure, detailing assets, equity and liabilities, with non-current and current assets, and forecast the balance sheet using a check column.

Develop balance sheet forecasting skills by linking working capital items to revenue, calculating receivable and inventory days, and constructing a forecasted statement of financial position and cash flow implications.

Learn to prepare an IFRS-based cash flow statement from accrual income statements and balance sheets, detailing operating, investing, and financing activities. Discover how to handle missing prior years in forecasting.

Master forecasting cash flow statements in IFRS format by starting from earnings before tax, adding back depreciation and interest, adjusting for working capital, and classifying operating, investing, and financing activities.

Compute opening and closing equity, dividends, and profit to complete the statement of financial position in MS Excel; forecast cash flows from operating, investing, and financing activities using historical data.

Construct and reconcile a forecast cash flow statement, fix references, and apply a dividend policy; link cash, balance sheet, and PNL to explore equity and liquidity under scenarios.

Use vlookup to extract customer details from a large table, fetching name, category, email, city, and sales by locking references and using exact-match lookups.

Learn to build a profit and loss model in Excel by creating a unique code from branch and account numbers, removing duplicates, and using VLOOKUP to populate details.

Use vlookup to extract account data across 2017–2019, adjust column order, and consolidate into a single sheet with proper column indices and range_lookup false for accurate profit and loss.

Map account codes to p&l accounts by resolving sign issues and grouping revenues, costs, admin expenses, staff costs, and cost of sales into a coherent income statement.

Convert the master datasheet into an income statement by organizing revenue, gross profit, admin expenses, and taxes for 2017–2019. Use sum formulas with locked ranges to pull figures.

Explore cash-flow based valuation to estimate a business's value using forecast and perpetual cash flows, discount rate, and perpetual growth, deriving present value and enterprise value.

Explore how to estimate cost of equity using CAPM, incorporating risk-free rate, market return, and beta, and analyze operational and financial risks in capital budgeting.

Derive the cost of equity for unlisted mid-sized firms using beta values and the CAPM, adjusting for debt, country risk, and hurdle-rate practices with practical benchmarks.

Explain how geared and ungeared betas separate business risk from financial risk, using benchmarks to estimate asset beta and then regear for debt to compute the cost of equity.

Develop a 10-year discounted cash flow model using free cash flow and terminal value, comparing unlived and leveraged capital structures with inflation, market research, and scenario-based selling price and volume.

Create and link a fixed asset schedule with scenario-based investment plans, detailing year-zero and year-one capex, depreciation over 10 years, and tax benefits from residual-value considerations.

Learn to build a combined dcf in excel by calculating operating cash flows with interest payments ignored from revenue, costs, depreciation, taxes, capex, working capital, and residual value.

Break down fixed and variable costs, depreciation, and marketing in a DCF model to avoid errors; scrutinize the Menteng plan to align staffing with 24/7 operations.

Learn to compute DCF using levered and unlevered cash flows, apply WACC to discount, and analyze capital structure effects on beta, cost of equity, and tax benefits.

Explore calculating project beta, cost of equity, and WACC in Excel using CAPM with a 4.5% risk-free rate and beta 1.09, including debt‑to‑equity scenarios and post‑tax costs.

Create a final output sheet for a DCF model in Excel, linking operating cash flow, capex, and working capital to compute present value and NPV, with scenario-based sensitivity analysis.

Learn how net present value measures wealth and how the internal rate of return—the discount rate that makes NPV zero—guides investment decisions, hurdle-rate choices, and sensitivity analysis.

Assess project risk with sensitivity analysis by linking NPV to changes in selling price, volume, costs, and investment, and estimate the percentage shift to drive NPV to zero.

Learn to value a business using market, income, asset, and cash flow based methods, consider synergies and non-financial factors, and apply terminal value and free cash flow to equity concepts.

Explore valuing a small software design business via a three-year free cash flow to equity forecast, applying cost of equity, depreciation, working capital, taxes, and terminal growth rate.

Forecast three-year cash flow to equity by adjusting operating profit for depreciation, working capital, taxes, and interest, then value the business with a 3% growth terminal value discounted at 10%.