Course Description

With blunt tools even a master cannot do much. “sharpen your saw”, as advised by Stephen R. Covey in his best-seller book, “The Seven Habits of Most Effective People”. The objective of this course is to bring finance and computer into one place.

The course tries to sharpen your computer skills, providing you the tools with which financial models and techniques can be easily applied for the valuation of financial assets and projects and for creating budgets. The course content focus on a variety of skills, including implementation of the time value of money concepts using MS Excel, depreciation methods, what-if analysis, matching and merging data sets, use of pivot tables, capital budgeting techniques with solver function, and advanced techniques used in financial modeling. The course also focuses on recording macros and editing them in visual basic. For complete details, download and read the course outline.

Course Outline

Too often, finance courses stop short of making a connection between textbook finance and the problems of real-world business. This course bridges this gap between theory and practice by providing a nuts-and-bolts guide to solving common financial models with spreadsheets. This course takes the students step by step through financial models and various data management issues, showing how they can be solved.

Week No. Description
 Lecture 1: Introduction to Financial Risk Management Download
Lecture 2 : Introduction to Derivatives Download
Lecture 3: Hedging Risk with Derivative Products Download
Lecture 4: Hedging with Futures and Options Download
Lecture 5 : Duration Analysis Download
Lecture 6 : Orange County Case Study Download
Lecture 7 : Market Risk Download
Lecture 12 : Credit Risk Download
Lecture 13 : Credit Risk II Download
Lecture 14 : Operational Risk Download
Lecture 15 : Operational Risk II Download

Lecture Notes and Files

Week No. Description
Lecture 1: Excel Environment Download
Lecture 2 : Excel Functions Download
Lecture 3: Formatting Text Download
Lecture 4: Formatting Text 2 Download
Lecture 4b Importing Text Download
Lecture 5 : Ratio Analysis Download
Lecture 6 : Time Val. of money Download
Lecture 7 : TVM Annuity Download
Lecture 8 : Loan Amortization Download
Lecture 9 : Time value of Money II Download
Lecture 10 : IF, Nested IF Download
Lecture 11 : Income Tax with IF Download
Lecture 12: Revision for mid-term Download
Lecture 13 : Cell Protection Download
Lecture 14: Conditional Formatting 1 Download
Lecture 17: If Function Download
Lecture 18: Vlookup Function Download
Lecture 18 – B: Vlookup with Multiple criteria Download
Lecture 19 Graphs and Charts Download
Lecture 20 Pivot Tables Download
Lecture 22 : Macros Download
Lecture 23 : Macros 2 Download
Lecture 23 : Macros – 3 Download
Lecture 24 : Solver Function Download
Lecture 25 : Solver Function Download
Lecture 26: Find cost of equity with Solver Download
Lecture 27: Panel Data Analysis Download


Assignment 1 : Importing Text

Assignment Marks = 3
Submission Deadline =
Submission Link
Tasks to be completed in the assignment

(a) Convert the above dataset to Excel format using the pipe “|” as a delimiter

(b) Extract all lines that have the word dividend, dividends, or div and make a separate sheet from it. Give that sheet a name “Dividends”

(c) In that dividend sheet, extract the dividend percentages to a new column using either right, left, mid, search, len functions or “text to column” option from the Data menu or combination of these techniques.

(d) In the dividend percentages, some of the records have capital O instead of zero 0, find and replace all such instances of O with 0.

(e) Repeat the above steps for finding profit/loss amounts from the main dataset

(d) Repeat the steps (a) to (d) for finding earning per share (eps) figures from the main dataset.

NOTE: Assignment copied from one another will get zero marks.

Please add explanations for the given steps using the Insert > text box

Extra Calss files