Advanced Excel for Business

Overview

Please contact Academicaffairs@stern.nyu.edu for all administrative matters.

These sessions are on Zoom, not in person. You can attend any part without attending other parts. However, please watch the zoom recording for any part you miss. You will be much more productive if you attend the entire series.

Zoom link: https://nyu.zoom.us/meeting/register/tJcudOivqj8tHtfwZEOgVTp0NOTNDYjDZkp2

After registering, you will receive a confirmation email containing information about joining the meeting.

Part 1 [Advanced]: Sunday, February 20, 2022, 1-4:30 PM
Part 2 [Advanced]: Sunday, February 27, 2022, 1-4:30 PM
Part 3 [Advanced]: Saturday, March 5, 2022, 1-4:30 PM
Part 4 [Advanced: Sunday, March 6, 2022, 1-4:30 PM

To register, please visit

For non-Stern students: https://www.eventbrite.com/e/excel-bootcamp-day-1-4-tickets-259568104187
For Stern students: https://nyustern.campusgroups.com/academic/rsvp_login?id=1467330&private_event=1

. If this link is not working, please Academicaffairs@stern.nyu.edu. Please do not copy me on your emails to Student Affairs as I am not involved 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.

Requirements

• These sessions will be recorded, but the recording will be kept for only one month.
• 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. I will illustrate Excel functions that do not work in versions released before 2021. Please contact Stern IT for Office 365 installation.
• Both Windows and MAC will work, although the Windows version is easier to use. Since Stern podiums have Windows, I will be using Windows in class.

Part 1 [Advanced]: 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.

Business 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

Excel best practices

• Separating assumptions from formulas to highlight assumptions clearly
• Setting up formulas that can be dragged across

Excel styles

• Excel styles for consistent formatting
• Modifying styles for global changes

Excel templates

• Using templates for consistent formatting across workbooks

Excel add-ins

• Excel Add-ins for sharing styles and code across workbooks

Part 2: Identifying key performance drivers and scenario analysis

Business skills taught

Key drivers

• Identifying key drivers and key performance indicators

Scenario analysis

• What-if analysis using data tables

Excel skills

Custom formatting

• Formatting dates and times
• Giving character to numbers

Seeking user input

• Data validation
• Spinners
• List boxes

Data tables

• One-dimensional tables
• Two-dimensional tables and their interpretation

Scenarios

• MATCH; INDEX
• VLOOKUP, HLOOKUP, XLOOKUP
• INDIRECT

Part 3 [Advanced]: Multiperiod models, waterfalls, and cascades

Business skills

Waterfalls and cascades

The skills listed below can make you ten times faster and more accurate.

• Building waterfalls and cascades efficiently
• Building advanced scenarios

Excel skills

Excel functions needed for multi-period models

• SUMPRODUCT, ARRAYS
• OFFSET
• Reversing using lookup

Automating charts

• Dynamic charts
• Advanced flexible charting using Offset

Part 4 [Advanced]: Optimizations and simulations; Classifying data

Business skills

• Optimizing advertising mix given a budget
• Breakeven sales
• Circular relationships

Excel skills

Integrating what-if analysis with scenarios

Combining the following functions to produce highly efficient spreadsheets

• Data validation
• Sumproduct
• Offset
• Indirect
• Data Tables

Classifying data

• Data filtering
• Pivot tables

Iterations

• Why are they needed
• How can they be avoided
• Recovering from iterations

Optimization

• Goal seek
• Solver
• Automatic goal seek and solver via VBA

Simulations and regressions

• NORM.DIST, NORM.S.DIST
• SLOPE, LINEAR REGRESSIONS