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

Assignment 3 : Week Three – Data Manipulation

Marks = 5
Deadline = October 27, 2020; till 11:59:59 PM
Submission Link =
Group A Upload Here
Group B Upload Here

Tasks to be completed in the assignment

Important Note:

  1. Complete each task on a separate sheet of the Excel file.
  2. Rename the Excel file name as Your Name (Please write Your name as it Appears in the Attendance Sheet).

1. On this page (Group A Here and Group B Here) Please download the PDF file whose link is assigned to you and convert it to Excel file.

2. The PDF file includes data for 32 items and one year, you need to reshape the data according to this sample file.

3. When you reshape the data, the data headers should appear in the first row as shown in the sample file.

4. The second row will include the item numbers from 1 up to 32

5. The first column should report the date variable in the MMM-YY format as shown in the sample file. And the Unit/Base columns should be dropped from the data.

6. Any non-numeric value such as …  should be found and replaced with empty value using Ctrl+H

7. This should create just one sheet, which should have the default name of Sheet1

8. Any rough work should go to other sheets in the file

Assignment 2 : Week Two Tasks

Marks = 5
Deadline = October 13, 2020; till 11:59:59 PM
Submission Link =
Group A Upload Here
Group B Upload Here

Tasks to be completed in the assignment

Important Note:

  1. Complete each task on a separate sheet of the Excel file.
  2. Rename the sheet names as 1, 2, 3, corresponding to the question numbers.
  3. Rename the Excel file name as Your Name (Please write Your name as it Appears in the Attendance Sheet).

1. If you deposit Rs. 800000 into an account earning 12% interest compounded quarterly, how much would you have in 5 years?

2. How much would you pay for an investment which will be worth Rs. 780,000 in three years? Assume interest is 9%.

3. You have Rs.1000,000 to invest at 8% interest. If you wish to withdraw equal annual payments for 7 years, how much could you withdraw each year and leave Rs.0 in the investment account?

4. If your company borrows Rs. 1300,000 at 7% interest and agrees to repay the loan in 10 equal semiannual payments to include principal plus interest, how much would those payments be?

5. You deposit Rs.17,000 each year for 10 years at 7%. Then you earn 9% after that. If you leave the money invested for another 5 years how much will you have in the 15th year?

6. If you deposit Rs. 90000 in a bank account and then keep on depositing Rs. 14000 each year for the next 12 years, what will be the ending value of these investments if the interest rate is 8%.

7. Arshad Mali has Rs. 42,180.53 in a brokerage account, and he plans to contribute an additional Rs. 5,000 to the account at the end of every year. The brokerage account has an expected annual return of 12 percent. If Arshad’s goal is to accumulate Rs. 250,000 in the account, how many years will it take for Arshad to reach his goal?

8. If you put Rs. 5000 in the stock market, how many years would it take you to triple your money if the market is making 12% a year?

9. If you put Rs. 10 away at the end of each month for the next 40 years at a 12% simple annual interest rate, how much money would you end up with? What if you started at the beginning of each month?

10. If you borrow Rs. 150,000 for a house at 8% simple annual interest rate for 15 years, what is your monthly payment?

11. How long would it take to accumulate Rs. 50,000 if you started putting Rs. 5 in the bank every month starting now at a simple annual interest rate of 7.3%? What if you started at the end of each month?

12. Your parents are planning to retire in 18 years. They currently have Rs. 250,000, and they would like to have Rs. 1,000,000 when they retire. What annual rate of interest would they have to earn on their Rs. 250,000 in order to reach their goal, assuming they save no more money?

13. An investment pays you Rs. 100 at the end of each of the next 3 years. The investment will then pay you Rs. 200 at the end of Year 4, Rs. 300 at the end of Year 5, and Rs. 500 at the end of Year 6. If the interest rate earned on the investment is 8 percent, what is its present value? What is its future value?

14. Washington-Atlantic invests Rs. 4 million to clear a tract of land and to set out some young pine trees. The trees will mature in 10 years, at which time Washington-Atlantic plans to sell the forest at an expected price of Rs. 8 million. What is Washington-Atlantic’s expected rate of return?

15. Assume that you inherited some money. A friend of yours is working as an unpaid intern at a local brokerage firm, and her boss is selling some securities that call for 4 payments, Rs. 50 at the end of each of the next 3 years, plus a payment of Rs. 1,050 at the end of Year 4. Your friend says she can get you some of these securities at a cost of Rs. 900 each. Your money is now invested in a bank that pays an 8 percent nominal (quoted) interest rate, but with quarterly compounding. You regard the securities as being just as safe, and as liquid, as your bank deposit, so your required effective annual rate of return on the securities is the same as that on your bank deposit. You must calculate the value of the securities to decide whether they are a good investment. What is their present value to you?

16. Assume that your father is now 50 years old, that he plans to retire in 10 years, and that he expects to live for 25 years after he retires, that is, until he is 85. He wants a fixed retirement income that has the same purchasing power at the time he retires as Rs. 40,000 has today (he realizes that the real value of his retirement income will decline year by year after he retires). His retirement income will begin the day he retires, 10 years from today, and he will then get 24 additional annual payments. Inflation is expected to be 5 percent per year from today forward; he currently has Rs. 100,000 saved up; and he expects to earn a return on his savings of 8 percent per year, annual compounding. To the nearest dollar, how much must he save during each of the next 10 years (with deposits being made at the end of each year) to meet his retirement goal?

17. What is the future value of a 5-year ordinary annuity that promises to pay you Rs. 300 each year? The rate of interest is 7 percent.

18. Find the amount to which Rs. 500 will grow under each of the following conditions: a. 12 percent compounded annually for 5 years. b. 12 percent compounded semiannually for 5 years. c. 12 percent compounded quarterly for 5 years. d. 12 percent compounded monthly for 5 years.

19. Find the present value of Rs. 500 due in the future under each of the following conditions: a. 12 percent nominal rate, semiannual compounding, discounted back 5 years. b. 12 percent nominal rate, quarterly compounding, discounted back 5 years. c. 12 percent nominal rate, monthly compounding, discounted back 1 year.

20. Add 5 questions of different complexity on your own to the assignment.

Assignment 1 : Week One Tasks

Marks = 5
Deadline = October 6, 2020; till 11:59:59 PM
Submission Link =
Group A Upload Here
Group B Upload Here

Tasks to be completed in the assignment

Important Note:

  1. Complete each task on a separate sheet of the Excel file.
  2. Rename the sheet names as 1, 2, 3, corresponding to the question numbers.
  3. Rename the Excel file name as Your Name (Please write Your name as it Appears in the Attendance Sheet).

Show practice on the following questions.

1. Use randbetween() function to generate random values between 1 and 1000 in cells A1:D10. Then find average, standard deviation, median, min, max, and range for each of the columns
2. Create a random series using rand() function in cells A1:A20, Then
a: find absolute of the values
b: Find negation
c: Convert all values to negatives

3. In cells A1:A10 write full names of 10 of your class fellows, then:
a: In the next column split the names on space
b: Then in column D, write last name first in all capitals, put a comma, and write first name in all small letters

4. Process the following email addresses and show them on separate rows in column A. In others words, each emails address should be shown on a separate row in Column A. Then explain the steps in column B. (i.e. how did you do it?)

mohsinalituri65@gmail%mohsinalituri65@gmail.com%jalalwazir7@gmail.com%shayanalikhan955@gmail.com %munibshah81@gmail.com%tariqjan203@gmail.com%hamzazaib49@gmail.com%aashii132@gmail.com %faizanshah3933@gmail.com%hamidzia0@gmail.com%azmatnoor454@gmail.com%kyuubisensei2@gmail.com %mbilalkhalil8@gmail.com%jamshedshinwarish@gmail.com%maisam.ali66a@gmail.com%qudratqacc1234@gmail.com %rahmatkhattak65@gmail.com%hamzakhan71900@gmail.com%athezaz.sm007@gmail.com%mushahid345@gmail.com %zarak.khan7567@gmail.com%khan.amir7066@gmail.com%harisqurashi77@gmail.com%abdulhaq8318@gmail.com %ibrahimkamawi6@gmail.com%abdurraqeeb_yasir@yahoo.com%mushahidb45@gmail.com%aryan.ali66a@gmail.com %lnsafian5454@gmail.com

5. Download this dataset, extract the data from zip file, and import the csv file into excel. This file contains Google Play Store data and has several statistics on the Google Play store apps. You are required to do the following:
a: Find a list of unique categories. Paste this list in Column N
b. Which category has the largest number of Apps?
c. What is the average rating per app in each category?
d. How many free apps are there in each category?
e. How many paid apps are there in each category?
f. What is average price per app if the app has rating of more than 4.5?
g. Get the numeric value from column M to show the android version in numbers. Your answer should appear in column P
f. How many apps are there in the ‘Arts and Design’ Geneses that have android version of less than 3?

6. Using Filtering technique,
a: make a separate dataset from the Apps that have a size less than 10MB?
b: Put this data in Sheet which should be named as 6. Highlight all apps on this sheet with light green background if the app is Free.
c: Delete all rows from the sheet that have a rating of NaN

7. Show how to convert formulas to values (Make your random dataset, apply formula in one column, show conversion to values in another column)

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


Paper Upload Links


Download Mid Term Paper

Group A Upload Here

Group B Upload Here

List of topics covered in the Mid-Term Exam


  1. Make cell reference absolute
  2. Make cell reference absolute for rows
  3. Make cell reference absolute for columns
  4. ABS()
  5. average()
  6. stdev()
  7. min()
  8. max()
  9. median()
  10. Negation
  11. All negative
  12. count()
  13. counta()
  14. countif()
  15. countifs()
  16. sum()
  17. SUMIF()
  18. sumifs()
  19. averageif()
  20. averageifs()
  21. Count string cells
  22. product()
  23. sumproduct()
  24. Named range
  25. Modify named range
  26. Select and color all text cells
  27. Select all formula cells
  28. Select all numeric cells
  29. Conditional formatting
  30. Conditional formatting using a formula
  31. Vlookup()
  32. Vlookup using two or more criteria
  33. Index()
  34. match()
  35. search()
  36. Freeze view
  37. Protect cell or range
  38. Protect sheets
  39. Protect file
  40. Validation
  41. Validation based on list
  42. Validation based on range
  43. Validation based on dates
  44. Make data entry sheet (like the one we used for Home Construction)
  45. PV()
  46. FV()
  47. PMT()
  48. NPER()
  49. NPV()
  50. IRR()
  51. RATE()
  52. Profitability index
  53. Amortization table
  54. Importing text
  55. Data to columns using delimters
  56. right()
  57. left()
  58. upper()
  59. lower()
  60. proper()
  61. concatenate()
  62. Merge cells using the & symbol
  63. Number to date
  64. Using PKR currency symbol
  65. IF
  66. Nested IF
  67. OR()
  68. AND()
  69. Tax calculation with nested IF
  70. Filtering Data
  71. Make separate data from filtered data
  72. Format Painter
  73. Convert formula to values
  74. Rand()
  75. Randbetween()
  76. What is the rule for the double click and filldown
  77. What is the short-cut for going to the end of the data below the current cell
  78. What the short-cut for auto-sum
  79. What is the short-cut to show all formulas
  80. What is the short-cut for selecting all data to the right fo the current cell
  81. If a given cells is empty, make current cell empty, otherwise use some formula in the current cells
  82. IFERROR()
  83. Find and Replace with Ctr + H
  84. Create company numeric IDs using IF function
  85. Cell borders
  86. Font color
  87. What is the difference between copy paste and dragging
  88. ROUND()

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.