Excel is a more powerful tool and has more applications than many users realize. For most Financial Professionals, Excel is the most-used spreadsheet and analysis application. In this series of progressive workshops students will explore the numerous ways spreadsheets can be used to support financial decision-making. This series of classes follows the critical thinking, problem-solving approach; it is designed to provide real experience in applying financial concepts to financial modeling. In the process, you will extend your knowledge into some of the more specialized and advanced financial capabilities of Excel and you should be better able to recognize the intuition behind financial concepts that you have already been taught in other finance courses.

The Excel for Financial Professionals workshops series are designed to progress from one to another. Class numbers are small, typically 10 to 15 attendees, allowing significant one-on-one time between instructor and participants.

 I. Excel for Financial Professionals: Introduction 
 

Skills required and Prerequisites:

• A basic knowledge of Excel spreadsheets is required

Completion of

• Fin 301 -Intermediate Managerial Finance (B- or higher)
• Fin 310 -Principles of Investments              (B- or higher)

      Course will cover:

• Understand the use of spreadsheets and Excel
• Build formulas containing relative, absolute, and mixed references
• Installing the Analysis ToolPak
• Financial Formulas:

* Formulas for Interest, Cash Flow, Investments, Annuities

- CUMIPMT - Cumulative Interest Payment

- CUMPRINC - Cumulative Principal

- EFFECT - Effective annual interest rate

-FV - Future Value of an investment

- FVSCHEDULE - Future Value with a variable rate

-IPMT - Interest Payment for an investment or loan

-IRR - Internal Rate of Return

-MIRR - Modified Internal Rate of Return

-NPER - Number of Periods for an investment or loan

-NPV - Net Present Value formula

-PMT - Periodic Payment for an annuity

-PPMT - Payment on the Principal for an annuity or loan

-PV - Present Value of an investment

-RATE - Interest rate per period

- XIRR - Internal Rate of Return (not necessarily periodic)

- XNPV - Net Present Value (not necessarily periodic)

* Functions for Coupons

- COUPDAYBS - Days from the Beginning of the Coupon period to the Settlement date

- COUPDAYS - Days in the coupon period that contains the Settlement date

- COUPDAYSNC - Days from the Settlement date to the Next Coupon date

- COUPNCD - Next Coupon Date after the settlement date

- COUPPCD - Previous Coupon Date before the settlement date

- COUPNUM - Number of coupons between the settlement and maturity date

* Finance Formulas for Securities

- ACCRINT - Accrued Interest

- ACCRINTM - Accrued Interest at Maturity

- DISC - Discount rate

- DURATION - Annual Duration

- INTRATE - Interest rate for a fully invested security

- MDURATION - Macauley modified duration (with an assumed par value of $100)

- NOMINAL - Annual nominal interest rate

- ODDFPRICE - Price per $100 face value with an Odd First period

- ODDFYIELD - Yield with an Odd First period

- ODDLPRICE - Price per $100 face value with an Odd Last period

- ODDLYIELD - Yield with an Odd Last period

- PRICE - Price per $100 face value

- PRICEDISC - Price per $100 face value of a Discounted security

- PRICEMAT - Price per $100 face value of a security that pays interest at Maturity

- RECEIVED - Amount received at maturity for a fully invested security

- YIELD - Yield on a security that pays periodic interest

- YIELDDISC - Annual yield for a discounted security (Treasury bill)

- YIELDMAT - Annual yield of a security that pays interest at maturity

* Formulas for Dollar Price Conversions

- DOLLARDE - Converts a dollar price from a Fraction to a Decimal number

- DOLLARFR - Converts a dollar price from a Decimal number to a Fraction

* Treasury Bill Functions

- TBILLEQ - Bond-equivalent yield for a Treasury Bill

- TBILLPRICE - Price per $100 face value for a Treasury Bill

- TBILLYIELD - Yield for a Treasury Bill

* Depreciation Formulas

-DB - Fixed-Declining Balance

-DDB - Double-Declining Balance

-SLN - Straight-Line Depreciation

-SYD - Sum-of-Years' Digits

II. Excel for Financial Professionals: Intermediate
 
Skills required and Prerequisites:

Completion of  Excel for Financial Professionals : Introduction

      Course will cover:

• Built-in functions :

* Logical functions

-AND - Returns TRUE if all of its arguments are TRUE

-IF - Specifies a logical test to perform

-IFERROR  - Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula

-NOT - Reverses the logic of its argument

-OR  - Returns TRUE if any argument is TRUE

* Statistical functions

-AVERAGE - Returns the average of its arguments

-AVERAGEIF - Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria

-AVERAGEIFS -Returns the average (arithmetic mean) of all cells that meet multiple criteria.

-CORREL - Returns the correlation coefficient between two data sets

-COUNT - Counts how many numbers are in the list of arguments

-COUNTA - Counts how many values are in the list of arguments

-COUNTBLANK - Counts the number of blank cells within a range

-COUNTIF - Counts the number of cells within a range that meet the given criteria

-COUNTIFS - Counts the number of cells within a range that meet multiple criteria

-COVAR  - Returns covariance, the average of the products of paired deviations

-GEOMEAN - Returns the geometric mean

-HARMEAN - Returns the harmonic mean

-INTERCEPT - Returns the intercept of the linear regression line

-MAX - Returns the maximum value in a list of arguments

-MAXA - Returns the maximum value in a list of arguments, including numbers, text, and logical values

-MEDIAN - Returns the median of the given numbers

-MIN - Returns the minimum value in a list of arguments

-MINA- Returns the smallest value in a list of arguments, including numbers, text, and logical values

-MODE - Returns the most common value in a data set

-PEARSON - Returns the Pearson product moment correlation coefficient

-PERCENTILE - Returns the k-th percentile of values in a range

-QUARTILE - Returns the quartile of a data set

-RANK -  Returns the rank of a number in a list of numbers

-RSQ - Returns the square of the Pearson product moment correlation coefficient

-SLOPE - Returns the slope of the linear regression line

-STDEV - Estimates standard deviation based on a sample

-STDEVA - Estimates standard deviation based on a sample, including numbers, text, and logical values

-VAR - Estimates variance based on a sample

-VARA - Estimates variance based on a sample, including numbers, text, and logical values          

* Lookup and reference functions

-CHOOSE - Chooses a value from a list of values

-HLOOKUP - Looks in the top row of an array and returns the value of the indicated cell

-LOOKUP - Looks up values in a vector or array

-TRANSPOSE - Returns the transpose of an array

-VLOOKUP - Looks in the first column of an array and moves across the row to return the value of a cell

• Working with External Data
• Create scenarios for what-if analyses
• Goal Seek
• The Solver
  

III. Excel for Financial Professionals:  Advanced
 
Skills required and Prerequisite:

Completion of  Excel for Financial Professionals : Intermediate

Completion of :

-Fin 410 - Securities Analysis    (B- or higher)

OR

-Fin 425  - Financial Derivatives (B- or higher)

OR

-Fin 499 - CFA Seminar              (B- or higher)

OR

With the permission of the workshop instructor if you are taking any of the above courses this semester.

        Course will cover:

• Financial Modeling

* Financial modeling involves developing a spreadsheet that describes a financial structure. Financial modeling uses the skill sets of finance, accounting, and spreadsheet design. This part of the course will focus on the basics of model building under Microsoft Excel with in depth focus on understanding frameworks and techniques. These models may include discounted cash flow analysis, capital budgeting, financing, and flow-of-funds models, costing, and portfolio analysis.

• Financial Management  Modeling

* Financial statement analysis

* Capital budgeting Modeling

• Investment Modeling

*  The features, behavior, and pricing of options.

*  Black-Scholes Option Pricing Model

* Option strategies modeling

* Investment modeling techniques

* Portfolio analysis modeling