FIN 470: Financial Analysis in Excel

Larry Schrenk, Instructor

Schedule and Supplements

Topic Number	Date	Topics	Readings
1 (Top)	Week 1 1/8 to 1/14	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/15 to 1/21	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/15, No Classes	Chapter 2
3 (Top)	Week 3 1/22 to 1/28	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/11   NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 5
4 (Top)	Weeks 4/5 1/29 to 2/4 2/5 to 2/11	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/25  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 6
5 (Top)	Week 6 2/12 to 2/18	BREAK-EVEN AND LEVERAGE ANALYSIS   Slides 5.1: Break-Even/Leverage Analysis I 5.2: Break-Even/Leverage Analysis II Excel Class (pdf) 5: Break-Even/Leverage Analysis (pdf) Practice Problems (xlsx) Practice Problems 5: Scenario Manager, Data Tables Excel Procedure Videos (Intermediate) Add-ins (8:36) xlsx Auto-Fill and Flash Fill (:) xlsx Cell References (14:25) xlsx Dynamic Arrays, Intro (:) xlsx Freezing and Splitting (5:11) xlsx  Excel Function Videos NA( ) Return #N/A (2:05) xlsx IFERROR(value, value_if_error) Determine if a formula returns an error value (5:11) xlsx Group Assignment 5, Break-Even, Leverage, Due: 3/3   NOTE: For full credit all spreadsheets must conform to the Excel Guidelines. Assessment Day, Tuesday 2/13, No Classes	Chapter 7
6 (Top)	Week 7 2/19 to 2/25	THE TIME VALUE OF MONEY Slides 6.1: Time Value of Money, Theory 6.2: Time Value of Money, Excel 6.3: Time Value of Money, Problems Excel Class (pdf) None Practice Problems (xlsx) Practice Problems 6: 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 6, Time Value of Money, Due: 3/10 NOTE: For full credit all spreadsheets must conform to the Excel Guidelines	Chapter 8
7 (Top)	Week 8 2/26 to 3/3	COMMON STOCK VALUATION Slides 7.1: Stock Valuation, Theory 7.2: Stock Valuation, Excel Excel Class (pdf) 7: Stock Valuation (pdf) Practice Problems (xlsx) Practice Problems 7: Charts, Plots, Histograms Excel Function Videos SEQUENCE(rows, columns, start, step) Create sequences (10:39) xlsx Exam 1, Friday 3/1 (Coverage: Through Topic 6: Time Value of Money)      Sample Exam with Solutions Group Assignment 7, Stock Valuation, Due: 3/31   NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 9
		Spring Break 3/4 to 3/8
8 (Top)	Week 9 3/11 to 3/17	BOND VALUATION         Slides 8.1: Bond Valuation, Theory 8.2: Bond Valuation, Excel Excel Class (pdf) 8: Bond Valuation (pdf) Practice Problems (xlsx) Practice Problems 8: 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 8, Bond Valuation, Due: 4/7   NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 10
9 (Top)		THE COST OF CAPITAL (Cancelled)
10 (Top)	Week 10 3/18 to 3/24	CAPITAL BUDGETING         Slides 10.1: Capital Budgeting, Theory 10.2: Capital Budgeting, Excel Excel Class 10: Capital Budgeting (pdf) Practice Problems (xlsx) Practice Problems 10: 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 10, Capital Budgeting, Due: 4/14  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.    Individual Project Proposal, Due: 3/31	Chapter 12
11 (Top)	Week 11 3/25 to 3/31	RISK AND CAPITAL BUDGETING Slides 11.1: Risk and Capital Budgeting Excel Class 11: Risk and Capital Budgeting (pdf) Practice Problems (xlsx) Practice Problems 11: 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 11, Risk Analysis, Due: 4/21  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 13
12 (Top)	Week 12 4/1 to 4/7	PORTFOLIO STATISTICS AND DIVERSIFICATION Slides 12.1: Portfolios, Theory 12.2: Portfolios, Excel Excel Class 12: Portfolio Statistics (pdf) Practice Problems (xlsx) Practice Problems 12: 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 12, Portfolios, Due: 4/28    NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.  Fall Registration, Monday 4/1	Chapter 14
13 (Top)	Week 13 4/8 to 4/14	ANALYZING DATASETS WITH TABLES AND PIVOT TABLES Slides 13.1: Pivot Tables Excel Class 13: Pivot Tables (pdf) Excel Procedure Videos (Advanced) Import Data (:) xlsx  Macros (:) xlsx Practice Problems (xlsx) Practice Problems 13: 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 13, Pivot Tables, Due: 4/21  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.     Spring Break Day, Friday 4/12	Chapter 16
14 (Top)	Week 14 4/15 to 4/21	WRITING USER-DEFINED FUNCTIONS WITH VBA Slides 14.1: VBA, Excel I 14.2: VBA, Excel II Excel Class 14: VBA I (pdf) Practice Problems (xlsx) None Excel Function Videos TBA         Research Day, Thursday 4/18         Group Assignment 14, VBA, Due: 4/28  NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.	Chapter 15
15 (Top)	Week 15 4/22 to 4/26	A MISCELLANY OF OTHER EXCEL FEATURES Slides 15.1: Miscellany, Excel I 15.2: Miscellany, Excel II 15.3: Miscellany, Excel III Excel Class 15:Miscellany,(xlsx) Practice Problems (xlsx) None Excel Procedure Videos (Advanced) Naming (:) xlsx  Protection (:) xlsx Quick Analysis (:) xlsx  Remove Duplicates (:) xlsx Solver, Goal Seek (:) xlsx Sparklines (:) xlsx  Excel Function Videos TBA No Group Assignment Individual Projects Due: 4/26 (To avoid penalties, please follow the submission instructions in D2L exactly.) NOTE: For full credit all spreadsheets must conform to the Excel Guidelines.
		Exam 2/Final Exam   Date: See Registrar's Website