FINANCIAL MODELLING BASIC NOTES

EXCEL

Þ Introduction to Excel

Microsoft Excel is a spreadsheet software developed by Microsoft for data organization, analysis, and
visualization. It is widely used for performing calculations, generating reports, and creating charts or graphs.
Excel is an integral tool for professionals across various sectors, including finance, accounting, marketing,
education, and more.

Microsoft Excel is an essential tool for data manipulation, analysis, and presentation. Its features, such as
formulas, functions, charts, and pivot tables, make it a powerful application for both personal and professional
use. While Excel has many advantages, such as its flexibility, speed, and ability to handle large amounts of
data, it also has limitations, especially when working with very large datasets or when security is a concern.
Regardless, it remains one of the most widely-used business tools in the world.

Features of Excel

1. Worksheet and Workbook:

o Excel files are known as workbooks, and each workbook contains one or more worksheets
(tabs or sheets). Each worksheet is a grid of cells arranged in rows and columns.
2. Data Entry and Formatting:
o Cells: The basic unit where data is entered.
o Formatting: You can format text (font, size, color), cells (alignment, borders, background
color), and numbers (currency, date, percentages).
3. Formulas and Functions:
o Excel supports a variety of formulas for mathematical, statistical, and logical operations (e.g.,
SUM, AVERAGE, IF).
o Functions are pre-built operations (e.g., SUM for summing values, VLOOKUP for data
lookup).
4. Charts and Graphs:
o Excel provides multiple chart types (bar, line, pie, scatter, etc.) to visually represent data,
making it easier to interpret trends and patterns.
5. Pivot Tables:
o A powerful feature for summarizing and analyzing large data sets, enabling users to view data
from different perspectives and find relationships in data.
6. Data Sorting and Filtering:
 o Data can be sorted in ascending or descending order, or filtered to display only the information
that meets specific criteria.
7. Conditional Formatting:
o Allows you to format cells based on certain conditions (e.g., highlighting values greater than a
threshold, color scales for data ranges).
8. Collaboration and Sharing:
o Excel supports collaboration by allowing users to share workbooks, track changes, and protect
cells or entire sheets with passwords.
9. What-If Analysis:
o Tools like Goal Seek and Scenario Manager help users perform simulations to forecast
different outcomes based on variable changes.

Uses of Excel

1. Data Organization:
o Organize and manage large volumes of data such as sales reports, inventory lists, employee
records, etc.
2. Financial Modeling:
o Used extensively in accounting and finance for tasks like budgeting, forecasting, financial
analysis, and reporting.
3. Data Analysis and Reporting:
o Analyze trends, perform statistical analysis, create detailed reports, and share insights with
stakeholders.
4. Project Management:
o Excel can be used for tracking project timelines, assigning tasks, and monitoring progress.
5. Inventory Management:
o Manage stock levels, track sales, and automate reorder reminders.
6. Personal Finance:
o Individuals use Excel for tracking income and expenses, preparing budgets, and calculating
loan payments.
7. Charts and Data Visualization:
o Create professional-quality charts and graphs to present data visually for reports or
presentations.
Advantages of Excel

1. Ease of Use:
o Excel has a user-friendly interface, with many functions accessible via the Ribbon and intuitive
drag-and-drop features.
2. Comprehensive Functionality:
o Excel offers a wide range of tools for analysis, from basic calculations to advanced data
analysis and statistical modeling.
3. Flexibility:
o Excel can be used for various purposes, from simple calculations to complex data models and
simulations.
4. Speed and Efficiency:
o Excel speeds up calculations and data processing, especially for large data sets, due to its
powerful computational abilities.
5. Data Analysis and Visualization:
o With features like pivot tables, conditional formatting, and charting tools, Excel helps to
analyze data and present it in a meaningful way.
6. Compatibility:
o Excel files are compatible across different operating systems (Windows, macOS) and integrate
well with other Microsoft Office tools like Word and PowerPoint.
7. Automation:
o Excel supports macros and VBA (Visual Basic for Applications) for automating repetitive
tasks and enhancing functionality.
8. Collaboration:
o Multiple users can collaborate on a workbook, especially in the cloud (via OneDrive or Excel
Online), allowing for simultaneous editing and sharing.

Disadvantages of Excel

1. Complexity for Beginners:

o While Excel is user-friendly, its advanced features (formulas, pivot tables, macros) can be
difficult for beginners to grasp.
2. Error-Prone:
o Excel models and formulas can be prone to human error, especially when dealing with large
amounts of data or complex formulas.
3. Limited to Local Storage (for non-cloud versions):
o If using Excel offline, users are limited by local storage, and sharing large files over email can
be cumbersome.
 4. Performance Issues with Large Data:
o Excel can slow down significantly or even crash when handling very large datasets (e.g.,
millions of rows).
5. Version Compatibility:
o Different versions of Excel (Excel 2003 vs Excel 2016, etc.) may cause compatibility issues,
especially when files contain advanced features like complex formulas or macros.
6. Security Risks:
o Excel files can be prone to security breaches, especially when sensitive data is stored in
unprotected or poorly encrypted files.

Common Functions in Excel

Excel offers a wide variety of built-in functions for different tasks. Below are some common categories and
examples:

Mathematical Functions:

• SUM: Adds a range of numbers.

o =SUM(A1:A10)
• AVERAGE: Calculates the average of a set of numbers.
o =AVERAGE(A1:A10)

Text Functions:

• LEFT: Extracts a specified number of characters from the left side of a string.
o =LEFT(A1, 3)
• RIGHT: Extracts a specified number of characters from the right side of a string.
o =RIGHT(A1, 4)

Lookup and Reference Functions:

• INDEX: Returns the value of a cell in a specified row and column within a range.
o =INDEX(A1:C10, 3, 2)

Date and Time Functions:

• TODAY: Returns the current date.

o =TODAY()
• NOW: Returns the current date and time.
o =NOW()
Logical Functions:

• IF: Performs a logical test and returns one value if true, another if false.
o =IF(A1 > 10, "Pass", "Fail")
• AND: Checks if multiple conditions are true.
o =AND(A1 > 10, B1 < 20)
• OR: Checks if at least one condition is true.
o =OR(A1 > 10, B1 < 20)

Statistical Functions:

• COUNT: Counts the number of cells that contain numbers.

o =COUNT(A1:A10)
• COUNTIF: Counts the number of cells that meet a specified condition.
o =COUNTIF(A1:A10, ">10")
• MAX: Returns the highest value in a range.
o =MAX(A1:A10)
• MIN: Returns the lowest value in a range.
o =MIN(A1:A10)

Þ General Overview

1. Excel Interface Overview

• Workbook: A file in Excel containing one or more worksheets.

• Worksheet: A grid of cells arranged in rows (numbered) and columns (labeled with letters).
• Cell: The intersection of a row and a column (e.g., A1, B2).
• Ribbon: The horizontal menu bar at the top that contains tabs like Home, Insert, Formulas, etc.
• Formula Bar: The area just below the Ribbon that shows the contents of the selected cell.
• Quick Access Toolbar: A customizable toolbar for easy access to frequently used commands.

2. Data Entry and Formatting

• Entering Data: Click on a cell and start typing. Press Enter to move to the next cell down, or Tab to
move to the next cell right.
• Editing Data: Double-click a cell to edit, or select it and modify the content in the Formula Bar.
• Formatting: You can change the appearance of text and numbers, such as font size, color, alignment,
and borders, using the options in the Home tab.
o Text Formatting: Bold (Ctrl + B), Italics (Ctrl + I), Underline (Ctrl + U).
 o Number Formatting: Currency, percentage, date, etc., using the Number section of the
Ribbon.

3. Basic Formulas and Functions

• Simple Math Operations:

o Addition: =A1 + B1
o Subtraction: =A1 - B1
o Multiplication: =A1 * B1
o Division: =A1 / B1
• Common Functions:
o SUM: Adds values, e.g., =SUM(A1:A10).
o AVERAGE: Calculates the average, e.g., =AVERAGE(A1:A10).
o MIN: Returns the minimum value, e.g., =MIN(A1:A10).
o MAX: Returns the maximum value, e.g., =MAX(A1:A10).
o IF: A conditional function, e.g., =IF(A1>10, "Yes", "No").
o COUNT: Counts the number of cells with numbers, e.g., =COUNT(A1:A10).
• Autofill: Drag the fill handle (small square at the bottom-right corner of a cell) to automatically fill a
series or copy a formula.

4. Cell References

• Relative Reference: Default, adjusts when the formula is copied (e.g., =A1+B1).
• Absolute Reference: Fixed reference that does not change when copied (e.g., =$A$1).
• Mixed Reference: Part of the reference is fixed (e.g., =$A1 or =A$1).

5. Working with Data

• Sorting: Sort data in ascending or descending order. Go to the Data tab and click on Sort.
• Filtering: Use the Filter feature to display only rows that meet specific criteria (e.g., show only items
greater than $100).
• Conditional Formatting: Automatically format cells based on their values (e.g., highlight values
above a certain threshold).
o Found in the Home tab under Conditional Formatting.

6. Charts and Graphs

• Creating a Chart: Select the data range, then go to the Insert tab and choose a chart type (e.g.,
column, bar, line).
 • Chart Tools: Once a chart is selected, you can modify its design, style, and layout using the Chart
Tools options that appear in the Ribbon.

7. Working with Multiple Sheets

• Adding Sheets: Click the + sign at the bottom to add a new worksheet.
• Renaming Sheets: Right-click on the sheet tab and select Rename.
• Navigating Sheets: Use the tabs at the bottom to switch between sheets, or use Ctrl + Page Up/Page
Down to move between sheets.

8. Data Validation

• Data Validation: Set rules for data entry in a cell (e.g., only allow whole numbers, dates, or specific
text). This is found under the Data tab > Data Validation.
• Drop-down Lists: Use Data Validation to create a drop-down list for selecting predefined values in a
cell.

9. Basic File Operations

• Save: Use Ctrl + S to save your work.

• Save As: Save a copy with a new name or format (e.g., Excel Workbook, CSV).
• Printing: Go to the File tab and choose Print. You can adjust print settings such as orientation and
margins.

10. Shortcuts

• Ctrl + C: Copy
• Ctrl + V: Paste
• Ctrl + X: Cut
• Ctrl + Z: Undo
• Ctrl + Y: Redo
• Ctrl + Arrow Keys: Navigate quickly across large datasets.
• Ctrl + Shift + L: Toggle filters on/off.
• F2: Edit selected cell.
• Ctrl + A: Select all cells in the worksheet.

11. Protection and Security

• Protecting Cells: You can lock specific cells to prevent editing. This is done through Format Cells >
Protection tab.
 • Sheet Protection: Protect the entire worksheet by going to Review > Protect Sheet. This prevents
changes to the structure of the sheet (e.g., adding/deleting rows or columns).

12. Keyboard Shortcuts for Navigation

• Ctrl + Home: Go to the first cell (A1).

• Ctrl + End: Go to the last cell with data.
• Alt + E, S, V: Paste Special.
• Ctrl + F: Open Find and Replace.
• Ctrl + H: Open Find and Replace with the Replace tab active.
 Þ Financial Modelling

1. Introduction to Financial Modeling

• Definition: A financial model is a tool used to forecast a business's financial performance based on
historical data and assumptions about future performance. The model is usually built in Excel and is
used by analysts, investors, and managers to make decisions.
• Purpose: To estimate future financial performance, conduct valuation analysis, make budgeting
decisions, and support strategic planning.
• Types of Financial Models:
o Discounted Cash Flow (DCF): Focuses on estimating the value of a business or investment
based on future cash flows.
o Leveraged Buyout (LBO): Used to analyze the potential of a leveraged buyout.
o Merger and Acquisition (M&A): Assesses the financial impacts of mergers and
acquisitions.
o Financial Statement Modeling: Focuses on the forecast and analysis of the company’s three
key financial statements.

2. Components of Financial Modeling

• Historical Data: The model starts with past financial data (usually 3-5 years) to analyze trends and
set the base for forecasting future performance. This data often includes:
o Income Statement: Revenue, expenses, and profit.
o Balance Sheet: Assets, liabilities, and equity.
o Cash Flow Statement: Cash inflows and outflows.
• Assumptions: Financial models are built based on assumptions such as:
o Growth rates (revenue, cost of goods sold, operating expenses)
o Discount rates (WACC – Weighted Average Cost of Capital)
o Inflation rates
o Tax rates
o Depreciation and amortization schedules
• Forecasting: Based on historical trends and assumptions, forecast the future financial performance.
This includes:
o Revenue Forecasting: Use drivers like sales volume, pricing, or market share.
o Cost Forecasting: Estimate variable costs (e.g., cost of goods sold) and fixed costs (e.g.,
administrative expenses).
o Capital Expenditure (CapEx): Forecast investments in assets (e.g., machinery,
infrastructure).
 o Working Capital: Forecast changes in working capital, such as accounts receivable,
inventory, and accounts payable.

3. Key Steps in Building a Financial Model

1. Set Up Your Workbook:

o Create separate sheets for Assumptions, Income Statement, Balance Sheet, Cash Flow
Statement, and Valuation.
o Label the sheets clearly and maintain an organized structure.
2. Input Historical Data:
o Input historical financial data for 3-5 years. This serves as the foundation of your model.
o Ensure accuracy in the historical data, as future projections depend on it.
3. Build Revenue Forecasts:
o Use past data and assumptions to forecast revenue.
o For example, if the business is in the retail sector, the forecast could be based on historical
growth in sales volume and pricing.
4. Forecast Expenses:
o Estimate direct costs (COGS) and indirect costs (Operating Expenses).
o Forecast each expense based on historical trends or benchmarks in the industry.
5. Develop Financial Statements:
o Income Statement: Start with revenue, subtract direct costs to get gross profit, then subtract
operating expenses to get EBIT (Earnings Before Interest and Taxes).
o Balance Sheet: Forecast assets (Current and Non-Current), liabilities (Current and Non-
Current), and equity (Shareholder’s equity).
o Cash Flow Statement: Derive cash flows from operating activities, investing activities, and
financing activities.
§ Operating Cash Flow = Net Income + Non-cash expenses + Changes in Working
Capital.
§ Investing Cash Flow = Capital Expenditures (CapEx) and Investments.
§ Financing Cash Flow = Debt issuance/repayment, equity issuance/repurchase,
dividend payments.
6. Link the Statements:
o Ensure the three key financial statements (Income Statement, Balance Sheet, and Cash Flow
Statement) are linked.
o Net Income from the Income Statement flows to the Retained Earnings section of the
Balance Sheet.
o Depreciation from the Income Statement is added back to the Cash Flow Statement as a
non-cash charge.
 7. Scenario and Sensitivity Analysis:
o Conduct scenario analysis to model best, worst, and base-case scenarios based on different
assumptions (growth rates, cost assumptions).
o Use sensitivity analysis to understand how changes in key variables (e.g., discount rate,
revenue growth) affect the output (e.g., company valuation).

4. Key Concepts in Financial Modeling

• Discounted Cash Flow (DCF) Valuation: The value of a business or project is calculated by
estimating the future cash flows and discounting them to present value using a required rate of return
(WACC).
o Formula:

• WACC (Weighted Average Cost of Capital): The average rate of return required by all of a
company’s investors (equity and debt). It is used to discount future cash flows in DCF models.
• Terminal Value: In a DCF model, this represents the business’s value beyond the forecast period,
often calculated using a perpetuity growth model or exit multiple approach.
• IRR (Internal Rate of Return): A metric used to evaluate the profitability of potential investments,
often calculated in LBO models or project finance.

5. Important Financial Ratios and Metrics

• Net Present Value (NPV): The difference between the present value of cash inflows and outflows
over time. NPV is used to evaluate profitability.
• EBITDA: Earnings Before Interest, Taxes, Depreciation, and Amortization. It is a measure of
operating performance.
• Leverage Ratios:
6. Excel Tips for Financial Modeling

• Use Named Ranges: To make formulas easier to read, use named ranges for variables like "Revenue
Growth" or "Discount Rate."
• Avoid Hardcoding Values: Always refer to assumptions in separate cells to keep the model flexible
and dynamic.
• Use Functions: Leverage Excel functions such as SUMIF, INDEX, MATCH, VLOOKUP, NPV,
IRR, and PMT for calculations.
• Scenario Manager: Use Excel’s Scenario Manager for running different scenarios based on
assumptions.
• Data Validation: Use Data Validation to control the types of data entered (e.g., percentage, dates,
whole numbers).

7. Best Practices in Financial Modeling

• Keep it Simple: Avoid overly complex formulas that are hard to understand and troubleshoot.
• Be Transparent: Always label assumptions clearly and show your work (e.g., link assumptions to
forecasted figures).
• Error Checking: Regularly check your model for errors like broken formulas or mislinks.
• Documentation: Document assumptions, formulas, and methodologies used in the model for future
reference or handover to others.