introduction to supply chain management for excel

introduction to supply chain management for excel

1. Excel Basics Formula basics Cell referencing basics Common excel errors Fill series Common excel shortcuts Logical operators Basic statistical functions Vlookup IF Statement

2. Data set to be used Customer Churn data (Churn-Modelling.xlsx)

3. Navigating Spreadsheet Rows Columns Formula bar Multiple sheets

4. How to Reference a Cell

5. What is a Range? Groups of cells selected or addressed together Address of the cell or the range is needed so that it can be referenced in a formula You can simply select the cell or cells(i.e. a range) to get the address

6. Using Cell References in Formulas Relative The row and column references can change when you copy the formula to another cell. By default, Excel creates relative cell references in formulas (e.g. A5). Fixed/Absolute The row and column references don’t change when you copy the formula to another cell because the reference is too an actual cell address. An absolute reference uses two dollar ($) signs in its address: one for the column letter and one for the row letter (e.g. $A$5) Mixed Either the row or the column reference is relative, and the other is absolute. Only one of the address parts is absolute (e.g. $A5 or A$5).

7. Common Excel Errors   Error Type  What it means How to fix it ######## Column isn't wide enough to display values Drag or double-click column border to increase width   #NAME? Excel does not recognize text in a formula Make sure that function names are correct, references are valid and spelled properly, and quotation marks and colons are in place #VALUE! Formula has the wrong type of argument Check that your formula isn't trying to perform an arithmetic operation on text strings or cells formatted as text   #DIVIO! Formula is dividing by zero or an empty cell Check the value of your divisor; #REF! Formula refers to a cell that it not valid Make sure that you didn't move, delete, or replace cells that are referenced in your formula

8. Fill Series Fill is not a command and can be used with dragging the mouse Fill a column with a series of numbers Select the first cell in the range that you want to fill. Type the starting value for the series. Type a value in the next cell to establish a pattern. Select the cells that contain the starting values. Drag the fill handle across the range that you want to fill. Can also use Fill option from the Editing group under the Home Tab

9. Vlookup The VLOOKUP function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. Syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value The value to be looked up in the first column of the lookup table table_array  The range that contains the lookup table col_index_num The column number within the lookup table from which the matching value is returned range_lookup Optional and True by default if nothing is specified which means no exact match is needed ( the first column of the lookup table must be in ascending order) False will look for an exact match

10. Vlookup and Naming the Table Naming the table makes it easier to work with in the Vlookup Create the Vlookup Table Name the Table Select the table range and Right Click Define Name Give the Table a meaningful name See the different tables defined using Name Manager under Formulas Tab

11. Approximate Match (Last parameter will be TRUE) If an exact match is not found, the next largest value that is less than lookup_ value is returned

12. Exact Match (Last parameter will be FALSE) VLOOKUP will search for an exact match. If VLOOKUP can’t find an exact match, the function returns #N/A.

13. IF Statement The IF function performs a logical test and return one value for a TRUE result, and another for a FALSE result Syntax =IF ( logical_test , [ value_if_true ], [ value_if_false ]) Sometimes needed when you want to decode values in a certain way =IF(K2=1,"Card","No Card") K2 is the cell number. Yours may be different than mine This works fine for two conditions but what is you have more than two conditions =IF ( logical_test , [ value_if_true ], [ value_if_false ])