Financial Modelling

Convert data to a table and use a pivot table to build an income statement with calculated fields for gross profit and net profit.

Manipulate pivot table data by dragging geography and month fields to update the P&L across geographies, states, or months. Insert slicers to filter and enable multi-select for dynamic analysis.

Copy and paste the existing pivot table to create a new report from the same data, then explore show values options such as percentage of grand total.

Insert new field by selecting state and cities, explore report layouts in design tab, compact form, outline form, and tabular formats, and toggle the visibility of grand totals and subtotals.

Unlock robust financial models by calculating actual days in a year with date functions, handling leap years, and generating monthly timelines via end-of-month dates and month-year extraction.

Apply a logical function using an if statement to calculate tax value from profit before tax, applying the tax rate only when profit is positive and otherwise returning zero.

Explore depreciation calculations using formulas and Excel functions for straight line method, declining balance method, sum of years digit method, double declining balance method, cost, salvage value, and useful life.

Explore irr and npv functions to evaluate a project, using a 70,000 investment and five-year inflows to derive an internal rate of return and compare it to external rates.

learn how the xirr function in excel handles multiple inflows and outflows with dates, overcoming end-of-year assumptions and showing how timing affects the internal rate of return.

Compute NPV and XNPV in Excel using a 10% discount rate on a ₹10,000 investment with dated inflows and outflows, showing negative ₹614 NPV and positive ₹21 NPV.

Compute annual loan installments with the PMT function and split each payment into interest and principal using ipmt, with a 120,000 loan at 13% over five years.

Explore how absolute references work in spreadsheets by using F4 to fix row and/or column, lock costs and specific rows when copying formulas across right and down.

Learn to forecast financial values using growth rates and a growth factor. Apply previous-year units times (1 plus rate) to project future sale values, expenses, and assets.

Convert years from row to column using transpose and compare dynamic array functions with static paste methods, showing how dynamic formulas update when base values change.

Explore how the analyze data feature in Excel Office 365 enables quick insights, auto-creates pivot tables and charts, and lets you ask questions in natural language to generate reports.

Master scenario analysis in financial modeling by evaluating future events, using base, best, and worst cases, and integrating scenario sheets with the model via data validation and combobox tools.

Learn to build scenario inputs using index and match to fetch base, best, and worst case percentages for revenue growth, inflation, and markup, with data validation and combobox integration.

Link inflation and markup data from the input sheet to drive scenario analysis in a financial model, using index or choose functions to update worst, base, and best scenarios.

Learn how to use Excel cell styles to color-code hard-coded values and formula results in financial models, and apply consistent formatting across multiple sheets.

Format actual and forecast years with a and f using custom number formatting, preserving numeric values, and learn center across selection for labeling in financial models.

Explore color coding strategies for financial modelling, using red for hardcoded values, black for formulas, and blue for references, and compare vertical and horizontal sheet layouts for schedules and models.

Master ten financial modeling best practices, from planning, format, and quality to revenue calculation, and define model objective, type, audience, and key assumptions.

Set up a financial model with a separate inputs or assumption sheet, choose vertical or horizontal layout, and label units clearly to link formulas.

Learn how to ensure quality output in financial modeling by including at least three scenarios, applying built-in error checks in balance sheets, and testing the model before delivery.

Learn to compute revenue for existing organizations and startups by using top-down or bottom-up approaches, incorporating market size, expansion, pricing, discounts, international factors, and historical data.

Forecast revenue by projecting units, purchase price, and exchange rate using growth factors. Calculate sale price per unit with a 22% markup and derive total revenue in local currency.

Project chain-store revenue by estimating annual store counts (50 per year), 16,000 sq ft per store, and revenue per square foot growth (2%, 4%, 5%, 6%) from 2018 to 2022.

Project ecommerce revenue by forecasting 2017 data into five years, applying growth to visitors, conversion, and average order rate to compute active buyers and revenue.

Calculate food chain revenue using 1.5% price growth and 3% order growth across 16 hours. Use the date function to compute year days and round orders before multiplying by price.

Analyze revenue for multiple product categories by defining units, prices, and increase percentages for each category, then multiply price by quantity to calculate total revenue.

Develop a five-year integrated financial model for a leading home appliances company, forecasting unit sales and revenue across Lahore, Islamabad, Karachi, Quetta and Gujarat with a 20% markup.

Link input and scenario data to build a complete financial model, using base, best, and worst cases for revenue growth, inflation, and markup, with watch window to visualize scenario impacts.

Learn to calculate payroll in financial modelling by linking headcount data and salary inputs, forecast salaries with yearly increments, and compute total annual payroll across departments.

learn to model operating expenses by categorizing fixed and variable costs, link them to the input sheet, apply an inflation factor, and use conditional formulas to forecast year-by-year expenses.

Learn to build a loan amortization schedule for a 1.2 million, 12% loan over five years using the PMT function to derive an annual installment and split interest and principal.

Discuss how to prepare the input sheet with manual data, link it to scenarios, and drive calculations across the introduction, input, and financial model sheets for Assignment #1.

Enter hard coded values into an inputs sheet and link to calculation sheet; use assumptions sheet and row-based percentages for inflation, freight, customs duties, and exchange rate across five years.

Compute revenue by multiplying selling price by units, using five units as shown; link input and assumptions sheets to the calculation sheet, use separate percentage columns, and avoid theoretical data.

Apply a percentage row per year to capture revenue impact from inflation, exchange rate, or markup, and add a unit of measure column. Create an inputs sheet and perform calculations.

Learn to prepare a financial model with a clear cover sheet and flexible input sheet, choosing box or tabular formats and vertical or horizontal layouts.

enter case study data into the input sheet, then build revenue schedules from sale units and pricing, applying scenario analysis with markup, inflation, and exchange rate.

Project payroll by calculating branch headcount—one gm, branch managers, and two sales executives per branch; add managers when new branches open, with a 10% salary increase and an input-to-model workflow.

Compute total rent by multiplying fixed rent per shop by the number of shops, and model inflation-adjusted operating costs and a 5 million loan over five years using PMT.

Learn to build a fixed asset schedule using straight-line depreciation for plant and machinery, furniture and fixtures, office equipment, and motor vehicles, linking opening costs to calculate depreciation and wdv.

Link revenue and schedules to compute gross profit and EBITDA. Calculate operating expenses, depreciation, amortization, taxes, net income, and changes in equity and earnings per share.

Learn to build a balance sheet by linking fixed asset written down value or cost and depreciation, and calculating current assets, liabilities, and working capital.

Prepare a cash flow statement using the indirect method. Classify activities into operating, investing, and financing, and adjust net income with non-cash items from the income statement and balance sheet.

Explain building a depreciation model with vertical and horizontal formats, straight-line method, half-year depreciation, and capex schedules for existing 35-year and new 47-year assets.

Create a dedicated working capital schedule for accounts receivable, inventories, and payables, using 45/65/60 day cycles to translate revenue and cost of sales into daily cash flow.

Learn how to calculate current and deferred tax using accounting profit and tax profit, and distinguish deferred tax assets and liabilities through timing differences.

Contrast preferred and common equity, noting fixed 5% dividends on the closing preferred balance and common dividends based on profit after paying the preferred dividend, using a 9% payout ratio.

Learn to rename cells with name box and create named ranges, including from selection, then apply these names in a discounted cash flow model featuring NPV, ROI, and payback period.

Develop a year-by-year cash flow model by linking price per order and orders, forecasting growth, and computing NPV, IRR, ROI, and payback.

Format financial models for printing and pdf conversion by using page setup, fit to one page wide, and print titles to repeat headers, ensuring all statements fit.

Audit your financial model using formula auditing to trace precedents and dependents, identify hard coded values, and highlight formulas or references across sheets.

Link three Excel files—data table, manager table, and states table—using Power Query and PowerPivot to build a data model, create relationships, and design a dashboard.

Create a pie chart from a pivot table report to power a dashboard, and customize data labels while hiding unnecessary field buttons and legends using the analyze tab.

Master line and bar charts from pivot tables to visualize sales by month for chain stores, and sales by manager, category, and state, with clear chart titles.

Insert and link slicers to a dashboard to dynamically filter multiple charts using report connections, Power Query, and Power Pivot in a financial modelling workflow.