Calendar: ICS Files for your Calendar
Part 1: Saturday, Feb 27, 2020 1-5 PM
Part 2: Sunday, Feb 28 1, 2020 1-5 PM
Part 3: Saturday, Mar 6, 2020 1-5 PM
Part 4: Sunday, Mar 7, 2020 1-5 PM
You can attend any part without attending other parts.
To register, please visit Student Affairs website before emailing them. Please do not copy me on your emails to Student Affairs. I have no involvement in the registration process. The seminar is open only to Stern MBAs OR any student in my classes. Please let them know that you are my student if you are not a Stern MBA student.
The workshop covers the following skills:
- Building projections of financial statements
- Developing scenarios and what-if analysis
- Solving investments and corporate finance problems
- Data analysis
- Please contact the relevant Stern offices for scheduling, fees, registration, location, etc. I have no involvement other than designing and teaching this course.
- These sessions will be recorded but the recording will be kept for only two weeks.
- You need to bring a computer to every class with sufficient battery power to last three hours. If you have any technical questions, please contact the IT department (212-998-0180), NOT me.
- Please get the most recent version of Excel. Microsoft Office 365 is free for students and has the most recent Excel version. That works the best. Please contact Stern IT for details.
- Both Windows and MAC will work although Windows version is easier to work with. Since our podiums have Windows, I will be using Windows in class.
- Make sure that you can connect your computer to the NYU wireless network.
Session 1: Building projections of financial statements
Developing and reviewing business plans is an integral part of what entrepreneurs and executives do.
Understanding how different pieces of a business plan fit together financially is critical to being a successful manager.
The business plans are also the basis of budgets used to monitor the progress of a business or division.
Accounting and finance skills taught
Linking financial statements
- Linking net income on the income statement and dividends on the cash flows to retained earnings
- Linking financing to debt and equity
- Linking cash flows to cash
Deriving cash flows from income statement and balance sheet assumptions
- Deriving receipts using revenues, receivables, and deferred revenues
- Deriving payments using expenses, prepayments, and payables
Excel skills taught
- Useful shortcuts: s01-excel-shortcuts.pdf
Excel best practices
- Separating assumptions from formulas to highlight assumptions clearly
- Setting up formulas that can be dragged across
Styles and Add-ins [This is an incredibly useful feature that is not used because most users are not aware of it.]
- Excel styles
Excel Add-ins: s02-gode-add-in-description.pdf
Checks and Errors
- Conditional formatting and check fields: s03-01-checks.pdf
- Reference errors: s03-02-ref-errors.xlsx
- Value errors: s03-03-value-errors.xlsx
- Num errors: s03-04-num-errors.xlsx
- Other errors: s03-05-miscellaneous-errors.xlsx
Iterations [Time permitting]
- Why are they needed
- How can they be avoided
- Recovering from iterations
Session 2: Developing scenarios and what-if analysis
Excel functions needed for multiperiod models
- SUMPRODUCT, ARRAYS: s05-01-sumproduct.xlsx
- OFFSET: s05-02-offset-function.xlsx
- Reversing using lookup: s05-03-reverse.xlsx; s05-04-reverse-another.xlsx
- Building scenarios using data tables: s06-01-data-table.xlsx
- Custom formatting: s09-01-custom-formatting.xlsx
- Data Validation: s09-02-data-validation.xlsx
- CHOOSE: s09-03-offset-indirect-choose.xlsx
- MATCH; INDEX: s09-04-index-match.xlsx
- INDIRECT: s09-05-indirect-pick-worksheets.xlsx
- VLOOKUP, HLOOKUP: s09-06-lookup.xlsx
- Spinners: s09-07-spinbox.xlsx
- Listboxes: s09-08-listbox.xlsx
Session 3: Investments and corporate finance problems
The focus here will not be to teach finance per se but to illustrate how to use Excel to solve finance problems.
- Illustration on how to compute present value and IRR using Excel
- Bank accounts
- Credit cards
- Car loans and mortgages
Session 4: Data analysis
Data analysis examples
The focus here will not be to teach data analysis concepts per se but to illustrate how to use Excel to analyze data.
- Classifying data
- Solving equations
- Data analysis functions
- Data filtering
- Pivot tables
- COUNT, COUNTA, COUNTIF, COUNTIFS, COUNTBLANK
- AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS
- MEDIAN, MODE.SNGL, MODE.MULT, RANK.AVG, RANK.EQ
- MAX, MAXA, MIN, MINA, LARGE, SMALL
- PERCENTILE.EXC, PERCENTILE.INC
- VAR.P, VAR.S, STDEV.P, STDEV.S
- COVARIANCE.P, COVARIANCE.S, DEVSQ, CORREL, PEARSON
- NORM.DIST, NORM.S.DIST
- SLOPE, LINEAR REGRESSIONS