CAT Cheatsheet Finals PDF

Preview

Summary

Simulating data from freq bins (DISCRETE IF DATA DISCRETE DIST NOT mentioned: SMALL (raw data, RANDBETWEEN(1, number of data points)) Uniform: RANDBETWEEN(min, max) Binomial: CRITBINOM(trials, probability_s, RAND()) Poisson: CRITBINOM(trials, RAND()) TODAY() returns the current date NOW() returns th...

Description

Simulating data from freq bins (DISCRETE • IF DATA DISCRETE & DIST NOT mentioned: X’ = SMALL (raw data, RANDBETWEEN(1, number of data points)) • Uniform: X’ = RANDBETWEEN(min, max) • Binomial: X’ = CRITBINOM(trials, probability_s, RAND()) • Poisson: X’ = CRITBINOM(trials, mean/trials, RAND())

TODAY() returns the current date NOW() returns the current date and time DATE(year, month, day) returns the serial number Date(2006, 10, 24)=39014 YEAR(serial_number) returns the year corresponding to the serial number YEAR(39014) = 2006

Simulating data from freq bins (CONTINUOUS) • IF DATA CONTINUOUS & DIST NOT mentioned: X’ = PERCENTILE (raw data, RAND()) • Uniform: X= min + RAND()* (max – min) • Normal: X = NORMINV(RAND(), mean, standard_dev • Exponential: X = (-Lambda) * LN(RAND())

Creating One Variable Table Data>What if Analysis>Data Table

MONTH(serial_number) returns the month MONTH(39014) = 10 DAY(serial_number) returns the day corresponding to the serial number DAY(39014) = 24 *the same goes for TIME, HOUR, MINUTE, SECOND

Creating Two Variable Table Data>What if Analysis > Data Table

Refer to TImevalue.xls if unsure State a dummy cell for variable value, when the table pops up, input this value into it

Trend (known_y, known_x, new_x, True/False) This returns a new value y.

Take note of the locking of cells for trend e.g =TREND(D$35:D$44,$C$35:$C$44,$C45)

Breakeven Point: FC/MC, FC/(P-V), FC/(1%forVC)*P Profit: Q(P-V)-FE

IF(,result if true, result if false)

SLOPE(known y, known x), aka the gradient

y = mx + b (L1 Alex Process)

SUM(number1, number2, ….)

INTERCEPT(known y, known x), aka the c in the eqtn

SUMIF(range, criteria, sum_range) returns the sum result

AVERAGE(number1, number2, ….)

RAND(), gives you a number from 0 to 1.0

MAX(number1, number2, ….)

RANDBETWEEN (bottom, top), inclusive. Position no

MIN(number1, number2, ….)

FREQUENCY(data array, bin array) 1.select an area for freq results, 2.enterformula, 3.CTRLSHIFTENTER

Use -Goal Seek >Data>What if Analysis > Goal Seek -Solver >Data>Solver

Vlookup (lookup_val ,array , col _num , range_lookup)

pmt = periodic payment (- or +) 0=pmt at the end of the period (default) 1=pmt at the start of the period (default) pv = present value (+ or -) fv = future value (+ or -) rate = interest rate per period (+) nper = number of periods (+) • Important: rate, nper and pmt be expressed in the same terms (e.g month or yrly) NPV(rate, value1, value2, …)*EXC:, CF at t=0] e.g pymt NPV(RATE, (CF1-CFX))+ CF0 IRR (value1, value2, … , *guess+)

COUNT(number1, number2, …) COUNTIF(range,criteria) Remember that the criteria should be in a textstring “ ”

Match(lookup_value,lookup_array,match_type) Provides you with the relative position Match Type 1 : ascending order, finds the largest value that is less than or equals to lookup value Match Type 0: any order, exactly equals to lookup value Match Type -1: descending order, finds the smallest value that is greater than or equals to lookup value

INDEX(array,ROW_num,COLumn_num)

Lookup is Match Type 0-False, 1-True MATCH & INDEX Using MATCH & INDEX, INDEX(result array, Match(lookup value,lookup array,matchtype), Match(lookup value,lookup array, matchtype)) *rembr to do Ctrl, Shift, Enter

Lookup (lookup_value, lookup_vector, result_vector) Lookup is Match Type 1

Monte Hall, Revealed Door Options Matrix

Monte Hall, Change Door Options Matrix

Using Solver, Simulating data from freq bins (CONTINUOUS) • IF DATA CONTINUOUS & DIST NOT mentioned: X’ = PERCENTILE (raw data, RAND()) • • •

Uniform: X= min + RAND()* (max – min) Normal: X = NORMINV(RAND(), mean, standard_dev Exponential: X = (-Lambda) * LN(RAND())

=IF(Door choice=Prize door,1,0) CUMREF:adding of whatever is before the no =SUM(selected cells/total no) *lock the first cell, e.g E$5

Using lookup for CRF, =Lookup(I16,I$8:I$12,H$9:H$13) =Lookup(Rand(), #colnx, #CumRf)

Using Vlookup for CRF, *only compares with the leftmost column, swop to make sure CUMRF is on the left. E.g =VLOOKUP(0.556,H9:I32,2)

Getting a larger sample size out of a small sample size

Used for discrete variables X’=SMALL(array,k) K being the position X’=SMALL(array, RANDBETWEEN(1,N)

Used for Continuous variables, X’=PERCENTILE(array,k), k is (0-1) X’=PERCENTILE(rawdata,RAND()) & RAND() is (0-1)...