Be Responsible -- Practice Safe Spreadsheet Modeling.

Back up your work frequently
, and redundantly. Don’t use portable flash drives as permanent storage. Back up to a reliable network or disk.

Save “versions” of your work. After making material changes to a spreadsheet model, save your work to a new version number, such as CapBudgProj-01.xlsx, CapBudgProj-02.xlsx, etc.

Use file names that are concise and descriptive. Include some detailed information that describes what’s in the file. You should also use concise and descriptive names for your directories and subdirectories.

All finance and accounting professionals should aim to become increasingly proficient with Excel over time.

Here’s a link to all the
Financial Functions in Excel 2007.

Taking Screenshots. Windows 7 includes a “snipping tool” in the Accessories Folder. The following video demonstrates how to use the tool:
Snipping Tool Video.



Capital Budgeting Functions and Formulas.

NPV.
NPV(rate,value1,value2,...) Calculates the net present value of a stream of cash flows. Requires inputs for the discount rate and values or cell ranges of the cash flow. Assumes first cash flow is paid at the end of period 1. NPV Video.

XNPV. XNPV(rate,values,dates) Calculates the net present value of a stream of uneven cash flows. The XNPV function is equivalent to the NPV function, where the user also specifies the exact date of each cash flow.

IRR. IRR(range of values,finance_rate) Calculates the internal rate of return of a stream of cash flows. Requires inputs for the discount rate and values or cell ranges of the cash flows. Assumes first cash flow is paid at time zero (a negative value), and the remaining cash flows (mainly positive) are paid at the end of periods 1, 2, . . . n. IRR Video.

XIRR. XIRR(values,dates,guess) Calculates the internal rate of return of a stream of uneven cash flows. The XIRR function is equivalent to the IRR function, where the user also specifies the exact date of each cash flow.

MIRR.
MIRR(values,finance_rate,reinvestment_rate) Calculates the modified internal rate of return of a stream of cash flows. Requires inputs for the discount rate, reinvestment rate, and values or cell ranges of the cash flows. Assumes first cash flow is paid at time zero (a negative value), and the remaining cash flows (mainly positive) are paid at the end of periods 1, 2, . . . n. MIRR Video.

Payback Period.
The period of time it takes for a business investment (or project) to recover, or “pay back,” its initial costs. Measured in units of time, usually years. Excel does not have an automatic function for calculating payback period. The Payback Period Video will walk you through the steps of how to create your own formula for payback period.

Discounted Payback Period. The payback period of the present value of a project’s cash flows. The easiest way to calculate discounted payback is by fitting the present value of a project’s cash flows into your model and use the Payback Period formulas you created above.



More Excel Functions and Tips -- Helpful for Course Projects.

Goal Seek.
Excel’s Goal Seek function allows you to determine what input value in your model will result in a specific output value. Goal Seek Video.

Scenario Manager. Excel’s Scenario Manager function allows you to manage and save scenarios in your models, such as “best case,” “worst case,” etc. Scenario Manager Video.

Move-Copy-Replace. If your spreadsheet inputs and outputs are located across different spreadsheets it’s easy to consolidate these spreadsheets or connect the information contained in multiple spreadsheets. Move-Copy-Replace Video.

Data Validation. If the models you build are likely to flow downstream in the organization to new users, it is sometimes useful to restrict the information these users can enter into certain cells. This can help them use your spreadsheet model more easily. Data Validation Video.

Estimating Beta with Excel. The video shows how to estimate a stock’s alpha and beta using Excel’s built-in linear regression toos. Estimating Beta with Excel Video.



Increasing Spreadsheet Functionality with IF, AND, and OR Statements.

IF Statements.
IF(logical_test,value_if_true,value_if_false) allows you to program a cell to return one value if a specified condition is TRUE, and another if a specified condition is FALSE. For example, the cell containing the formula =IF(A1>=90,”A”,”B”) would print “A” if the value in cell A1 was greater than or equal to 90, otherwise it would print “B.”

AND Statements. AND(logical1,logical2, . . . ) returns TRUE if all the specified arguments are true, and FALSE if one or more of the arguments are false.

AND statements can be nested inside of IF statements, creating a statement that says “print or calculate X if all of the following arguments are true, otherwise print or calculate Y.” For example, the cell containing the formula =IF(AND(A1=”Blue”,B1=”Green”),”Yes”,”No”) will display “Yes” if cells A1 and B1 contain “Blue” and “Green” respectively, otherwise it will display “No.” [Notice that only text values need to be in quotation marks.]

OR Statements. OR(logical1,logical2, . . . ) returns TRUE if any of the specified arguments are true, otherwise returns FALSE.

OR statements can be nested inside of IF statements, creating a statement that says “print or calculate X if any of the following arguments are true, otherwise print or calculate Y.” For example, the cell containing the formula =IF(OR(A1=5,B1=4),A1+B1,A1-B1) will calculate the sum of A1 and B1 if A1 contains 5 or B1 contains 4, otherwise the cell will calculate the difference A1 minus B1.



Looking up Table Values and Formatting Cells Based on Their Contents.

LOOKUP. LOOKUP(lookup_value,lookup_vector,results_vector) allows you to locate a value from either a one-column or one-row range of cells.

VLOOKUP. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) allows you to locate a value in the first column of a range (or table) of cells and return a value from any cell on the same row.

HLOOKUP. HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) allows you to locate a value in the first row of a range (or table) of cells and return a value from any cell in the same column.

Conditional Formatting. Conditional Formatting allows you to automatically format the appearance of cells according to their contents.