Chapter 8
PV Growth period PV Termination Value PV total
(Pmt/(intreset-g))*(1-(1+g)^# of growing periods)/(1+interest)^# of growing periods)) PV(interest,term period,0,-term value) PV growth + PV termination
Error Tolerance
An absolute value formula can be designed with a specification of how much error to tolerate. The absolute value will make it so it does not matter which number is slightly larger.
Formulaic errors
primary source of errors, but this is basically bad math an incorrectly typed formula that calculates wrong, using an incorrect formula/function, incorrect reference formulae, and inconsistencies from time period to time period
Balance Checks
Check sums are often used to ensure that certain elements in a model stay in balance
Array
one-dimensional (e.g., cell, column, row or virtual) or two-dimensional (e.g., range) collection of items that can be operated on individually or as a whole. Formula shortcut by Ctlr+Shift+Enter
Financial functions
perform common financial calculations (e.g., PV, FV, NPV, IRR, PMT)
Functions
predesigned formulas may involve complex operations and reduce errors
Relative References Mixed References Absolute references
no dollar signs use a dollar sign before either row or column use a dollar sign for both
Constantly growing annuity
(payment/1-g)*[1-(1-g)^n/(1+i)^n]
Profitability index
+(NPV(rate,value_range)+ initial_cash_expenses/(ABS(initial_cash_expenses)))
1. #DIV/0! 2. #N/A 3. #NAME! 4. #NULL! 5. #NUM! 6. #REF! 7. #VALUE!
1. Can't divide by zero 2. Data not available 3. Undefined range or cell name; misspelled 4. Cannot return the intersection of two ranges because they don't intersect 5. A numeric argument is expected but not found; an invalid argument is used in a function 6. Referenced row, column or worksheet is deleted 7. Argument is incorrect data type
Order of Operations
1. Exponentiation 2. Multiply, divide 3. Plus, minus 4. Concatenation 4. Equal, greater than, less than, greater than or equal to, less than or equal to, not equal to
Purpose of Array
1. To ensure that all calculations are identical 2. To reduce the chances that a complex formula is in error 3. To prevent overwriting or tampering with one or more formulae in the range 4. To prevent addition or deletion of rows or columns occupied by the array range when this could affect other calculations, etc.
Best Practices for charts
1. Use a range for the axis that highlights the area of significance by customizing the minimum and maximum values for the range. 2. Shorten labels and legend information to make them easier to read. 3. Sort data that are not in a time series (or other specific order) from largest to smallest or vice versa. 4. Use a bit of graphic enhancement such as the beveling in the prior exhibit, but avoid three-dimensional charts or fancy graphics. 5. If you use colors and shading, test it in a printout and on a screen to ensure that chart components are easily discernible and text is clearly legible. (Black text on a white background is best.) 6. Carefully consider what ranges to use for unit data. Too few increments make the chart hard to interpret and too many look cluttered.
Creating Macro
1. click empty cell 2. record macro 3. enter descriptive name 4. Ctlr + Shift + Enter 5. stop recording
Prevent Errors from Occurring
1. validating data 2. formatting a worksheet to restrict potential inputs
Annual Yield
=(Interest Earned( =FV-Principal)/ PV)/ Investment in Yrs
COUNTIF SUMIF
=COUNTIF(range,criteria) =SUMIF(range,criteria,[sum_range])
Payback Period If method
=IF(AND(previous year cumulative cash flow<0,current year cumulative cash flow>0),(0 - previous year cumulative cash flow)/current year after-tax cash flow + previous year project year),0)
IF statement
=IF(logical_test,value_if_true,value_if_false) Can be inefficient
Total Investment
=Initial investment + Total Periodic deposits
Returning the Payback Period as an Output
=MAX(IF(array>0,array)) Ctlr+SShift+enter
NPV IRR Profitability Index Payback period
=NPV(rate,Value1,[Value2],[Value3])+Initial Investment =IRR(values,[guess]) =1+(NPV(rate,value_range)+initial_cash_expenses /(ABS(initial_cash_expenses))) =sum prior period cumulative cash flow/ current after tax cash flow
OFFSET Function& Definition
=OFFSET(reference,rows,cols,[height],[width]). Reference cells will be wrong when new columns are added
Alt Method: PERCENTRANK
=PERCENTRANK.INC(array,x,significance) array= cumulative CF calcs. per year for payback period x=value want to know rank significance= number of significant digits
Present Value Function
=PV(rate,nper,pmt,[fv],[type])
VLOOKUP Function
=VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
Check Sums
A check sum tests if two sums balance and takes the difference between the two
Go to Special option
A good use for this function is to search for all formulas on a tab and then zoom out to see the whole page and to find hard find hardcoded values
Normalized data does not contain
Blank columns or rows Summary columns or rows Row headers
alternative to using List Validate
ComboBox
Userforms
Custom dialog boxes with radio buttons Input boxes that guide end user entry Message boxes that provide users with instructions or verify that a change should be made
Declining Balance
Declining balance depreciates at a fixed rate but period and first-year months can be specified
Double Declining
Double-declining balance is an accelerated depreciation method that depreciates the most in the first year and steadily declines each successive year
Format of a function
FUNCTION(argument1, argument2, ...)
Value of investment at the end of term
FV(Periodic rate, #of Deposits * Yrs of investment,-Periodic Deposit,- inital investment,IF(Annunity Due(TRUE),1,0))
Total Interest Paid
FV-PV
single cell array
Generates value in one cell bur might perform two functions at once
Multicell Arrays
Generates values in multiple cells
Payback period and discounted payback period
IF statements or PERCENTRANK.INC
MAX and MIN
MAX returns the highest value of all arguments MIN the lowest
FV Factor
POWER(1+rate/12,12*Investment term)
Monthly Compounding Interest Rate PV at Beginning w/ Compounding Alt. PV
POWER(compounding rate, (1/# of Compounding periods))/100 PV(Period rate Cmp, Cmp*Yrs,0,-FV) FV*1/((1+annual rate/# of cmp)^(Yrs*#Cmp))
PV at beginning of term
PV(Annual Rate, Investment YRs,0,-FV)
PV of ordinary annuity Ordinary annuity Function Annuity Due
Payment*[1/i - 1/i(1+i)^n] PV(rate,#of periods,-pmt,0,0) PV(rate,# of periods,-pmt,0,1)
future value
Principal * (1 + Rate)Term
capabilities of VBA
Processing data in a series of workbooks Adding boilerplate text to a number of cells Automating repetitive tasks or operations Creating custom commands or functions Creating end-user forms such as data entry templates
Slicers
Sorting data is also accomplished by clicking on the field drop-down in the PivotTable Field List and then selecting sorting. Can block some data
Dragging Fields
Start with the broadest category then by logical subsets
Types of Depreciation
Straight-line depreciation: =SLN(cost,salvage,life) Declining balance: =DB(cost,salvage,life,period,[month]) Double-declining balance =DDB(cost,salvage,life,period,[factor]) Sum-of-the-years' digits: =SYD(cost,salvage,life,period)
two basic types of macros
Sub procedure is a new command that performs several tasks using VBA code Function procedure returns a single result in a single cell, much like a normal worksheet function
large amounts of text
Text boxes
Conditional Formatting
To make check sums or other error checks even more obvious, they can be color-coded depending on what result is displayed
Interest Earned
Value of investment at the end- Total investment
VBA
Visual Basic for Applications (VBA) is a programming language used to develop custom functions and capabilities in Excel
pivot table
a summary report that FP&A professionals can change dynamically to display and summarize data from a worksheet, BI, or database source in a multitude of ways. it creates cross-tabulations of data, which are different ways of sorting data to highlight the interrelationships between categorical data
Bar charts
are good for data that are not a time series because most people are accustomed to seeing a time series across the bottom row
Line charts
are ideal for showing trends in sequential data points in a continuous series, meaning that additional data points might fall between the measurement points if disaggregated *To add a trend line to a line chart, in the Chart Tools area that appears when the chart is selected, on the Layout tab, click Trendline
Footers
are not good for documenting assumptions because they cannot be linked to a specific tab and cannot be viewed easily in the soft copy.
ist Validate tool
avoid mispelling errors and adds a drop down menu Data tab within the Data Validation dialog box
Calculated field/ Ite,
calculated field is a new field derived from other existing fields. To add, PivotTable Tools tab, Options tab, select Fields, Items & Sets, and then click Calculated Field calculated item is a calculation that is performed for the items within one field and must reside in the Column Labels
Error Check Alerts
can add a worksheet tab devoted to summaries of other error checks or add a summary cell to make it more user-friendly, it could be =IF(COUNTIF($D$2:$D$30,"ERROR")>0, "Errors exist in this model","OK")
Arguments
can be cell references, hard-coded numbers, "text" (must be in quotation marks), an operation or a nested function
CONCATENATE function
can be used to join the various parts into a whole sentence
MATCH Function& Definition
can be used to look up a column by name rather than relying on just the distance from a reference cell and takes a lookup value and tries to match the value within a given range. =MATCH(lookup_value,lookup_array,[match_type])
Data validation
can be used to restrict data entry, but it also includes an Input Message dialog box tab for entering a title and input message
TEXT function
can reference the contents of a cell and return it as formatted text that can be concatenated with other hard-coded text
INDEX and MATCH
combined as lower maintenance, faster processing, and more adaptable versions of lookup functions. =INDEX(array,row_num,[column_num]) can handle tables that are arranged with row and column headers Avoids problems when adding a new column
Conceptual logic
error testing sometimes is obvious only once the model is being built and/or populated with data
Logical functions
evaluate a condition and return TRUE, FALSE, or value if true, value if false. IF, AND, OR. Include Math and logic operators
Array constants
exist only in memory and can not contain formulas, functions or other arrays
annuity
future value of a series of deposits
Pie Charts
good for showing common-size data because they indicate a percentage portion of a whole
documenting assumptions or instructions
hard-coded comment text
Output errors
he issue is often incorrect cell referencing, problems with time or date switches, or graphic outputs not updating properly
XY Scatter Charts
independent variable (the control variable) is plotted on the x-axis (horizontal). dependent variable (the variable influenced by the control value) is plotted on the y-axis
macro
is a set of VBA instructions that is performed automatically once initiated and less transparent and is used for automated tasks and correct flaws in imported data
Input errors
is centered on checking data units, actual data entry errors and missed data
name box
is for setting a print range so that various ranges can be printed quickly
Lookup functions
look up a value and return a single value in a related column or row (e.g., HLOOKUP, VLOOKUP)
Value lookups
look up a value from a table (any rectangular range of data) and return a value from a nearby related cell
LOOKUP function
looks for a value in a one row or one column range that must be in ascending order and then returns a value from the same position in a different one-row or one-column range. =LOOKUP(lookup_value,lookup_vector,result_vector)
circular reference
reference that points back to itself or a chain of references that circle back upon themselves. Best to respond to warning
Validate Data capabilities
restricting inputs into databases, entry forms or worksheets to specified criteria 1. restrict data type 2. restrict data length 3. restrict data to specific format 4. require entry of data in a field 5. create lists to limit choices
Formulas
simply any set of operations and cell references that start with an equal sign
First step in making a pivot table
start with the end product or business question that needs to be answered. It helps guide development of a pivot table arrangement that suits the objectives
Aggregation functions
sum or count vertical or horizontal data. COUNTIF, SUMIF
Column charts
they are simple and can present a time series or unrelated data points well and highlighting the relative size differences between absolute data metrics
Two-dimensional arrays
use commas for items in the same row and semicolon dividers for showing when to break to the next row below, starting over at the first column in the range
One dimensional arrays
vertical when arranged in a column of cells using a semicolon and horizontal when arranged in a row using a common