CAT Finals Notes PDF

Preview

Summary

CAT FINALS PREP 1. Copy paste special 2. Autofill a. Only when there is a obvious trend b. If not, use TREND(known y, known x, new x) 3. Add appropriately a. If dragging vertical, no change, add to numbers b. If dragging horizontal, no change, add to letters 4. Black Box Diagram 5. Influence Diagram...

Description

CAT FINALS PREP Knowledge/skills: 1.! Copy – paste special 2.! Autofill a.! Only when there is a obvious trend b.! If not, use TREND(known y, known x, new x) 3.! Add $ appropriately a.! If dragging vertical, no change, add to numbers b.! If dragging horizontal, no change, add to letters 4.! Black Box Diagram

5.! Influence Diagram

6.! Chart Plotting (including how to format and add trendline) a.! X is independent variable b.! Y is consequence of dependent variable c.! SCATTER PLOT d.! Right click to add trendline e.! Select line and show R2

7.! Goal Seek a.! Under “what-if analysis”, select 1 objective cell and 1 decision cell and desired objective 8.! Spin Button a.! Developer -> Insert -> Form Control b.! Right click and Format Control c.! Can press up/down to increase/decrease the value of linked cell 9.! Option Button a.! Create option buttons (2 or 3) and link them to SAME cell b.! Cell will show 1, 2 or 3 depending on option selected c.! Use CHOOSE function to assign values to 1, 2 or 3 10.!Data Validation (Drop Down List) a.! Data -> Data Validation -> List -> Select array to choose from 11.! Macro !! Record and run a macro + link it to a button !! Macro to build a dynamically changing list (use relative reference) 12.!SUMIF, COUNTIF a.! Range, Criteria insert it inside “ ” b.! “ Data Table -> Column input is input variable you want to change

[SIMULATION] 18.!Simulation using restricted options given a.! Create an options matrix to reference to for possible outcomes

b.! Trial 1000x then tabulate results c.! Can let Win = 1, Lose = 0 d.! Use IF(RAND() stock, divided by total count ii.! =COUNTIF(A1:A100,”>”&B1:B100)/100

[SOLVER] 23.! Solver a.! Set 1 objective cell and multiple decision cells b.! Set constraints more than or less than (refer to organized sheet) c.! RHS MUST BE NUMBER VALUE ONLY d.! Setup with Input, Calculations, Decisions (>1 CELL), Objective (1CELL) and Constraints

e.! f.! Solver, Select Objective and Decision Cells. Add constraints together can group those with same signs.

[TIME VALUE OF MONEY] 24.!Group 1: Financial Functions, can compute each one, knowing the other four variables

25.!Group 2: Valuation Measures

minus Initial investment to get actual NPV

*Accept if NPV>0 or IRR>cost of borrowing

[DATA FITTING] 26.! Columns to build a.! # - just the numbering b.! Data – from the question, sorted ascending c.! Empirical – position/total count d.! Normal – NORMDIST of data point w.r.t. mean and stdev e.! AbsDev – Absolute Difference between Empirical and Normal f.! *Min, Max, Mean, Stdev parameters and MaxAbsDev on top 27.! Copy paste the Normal and AbsDev with the parameters and MAD a.! Use Solver to Minimize MAD by changing parameters. 28.!Use new parameters for decision making 29.!Add points to the data column, and NORMDIST it for extrapolation of curve

[TIME MANAGEMENT] 30.! DATE FUNCTIONS (if add or divide between 2 date cells of same formatting, can get days)

31.! TIME FUNCTIONS (if add or divide between 2 time cells of same formatting, can get hh:mm:ss)...