What is a keyboard shortcut to open a file?`
What is the recommended workbook calculation setting for Excel?
Automatic Except for Data Tables
1. Hit F2 to get into the existing formula; delete any incorrect formulas or operators 2. Hit F2 again to enable “Enter” mode on the bottom-left corner of the Excel sheet 3. Holding down Ctrl, use PageUp or PageDown to find the desired worksheet 4. Let go of the Ctrl and PageUp/Down keys 5. Use the arrow keys to locate the desired cell reference 6. Hit Enter
What is the keyboard shortcut to move sheets within a workbook?
What is the keyboard shortcut to open the formatting cells dialog box?
What are the keyboard shortcuts for auto-fitting a range of columns?
1) Ctrl Tab 2) Shift Tab 3) Spacebar
1. Group the wroksheets by hitting Ctrl Shift and use the PageUp/Down keys to select the worksheets. 2. In the active sheet type in “Company Financials” in A1 and apply the desired width to all columns 3. Remember to hit Ctrl Pageup or Pagedown to make sure that future edits only apply to active sheet.
1. Shift Alt Right Arrow 2. Alt a h 3. Alt a j
What is the keyboard shortcut to open the paste special dialog box?
Which of the following keys IS NOT a way to trace precedent cells?
What is the keyboard shortcut to freeze panes within a worksheet?
=IF(AND(B1>0,B2>0),”animal lover”,”lonely person”)
Identify the formula that will output 2016 expenses (55).
Identify the formula that will output 2016 expenses:
Identify the formula that outputs 2016 expenses.
=VLOOKUP(“Expenses”,A1:D4,3,0)
Identify the formula that outputs data from the table based on the user inputs in cells C6 and C7.
=OFFSET(A1,MATCH(C6,A2:A4,0),MATCH(C7,B1:D1))
Identify the formula that sums the property values of properties in Boston.`
=SUMIFS(B2:B5,C2:C5,”Boston”,D2:D5,”>1950″)
Tells you what cell you are in (top left below the ribbon)
Ctrl + PageDown/PageUp
(Option + RightArrow/LeftArrow)
Alphabetically labeled (A, B, C, etc.)
Numerically labeled (1, 2, 3, etc.)
Ctrl Fn F2
Use arrow keys to peruse the Mac ribbon
Hit Fn and then the function key to use the function keys themselves on a Mac
Within each of the main tabs you will find all of Excel’s features, grouped by commands
Navigating to the Toolbars Without the Mouse
Important Features: Pivot Table, Charts, Header & Footer
Important Features: Print Area, Fonts, Gridlines
Important Features: New Comment
Important Features: Zoom, Gridlines
Important Features: Record Macro, Add-Ins
Accessing Settings (Excel Options)
File > Options (Alt F T or Alt T O)
MAC: Excel > Preferences
General:
Change “Sheets in New Workbook” to 3
Uncheck “Show Workbook Gallery when opening Excel”
Calculation/Functions:
Change Calculation Options to “Automatic except for data tables”
Check “Enable iterative calculation”
Edit/Advanced:
Uncheck “After pressing Enter, move selection”
*Optional – Check “Automatically insert a decimal point”
Shortcuts to Allow the User Input to Add More Sheets to a Workbook
Alt i w
Alt h i s
Shift F11
Alt Shift F1
Find or Find and Replace Shortcut
What is the recommended workbook calculation setting for Excel?
Automatic Except for Data Tables
Add a Cell Reference From a Different Worksheet into an Existing Formula
Hit F2 to get into the existing formula; delete any incorrect formulas or operators
Hit F2 again to enable “Enter” mode on the bottom-left corner of the Excel sheet
Holding down Ctrl, use PageUp or PageDown to find the desired worksheet
Let go of the Ctrl and PageUp/Down keys
Use the arrow keys to located the desired cell reference
Hit Enter
Alt H O W
(Home > Format > Column Width)
Alt H O H
(Home > Format > Row Height)
Autofit the Column Width Command
Alt H O I
(Home > Format > Autofit Column Width)
Select Column & Range of Columns
Ctrl Space
Range: Ctrl Space Shift+RightArrow
Shift Space
Range: Shift Space Shift+DownArrow
Ctrl + C to copy, Ctrl + V to paste
Navigating Format Cells Dialog
Combining Data in Two Sheets into Another Sheet
(1) Copy and paste the relevant headers into the third sheet
(2) Type “=” in the relevant column in the third sheet and then use Ctrl PageUp/Down (Option Right/LeftArrow) to get to the first sheet and find the right data
(3) Press “+” and then find the relevant data in the second sheet and press “Enter”
(4) Ctrl+C to copy that formula and then apply it to the rest of the table
(5) Delete and retype any important formulas (ex. Net Profit)
(6) Add in formatting by Ctrl+C the table in sheet 1 or 2, moving to the upper corner of the table in sheet 3 and using Paste Special and then format: Alt+E+S+T or Ctrl+Alt+V+T (Ctrl+Cmnd+V+T)
Let’s the user dictate the specific attributes of a cell or highlighted region that should be pasted
Referencing Cells from Other Workbooks
How to Autofit a Range of Columns
Moving Around the Format Cells Dialog
Move across tabs with Ctrl+Tab
Move counterclockwise across form elements with Shift+Tab
Select a checkbox with Spacebar
Portions of the worksheet that are bounded and separated by vertical and/or horizontal bars
To unfreeze, hit Alt+W+F+F again
(View>Freeze Panes and then View>Unfreeze Panes)
*Note: To create a horizontal freeze, go to the leftmost column!
Allows users to scroll in both areas of the worksheet, while rows and columns in the non-scrolled area remain visible
Directions:
(1) Click the cell below the desired row and to the right of the desired column where you want to split panes and hit Alt+W+S
(2) To un-split, click Alt+W+S again
(3) Press F6 to move from pane to pane in a clockwise direction; press Shift+F6 to move from pane to pane in a counter-clockwise direction
(View>Split)
*Note: To create a horizontal split, go to the leftmost column!
Go to the Beginning of an Active Cell Formula
(Fn+Ctrl+LeftArrow or Cmnd+UpArrow)
Go to the End of an Active Cell Formula
(Fn+Ctrl+RightArrow or Cmnd+DownArrow)
Jumping from One Formula Element to the Next
Highlight Formula Elements & Maintain Contiguous Elements
Highlight the Whole Formula of an Active Cell
How to Revert Back to Original Formula after Editing an Active Cell
How to Exit Cell Edit Mode in an Active Cell
Highlight the cell you want to copy
Shift+RightArrow to the cells that you want to be filled
Highlight the cell you want to copy
Shift+DownArrow to the cells that you want to be filled
(1) Go to any cell in the row below the desired row
(1) Highlight the row below the desired row with Shift+Space
(2) Press Ctrl Shift + to insert the new row
(1) Go to any cell in the desired column
(1) Highlight the desired column by pressing Ctrl+Spacebar
(2) Insert a column by pressing Ctrl Shift +
Highlight the row/column and press Ctrl –
How to change a list of numbers quoted in 1,000s to 1s
Allows you to apply operations to large amounts of data
You can convert large amounts of data to positive to negative or vice versa
*Note: You should ONLY paste on numbers that are hard inputs
Converting Positive Numbers to Negative
Special paste the new numbers using the Subtract operation – Alt+E+S+S
In another cell, enter the number -1
Ctrl+C -1 and special paste on the relevant numbers using the multiply operation (Alt+E+S+M)
Allows users to convert a vertical list of data into a horizontal list of data, and vice versa
(1) Highlight and copy the list of numbers
(2) Move your cursor outside of the list range
(3) Press Alt+E+S+E (Paste Special Transpose)
*Note: Be careful when applying a paste special transpose on formulas — they don’t work on formulas with relative references
Note: Dependent calculations DO NOT change when you cut cells!
Number Format: 2 decimals, 000 separator
Currency Format: 2 decimal places
Percentage Format with No Decimal Places
Date Format with the Day, Month, and Year
Ctrl+F3 > Alt+N to name a new cell
(Fn+)F4 on the name of the cell in a formula
Keep clicking F4 to toggle the type of anchor
Inserting & Deleting Worksheets
Add a new worksheet by pressing Alt+H+I+S
Delete a worksheet by pressing Alt+E+L
(1) Hit Ctrl+Spacebar to select the desired column
(2) Hit Shift+Alt+RightArrow (Option+Shift+RightArrow) to create the group
(3) Hit Alt+A+H to hide the columns
(4) Hit Alt+A+J to unhide the columns
(5) Hit Shift+Alt+LeftArrow (Option+Shift+LeftArrow) to remove the group
(1) Hit Shift+Spacebar to select the desired row
(2) Hit Shift+Alt+RightArrow (Option+Shift+RightArrow) to create the group
(3) Hit Alt+A+H to hide the columns
(4) Hit Alt+A+J to unhide the columns
(5) Hit Shift+Alt+LeftArrow (Option+Shift+LeftArrow) to remove the group
You can hide data by hitting Alt+H+O+U+R for rows and Atl+H+O+U+C for columns
Helpful if you would like to format data in the same manner or enter the same data across multiple sheets
By grouping multiple worksheets, any data and formatting that you would perform in one of the grouped worksheets would automatically be reflected in all of them
Hold down Ctrl+Shift and press Page+Up/Down to reach the worksheets you would like to group (Shift+Click on the desired worksheets)
All of the grouped worksheets are highlighted and the file name on top of the Excel screen should show [Group] are it
To ungroup, press Ctrl+PageDown
Good: (Fn+)F2 — go to a desired cell and hit the F2 key
Excel will highlight (in different colors) all the cell components of an existing formula present in that cell
Better: Ctrl+[ and Ctrl+] — hitting Ctrl [ on a cell will highlight the precedent cell(s)
Keep hitting Ctrl [ and it will take you to the next precedent
Hitting Ctrl ] jon a cell will do the same thing for dependent cells
Best: Excel Auditing Functions with Alt+M+P for precedents and Alt+M+D (Functions > Trace Precedents or Functions > Trace Dependents)
Remove arrows with Alt+M+A+A
Allows you to navigate to connected worksheets — hit (Fn+)F5+Enter to go back to original cell
Highlight the region you want to center across
Go to Formatting with Ctrl+1 (Cmnd+1)
Click Center Across Selection in Horizontal Alignment
Useful to quickly format constants vs. formulas
(1) Highlight the relevant region
(4) Hit Constants and Uncheck Text, Logicals, and Errors
(5) Hit Enter to select all constants
(6) Use Ctrl+1 (Cmnd+1) to add special formatting
Constants are usually distinguished from formulas, such as by blue text vs. black text
(1) Highlight the relevant region
(2) Use Go To Special and select blanks
(3) Hit Enter
Identifying Where Comments Are
(1) Highlight the relevant region
(2) Use Go To Special and select comments
(3) Hit Enter
Allows you to create your own conditional formats or use a preset from Excel
Alt+O+D or Home > Conditional Formatting > New Rule / Alt+H+L > New Rule for customs
Alt+H+L or Home > Conditional Formatting > Highlight Cells Rules for presets
Identify which numbers in a column are above some number
=and([first statement],[second statement)
Name: =”Income Statement for “&[Cell]
Date: =”Share price as of “&TEXT([Cell], “mm/dd/yy”)
(4) Type in your custom format as [positive numbers];[negative numbers];[zero];[text]
1 = True/0 = False: “True”;”Invalid”;”False”
Custom Format: Aligning Decimal Points
Add “_)” after and “_(” before the positive numbers and zero formatting
Ex. _(0.0_);(0.0)
Custom Format: Adding a Comma Separator
Add “#,##” before the zeros
Ex. _(#,##0.0_);(#,##0.0)
Custom Format: Aligning Non-Multiple Numbers with a Multiple
#,##0.00_);(#,##0.00);”Balance”
Custom Format: Negative number in parenthesis, aligned with positive number format
Custom Format: L + [] Basis Points
Custom Format: Changing the Color of Negative Numbers to Red
Add [red] at the beginning of the negative numbers format
Ex. _(#,##0.0_);[Red](#,##0.0)
#REF! Referencing a previously deleted cell
#NAME? Text not valid (ex. incorrect function name)
#VALUE! Incorrect arguments (ex. Using text as a number)
####### Column not wide enough
Ctrl+F
Write what you want to find
Click Replace
Write what you want to replace
Use Options to restrict where you’re searching, match the case, etc.
Note: You can replace references in formulas as well by finding “[cell name]” and replacing with “[new cell name]”; Ex. Find “j7” and replace with “b2”
(1) Go to Page Layout > Page Setup
(2) Change to Fit to 1 pages wide by [however many you want] pages tall or change from landscape to portrait
(3) Change the margins and center on page horizontally and vertically in the Margins tab
(4) Add custom headers and footers in the Header/Footer tab (page numbers, date, timestamp, file name, etc.)
(5) Limit the print area in the Sheet tab, or add rows to repeat at the top of each page
What are all the ways to trace precedent cells?
Create a Dropdown Menu Command
Change the Cell to a Number Format
Pre-built combinations of operations in Excel that facilitate spreadsheet analysis
=NOW()
Spits out the current time and date
=Average([number 1],…,[number x])
Gives an average of the range
Greater Than or Equal to Functions
Generally follows the structure:
=IF([First Criteria for Yes], =IF([Second Criteria for Yes],”Yes”,”No”),”No”)
=IFS(Criteria 1, Value if Criteria 1 is True, Criteria 2, Value if Criteria is True, …)
Error-Trapping Function IFERROR
Creating Dynamic Headers by Combining Cell References with Text (“&”)
start_date represents a starting date reference
months represents x number of months before or after the start_date
Note: To output a date x months before a start_date, x should be negative
Dates in Excel as Serial Numbers
Using IF statements to see whether a cell has anything in it
DATE, DAY, MONTH, and YEAR Functions
=OR(logical1,logical2,…) evaluates to true if at least one argument is true
=HLOOKUP(lookup value, table range, row number) searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array
Use this when your comparison values are located in a row across the top of a table of data and you want to look down a specified number of rows
=VLOOKUP(lookup value, table range, column number) searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table or array
Use this when your comparison values are located in a column to the left of a table of data, and you want to look across (and to the right) a specified number of columns
If looking up text, add a 0 as the [range lookup] value for the fourth argument
Common Errors with HLOOKUP, VLOOKUP, CHOOSE, OFFSET, & INDEX
Syntax: =MATCH(lookup_value,lookup_array,match_type)
Match type is an exact match (0), greater than (-1), less than (1) — we only really use 0
Combining MATCH Function with Lookup & Reference Functions
=INDIRECT(reference text) returns the reference specified by a text string
=INDIRECT(“B4”) will output the value of what is in cell B4
The most common way to get value out of this function is to combine with concatenate (&)
Ex. When creating a model for a flexible user defined start and end date for calculating a cumulative EBITDA result, you can use INDIRECT and &
Data Validation & Creating Drop-Down Menus
A utility in Excel whose most frequently used feature is its ability to create simple and quick drop-down menus
(1) To create a dropdown menu, with the cell where you want your drop-down menu active, open the data validation form Alt+D+L or Alt+A+V+V (Data>Data Validation)
(2) Within the Settings tab, select list from the dropdown menu
(3) Within the ‘Source:’ field, identify a contiguous cell range containing the data you want to include in your dropdown, and hit OK and you should see your dropdown menu appear (note: it only appears when you are on the active cell)
To combine INDIRECT with MATCH, you can use INDIRECT’s second argument: TRUE is in the form A1, FALSE is in the form R1C1, or row 1 column 1
Ex. =INDIRECT(“R1C1”,FALSE)
Now, you can use INDIRECT, MATCH, and & to create a dynamic function
Note: With INDIRECT, you have to start the MATCH array from the very edge of the worksheet (first column and first row) for the correct row and column number; or, you can add the number of rows above or columns to the left of the beginning of your array to the MATCH function
Using COLUMN and ROW Functions as Counters in Complex Formulas
Allow us to examine a piece of output data – such as a company’s EPS – and how it is impact by changes in input variables such as revenues and gross margin assumptions
Output the results in a presentation-friendly matrix
Often used for sensitivity analysis (i.e. EPS’s sensitivity to changes in gross profit margin) and is used widely by analysts to illustrate a range of possible output values
Building a Vertical Data Table
Layout assumptions on the LEFT
(1) Identify the output variable
The variable you are trying to sensitize is the output variable
Must be referenced from your analysis into the top right corner of the data table
(2) Hard-code the input variable sensitivities
The variables whose impact on the output variables you want to analyze are the input variables
Input variable assumptions should not be referenced from the analysis, but rather be hard-coded and arranged in the column to the left of the output variable
(3) Run the data table
Hit Alt+D+T (Data>Table…) to access the Data Table dialog
Row Input Cell: Not needed for vertical tables
Column Input Cell: Reference the input variable from the model
Highlight the entire range (including the output variable) and hit OK when done – the data table should populate
You may need to hit F9 if Excel is set to “manual” or “automatic calculations except for data tables”
Important: Data tables must always be in the same worksheet as the input variables
Alt+Up/DownArrow (Option+Up/DownArrow)
Building a Horizontal Data Table
From a substance standpoint, it’s the same as vertical
(1) Referenced output variable from your analysis into the bottom left corner of the data table
(2) Input the input assumptions in the row above and one cell to the right of the output reference
(3) Highlight the entire range (including the output variable) and hit Alt+D+T; the Data Table dialog will appear
Row Input Cell: Reference the input variable from the model
Column Input Cell: Not needed
Hit OK when done – the data table should population; if not, hit F9
Building a Two-Sided Data Table
Same as vertical data table, but allows for 2 inputs instead of one
Output variable must be referenced from the model into the top left corner of the data table
When Data Tables Fail: Self Referencing IF Statement
When a table if on a separate worksheet, it doesn’t work
To solve this, you can use a self referencing IF statement
Using IF and AND, you can create a self referencing IF table where you manipulate the original data to populate the table with the appropriate values
Ex. See picture and sheet “Self Referencing IFs” in Practice Workbook
Note: In order for this to work, iterations must be turned on and you cannot go into the cells once you populate the table
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],…)
Allows you to choose the lookup value (what you want to look for), the array the lookup is located in, and the corresponding array the return value should be located in
Also allows you to return an IFERROR message if not found
Note: Does not break if columns are added by decoupling the lookup array and the return array, like OFFSET MATCH function but simpler
Using XLOOKUP to Generate Multiple Values
Scenario Analysis Using XLOOKUP
Where XLOOKUP Loses to INDEX MATCH
SUMPRODUCT with Embedded Criteria
A lesser known feature of SUMPRODUCT is the ability to embed criteria directly into the arrays
For example, we can directly calculate proceeds on options that have an exercise price less than the share price so you no longer need to calculate option proceeds for each tranche:
We have 2 criteria — the options # and the exercise price per tranche, which is multiplied against a TRUE or FALSE criteria for each exercise tranche
Tranche 1 evaluates to TRUE, so Excel multiplies the TRUE by the Tranche 1 exercise price, and then by the # of options
Tranche 2 & 3 evaluate to FALSE, and become 0 when multiplied by the exercise prices
Note: See MATH sheet of Excel Practice workbook for more examples
When their exercise price is less than the strike price (they’re “in the money”)
Identical to SUMIF and SUMIFS but instead of summing, this function averages the data in the range
=ABS(cell or number) gives you the absolute value
=COMBIN(number, number chosen)
Ex. =COMBIN(4, 2) gives you the number of two person combinations out of a number of 4 people
=MIN(number 1, number 2, …) returns the smallest number in a specified set of values
COUNT, COUNTA, and COUNTIF Functions
COUNT =COUNT(value1, value2, …) counts the number of cells that contain numbers within the list of arguments; cells with text are disregarded
COUNTA Same as COUNT except cells with numbers and text are counted
COUNTIF =COUNTIF(range, criteria) counts the number of items in the range that satisfy a specific criteria – similar to the SUMIF function
Returns the present value of a series of future payments
Syntax: =PV(rate, nper, pmt, fv, type), where…
Note: Rate represents the rate per period
If the future value is 0, then omit the fv argument
Returns the future value of an investment based on constant payment and interest rate
Syntax: =FV(rate, nper, pmt, pv, type), where…
An improvement on NPV for when the timing of cash flows is uneven
=XNPV(rate, values, dates) returns the net present value for a set of cash flows that do not necessarily occur at equal time intervals
Unlike NPV, the first cash flow is not discounted, while dates of subsequent cash flows can be included in the formula in any order
Hacking NPV to Calculate Beginning of Period
=IRR(values, guess) returns the IRR for a series of values
IRR is the rate corresponding to an NPV of 0
While values do not have to be identical each period, the periods are assumed to be equally far apart
‘Guess is an optional argument; Excel needs a starting point to iterate to the right IRR. If you choose not to input a “guess”, Excel will automatically use 10% as the guess
Identifies the length of the string of text or numbers
=LEN(text)
=LEFT(text, number of characters)
Inputs the first however many characters of a string
=RIGHT(text, number of characters)
Inputs the last however many characters of a string
=PROPER(text)
Converts strings into the proper/upper case
=TRUNC(number, number of digits) takes a number and carries it to the specified number of digits
=FIND(find text, within text, [start number])
The same as SEARCH but is case sensitive!
Note: To replace with text, use quotation marks!
Tries to guess at to what kind of data you’re trying to get at
Shortcut: Ctrl+E or Alt+A+F+F (Data > Flash Fill)
Data > Text to Columns
Usually use delineated
Note: If there is a column that has spaces within it and the columns are delineated by spaces, you can fix this by concatenating the columns with the corresponding data within them, separating each column by a space (” “)
Then, copy and special paste the values into the correct column
If there are several columns included in this, you can use flash fill to separate the columns
Highlight the relevant data range and then click remove duplicates
Select all columns to ensure true duplicates
Highlight an entire table or have the cursor in one of the cells in the table
Hit Alt+D+S (Data Tab > Sort) to bring up the ‘Sort’ meenu
You can choose to sort various columns by value, cell color, or font color, and in ascending or descending order
Note: You can sort multiple levels, so if you wanted to sort by industry first and then by revenue within each industry, you just need to click ‘add level’
Combining Sort with Subtotal – Alt+A+B (Data > Subtotal) – can add further clarity to data sets
Alt+A+T (Data > Filter)
A tool that enables you to filter by a wide range of criteria
You should now see drop-down arrows in the column headings of your table
Notice that the filtered rows have been hidden but not deleted; fortunately, if you copy and paste the filtered range to another area of the worksheet or a different worksheet altogether, it does not copy over the hidden rows
Once a filter is applied you can tell which column was filtered via the funnel icon
To remove individual filters, click on the filter icon and ‘Clear filter’
To remove all filters, hit Alt+A+T again
An efficient, visual tool for analyzing, exploring, and presenting large amounts of data
It is particularly useful when your data needs to be sliced and diced in a variety of ways
Answers Questions Like:
(1) Which property type is most expensive/most common in a specific region?
(2) Which REIT had the greatest sales within a specific region or for a specific property type?
Has the ability to pivot, or quickly swap out different row and column parameters to observe data in different ways
Use your arrows to move to any cell inside the table of data and hit: Alt+N+V or Alt+N+V+T (Insert > Pivot Table)
Excel will automatically highlight the entire table of data. Select to open the Pivot Table in a new worksheet.
A new worksheet will open showing the Pivot Table Field List. This shows a list of the data’s categories (REIT, property type, etc.) and the 4 areas of the Pivot Table: Filters, Columns, Rows, Values (the outputs).
Using your mouse, you can drag any of the data categories into one of the 4 areas of the table, the Pivot Table itself will automatically reorganize to accommodate the newly added category
Clicking the drop-down arrow allows you to sort and filter the column headers in various ways (ex. if you wanted to show data only for BRE Properties, you could unselect the other REITs)
Note: Values are automatically summed. If you want to get something else (count, average, max, etc.) you can find this under the Value Field Settings by clicking on the corresponding value. You can also change what the values are shown as (% of grand total, etc.) under the same menu under Show Values As.
Use a LAMBDA function to create custom, reusable functions and call them by a friendly name
=LAMBDA(parameter_or_calculation,parameter_or_calculation, …)
Note: You must identify the parameters you need upfront before defining them
=LAMBDA(base,growthrate,base*(1+growthrate))(Cell for Base,Cell for Growth Rate)
Hit Alt+M+N (Formula > Define Name) to invoke the name manager
Create a new name GRATE and paste the LAMDA function in ‘Refers to’
LAMDBA for CAGR: =LAMBDA(vfinal,vbegin,t,(vfinal/vbegin)^(1/t)-1)(vfinal,vbegin,t)
New Function named CAGR: =LAMBDA(vfinal,vbegin,t,(vfinal/vbegin)^(1/t)-1)
vfinal = final value, vbegin = beginning value, t = time period
For Days Sales Outstanding Calculations
LAMBDA for DSO: =LAMBDA(AR,Revenue,DaysinPeriod,DaysinPeriod/(Revenue/AR))(AR,Revenue,DaysinPeriod)
New Function named DSO: =LAMBDA(AR,Revenue,DaysinPeriod,DaysinPeriod/(Revenue/AR))
For implied terminal growth rate
New Function named IMPLIEDG: =LAMBDA(rate,cashflow,value,(rate-cashflow/value)/(1+cashflow/value))
=IMPLIEDG(rate,cashflow,value)
quarters = Number of quarters from the quarter end date
For Treasury Stock Method: For a company that has issued options to its employees, how diluted are those options? Should they be included in the share count?
LAMBDA for TSM: =LAMBDA(numoptions,strikeprice,currentprice,SUMIF(strikeprice,”<“¤tprice,numoptions)-SUMPRODUCT(numoptions,strikeprice*(strikeprice<currentprice))/currentprice)(numoptions,strikeprice,currentprice)
New Function named TSM: =LAMBDA(numoptions,strikeprice,currentprice,SUMIF(strikeprice,”<“¤tprice,numoptions)-SUMPRODUCT(numoptions,strikeprice*(strikeprice<currentprice))/currentprice)
=TSM(numoptions,strikewprice,currentprice)
numoptions = array of options, strikeprice = array of strike prices for options, currentprice = current share price
For Sheet Name: Grabs the name of the current worksheet
reference = any cell reference on the corresponding worksheet
How to Use Lambdas Across Multiple Workbooks
These instructions are called macros
Recording a Macro (Blue Color, 1 Decimal Place, Comma Delineated, Yellow Background)
(1) Select ‘Use Relative References’
(2) Place the cursor in any cell
(3) Alt+L+R brings up ‘Record Macro’ menu (File > Record Menu)
(4) Input a name and shortcut key (Ctrl+Shift+Z); as soon as you hit ‘OK’, you are starting to record this macro
(5) Format the cell using Ctrl+1
(6) When finished, hit Alt_+L+R or stop recording to stop recording
(7) Try going to any cell and hit Ctrl+Shift+Z; those cell’s contents should be formatted per your preferences
After you run your macro, you lose the ability to undo all the prior work, so be careful!
Custom shortcuts override pre-existing shortcuts
If you want your macro to run every time you use Excel, select Personal Macro Workbook instead of ‘This Workbook’
After the macro is created, you can always delete or rename a macro or change shortcut keys assigned to it by clicking ‘Macros’ in the Developer tab