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