Financial Modelling and Forecast Techniques using Excel Tool

Learn break-even analysis to determine units and revenue needed to cover fixed and variable costs, compute contribution margins, and create the breakeven chart in Excel, with sensitivity analysis.

Perform a practical break-even analysis in Excel, calculating fixed and variable costs, contribution margin, and break-even units, then derive break-even revenue from unit price and chart the results.

Explore sensitivity analysis to see how changes in independent variables affect dependent outcomes within Excel, using scenarios and data tables to forecast, assess risk, and inform decision making.

Explore sensitivity analysis in excel using goal seek to determine how many shares to sell to reach a target operating profit of 29 million.

Explore how to build a true variable inputs data table to perform sensitivity analysis in Excel, linking inputs to operating profit and loan payments.

Use solver to perform sensitivity analysis in Excel, set an operating profit goal, adjust quantity sold and base price, and impose constraints like sold ≥ 1000 and price ≤ 58,000.

Learn to use Excel's sensitivity analysis and Scenario Manager to build base, best, and worst cases, save scenarios, and generate operating profit summaries.

Explore business and financial forecasting using the Excel forecast function to predict future values from past revenue and expenses, and learn its syntax with x values and known (independent) values.

Explore how to apply Excel's forecast function to extrapolate earnings across dates, using absolute and relative references, and learn to perform multi-cell forecasts with array formulas.

Learn to use the forecast.linear function to extrapolate the balance sheet from historic values, with absolute and relative references for fast, statistically driven forecasts.

Explore the Excel growth function to predict exponential growth from existing data. Learn its syntax, inputs, and how it compares to the forecast function for revenue forecasting.

Explore how to use the growth function in Excel to forecast exponential revenue growth, create charts, and add a trend line for the line of best fit.

Learn how the trend function in Excel computes a linear trend line from known Y and X values and extrapolates new Y values using the least-squares method to forecast trends.

Forecast sales data with Excel's trend function by specifying forecast periods and new_x values, using absolute references, and visualize the result with a trend line.

Understand the coefficient of determination (R2) as a measure of goodness of fit in regression. Higher values indicate a better fit, while R2 does not prove causality.

Learn to calculate the coefficient of determination in a regression analysis using Excel, via RSQ and correlation, and interpret R-squared for the line of best fit and forecasts.

Understand the cost of capital as the blended debt and equity rate, the minimum return for projects, and how it guides investor risk and profitability assessments.

Explore how to calculate the cost of equity using the capital asset pricing model and the dividend capitalization model, including beta, risk-free rate, market return, and dividend growth in Excel.

Calculate the cost of equity using the CPM formula with risk-free rate, beta, and expected market return, and illustrate with dividend capitalization model using dividends, price, and growth rate.

Calculate the CAPM cost of equity for IBM by estimating beta from S&P 500 returns, deriving the risk-free rate, and annualizing the expected market return in Excel.

Derive the after-tax cost of debt using interest expense (1 - tax rate) and illustrate the before-tax cost with an Excel example.

Learn to compute the cost of debt and after-tax cost using formulas for loan and bond scenarios in Excel, including rate, tax rate, and coupon data.

Understand how to compute the weighted average cost of capital (WACC) from equity and debt costs, with tax effects, to guide project evaluation and budgeting in Excel.

Compute the weighted average cost of capital by blending cost of equity, based on risk-free rate, equity risk premium, and levered beta, with after-tax debt cost and debt-to-capitalization weights.

Explore how to apply linear programming and integer linear programming to capital budgeting, balancing fixed and variable costs, revenues, and constraints to optimize project selection and financing in Excel.

Optimize an investment mix with linear programming in Excel, maximizing returns from municipal bonds, bank CDs, and a high-risk fund under 12,000 with a 2,000 cap and a tax constraint.

Demonstrates solving a four-year, multi-investment cash flow problem with linear programming in Excel, allocating funds across investments B, C, D, E and CDs to maximize final-year cash while respecting limits.

Learn to solve capital rationing with integer programming in excel solver, using binary variables, sumproduct, and npv to optimize project selection under period-specific capital constraints.

Minimize costs in a meat and cheese production problem using linear programming to meet 11 units of carbohydrates and 5 units of protein at minimum cost, solved with Excel solver.

Maximize profits from a two-product mix in Excel using linear programming, subject to labor, chipsets, and electronic component constraints, with solver and sensitivity analysis.

Explore project evaluation techniques in excel, calculating present value, future value, npv, irr, mirr, and xnpv for scheduled cash flows and profitability index.

Learn to compute the future value of regular and one-time investments in Excel using formulas and the FV function, applying rate, periods, present value, and compounding.

Calculate the present value of future value using Excel formulas, applying a 15% discount rate over three years and using the PV function.

Apply the pv function to calculate present value for an 800,000 Niira cash flow in one year and two years, using 10% and 20% discount rates.

Learn to calculate future value in Excel using the FV function, applying present value and a 10 percent compounding rate, and build a sensitivity analysis with data tables.

use the payback period method to evaluate a project by comparing yearly inflows and outflows to the required payback period.

Learn to evaluate a project using NPV and IRR in Excel. Using a 2 million cost, 800 yearly cash flow, four years, and a 10 percent discount rate, determine viability.

Calculate internal rate of return using the xirr function for irregular cash flows with specific dates, and compare with net present value to assess project viability.

Learn how to compare mutually exclusive projects using NPV, IRR, payback period, and a crossover rate analysis, guided by cash flows and the cost of capital.

Learn the MIRR method for the internal rate of return, using initial investment, net income, finance and reinvestment rates to assess two projects' viability.

Analyze capital budgeting decisions using NPV, IRR, payback, profitability index, and accounting rate of return to judge project viability against the required rate of return and cross-over rate.

Analyze liquidity, solvency, profitability, and growth using core financial ratios. Compute current ratio, net trade cycle, debt ratio, interest coverage, ROA, ROE, and revenue growth in Excel.

Use Excel to calculate liquidity, solvency, and profitability ratios—current ratio, debt ratio, interest coverage, return on assets and return on equity—using 2013–2015 data.

Common size statements express income statement and balance sheet items as percentages of base values, enabling vertical and horizontal analysis for cross period comparisons of margins, debt, and assets.

Create common size statements in excel by expressing balance sheet items as percentages of total assets and income statement items as percentages of revenue.

Explore how Dupont analysis decomposes return on equity into net profit margin, asset turnover, and financial leverage to reveal performance drivers.

Compute return on equity via du Pont analysis by multiplying profit margin (net income over revenue), asset turnover (revenue over average assets), and financial leverage (average assets over average equity).

Empower non-accountants to read and build the three core financial statements—the balance sheet, income statement, and cash flow statement—and grasp assets, liabilities, and equity for financial modelling.

Learn to build a balance sheet in a worksheet, compute current and non-current assets, liabilities, and shareholders equity with retained earnings, and verify assets equal liabilities plus shareholders equity.

Explore the income statement and perform calculations to derive net revenue, gross profit, cost of goods sold, total expenses, and earnings before interest and tax, then net earnings in Excel.

Explore the cash flow statement by calculating cash from operations, investing, and financing, then derive the closing cash balance and analyze changes in working capital.

Explore the relationship among the balance sheet, income statement, and cash flow statement, showing how net income, depreciation, and working capital drive retained earnings and cash flow.

Learn to build an income statement from raw data by classifying accounts into revenue, cost of goods sold, depreciation, interest, taxes, and operating expenses, using year-based sums for year-over-year insights.

Create a balance sheet summary across years by using lookup to pull inventory, plant and equipment, cash, other assets, and equity and liabilities, with careful formatting.

build a dynamic three-statement financial model in excel from historical data, using schedules and assumptions to forecast revenue growth, cost of goods sold, operating expenses, and depreciation and amortization.

Develop forecast assumptions for a three statement model in Excel by modeling revenue growth with best, base, and worst cases, using the choose and match functions, and locking references.

Learn to build a 3-statement forecast by projecting revenue with a 5% growth, applying COGS and OpEx as revenue percentages, and analyzing depreciation, interest, taxes, and scenarios.

Build a linked balance sheet forecast by creating debt and working capital schedules, forecasting equity and retained earnings, and linking depreciation and capex to cash flow.

Build a 3-statement model and forecast by creating a depreciation schedule, linking opening and closing PPE, and estimating CapEx and depreciation to project the balance sheet and income statement.

Build and forecast a full 3-statement model in Excel, projecting debt, interest expense, taxes, income statement items, retained earnings, and cash flow across historical and forecast periods.

Develop a cash flow forecast from net income, add back depreciation, and adjust for working capital changes, then reconcile with the balance sheet and analyze best, base, and worst cases.