**Course Description**

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 – Exercise | Read |

Lecture 17: If Function | Download |

Lecture 18: Vlookup Function – Exercise File1 | Download |

Lecture 18: Vlookup Function – Exercise File2 | Download |

Lecture 18 – B: Vlookup with Multiple criteria | Download |

Lecture 19: Graphs and Charts | Download |

Lecture 20: Pivot Tables | Download |

Lecture 21: Introduction to Macros | YouTube Video |

Lecture 21 – B : Relative Macro | YouTube Video |

Lecture 21 – C : Absolute Macro | YouTube Video |

Lecture 21 – D : Macro-Enabled File | YouTube Video |

Lecture 23 : Macros 2
Printing Cheque with Macro Part 1 [YouTube] Printing Cheque with Macro Part 2 [YouTube] Printing Cheque with Macro Part 3 [YouTube] Printing Cheque with Macro Part 4 [YouTube] Printing Cheque with Macro Part 5 [YouTube] |
Download |

Lecture 23 : Macros – 3 | [XLSX] Download |

Lecture 24 : Solver Function | Download |

Lecture 25 : Solver Function | Download |

Lecture 26: Find cost of equity with Solver | Download
Download |

Lecture 27: Panel Data Analysis | Download |

## Macros for Analysis

Submit assignment here

Upload before January 21, 2023

You have been provided with a spreadsheet containing sales data for a hypothetical company. Open the spreadsheet and familiarize yourself with the data. The spreadsheet contains the following columns:

Date: The date that the sale was made.

Quantity: Quantity sold

Product: The name of the product that was sold.

Quantity: The number of units of the product that were sold.

Price: The price of each unit of the product.

Total: The total revenue from the sale (quantity * price).Your task is to use macros to automate the analysis of the data and create a report to present to the management team.

- Open the spreadsheet and familiarize yourself with the data. The spreadsheet contains the same columns as in the previous assignment.
- Create a macro that calculates the total number of units sold and total revenue for the entire period.
- Create a macro that generates a PivotTable report showing the total number of units sold and total revenue by product.
- Create a macro that generates a bar chart based on the data in the PivotTable.
- Create a macro that calculates the total number of units sold and total revenue for each salesperson using the SUMIFS function.
- Create a macro that generates a line chart based on the data for each salesperson.
- Create a macro that uses the IF function to create a new column in the spreadsheet indicating whether the sale was made to a new or returning customer.
- Create a macro that calculates the total number of new and returning customers using the COUNTIF function.
- Create a macro that generates a pie chart based on the data for new and returning customers.
- Write a short report (1-2 pages) summarizing your findings and including screenshots of your charts. Be sure to include any insights or recommendations for improving sales. In the report, explain how your macros have streamlined the data analysis process and made it easier to generate reports.

## Solar Project

Submit assignment here

**Important Note:**

- Complete each task on a separate sheet of the Excel file.
- Rename the Excel file name as Your Name (Please write Your name as it Appears in the

Attendance Sheet).

Marks = 10 Marks

Time: December 31, 2021

Instructions

- Make a proper feasibility report, with calculations in Excel and Writeup in MS word
- The initial cost of the project will include: Cost of solar panels (10 KW), Grid tied inverter (without batteries), and installation cost. (Get proper quotations from available vendors with their letter heads)
- Forecast the cash flows with the following variations:
- Each variation will be given on a separate sheet. Assume that PESCO will buy units from you at Rs 10 per unit
- Assume that PESCO will buy units from you at cost of production, that is nearly Rs 25 per unit

- Use discount rate of Risk free + some risk premium. Just the risk premium, currently the risk premium is around 8%. But you can adjust it given the riskiness of this project
- Consider just the financial aspects of this project and decide whether to accept this project
- Consider environmental benefit of the project and then decide whether this project is good.
- Consider that electricity production is a burden on the government and you are contributing to the society with this project, should we accept this project?
- Consider that electricity supply is not stable, is this project valuable in this context?

## Assignment 1 : Basic Functions and Text Manipulation

Marks = 5

Deadline = October 23, 2021; till 11:59:59 PM

Submission Link = Upload Here

**Important Note:**

- Complete each task on a separate sheet of the Excel file.
- Rename the Excel file name as Your Name (Please write Your name as it Appears in the

Attendance Sheet).

1. Download some example data from Keggle and complete the following tasks?

a. Apply functions related to summary statistics

b. Apply product(), sumbproduct(), abs() function on data of your choice

2. Download **this data ** and perform the following activities:

a. Import this data to Excel

b. Extract Loss amount to separate column

c. Extract Profit after taxation to a separate column

d. Extract dividend percentage to a separate column

e. Replace o with 0 if there is any typing error in the profit or loss amount

f. Make a separate sheet of company symbols. The symbols should not have duplicates

3. Show practices on age calculation

4. Show practice or concatenate function

5. Use at least 5 functions that are not covered in the above exercise

## Assignment 2 : Time Value of Money

Marks = 5

Deadline = October 30, 2021; till 11:59:59 PM

Submission Link = Upload Here

**Important Note:**

- Complete each task on a separate sheet of the Excel file.
- Rename the sheet names as 1, 2, 3, corresponding to the question numbers.
- 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.

# Final Paper

Download Mid-Term Paper Upload Paper

# List of topics covered in the Mid-Term Exam

- Make cell reference absolute
- Make cell reference absolute for rows
- Make cell reference absolute for columns
- ABS()
- average()
- stdev()
- min()
- max()
- median()
- Negation
- All negative
- count()
- counta()
- countif()
- countifs()
- sum()
- SUMIF()
- sumifs()
- averageif()
- averageifs()
- Count string cells
- product()
- sumproduct()
- Index()
- match()
- search()
- PV()
- FV()
- PMT()
- NPER()
- NPV()
- IRR()
- RATE()
- Profitability index
- Amortization table
- Importing text
- Data to columns using delimters
- right()
- left()
- upper()
- lower()
- proper()
- concatenate()
- Merge cells using the & symbol
- Number to date
- Using PKR currency symbol
- IF
- Nested IF
- OR()
- AND()
- Tax calculation with nested IF
- Make separate data from filtered data
- Format Painter
- Convert formula to values
- Rand()
- Randbetween()
- What is the rule for the double click and filldown
- What is the short-cut for going to the end of the data below the current cell
- What the short-cut for auto-sum
- What is the short-cut to show all formulas
- What is the short-cut for selecting all data to the right fo the current cell
- If a given cells is empty, make current cell empty, otherwise use some formula in the current cells
- IFERROR()
- Find and Replace with Ctr + H
- Cell borders
- Font color
- What is the difference between copy paste and dragging
- ROUND()
- Create Excel Functions
- Age calculation

# List of topics covered in our final exam

- Select and color all text cells
- Select all formula cells
- Select all numeric cells
- Conditional formatting
- Conditional formatting using a formula
- Vlookup()
- Vlookup using two or more criteria
- Freeze view
- Protect cell or range
- Protect sheets
- Protect file
- Validation
- Validation based on list
- Validation based on range
- Validation based on dates
- Make data entry sheet (like the one we used for Home Construction)
- CONDITIONAL FORMATTING

1. Text based formatting

2. Formula based formatting

3. Formatting only numbers

4. Formatting only formulas

5. Formatting only textFILTERING DATA

Charts and Graphs

1.Named range

Modify named range

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

Pivot Tables

1. Rows

2. Columns

3. Filters

4. Values

5. Average, sum, min, max, etc

6. Pivot Charts