CAT Week 1

AUTOFILL FUNCTION
 Auto-Fill Function:
- Extrapolation; Filling in next data point based on initial and current data point
- Best Fit Line
- Ignores X-values (Difference from trend, slope, intercept)
- Can be also used for Non-numbers
 Drag across
 To customize an autofill:
File  Options (preferences)  Advanced  Custom List  Add/Edit
Autofill

BASIC FUNCTIONS

 Average: =AVERAGE(Number 1, Number 2)

 Maximum: =MAX(Number 1, Number 2)

 Minimum: =MIN(Number 1, Number 2)

 Transpose
- To put a vertical role of numbers into horizontal role
 Click right  Paste Special  Transpose

LOCK CELLS

 Lock Cells
- Mixed Referencing:
1. Lock cell in Row: Put $ before Number (C$5)
 Vertical Stays  Prevents moving down
2. Lock cell in Column: Put $ before Alphabet ($C5)
 Horizontal stays  Prevents moving right
- Absolute Referencing:
Lock cell: $C$5

FORMULAS

 Break Even Formula: Fixed Cost / (SP-VC)

- Total Marginal Contribution = Fixed Cost

 Profit Margin Percentage Formula: Profit / Revenue

 Marginal Contribution Per Unit Sold Formula: (1-VC%)*Unit SP

CAT WEEK 2

 Documentation:
- Description | Cell | Formula | Label (If any)
 Annual Demand | B6 | <input> | Annual_Demand
 #Orders/year | E6 | =B6/E6 |

 Productivity = Demand / Number of Equipment

 Decimal Rounding

- Home 

TREND FUNCTION

 Trend( ) Function: TREND(Known_Y, Known_X, New_X, Const)

- Returns us a New_Y value
-

- Using best fit line to predict New_Y

- Const is either TRUE or FALSE
 Y = Mx + B
 True/Omitted: B is computed & accepted
 False: B is 0; Best Fit line must pass through origin
(0 Demand = 0 Productivity)
- Ultimately, it is up to businesses to decide if Const should be TRUE or FALSE
- Description of trend and quantifying it is important:
 Machine rate is generally increasing, it may be due to:
1. Technological Advancement
2. Productivity is not at maximum
 Number of Machines to be purchased is in decimal places, we cannot simply
round up/down
- Explain all reasoning to client and ask them to make the decision

 Create a graph using Fitting Trendline:

1. Select x and y values
2. Create scatterplot (Scatter, Smooth marked scatter)
3. Right click chart, Add trendline
4. Right click chart, Format trendline, Options, Display equation and R-Squared
value on chart
  3 Methods for using Trend Function:
1. Old is Better (Correct & Sufficient)
- Uses only the actual numbers, may be inaccurate
- Actual numbers may be very long ago (outdated)
- Safe if there are no changes in the trend
- To predict “12th Number”, we use 1-10

2. More is better (Correct but does not Value-Add)

- Singapore Approach
- Looks at everything, actual and forecast
- 11th value will always lie on the line
- To predict “12th Number”, we use 1-11

3. Moving Average (Depends)

- Actual vs Forecast
- Window gets dragged down
- Ignores old values and keep adding new values, line’s slope will be changed
and newly predicted values will be different
- Error is carried down; may be inaccurate
- To predict “12th Number”, we use 2-11
- Gives higher numbers, may have opportunity costs incurred due to
over/under prediction

BLACK BOX

 Summarizes input variables, output variables

- Decisions: Controllable input
Input variable decided by user
- Parameters: Uncontrollable Input
Input variable with a given value
- Performance Measures: Interested Output
Output variables with value of key interest to the user
- Consequence Variables: Calculated Output
Output variables with values produced in calculations
INFLUENCE DIAGRAM

CONDITIONAL FORMATTING
 See which is the profitable area
- Conditional Formatting  Color Scales

SOLVER
 Used to find MAX, MIN, BREAK EVEN
- Tools  Solver
 Set objectives = profit; Value of = 0, By Changing = Room Rate; Solve

GOAL SEEK
 Find Optimal BREAK EVEN
- Set Target cell = Profit; To Value = 0; By Changing = Room Rate

INTERCEPT & SLOPE

 Y = SLOPE*X + INTERCEPT (Y=M*X + C)
- C  Intercept =INTERCEPT(Number 1, Number 2)
- M  Slope = SLOPE(Number 1, Number 2)
-
Intercept Slope

 There is a relationship between Demand and Price

- Demand = Dependent = Y
- Price = Independent = X
- Y is dependent on X

IFERROR
 IFERROR(Value, Value_if_error)
- Value  The value, reference/formula to check for error
- Value_if_error  The value to return if an error is found

TRADE-OFF-ANALYSIS
  Used when decision variable values (prices) are changed to examine how
performance measures (profit) trade-off against each other
- To find: Optimal Price setting, Break-even Price

SENSITIVITY ANALYSIS
 Used when uncontrollable parameter values (slope) are changed to examine how
sensitive performance measures (profit) are to parameter changes
- Highlight whole table  Data  What-if Analysis  Data Table
- Row & Column Input cell: Cell you want to take reference from (Highlight)
Data Table
 If change value is stacked in column then it will be column input, else is row

 One-Variable DataTable

 Two-Variable DataTable

- Note: 2. Is column input & 3. is row input

 Row Input Cell: Input that’s changing the values in each row
 Column Input Cell: Input that’s changing the values in each column
CAT WEEK 4

IF, AND, OR FUNCTIONS

 IF(_ >/</=3, _, _)
- IF(Requirement, Value if True, Value if False)
- Logical test can be A1=“apple”, A1<=55, A1<C4
- Nested If: IF(Logical Test, IF(Logical Test 2, value if true, value if false), value if
false)
- If nested if too complicated:

 Then INDEX MATCH to get correct cell

 *TRY TO SEE IF CAN 1-x / ANY k-x to get answer for y (X = 1st result, Y = 2nd
result)

 AND(_=”_”, _ = “_”)
- Both requirements are met
- If both answers are correct, answer will be “TRUE”
- If both answers are wrong, answer will be “FALSE”

 OR(_=”_”, _=”_”)
- One requirement is met
- If either one is correct, answer will be “TRUE”
- If both are wrong, answer will be “FALSE”
LOOKUP FUNCTIONS

 LOOKUP(lookup_value, lookup_vector, result_vector)

- Lookup_value  Value you want
- Lookup_vector  Cells you want to search
- Result_vector  Cells you want to extract results

- Cells of Lookup Vectors must be = size of Result Vectors

- Lookup cannot give exact match, but only an estimation
- Lookup Vector must be arranged in ascending order
- Lookup Value is the largest value <= Value in Lookup Vector

 VLOOKUP(lookup_value, table_array, column_number, range_lookup)

- lookup_value  Value you want
- table_array  All the tables
- column_number  Which column from the table you want

- It will always search for the value in the leftmost column of the table
- 3 problems:
1. It is very vulnerable to data insertion
2. Lookup factor must occur before your return factor
3. Lookup will save unnecessary data
(Your RAM will be memory inefficient as all values would be copied)
- Always lock cells for VLookup
 HLOOKUP(lookup_value, table_array, row_number, range_lookup)
- Always search for the lookup value in the top row

 RANGE_LOOKUP
- Found in VLookup & HLookup Functions
- TRUE/Omitted  Values must be in ascending order & the result is an
approximate value
- FALSE  Values no need to be arranged in order, and the result is an exact
match. If not, it will be #NA

 REVISING TABLE TO SUIT LOOKUP:

# Widgets Unit Price # Widgets Unit Price Range

1 to 200 $8.90 (Lower Bound)
1 $8.90 1 to 200
201 – 500 $8.70
201 $8.70 201 – 500
501 – 2,000 $8.00
501 $8.00 501 – 2,000
2,001 – 5,000 $7.50
2,001 $7.50 2,001 – 5,000
5,001 – 10,000 $6.50
5,001 $6.50 5,001 – 10,000
10,001 or more $5.75
10,001 $5.75 10,001 or more

 MATCH(lookup_value, lookup_array, match_type)

 Lookup_value = Value you want
 Lookup_array = Cells you want to search
- Gives us the position of the item
- Match Type = 1  Must be placed in ascending order
(Returns largest value that is <= Lookup_value)
- Match Type = 0  Can be any order, exact match
- Match Type = -1  Must be placed in descending order
(Returns smallest value that is => Lookup_value)

 INDEX(array, row_number, column_number)

 Array = All the tables
 Row_number = Which row in the table you want to search
- Returns either:
1. Individual value in a table
2. An array  Selected by row and column number indexes
 [row_number=0]: Returns entire COLUMN
 [column_number=0]: Returns entire ROW
  Combination of INDEX() and MATCH()
- Returns value by using position found by MATCH()
- INDEX(array, MATCH(), MATCH())
 1st MATCH ()  Returns Row position  Row_num
 2nd MATCH ()  Returns Column position  Column_num

SUMIF FUNCTION
 SUMIF(range, criteria, sum_range)
 Range = Range of cells you want evaluated
 Criteria = Selection criteria (MUST BE ENTERED AS TEXT STRING)
 Sum_range = Actual cells to sum
- Returns the sum result

CONCATENATE() OR &
 Concatenate text together to form longer text by using
CONCATENATE() or &
- Using concatenate: =CONCATENATE(“Michelle”,” “, “Cheong”)
- Using & sign: = “Michelle” & “ “ & “Cheong”
 Excel automatically converts numbers to text when concatenating
SOLVER & LINEAR PROGRAMMING
 Minimizing/Maximizing linear cost function subject to linear equality & inequality
constraints
 4 possible results:
1. Unique optimal solution
2. Multiple optimal solution
3. Optimal cost is negative infinity for minimization / positive infinity for
maximization
4. No feasible solution
 LP is made up of:
1. Decision Variables (Real, Binary, Integer)
2. Constraints (<=, =>, =)  Capacity + Non-negativity constraints
3. Objective Function (Minimize/Maximize)


3. Highlight whole column  Use (control + shift + enter)  Array formula for:
# of Projects * Manpower Required automatically
4. Constraints  Whole column of total needed <= Whole column of total
available
CAT WEEK 5

MONTE CARLO SIMULATION

 Simulating scenarios repeatedly using random generation of input variables to the
model
- Random generation of input can be obtained from known distribution if you
know that the distribution best describes the input variable

 RAND()
- Returns a evenly distributed random real number between 0 & 1 (excluding 1)
- Represents a continuous uniform distribution where each number is likely to
occur
- To generate a random real number between continuous numbers A and B:
RAND()*(B-A)+A
- To generate a random real number between 2 non-continuous numbers A and B:
IF(RAND()<0.5, 1, 3)

 RANDBETWEEEN(bottom, top)
- Same as =RAND()*(Max – Min) + Min
- Returns a random integer number defined between bottom and top (inclusive)
- Represents a discrete uniform distribution where each number is equally likely

 Coin Throw Game =IF(RAND()<0.5, “heads”, “Tails”)

- Randomly 50-50 between 2 non-adjacent numbers

 LARGE(array, k)
- Large(Discrete)
- Returns the Kth largest value

 SMALL(array, k)
- Small(Discrete)
- Returns Kth smallest value

 PERCENTILE(array, k)
- Percentile(When dealing with cumulative raw data)
- Returns kth percentile in array (Interpolated value)
- Returns kth largest value

 Simulations without raw data

1. UNIFORM: =MIN+RAND()*(Max-Min)
2. NORMAL: =NORM.INV(RAND(), mean, SD), STANDARD NORMAL:
norm.s.inv(RAND())
3. EXPONENTIAL: =-MEAN*LN(RAND())
CUMULATIVE RELATIVE FREQUENCY (CONTINUOUS FUNCTIONS)
 Normal Distribution
- Returns the standard normal cumulative distribution function
(Area under the curve)
- Where Z ~ N(0,1), ), μ =0 and σ =1.
- NORMDIST(z, mean, standard_deviation, TRUE)
 returns probability of z happening
 Cumulative: TRUE = CDF, FALSE = PDF
- NORM.INV(probability, mean, standard_deviation)
 Returns Z; the value of interest
- NORMDIST(Z)
 Standard Normal
 Z = value of interest
 Returns only CDF

 Exponential Distribution
- EXPONDIST(x, 1/mean, TRUE)
 Cumulative: TRUE = CDF, FALSE = PMF
 X = Value of interest
 Mean = Average inter-arrival time
- Time between arrivals

 Uniform Distribution
- Cumulative Relative Frequency = (x – min) / (max – min)
 X = Data point
 min, (max-min) are input parameters that are arbitrary values until solved by
solver
- Cumulative Uniform Distribution: (MAX-MIN)*RAND()+MIN

CUMULATIVE RELATIVE FREQUENCY (DISCRETE FUNCTION)

 Binomial Distribution
- BINOMDIST(number_s, trials, probability_s, cumulative)
 number_s = No. of successes (No. of YES)
 trials = No. of trials
 probability_s = probability of success
 cumulative: TRUE = CDF, FALSE = PMF
- Possible outcomes FIXED

 Poisson Distribution
- POISSONDIST(x, mean, TRUE)
 x = No. of outcomes
 mean = expected value
 cumulative: TRUE = CDF, FALSE = PMF
- Possible outcomes UNLIMITED
  Uniform Distribution
- Cumulative Relative Frequency = (x – min) / (max – min)
 X = Data point
 min, (max-min) are input parameters that are arbitrary values until solved by
solver
- Discrete Uniform Distribution: Old value*(1+ RANDBETWEEN(min,max)/100)

DATE & TIME MANAGEMENT

 TODAY()
- Returns the current date

 NOW()
- Returns the current date and time

 YEAR(serial_number)
- Returns the year corresponding to the serial number

 MONTH(serial_number)
- Returns the month corresponding to the serial number

 DAY(serial_number)
- Returns the day corresponding to the serial number

 DATE(year, month, day)

- Returns a serial number

 HOUR(serial_number)
- Returns the hour corresponding to the seriel number
CAT WEEK 7

COUNTIF
 COUNTIF(range, criteria)
 Range = Range of cells from which you want to count cells
 Criteria = input as text
- Returns the number of the cells that satisfy the evaluation criteria

 COUNTIFS(range1, criteria1, range2, criteria2,…)

- Criteria can be “apple”, “<=55”, “<”&C4, ">24-12-2012", ">6:00PM"
- NOTE: “<>” = Not equals to
FREQUENCY WORKSHEET FUNCTION
 FREQUENCY(data_array, bin_array)
 Data_array = contains the raw data
 Bin_array = contains the bins upper bound values
- Returns the number of items in the data_array <= bin values

CUMULATIVE FREQUENCY TABLE

"CumProb" #Arrivals Probability
0.0 0 0.1
0.1 1 0.2
0.3 2 0.3
0.6 3 0.3
0.9 4 0.1

 Type 1
With reference to data fit*
Step one: sort data from smallest to largest using small function
Assign a value to each data using formula
If is empirical use: data point number/count of data points
Others use the Statistical formula
E.g.
# Data Sorted Empirical
=Small([Data array],[#]) =[#]/COUNT(Data array)
1 10 10 0.2
2 40 20 0.4
3 30 30 0.6
4 20 40 0.8
5 50 50 1

 Type 2
 Data array (can be randomly arranged)
1 1 1 1 1 2 2 2 3 3
3 3 3 4 4 5 6 6 6 6
Bin array
Bin Cumulative Frequency Cumulative Probability
=FREQUENCY([Data array], [Bin]) = Cumulative Frequency/total count
1 5 0.25
2 8 0.4
3 13 0.65
4 15 0.75
5 16 0.8
6 20 1

*Note: to use for lookup best to shift one down, I.e. bin 1 = 0, 2 = 0.25, 3 = 0.4, etc…