Notes for all videos PDF

Preview

Summary

Notes from all videos for Dr Armstrong...

Description

Module 1 1.1 VIDEO -always have a title on the sheet -“=SUM(2,235)” = 237 1.2 VIDEO -adding errors? switch to accounting format to check accuracy of numbers 2.1 VIDEO -“=countA” counts the number of cells chosen regardless of what they have in them -“count” counts number of cells with numeric values in them 3.1 VIDEO -hit control & + & r and drag across cells to copy and paste formula -use d for down, L for left… -put $ in front of formula letters to maintain that cell e.g. “=$B1*C1 -can also be put in front of number to allow an increment of the letter (but not the number) -$B$11= absolute referencing, nothing will change when dragged -B$11or $B11 = mixed referencing keeps row/column from changing when dragged -B1= relative referencing -naming cells: when you keep using a certain cell, change on top left (no spaces or special characters) VIDEO 3.1 -right click on sheet name to copy sheet onto another -if you select all sheets, what you do in one happens in all

1 ! of 24 !

Module 2 VIDEO 1.1 -“=round(.5,0)” rounds up to integer (use 1 for 1dd, use -2 for rounding to nearest 100 e.g. 1050=1100) -rounddown and roundup always do what they say -don’t copy and paste values! use paste special and choose values -use format painter to copy and paste format (number of dp for example) VIDEO 1.2 -seeing the whole page: go to view and use freeze panes (click specific cell to see everything up and left of it) -right click on row/column name and split, double click to remove it -mean =average -median =median -mode =mode.sngl returns first most recurring value it runs into -standard deviation =stdev.s -reference from different sheets =name!X69 -check referencing with trace precedents in formula tab VIDEO 1.3 -all dates are based on January 1, 1900 e.g. days since this date

-

=year gives you the year from a date, =month, =day, =hour, minute, =second… 5.5 = jan 5 1900 at 12 noon =yearfrac gives time between 2 different years =networkdays, may include holidays if set =weekday gives day of the week use ctrl and semi colon to input date that never changes =today for it to update when you open it =now for date and time (will change every time you do something on the spreadsheet =date to construct a date from different cells or just input it =text and then input what you want

- use custom format cells to edit - copy and paste date format to be used as a guideline - use TEXT(cell, “format”) to change date number into something normal - =concatenate to put different texts together 2 ! of 24 !

VIDEO 2.1

-

=rank.eq(number you’re ranking, list of numbers to refer) =rank.avg same as eq but calculates average position of numbers of equal rank =large(cell range, which largest value you want to display) k=1 for largest =small(same as above but lowest values)

VIDEO 2.2

- =countif counts the number of values that meet a certain criteria - =countif(range, criteria e.g. “>1.2” or “A”) - can have multiple criteria - not case sensitive! - boolean are not text, so no quotes needed - use * as comodín - use ? as comodín for any character - =sumif(range, criteria, what you’re adding). Adds up values if they meet a certain criteria - range: range of cells we compare criteria against - criteria: greater than? equal to? - optional parameter sum range: column of data we want to sum up VIDEO 2.3 -name a bunch of cells by selecting and changing name on top left

- =averageif returns average of cells that meet a certain criteria VIDEO 3.1 -use format cells and custom to display anything we want -“Labour rate= “ $#.00 -> cell only has 35, but shows the whole sentence -data, what if analysis, goal seek = set a certain target and values change accordingly (works backwards) -go to settings, formula, maximum iterations if it doesn’t find it to give it more tries VIDEO 3.2

- =randbetween(low value, high value) generates integer between and including these parameters - =rand returns 00,Penalty90Day,if(E9>0,Penalty60Day,if(D9>0,Penalty30Day,0))) -0 returned? none of the choices before it were true VIDEO 2.4 -difference between “true” and true -text values will be read as true, so careful!

6 ! of 24 !

VIDEO 3.1 -3 false/reject: =not(or(B7:D7)) returns true -> return “Reject” =if(not(or(B7:D/)), “Reject”,”Scenario 1 is false”) -accept scenario: =and(B14,or(C14:D14)) returns true if B14 is true and either of C or D are true -make it pretty with an if statement =if(and(B14,or(C14:D14)),”Accept”, “Failed acceptance test”)) VIDEO 3.2 -putting the two statements from video 3.1 together: =if(not(or(B7:D/)), “Reject”,”if(and(B7,or(C7:D7)),”Accept”, “Further evaluation”)) -new customer? this checks to see if value is true/false, if not it returns false =islogical(K3) =if(islogical(K3),if(not(or(B7:D7)), “Reject”,if(and(B7,or(C7:D7)),”Accept”,“Further evaluation”)) VIDEO 3.3 formula evaluator examples!

7 ! of 24 !

Module 5 VIDEO 1.1 What’s better than a long nested if function? a vertical look up function =vlookup(value we want to look up in table, table (without headers), column number we want value from, true for approx false for exact) -make sure table is in ascending values -look up value needs to be left of column VIDEO 1.2 This is about the horizontal look up. -also ascending value left to right =hlookup(value we wanna look up, table, row we want value from, true/false) -put a -ve sign in front of the function to make it negative (to find out price discounted by multiplying with whatever total value is) VIDEO 2.1 -remember to rename entire tables for easier referencing -if you can’t have the looked up value to the left of the table, use LOOKUP =lookup(value looked up, range of cells you wanna compare, this needs to be the same size as the range of cells and it’s the result you want) -there is no true/false, it is TRUE automatically -one dimensional VIDEO 2.2 =index(array, row, [column]) -array: table of values (only values no headers) -returns a value or reference of the cell at the intersection of a particular row and column, in a given range -two dimensional =index(references, row,[column],[area]) -references: all the tables you’re using, separated by commas and in parenthesis -use to find a row/column intersection in a list of tables -area: which reference table we want to refer to -3 dimensional 8 ! of 24 !

VIDEO 3.1 “is” function: 13 types — used to get info about a value before performing an action on it isblank — returns true if cell is empty =if(isblank(a13),””,(vlookup(a13,Products,2,false)) iserr — returns true if there is an error (except N/A) iserror — returns true if there is an error, including N/A iseven or isodd — checks for odd or even value isformula — contains formula islogical — contains true or false boolean isna — checks for N/A isnontext isnumber isref — checks if its a reference istext — checks if its text VIDEO 3.2 unit price formula: 1. determine lookup value table 1 or 2? use if function to determine value 2. lookup table/array =choose(index number 2, list of choices “A”, “B”,”C”) returns B Chooses a value or action to perform from a list of values, based on an index number. 3. column index number 4. range lookup (true/false) VIDEO 3.3 Unit price formula completed: =vlookup(step 1, step 2, step 3, step 4) =vlookup(if(k5=1,A9,C9),choose(K5,Price1,Price2),2,Step4) Step 4: K51 OR if(K5=1,false,true) =if(isblank(a9),””,vlookup(if(vlookup(A9,Products,3,False)=1,A9C9),choose(vlookup(A9,Products, 3False),Price1,Price2)2, vlookup(A9,Products,3,False)1)) -trace precedents (in formulas): visually shows which cells affect a formula 9 ! of 24 !

VIDEO 3.4 match — searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =match(25,A1:A3,0) returns the number 2, as 25 is the second item in the range. =match(value you want position info from, range cells,0 for exact 1 for approx) Index function process: 1. Parameter 1: Reference (tables to be referenced) Shipping1, Shipping2, Shipping3, Shipping4 2. Parameter 2: Row number =vlookup(stateinput,States,3,false) 3. P3: Column number =match(values you want to look up, array of values to look into, 0 for exact) match — another look up function to find where x is located in another table, we use this one with text 4. P4: Area number (which of the referenced tables should be used?) =match(F6,{“regular”,”preferred”,”most preferred”,”UberPreferred”},0) -{ }: this creates an array within the function Final formula: =index((Shipping1,Shipping2,Shipping3,Shipping4),vlookup(F4,States, 3,False),match(f5,Shipping!b5:e5,0),match(f6,{“regular”,”preferred”,”most preferred”,”UberPreferred”},0)) -name manager: found in formulas to see all names applied to sets of cells

10 ! of 24 !

Very difficult example: -we are dividing the number of miles by gallons to figure out how many gallons we need, and then multiplying that by the price so its: blank thingy or (total miles/mileage)*gas price this division gives us how many gallons we use in total =if(isblank(a11),””,I$4/(index((HW,City,Combo),match(vlookup(a11,Models,9,false),Mileage! B$4:b$6,0),match(d11,{4,6,8,”turbo”}),match(i$5,{“HW”,”City”,”Combo”})))*vlookup(i$9,GasPrices, 2,false))

11 ! of 24 !

Module 6 VIDEO 1.1 -present value PV: original amount at the beginning of the transaction -period: how many times per year the loan is compounded -future value FV: payment due at end of loan (sneaky balloon payment) -periodic payment PMT: =pmt(rate in per period basis so divide rate by period, nper number of periods in total so periods times years, PV present value, FV future value, when payment is due 0 for end 1 for beginning of period) -negative or positive value? shows the direction of the cash flow in financial functions relating to borrower, what I get will be positive and what I give will be negative -APR: annual percentage/interest rate -fv: future value of an investment (rate, nper, pv, type) VIDEO 1.2 -delay project and use profits: how much do we need to invest to get goal? =pv(rate, nper, pmt, fv, type) rate — nper, pmt, pv, fv, type, guess (iterative process function, so first tries 20 times, and then returns error if value is not found, use this to start guessing at a different point e.g. 0.1 to avoid error) this (AND ALL APR) are interest per period, so make sure you divide whole function by number of periods to find ANNUAL value duration in years — find number of periods, and then divide this by the number of periods per year to find total duration =nper(rate, pmt, pv) VIDEO 2.1 -interest expense: money the bank makes (extra money on top of loan, interest payment) interest payment: ipmt(rate, period number, total number of periods, PV original loan, future value,0) principal payment: ppmt(same as ipmt) just copy and paste

12 ! of 24 !

VIDEO 2.2 cumipmt: how much interest will be paid between two points in time (rate, nper, pv, start period, end period) start period formula: start - (periods per year - 1) end period formula: year * number of periods TYPE: USE 0 FOR ALL FORMULAS IN THIS COURSE -paste special: transposing flips vert to hor VIDEO 2.3 -depreciation: the loss in value of a good over time for a company: they spread the cost of an asset over its useful life (money is spent upfront, but per year for taxes) loss in equipment value = negative cash flow (so negative value) -salvage: how much equipment is worth at the end of its useful life (selling it) -straight line depreciation formula: =sln(cost, salvage, life), remember to place -ve at the beginning VIDEO 2.4 -formulas - name manager - add new name for assigning a value to a word without putting it in a cell -if tax is negative, use if statement to make tax = 0 -add back depreciation: value of company decreases with depreciation, but it is not real money (as its paid at the beginning) so that ‘lost’ value has to be added back. It affects the value of business, not the cash flow. Simply reference and put a negative in the front to counter the -ve value; values then cancel out in the final cash flow total. -cumprinc: same as cumimpt, used for principal payment per year instead. So copy what’s inside cumimpt and paste in cumprinc. -cash flow = income after taxes + add back depreciation + subtract principal (loan) payments VIDEO 3.1 -net present value NPV: is the investment of 1m more valuable here or in another company? =npv(hurdle rate, cash flow from current company) +ve: current investment better than alternative 0: equal investment -ve: alternative is better 13 ! of 24 !

remember to add million dollar investment (it substracts 1m) -hurdle rate: profit per year that other company would give at what hurdle rate would NPV=0”)/count(A1:C27) VIDEO 3.2 viability stuff

14 ! of 24 !

Module 7 VIDEO 1.1 -before importing, data needs to be in a file type that can be easily imported .csv = comma separated value comma = break between cells =concatenate — joins several text strings into one text string (first piece of text, second, third…) For csv into xlsx stuff: use concatenate for rows you want to put in a single cell put commas between each text in concatenate — A1,”,”,B1,”,”,C1…) put trim before each cell in concatenate to remove spaces before and after text string — trim(A&) use =right to figure out which cells correctly referenced by finding specific endings e.g. 2033 use =find to search a specific text string within a string and return its location =find(text you want to find, where you want to find it) =find(2,2033) returns 1 =left(text used, number of letters to the left you want to show) VIDEO 1.2 continued from video 1.1: use sort to weed out bad data by column — the other cells are irrelevant, all the data we need is at the top already! use text to columns careful with numbers that start with 0 as they will be deleted, so make that whole cell as text save as a new good csv file importing data: open new sheet and import data “from text” to avoid any 0 elimination use comma delimiter remember to set zip codes as text!! subtotal in data: counts stuff within tables

15 ! of 24 !

VIDEO 1.3 data may look like a table, but in reality it’s just an unstructured range of cells where the integrity of the data could be easily compromised by mistakes excel table: single unit of data, create with insert and then table VIDEO 2.1 database: set of interrelated data that is stored in tables table: collection of fields that describes one specific concept (entity) record: a set of fields that describes a product or person (a row) Export data from DBMS (database management system application) into excel data, from access, locate file use other sources and microsoft query to only get the wanted part of the database existing connections = run previously saved queries again dates are numbers, which are days since 1900 2.25 = how much of the day we are past midnight = 6am yearfrac: number of whole days between two dates VIDEO 2.2 -pivot table: interactive and dynamic tool that allows us to easily work and analyse data insert - pivot table VIDEO 3.1 -powerview: creates dashboards that show health of organisation VIDEO 3.2 -report field goes on top VIDEO 3.4 -insert slicer: graph thing about a bit of data -show values as percentage within options at the top -LOWER(): Converts text to all lower"case (small letters) -UPPER(): Converts text all to upper case (capital letters) -PROPER(): Converts text to title form by capitalising the first letter of each word

16 ! of 24 !

Module 8 VIDEO 1.1 -what if analysis: changing certain values to see the outcomes of certain formulas -data tables: asks a what if question involving a range of cells, performs many calculations at once and compares results on a single worksheet, and runs a simulation with a two-variable data table -one variable: one input cell and many result cells, a set of input values must be arranged perpendicular to the set of output formulas, with no value or formula located at the intersection of the set of input values and output formulas -two variable: 2 input cells, only one result cell (determines how 2 values affect a single result), two sets of input values must be set up perpendicular to each other, and the output formula must be in the cell at the intersection of the two sets of input values -worksheet: values and formulas must be on the same worksheet as the table -scenario manager: consolidates multiple what if models in one worksheet, can switch between scenarios to see how sets of assumptions affect results -simulation: using expected values (probabilities) -breakeven analysis: type of what if analysis that concentrates on an activity at or around the point at which a product breaks even (where is the profit 0? so costs = revenue) -sensitivity analysis: type of what if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions (if you change a value of the assumption, how do the results change) VIDEO 1.2 One variable analysis video: -worksheet divided into assumptions and projections tables -contribution margin: sales - variable expenses = how much money there is to offset fixed expenses -marginal pretax income = marginal income before taxes -use control tilde (above tab) to see which cells are used for calculations -remember to use -ve sign for expenses

17 ! of 24 !

Set up table:

Custom formatting to improve readability: -K5 and customise, so custom and then the header you want: “Sales Revenue”;“Sales Revenue”;“Sales Revenue”; This is because it needs to display the same header when its +ve, -ve, or 0 Select all table and in data use what if analysis, then data table, then input your 1 variable Exact breakeven: data, what if, goal seek, value 0. Shows in input table Sensitivity analysis: change value in original estimate and then see how it affects output results VIDEO 1.3 Two variable data tables: -1 variable as a column and the other as a row -top left corner put marginal income before taxes cell, this is then custom format to say COGS/Unit -then select the whole table, data, what if, data table, put info on both variables VIDEO 2.1 3 different analysis: use the scenario manager -assumptions = input = changing cells (allows 32 per scenario), output = result cells -a scenario must be in a single worksheet -for taxes, only calculate them if the income is +ve (use if statement)

18 ! of 24 !

-step variable relationship: the cost to produce an item doesn’t vary directly with each number of units sold but in groups -use vlookup to find number -sumproduct: takes arrays of cells, multiplies the array elements together, and then adds them up

-blue x red and all that added up -changing cells: column A for result cell number and B for the defined name -all defined names have a scope, which specifies the location where that name is valid -global scope: can be used in any sheet -local scope: can only be used in that sheet -> formulas, define name, scope VIDEO 2.2 -see 2 workbooks at the same time: use view and then tile, then use control and drag to new workbook to see both at the same time (makes a copy on new workbook) -each scenario needs a unique name, changing cells are units sold and marketing cost -scenario summary: go into what if, then summary VIDEO 3.1 -2 variable data table can use simulated results to show how probable something is (not real data) -cumulative probability distribution: assigning a probability to every interval of numbers, the probability (ascending) is on the left, and number of units sold on the right (to be able to properly use a vlookup function) -use vlookup and then random number generator to find the demand in units VIDEO 3.2 -200 iterations are reasonably accurate -this 2 variable data table is the simulation, different from the previous 2 variable one because in reality we don’t have a second variable, the vertical axis of the table contains number 1 to 200 for each iteration -upper left cell = marginal income before taxes -data, what if, data tabl...