FIN 470: Financial Analysis in Excel

Larry Schrenk, Instructor

Schedule and Supplements

Topic Number	Date	Topics	Readings
1 (Top)	Week 1 1/13-1/19	INTRODUCTION TO EXCEL Slides 1.1: Course Introduction 1.2: Excel Introduction 1.3: Excel Details S&P Capital IQ S&P Capital IQ Login (Website) Introduction to S&P Capital IQ (Video, 27:37) S&P Capital IQ Resource Page (Webpage) Notes on Excel Procedure and Function Videos (IMPORTANT) Some videos and the accompanying spreadsheets are still being developed. All should be available by mid-semester. The captions and transcripts have not yet been corrected; I plan to have those completed by mid-semester as well. All videos in the course are .mp4 files Complete List of Excel Videos Captioning/Transcript: Accurate captioning is available for all course videos and transcripts are available. To activate captioning, use the CC button at the bottom right of the video player.  I STRONGLY RECOMMEND that you watch the videos with closed captioning to increase your comprehension of the material. Playback Speed: The video player allows playback at different speeds. I often listen to lectures or webinars at 1.25x or 1.5x, but this may not work well for technical material.   Excel Procedure Videos (Basic) Excel Tour (11:12) xlsx Creating and Saving (9:09) xlsx Entering Data (14:04) xlsx Copying, Moving, and Pasting (8:34) xlsx Paste Special (14:05) xlsx Deleting (13:37) xlsx Search, Sort, and Filter (17:49) xlsx Font Formats (14:27) xlsx Number Formats I (10:14) xlsx Number Formats II (13:01) xlsx Cell Formats I (9:04) xlsx Cell Formats II (10:00) xlsx Formulae, Intro I (4:44) xlsx Formulae, Intro II (10:21) xlsx Charts, Intro (15:45) xlsx Printing (6:04) xlsx IMPORTANT NOTES Captioning/Transcript: (By midsemester) Accurate captioning is available for all course videos and transcripts are available. To activate captioning, use the CC button at the bottom right of the video player. I STRONGLY RECOMMEND that you watch the videos with closed captioning to increase your comprehension of the material. Playback Speed: The video player allows playback at different speeds. I often listen to lectures or webinars at 1.25x or 1.5x, but this may not work well for technical material.   Excel Function Videos GEOMEAN(number1, number2, ...) Calculate the geometric mean (11:13) xlsx AVERAGE(number1, number2,...) Calculate the arithmetic mean (9:56) xlsx         NOTE: There is no Group Assignment 1.	Chapter 1
2 (Top)	Week 2 1/20-1/26	FINANCIAL STATEMENTS AND ANALYSIS TOOLS Slides 2.1 Financial Statements, Theory 2.2 Financial Statements, Excel (Video, 26:41) Excel Class (pdf) 2: Financial Statements (pdf) Excel Function Videos                          From Chapter 2 SUM(number1, number2, ...) Total numbers or a range of cells (4:05) xlsx AVERAGE(number1, number2,...) Average numbers or a range of cells (9:56) xlsx COUNT(value1, value2, ...) Count the numbers in a range (5:31) xlsx MAX(number1, number2, ...) Find the maximum of numbers in a range (2:01) xlsx MIN(number1, number2, ...) Find the minimum of numbers in a range 2:01) xlsx CHOOSE(index, value1, value2,...) Select a value based on an index number (2:32) xlsx SWITCH(expression, value1, result1, default  or value2, result2, ...) Return a result based on the first matched value, or the default value (3:18) xlsx                        From Chapter 3 (which we do not cover in this course)  IF, IFS (logical_test, value_if_true, value_if_11false) I Return a value dependent on test (:10:49) xlsx IF, IFS (logical_test, value_if_true, value_if_11false) II Return a value dependent on test (14:18) xlsx AND(logical1, logical2,...) Returns true if all arguments true (15:03) xlsx OR(logical1, logical2,...) Returns true if one argument is true (15:03) xlsx   Group Assignment 2, Financial Stratements, Due: 2/4 Help Video: Downloading Financial (and Other) Data (mp4, 16:59) NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.   MLK Day, Monday 1/20, No Classes	Chapter 2
3 (Top)	Week 3 1/27-2/2	FINANCIAL STATEMENT FORECASTING NOTE: Chapters 3 and 4 are not covered in this class. Slides 3.1: Pro Forma Forecasting, Theory 3.2: Pro Forma Forecasting, Excel Excel Class (pdf) 3: Pro Forma Forecasting (pdf) Practice Problems (xlsx) Practice Problems 3: Goal Seek, Naming, TREND NOTE: Items introduced in Practice Problems are not covered on the exams unless they are included in other parts of the course. Excel Function Videos AVERAGE(number1, number2,...) Calculate an arithmetic average (9:56) xlsx SLN(cost, salvage, life) Calculate straight-line depreciation (9:28) xlsx TREND(known_y’s, known_x’s, new_x’s, const) Forecast future outcomes based  on a time trend (17:39) xlsx INTERCEPT(known_y’s, known_x’s) Find the intercept of a regression equation (17:39) xlsx SLOPE(known_y’s, known_x’s) Find the slope of a regression equation (17:39) xlsx                         From Chapter 4 (which we do not cover in this course) DATE(year, month, day) Calculate a date serial number (12:52) xlsx YEAR(serial_number) Extract year from a date (12:52 xlsx MONTH(serial_number) Extract month from a date (12:52) xlsx DAY(serial_number) Extract day from a date (12:52) xlsx CONCAT(text1, text2, ...) Join text strings (8:21) xlsx TEXTJOIN(delimiter, ignore_empty,  text1, text2, ...) Join strings with a specified delimiter (8:21) xlsx TEXT(value, format_text) Convert a number to formatted text (5:00) xlsx IF(logical_test, value_if_true, value_if_false) I Return a value based on a logical test (10:49) xlsx IF(logical_test, value_if_true, value_if_false) II Return a value based on a logical test (14:18) xlsx MIN(number1, number2, ...) Determines the minimum of a list of arguments (2:01) xlsx MAX(number1, number2, ...) Determine the maximum of a list of arguments (2:01) xlsx AND(logical1, logical2, ...) Returns true only if all arguments are true (15:03) xlsx OR(logical1, logical2, ...) Returns true if any argument is true (15:03) xlsx Group Assignment 3, Pro Forma Forecasting, Due: 2/9   Group AI Assignment 1: Due 2/9 NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 5
4 (Top)	Weeks 4/5 2/3-2/9 2/10-2/16	FORECASTING SALES WITH TIME SERIES METHODS Slides 4.1: Time Series Forecasting I 4.2: Time Series Forecasting II Excel Class (pdf) 4: Time Series Forecasting (pdf) Practice Problems (xlsx) Practice Problems 4: MA, AVERAGEIF, VLOOKUP, SUMXMY2, Solver Excel Procedure Videos (Intermediate) Histograms (6:42) xlsx Linear Regression (14:32) xlsx Excel Function Videos AVERAGE(number1, number2,...) Calculate an arithmetic average (9:56) xlsx XLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Look up values in a table (:) xlsx SUMXMY2(array_x, array_y) Calculate the sum of squared errors (8:17) xlsx ROW(reference) Return the row number (2:53) xlsx FORECAST.ETS(target_date, values, timeline, seasonality, data_completion, aggregation) Create a time series forecast (:) xlsx Group Assignment 4, Time Series Forecasting, Due: 2/23  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines. Assessment Day, Tuesday 2/11, No Classes	Chapter 6
5 (Top)	Week 6 2/17-2/23	THE TIME VALUE OF MONEY Slides 5.1: Time Value of Money, Theory 5.2: Time Value of Money, Excel 5.3: Time Value of Money, Problems Excel Class (pdf) None Practice Problems (xlsx) Practice Problems 5: Conditional Formatting, Heat Maps Excel Function Videos FV(rate, nper, pmt, pv, type) Find the future value (10:30) xlsx PV(rate, nper, pmt, fv, type) Find the present value (10:30) xlsx PMT(rate, nper, pv, fv, type) Payment of an annuity (16:45) xlsx NPER(rate, pmt, pv, fv, type) Number of periods (16:45) xlsx LN(number) Natural logarithm (1:38) xlsx RATE(nper, pmt, pv, fv, type, guess) Yield of an annuity (16:45) xlsx NPV(rate, value1, value2,...) Present value of uneven  cash flows (10:10) xlsx IRR(values, guess) Yield of uneven cash flows (10:40) xlsx EFFECT(nominal_rate, npery) Effective annual rate (7:10) xlsx NOMINAL(effect_rate, npery) Nominal annual rate (7:10) xlsx EXP(number) Raise e to a power (1:38) xlsx Group Assignment 5, Time Value of Money, Due: 3/2 Group AI Assignment 2 (link TBA): Due 3/2 NOTE: For full credit all spreadsheets must conform to the Excel Guidelines	Chapter 8
6 (Top)	Week 7 2/24-3/2	COMMON STOCK VALUATION Slides 6.1: Stock Valuation, Theory 6.2: Stock Valuation, Excel Excel Class (pdf) 6: Stock Valuation (pdf) Practice Problems (xlsx) Practice Problems 6: Charts, Plots, Histograms Excel Function Videos SEQUENCE(rows, columns, start, step) Create sequences (10:39) xlsx Group Assignment 6, Stock Valuation, Due: 3/9   NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 9
7 (Top)	Week 8 3/3-3/9	BOND VALUATION         Slides 7.1: Bond Valuation, Theory 7.2: Bond Valuation, Excel Excel Class (pdf) 7: Bond Valuation (pdf) Practice Problems (xlsx) Practice Problems 7: Data Validation Excel Function Videos ROUNDUP(number, num_digits) Round a number up, away from 0 (5:02) xlsx PRICE(settlement, maturity, yld, redemption, frequency, basis) Value of a bond (11:12) xlsx DAYS360(start_date, end_date, method) Count Days using the 30/360 Method (6:55) xlsx YIELD(settlement, maturity, rate, pr, redemption, frequency, basis) Yield to maturity of a bond (11:12) xlsx YEARFRAC(start_date, end_date, method) Calculate the Fraction of a Year using a Day Count Method (6:55) xlsx DISC(settlement, maturity, pr, redemption, basis) Bank Discount Rate (11:12) xlsx YIELDDISC(settlement, maturity, pr, redemption, basis) Bond Equivalent Yield (11:12) xlsx EDATE(start_date, months) Find a date months after a specified date (6:55) xlsx DURATION(settlement, maturity, coupon, yld, frequency, basis) Macaulay Duration (4:23) xlsx MDURATION(settlement, maturity, coupon, yld, frequency, basis) Modified Duration (4:23) xlsx                    Group Assignment 7, Bond Valuation, Due: 3/23   NOTE: For full credit all spreadsheets must conform to the Excel Guidelines. Exam 1, Friday 3/7 (Coverage: Through Topic 6: Stock Valuation)      Sample Exam with Solutions	Chapter 10
		Spring Break 3/10 to 3/14
		NOTE: The schedule of topics after Spring Break is still under construction.
8 (Top)	Week 9 3/17-3/23	CAPITAL BUDGETING         Slides 8.1: Capital Budgeting, Theory 8.2: Capital Budgeting, Excel Excel Class 8: Capital Budgeting (pdf) Practice Problems (xlsx) Practice Problems 8: Data Protection Excel Procedure Videos (Intermediate) Hyperlinks (:) xlsx Quick Access Toolbar, Ribbon (:) xlsx Short Cuts (4:12) xlsx  Status Bar (4:10) xlsx Tables (:) xlsx Trendlines (6:27) xlsx Excel Function Videos SUMPRODUCT(array1, array2, ...) Calculate the sum of the product of corresponding cells (8:17) xlsx  SMALL(array, k) Return the kth smallest number in a range (2:04) xlsx SLN(cost, salvage, life) Calculate Straight-Line Depreciation (9:28) xlsx XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] ) Searches a range or an array, and then returns the item corresponding to the first match it finds  (:) xlsx VDB(cost, salvage, life, start_period, end_period, factor, no_switch) Calculate Variable Depreciation (9:28) xlsx IFERROR(value, value_if_error) Handle Errors (5:11) xlsx IRR(values, guess) Calculate the IRR (10:40) xlsx MIRR(values, finance_rate, reinvest_rate) Calculate the MIRR (10:40) xlsx FILTER(array, include, if_empty) Filter a range (7:17) xlsx TEXTJOIN(delimiter, ignore_empty, text1, text2, ...) Join a list of text strings (8:21) xlsx      Group Assignment 8, Capital Budgeting, Due: TBA  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 12
9 (Top)		RISK AND CAPITAL BUDGETING Slides 9.1: Risk and Capital Budgeting Excel Class 9: Risk and Capital Budgeting (pdf) Practice Problems (xlsx) Practice Problems 9: Forms (for Data Entry) Excel Function Videos SLOPE(known_y’s, known_x’s) Calculate the slope of a regression line (17:39) xlsx VAR.P(number1, number2, ...) Calculate a population variance (5:47) (not available) VAR.S(number1, number2, ...) Calculate a sample variance (5:47) (not available) STDEV.P(number1, number2, ...) Calculate a population standard deviation (5:47) (not available) STDEV.S(number1, number2, ...) Calculate a sample standard deviation (5:47) (not available) NORM.S.DIST(z, cumulative) Calculate the area under a standard normal curve (4:28) xlsx COUNTIF(range, criteria) Count the numbers in a range that meet a criteria (5:31) xlsx Summary statistics (average, standard deviation, slope, etc.) can be very misleading. It's best to always LOOK at the plotted data to undertand what is going on. Here is an excellent video on this issue: The DataSaurus, Anscombe’s Quartet, and why summary statistics need to be taken with a grain of salt   Group Assignment 9, Risk Analysis, Due: TBA    Group AI Assignment 3 (link TBA): Due 3/30 NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 13
10 (Top)	Week 10 3/24-3/30	PORTFOLIO STATISTICS AND DIVERSIFICATION Slides 10.1: Portfolios, Theory 10.2: Portfolios, Excel Excel Class 10: Portfolio Statistics (pdf) Practice Problems (xlsx) Practice Problems 10: Basic Linear Algebra Excel Procedure Videos (Advanced) Analyze Data (:) xlsx Conditional Formatting (:) xlsx  Data Validation (:) xlsx  Data Visualization (:) xlsx  Fuzzy Matching (:) xlsx  Excel Function Videos CORREL(array1, array2) Calculate correlation (3:17) xlsx COVARIANCE.S(array1, array2) Calculate covariance (3:17) xlsx MMULT(array1, array2) Matrix multiplication (:) xlsx TRANSPOSE(array) Matrix transpose (:) xlsx  Group Assignment 10, Portfolios, Due: TBA    NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.    Individual Project Proposal, Due: TBA	Chapter 14
11 (Top)	Week 11 3/31-4/6	ANALYZING DATASETS WITH TABLES AND PIVOT TABLES Slides 11.1: Pivot Tables Excel Class 11: Pivot Tables (pdf) Excel Procedure Videos (Advanced) Import Data (:) xlsx  Macros (:) xlsx Practice Problems (xlsx) Practice Problems 11: Data Models Excel Function Videos FILTER(Array, Include, If_Empty) Filter a range for items that meet specified criteria (7:17) xlsx AVERAGEIF(number1, number2,...) Calculate an average of the data that meets the criteria (9:56) xlsx GETPIVOTDATA(Data_Field, Pivot_Table, Field1, Item1, ...) Return a Value from a Pivot Table (:) xlsx  INDEX(array, row_num, [column_num])  Returns a value or the reference to a value from within a table or range (:) xlsx  XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])  Searches for a specified item in an array or range of cells, and then returns the item's relative position (:) xlsx     Group Assignment 11, Pivot Tables, Due: TBA  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 16
12 (Top)	Week 12 4/7-4/13	WRITING USER-DEFINED FUNCTIONS WITH VBA Slides 12.1: VBA, Excel I 12.2: VBA, Excel II Excel Class 12: VBA I (pdf) Practice Problems (xlsx) None Excel Function Videos TBA        Group Assignment 12, VBA, Due: TBA         Group AI Assignment 4 (link TBA): Due 4/20 NOTE: For full credit all spreadsheets must conform to the Excel Guidelines. Fall Registration, Monday 4/7  Spring Break Day, Friday 4/11	Chapter 15
13 (Top)	Week 13 4/14-4/20	POWER BI I Slides 13: TBA Excel Class 13: TBA Practice Problems (xlsx) None Excel Function Videos TBA	TBA
14 (Top)	Week 14 4/21-4/27	POWER BI II Slides 14: TBA Excel Class 14: TBA Practice Problems (xlsx) None Excel Function Videos TBA Research Day, Thursday 4/24, No Classes	TBA
15 (Top)	Week 15 4/28-5/4	A MISCELLANY OF OTHER EXCEL FEATURES Slides TBA Excel Class 13: TBA Practice Problems (xlsx) None Excel Function Videos TBA Group AI Assignment 5  (link TBA): Due 5/4	TBA
		Exam 2/Final Exam   Date: See Registrar's Website