Excel for Business: Introductory (1-2) & Intermediate and Advanced (3-6)

Overview

Become an Excel power user and boost your productivity. This seminar helps you apply advanced Excel techniques to business problems. By the end of this course, you should be able to:

• Organize spreadsheets to optimize teamwork and analyze scenarios
• Create concise and understandable spreadsheets that reduce the risk of errors
• Utilize advanced Excel techniques to automate and condense schedules, build scenarios, and make spreadsheets responsive.

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

These sessions are in person in KCMC 1-70 , but you can also Zoom in. 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. Although sessions 1 and 2 are labeled as introductory, I think most of you will not know at least 30% of the short-cuts and efficiency enhancing methods we will discuss in these two sessions. You will be much more productive if you attend the entire series.

You need to be logged in as an NYU (not NYU Stern) user to watch the video recordings. First, open a private or incognito browser. Log in to your NYU Zoom account. Then in another tab in the same browser, please open http://www.dangode.com/excel/index.htm. Click on one of the recordings. If you still have issues, contact Stern IT. Please do not email me.

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

Part 1 [Introductory]: Saturday,  October 1, 2022, 1-4 PM, Click here for the recording after following the instructions above.
Part 2 [Introductory]: Sunday, October 2, 2022, 1-4 PM Click here for the recording after following the instructions above.
Part 3 [Advanced]: Saturday, October 8, 2022, 1-4:30 PM Click here for the recording after following the instructions above.
Part 4 [Advanced]: Sunday, October 9, 2022, 1-4:30 PM Click here for the recording after following the instructions above.
Part 5 [Advanced]: Saturday, November 5, 2022, 1-4:30 PM Click here for the recording after following the instructions above.
Part 6 [Advanced]: Sunday, November 6, 2022, 1-4:30 PM Click here for the recording after following the instructions above.

To register, please visit the Student Affairs website before emailing them at 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. If this link is not working, pleasePlease 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. The links to teh recordings will be posted here and emailed to you.
• 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 [Introductory]: Time value of money, credit cards, car loans, and mortgages

Business skills taught

The focus here will not be to teach finance per se but to illustrate how to use Excel to solve finance problems.

• Setting up loan amortization tables for various types of loans
• Time value of money problems; annuities

Excel skills taught

Understanding the Excel interface and setting up files

• Accessing menus
• Excel options for data creation
• Undo and redo
• File manipulation: Open, Save, Save As, Close Workbook, and Switch Programs
• View: Maximize window, Remove all toolbars, Hide Ribbon, Title Bar, and Status Bar, Zoom options, Print gridlines, View gridlines, Print preview
• Custom views, Freeze Panes, and Split Window

Navigation and selection

• Navigating between sheets and screens: Next worksheet, Previous worksheet, Right one screen, Left one screen, Navigating to a specific worksheet
• Moving around a sheet: Beginning of a row, The first cell on a worksheet, Move to the edge of data regions Turn on End mode, End of a data region (last nonblank cell). Last used cell on a worksheet
• Extend selection, Select entire row, Select entire column, Select current data region, Select region/worksheet, Select only visible rows and columns,, Select array formula range
• Find and replace, Find different cells
• Go to a cell with specific address or attributes

Enter text

• Edit a cell and show its precedent cells
• Insert, Edit, and Delete comment
• Insert or Edit threaded comment
• Wrap text
• Merge cells [Use very sparingly]
• Center across selection [Preferred]
• Spell check and Thesaurus

Enter formulas and name cells

• Relative versus absolute addresses: A1-style versus R1C1-style, Toggle absolute references
• Autosums
• Create name Create names from selection
• Paste names if names are present Name Manager
• Enter an array formula
• Insert function and Insert function arguments

Format numbers and characters [Excel styles are covered later]

• Show Format Cells dialog
• Access format number dialog
• Toggle Bold, Italic, Underline, Strike-through
• General number format, Decimals, Time, Date, Currency, Percentage, Scientific notation
• Fonts, Zip codes

Alignment and indentation, Borders and Shading, Width and height

• Align: Left, right, center, top, bottom, and middle
• Indent and outdent
• Border dialog, Single and double borders, Shading,
• Row heights and column widths, show and hide
• Difference between merging cells and centering across selection
• Difference between wrapping text and starting a new line in a cell
• Group and ungroup

Insert, delete, and clear

• Insert and delete sheets, rows, columns, and cells
• Different types of Clear

Copy, paste, and fill

• Select cells using keyboard
• Copy Cut/Move
• Show Clipboard to copy multiple items
• Different types of paste, Paste Special Dialog
• Format painter
• Copy formula from the cell above, Copy value from the cell above
• Fill down, right, left, and up
• Fill selected region with data
• Fill Series
• Fill justify
• Flash Fill: Fill based on a pattern

Formula auditing and calculations

• Formula auditing and calculations
• Toggle display of values/formulas
• Trace Precedents, Trace All Precedents, Trace Dependents, Trace Al Dependents Clear all arrows
• Calculate all worksheets in all open workbooks
• Evaluate

Setting up spreadsheets and cell types

• Setting up an Excel worksheet to separate inputs from outputs

Charting

• Basic chart types
• Setting chart parameters

Excel financial functions

• NPV
• XNPV
• IRR
• XIRR
• FV
• NPER
• PMT
• PPMT
• RATE
• NOMINAL
• EFFECT

Part 2: Introductory data analysis

Business skills

• Summarizing data
• Identifying trends
• Slicing data from multiple perspectives

Excel skills

Viewing sheets

• Freezing panes
• Grouping rows and columns
• Grid lines
• Print layouts

Custom formatting

• Formatting dates and times
• Giving character to numbers

Checks and errors

• Conditional formatting and check fields
• Reference errors
• Value errors
• Num errors
• Other errors

Logicals and conditionals

• IF statements
• AND, OR, and NOT statements
• CHOOSE statements
• AGGREGATE function

Data summarization and analysis

• COUNT, COUNTA, COUNTIF, COUNTIFS, COUNTBLANK
• AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS
• MEDIAN, MODE.SNGL, MODE.MULT, RANK.AVG, RANK.EQ
• MAX, MAXA, MIN, MINA, LARGE, SMALL
• FREQUENCY
• PERCENTILE.EXC, PERCENTILE.INC
• VAR.P, VAR.S, STDEV.P, STDEV.S
• COVARIANCE.P, COVARIANCE.S, DEVSQ, CORREL, PEARSON

Classifying data

• Data filtering
• Pivot tables

Part 3 [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 4: 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

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 5 [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

Integrating what-if analysis with scenarios

Combining the following functions to produce highly efficient spreadsheets

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

Part 6 [Advanced]: Optimizations and simulations

Business skills

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

Excel skills

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