Introduction to spreadsheet that enhances your data analysis

Introduction to spreadsheet modelling

1. Introduction to Spreadsheet Modeling SESSION – V | Statistical formulas MODULE - I Dr. P. C.  Bahuguna  | Associate Professor, SOB Scan to Know More About Me 

2. C H E C K Presentation for UPES Faculty & Students  XLOOKUP VLOOKUP INDEX MATCH

3. WIFM? TO BE ADDED SESSION – V | Statistical formulas Presentation for UPES Faculty & Students 

4. Presentation for UPES Faculty & Students  MEDIAN MODE STDEV.P SESSION  To be Covered STATISTICAL FORMULAS NPV 5 SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

5. Presentation for UPES Faculty & Students  BROAD PLAN OF THE COURSE STARTING WITH MS EXCEL FORMULAS / FUNCTIONS CONDITIONAL LOGIC PROCESSING DATA EXCEL CHARTS LINEAR PROGRAMING THROUGH SOLVER DECISION MAKING IN UNCERTAINTY

6. BASIC FUNCTIONS: MEDIAN . The MEDIAN function is a premade function in Excel, which returns the middle value in the data. It is typed =MEDIAN How to use the =MEDIAN function, step by step: Select a cell (H2) Type =MEDIAN Double click the MEDIAN command Select a range (A2:G2) Hit enter SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

7. BASIC FUNCTIONS: MODE . The MODE function is a premade function in Excel, which is used to find the number seen most times. This function always returns a single number. It is typed =MODE.SNGL It returns the most occurring number in a range or array. SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

8. BASIC FUNCTIONS: STDEV.P . The STDEV.P function is a premade function in Excel, which calculates the Standard Deviation (Std) for the entire population. It is typed =STDEV.P SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

9. BASIC FUNCTIONS: STDEV.S . The STDEV.S function is a premade function in Excel, which calculates the Standard Deviation (Std) for a sample. It is typed =STDEV.S SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

10. BASIC FUNCTIONS: PMT . SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5 PMT, one of the  financial functions , calculates the payment for a loan based on constant payments and a constant interest rate. =PMT(rate, nper ,  pv , [ fv ], [type]) The PMT function uses the following arguments: Rate (required argument) – The interest rate of the loan. Nper  (required argument) – Total number of payments for the loan taken. Pv  (required argument) –  The present value or total amount that a series of future payments is worth now. It is also termed as the principal of a loan. Fv  (optional argument) – This is the future value or a cash balance we want to attain after the last payment is made. If Fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. Type (optional argument) – The type of day count basis to use. The possible values of the basis are:

11. BASIC FUNCTIONS: PMT . Type (optional argument) – The type of day count basis to use. The possible values of the basis are: SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

12. BASIC FUNCTIONS: PMT . Let’s assume that we need to invest in such a manner that, after two years, we’ll receive $75,000. The rate of interest is 3.5% per year and the payment will be made at the start of each month. The details are: SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

13. BASIC FUNCTIONS: NPV . The NPV function is used to calculate the Net Present Value (NPV) It is typed =NPV =NPV(rate, value1, value2, ...) rate: The Discount rate. value: The cells where the cash flows are. SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

14. BASIC FUNCTIONS: X NPV . The XNPV formula in Excel requires the user to select a discount rate, a series of  cash flows , and a series of corresponding dates for each cash flow. =XNPV(Rate, Cash Flows, Dates of Cash Flow) The XNPV function uses the following three components: Rate – The discount rate to be used over the length of the period Values (Cash Flows) – This is an array of numeric values that represent the payments and income where: Negative values are treated as outgoing payments (negative cash flow). Positive values are treated as income (positive cash flow). Dates (of Cash Flows) – An array of dates corresponding to an array of payments. The date array should be of the same length as the values array. SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

15. BASIC FUNCTIONS: X NPV . Below is a screenshot of an Example of the XNPV function being used in Excel to calculate the  Net Present Value  of a series of cash flows based on specific dates. Key assumptions in the XNPV example: The discount rate is 10% The start date is June 30, 2018 (date we are discounting the cash flows back to) Cash flows are received on the exact date they correspond to The time between the start date and the first cash flow is only 6 months SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

16. BASIC FUNCTIONS: XIRR . The XIRR Function in Excel calculates the internal rate of return (IRR) for an irregular series of cash flows, i.e. received on non-periodic dates. The XIRR Excel function requires two inputs, which are the following: The Range of Cash Inflows / (Outflows) The Range of Dates Corresponding to Each Specific Cash Flow =XIRR(values, dates, [guess]) SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

17. BASIC FUNCTIONS: XIRR . Suppose a project started on January 1, 2018. The project gives us cash flows in the middle of the first year, after 6 months, then at the end of 1.5 years, 2 years, 3.5 years, and annually thereafter. The data given is shown below: SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5

18. Presentation for UPES Faculty & Students  MEDIAN MODE STDEV.P NPV SESSION – 5 Statistical formulas Scan to Access the Presentation  SESSION 1 SESSION 2 SESSION 3 SESSION 4 SESSION 6 SESSION 5