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: 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

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 | PSX Data  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 : Capital Budgeting | Uneven Cash Flows  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 14: Conditional Formatting 2 – Excercise  Read
Lecture 17: If Function  Download
Lecture 18: Vlookup Function  Download

Download

Download

Lecture 18 – B: Vlookup with Multiple criteria  Download
Lecture 19 Graphs and Charts  Download
Lecture 20 Pivot Tables  Download
Lecture 22 : Macros  Download

Download

Download

Download

Download

Lecture 23 : Macros 2  Download

Download

Download

Lecture 23 : Macros – 3  Download

Download

Download

Lecture 24 : Solver Function  Download

Download

Lecture 25 : Solver Function  Download
Lecture 26: Find cost of equity with Solver  Download

Download

Download

Lecture 27: Panel Data Analysis  Download

Assignments

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

Exam Papers


Comprehensive Paper

MS Word Report

Extra Calss files


Class Photo [ Session 2019] View Here

Additional Resources


1. Excel Shortcuts

Results and Feedback


Session 2020-21

BS Acc. Finance – Group A

BS Acc. Finance – Group B


List of topics covered in our final exam


CONDITIONAL FORMATTING
1. Text based formatting
2. Formula based formatting
3. Formatting only numbers
4. Formatting only formulas
5. Formatting only text

FILTERING DATA

Pivot Tables
1. Rows
2. Columns
3. Filters
4. Values
5. Average, sum, min, max, etc
6. Pivot Charts

Charts and Graphs
1. Bar charts
2. Line Charts
3. Scatter
5. Pie
6. Chart layout / Designs
7. Changing chart axis names, titles, shape, colors

Vlookup
1. Named ranges
2. Modifying a named range
3. Single criteria lookup
4. Multiple criteria lookup

List of Macros
1. Change cells
2. Change sheets
3. Add a specific value to a cell existing value
4. Delete / color every n column
5. Delete /color every n row
7. Find average / SD/
8. Format Tables
9. Copy data from one sheet to another
10. Clear the data

12. Save file as Macro-Enabled Workbook

Print Excel Data
1. Set print area
2. Modify print area
3. Print row titles
4. Landscape / Portrait
5. Setting margins
6. Setting page breaks
7. Page numbers
8. Print only selected text
9 Change Print Area by hold and drag

10. Print Header and Footer

11 Print only pages 10 to 15

12. Custome scaling

13 Margins

14. Orientation

15. Page numbers

16. Page breaks

17. Page size

18. Freeze rows / Columns

19. Header / Footer

20. Print IMSCiences logo in light color in the background

SOLVER

MS WORD

1. Table of Contents

2. Page Numbers

3. Sections and Section page numbers

4.