Course: Business Valuation & Spreadsheet
Modelling
Excel Methods Session 2: Advanced Functions
For Workbook 2
Data Validation
Set the value of a cell within a defined
range or format
Use a dropdown list with desired
values within a range
Complete the exercise in the Data
Validation sheet
Exercise 3: Scenario
Manager
An advanced feature provided in excel. Part of
What-if Analysis
Use the data in the tab Scenario Manager.
You can create a series of scenarios.
Scenario manager shows the outcome of each
scenario.
Go to Data What-if Analysis Scenario
Manager…
Exercise 4: Goal Seek
Another advanced feature of
excel. Also part of What-if
Analysis.
Use the data in the Goal Seek
tab.
Goal seek can be used to
determine the value of a given
independent variable by
assigning a target value to a
dependent variable
Exercise 5: Solver
Solver is a very powerful tool in
excel. It can solve for a desired
dependent variable result, by
changing the independent
variables.
Go to the tab named Solver.
Complete the exercise.
You will have to install solver by
going to, File Excel Options
Add-ins Select ‘Solver Add-in’
The solver:
Go to Data Solver
The Solver
You can maximize or minimize
the value of a function n solver.
You can do this by changing a
certain series of variables, which
you can specify.
You can also add constraints,
specifying conditions and
boundaries.
Data Tables
Useful when multiple possible input
scenarios are available for a variable.
The inputs can be in a row or in a
column.
The intended output cell should have a
formula connecting it to the input
values that need to be replaced.
Go to Data What-if analysis Data
Table
Follow the instructions in the Data
Tables sheet for a practical hands-on.
Financial Functions: PV, FV,
RATE, NPER
The PV (Present Value) and FV (Future
Value) functions are extremely versatile.
They can be used to compute the present
and future values of a single amount,
multiple values, and annuity.
These functions are designed to
accommodate cash flows made at the
beginning of the period.
RATE can determine the interest rate
implied in future cash flows.
NPER can determine the period of
investment for the given details.
Financial Functions: Loan
Functions
Loan EMI calculation: the PMT function
Loan interest paid in a month: IPMT
function
Loan principal paid in a month: PPMT
Loan cumulative interest paid in a
range: CUMIPMT (the loan amount
selected cannot be negative)
Loan cumulative principal paid in a
range: CUMPRINC (the loan amount
selected cannot be negative)
Financial Functions: NPV, IRR,
MIRR
The NPV function returns the sum of
present values of cash flows starting
from period 1. The cash outflow must
be subtracted separately from the
result to arrive at the intended NPV.
IRR returns the internal rate of
return. The negative cash flows are
considered as outflows.
MIRR is the modified version of IRR
with a reinvestment assumption.
Using VBA to write
function.
Enable the developer tab from
Excel options under the
customize ribbon.
Once done, go to the developer
tab, click Visual Basic under
Modules in the current workbook,
right click and add a module.
Now add the function for Present
Value of Growing Annuity (PVGA).
Syntax given in the next slide.
VBA Syntax
“Practice doesn't make perfect.
Practice reduces the imperfection.”
~Toba Beta
Thank You
Session End