Managerial Computing


MGRCMP is all about computations a decision maker needs to do on a daily basis to make efficient decisions. This course is taught via MS Excel.

What-If Analysis

What-if Analysis tools are powerful to automate computations using existing formula references and prevent redundant copy-pasta of entire computation workflow.

Three what-if analysis tools are:


VLOOKUP does a vertical search and gets you a value for a given column offset. HLOOKUP does a horizontal search and gets you a value for a given row offset. LOOKUP does relative search on a target array wrt given lookup value and input array.

Index and Match

Index gives VALUE for given X and Y offset of a reference array. Match gives OFFSET (index) of given lookup value in a given array. Match could do less, greater or exact matches.

Selecting Spreadsheet Areas

Gives all the rows for 3rd column
Gives area of given height and width, from given reference and offsets.

Filtering Data

Filters could be used to selecting a subset of the given data. Advanced filtering options help filter data. A common methodology is to write a boolean conditionals for data in first row of the given database (table) and use Advanced filters with them. This will iterate conditions over all rows of the the database.

Another method would be using Pivot Tables.


All D- Functions to use functions on a DB and for given criteria.


Array function, gives frequencies for given bins inclusive upper bounds


Dates are stored as number of days since 1/1/1900
Time is stored as percentage of day passed

Format as date
Gives day number
Returns full month name for a given month number
29-02-2003 is taken as a value - Bug in excel

Pivot Table


Case sensitive function to substitute
EXACT is case sensitive
TRIMs spaces
ASCII Value Corresponding to the charachter
Extract characters in between a string


Cell Referencing

Returns cell/array for given reference in A2/names range
Returns array

Last Updated: 19-20-26-27 Aug 2021

Financial Formulae

1. NPV

Worksheets and Workbooks

1. CtrlHome: Selects cell A1
2. CtrlEnd: Selects the cell at the intersection of the last-used column and last-used row on the worksheet
3. Page Up: Scrolls up one screen
4. Page Down: Scrolls down one screen
5. AltPage Up: Scrolls left one screen
6. AltPage Down: Scrolls right one screen
7. CtrlPage Up: Selects the previous worksheet
8. CtrlPage Down: Selects the next worksheet
9. CtrlTab: Moves to the next open Excel workbook


1. [WINDOWS only] Alt : Pressing the Alt key once will allow you to quickly select a ribbon using its letter key

Basic Techniques:


Financial Functions [TBD]


1.ctrl+H : Find and Replace
2. ctrl+shift+1 : Number format (2 decimals)
3. ctrl+shift+2 : Time format
4. ctrl+shift+3 : Date format


Using ' before value will store value as a string in the cell


Last Updated: 26-24,20-18 June 2021