ADDITIONAL INFORMATION. Building Financial Models with Microsoft Excel is an independent publi- cation and is not affiliated with, nor has it been authorized, . •Cash flow statementA cash flow statement tracks all the money flowing in and out of your business and can reveal payment cycles or seasonal. Building Financial Models with Microsoft Excel: A Guide for Business Professionals Pro Excel Financial Modeling: Building Models for Technology Startups.
|Language:||English, Spanish, German|
|Distribution:||Free* [*Registration Required]|
The goal of Your Money, Your Goals. How should I bring up money topics?.. Tool 1: Debt Your Money, Your Goals - A Financial Accounting and. TLFeBOOK BUILDING FINANCIAL MODELS A Guide to Creating and Interpreting Financial Statements JOHN S. TJIA McGraw-Hill New York Chicago San. THE ARCHITECTURE AND DESIGN OF THE FINANCIAL MODELING TOOL. The financial modeling framework allows one to build a Financial Model.
One cell can also be thought of as a range, whose top left address is the same as the bottom right. Likewise, the whole sheet from A1 to IV is range. F Keys and Ranges 59 Defining a Range Name Quickly When defining a range name, use the Name Box at the upper left-hand corner just above the corner where the column letter A and the row number 1 meet.
Here, cell B3 has been named TaxRate. This quick shortcut is good for naming any size range. This named range becomes convenient to use when you are further down in the spreadsheet and need the input number. You can just type TaxRate instead of having to find the exact cell address. The cell D7 shows the formula that you would have to write if you did not use the TaxRate range name. As you can see, this requires more steps.
Naming Many Single-Cell Ranges at the Same Time A variation of this longer sequence is actually useful and is a time saver when you want to name many single-cell ranges at the same time. This can occur when you need to set some toggle ranges, for example. Type the range names that you want to create in the spreadsheet.
For our example, write them down vertically. In this example, we are going to name the cells in column C with the names that we have entered in column B: F Keys and Ranges 61 2. A user form like that shown in Figure will appear. Click on OK. The user form explains, if rather cryptically, that Excel is creating the named ranges based on the names in the left column of the block you have highlighted.
By this approach, you could also have created named ranges based on labels you have into the top, bottom, or right of the highlighted block.
You can also use range names to describe switches. For instance, you may have a cell that holds the switch for the use of a U. In this case, consider making the range name more self-explanatory. So it may look like this in a formula: At the dialog box that you see, select the range name in the list box and then click on the Delete button.
Functions In this chapter, we continue to look at the spreadsheet aspect of Excel and what we can do with it. We have gone over F keys and ranges, but now we come to the true power of Excel: Excel has a list of functions that it offers, and you can see them by clicking on the icon that looks like fx on the formula bar in versions earlier than XP, the fx is visible in the toolbar. This will display the full list of functions. There are some functions in all, in 10 major classes, but you do not need to be familiar with all of them.
In fact, you can be quite good in Excel and in modeling by just knowing about 50 or so. The list below presents the 36 functions that I would consider important to have in your basic repertoire. The list that follows presents 18 others that would be helpful to know as part of a more advanced skill set. Theses are functions that allow some measure of decision-making ability in your formulas. These are for working with numbers.
These are for combinations of counting and summing. These are for ways of looking up data. These are for specifying dates and time intervals. These are for finding out different types of information in cells. These are for working with text. These are for calculating returns and net present value. More Advanced Functions to Know u Math and trigonometry functions: DAYS u Text functions. Functions 65 function you want to use, and then a set of parentheses.
Within the parentheses, you need to specify the types of informa- tion, called arguments, that the function needs. Others may need one, two, or more arguments. Some take optional arguments. You can enter them or leave them out. Arguments can be: SUM A1: A10 or SUM A1: A10,A20,B30 u A whole column: A u A whole row: MAX H10 u Optional with a marker: When you enter an argument that is a range reference e.
A10 , you can use the period. Excel will automatically convert that to the colon: In this way, you only have to press one key, rather than two the Shift and semicolon keys to get to the colon. We will talk about them in the following way: The starting point: Alternatives to IF 3. Functions for adding 4. Functions for counting 5. Functions for dates 6. Functions for looking up data 7. Dealing with errors 8. It has three arguments, and its syntax is as follows: This in effect tells Excel: Functions 67 function itself.
We could write: If we want the choices to show text, we have to enclose those messages in double quotes: It will always appear as capitals. Try this.
In an empty spreadsheet, put the number 1 in cell A Then, in A11, write just the condition of the IF statement we have been experimenting with. We want Excel to calculate this. To avoid confusion, do not use these key- words for naming range names. What this means is that in the IF formula we were using: This is the basis of something called Boolean logic, a system of algebraic logic invented by the English math- ematician George Boole — What can we do with this?
We can use this Boolean logic as another way to create an IF condition. Here is an example: The IF statement: Functions 69 would look like this in a Boolean: This formula, for example, tests whether A10 is not a zero.
If it is not, then use it as the denomi- nator in the fraction. If it is, then just return a 0. We do not want to calculate the ratio if A10 is 0, because dividing by zero will lead to an error: A word of caution: Nested IF Statements We have been doing only one thing so far. This is to look at an IF statement that looks at whether one condition is true or not, which leads to two choices, do this or do that. There will be occasions when you want to look at more than one condition that leads to more than two choices.
In other words, if one condition is true, do this; otherwise, if a second condition is true, then do that; otherwise, do a third thing. Essentially, the do this or do that can be other IF statements. The limit of seven nesting levels refers to any combination of functions, not just IF. Be Careful: A Zero May Not Be a Zero In cases where the condition of an IF formula is whether a cell is 0 or not, be aware that sometimes a cell may look like it contains a 0, but does not.
Excel translates numbers from the base 10 numbers to the hexadecimal base 16 code of the computer for its calculations and then retranslates that back again. A discre- pancy may result. This discrepancy is miniscule you may see a number like 0. It can, however, have a great effect on an IF condition.
If you set a formula such as: Functions 71 function to 1 or 2 decimal places, thus: They are the following: MIN shows the smallest value. To show results that are always above zero, you can write it with an IF statement: You can write it with the formula that is more efficient, like this: Show the maximum of or the greater number between A10 or 0.
Thus, if A10 is a negative number, 0 is the greater number, and that is the result you will see. If it is posi- tive, then A10 will be the number displayed.
Likewise, the following are equivalents: Show A10 only if it is less than zero, but show it as a positive number: In the case where we want to find the maximum or mini- mum values of a range or a row, there is no other way but to use MAX or MIN: The MAX function will show only numbers in A10 that are equal to or greater than 0.
Potentially, this could be, say, So this is a way to limit the number that is being read elsewhere in the model to only the values between 0 and 5. Another way is to put a limit on the input cell so that only this range of values can be entered.
You can do this through the Data Validation feature of Excel. The index number cannot be a 0. If we had written this with IF, it would have looked like this: G10 , for example.
You cannot do that with IF either. The limit for the values listed is 29; thus, the index number must be between 1 and To have the option of entering a 0 in order not to select any value, use the following variation. Functions 75 different parts of a worksheet or from different worksheets. However, it does not have a limit of the number of choices, so you can actually choose from a lot more choices than CHOOSE.
We can use this ability to pick a remote cell as a way to choose, but it is important to remember this is a far more power- ful function. The first number after the reference cell is the number of rows away from the reference cell. A positive number means the row is below the reference cell; a negative number means it is above. The second one is the number of columns away; a positive number means to the right of the reference cell; a negative number means to the left.
In other functions in Excel where you need to define rows and columns, the system is the same. It is always rows first and then columns. You can have a negative row count and a positive column count, and vice versa. These are set to work an input toggle in cell A1.
In this example, there is another important point to note. It is not an alternative to IF as such, but you could say that it is an alternative to a second IF. The AND syntax is: AND condition1,condition2,. Functions 77 Notice that the AND precedes everything and the conditions are enclosed in parentheses.
AND can contain up to 30 conditions. AND is very helpful if you need to write a formula for the following, for example: OR condition1,condition2,. Again, notice that OR precedes everything. You can also nest other SUMs and the results of other functions: On the other hand, if you had written: The range in SUM will automatically expand as you insert intervening rows or columns, if you are SUMming across a table.
However, you must be careful to recheck the range if you have been inserting or deleting rows at the top or bottom or left and right edges of the table.
It will add the items in a range of values only if they fulfill a condition. The syntax is: Criteria is a condition related to Range. SumRange consists of the items that will be summed depending on the results of Criteria and Range.
We want to find out the total items related to the listing for Alex, and the formula in F2 is: B7 is the Range. The formula sums all the items in C2: C7 , for example. Range is the same as SumRange. Here is an abbreviated sheet, listing three divisional revenues in rows 20, 40, and The highlighted formula in D61 is shown in the edit box.
We copy this formula across that line to columns E and F. Note four important points: The formula in cell D64 has absolute references for the columns in Range and Criteria, since we want these always to read column C. The two ranges must be of the same size. In this case, they are 60 rows each. If they are not of equal size, this formula can give wrong answers. As noted above, we could have used a reference here to another cell which holds this text.
If you do put in another cell, make sure that that cell is not part of the two ranges, as this would cause a circular reference. Do not have the two ranges include the rows they are on. Functions 81 If you add more divisional sales lines between rows 1 and 63, they will automatically be part of the sum of Total Sales. By the same magic, if you delete any divisional sales seg- ments, the total will continue to work and will not return any error messages.
The syntax consists of two or more ranges, both of which must be the same size, and the function multiplies each element in one range with the corresponding element in the other range. It will disregard entries of text. C4 would return the number 6. It is similar to the SUMIF but returns a count of the items that meet the condition, rather than the sum. It only has two argu- ments. In this case, the Criteria must be stated as a test. The following example shows the number of items that are above 25 and looks to the range C2: A cell with a 0 is part of the calculations.
This is shown in the following illustration: However, if they point to at least one cell that contains the value 0, then the function will work. Occasionally, you may have to enter quarterly dates or work with days, months, and years. If we want to have a good control of the dates in the column so that we can easily change them, then we will have to understand how dates work in Excel. How Excel Keeps Track of Dates Excel keeps track of dates by assigning a number, or a serial value, to a date, starting with the number 1 for January 1, The upper limit is the serial value 2,, for December 31, By using different data formats, you can make the serial value appear in the date format you want, including non-U.
Monthly Dating Here is a simple dating problem: How do we make a date go up by one month? Or more specifically, how do we get from the end of one month to the end of the next month?
Even with serial values for dates, we cannot just add 30 or Adding The solution is to work with years, months, and days. If we want to go up one month, we simply add 1 to the month desig- nator, no matter what the length of the month is. Likewise, to go up one year, we add 1 to the year designator, leap year or not. Functions 85 month, or day it is for any serial value representing any time between January 1, and December 31, Thus, if we picked a serial value like This is to say that December 31, is 37, days away from December 31, January 1, , being day 1 in the serial value, is 1 day away.
I am using here the U. Excel can be set to show different dating formats so that the serial value will appear with the correct order of days, months, and years. However, if the date were placed in another cell and the function referenced that cell, you do not have to worry about double quotes. Going the other way, we can write a date using the compo- nent parts of year, month, and day. For this, there is the DATE function. Because Excel functions can use the results of other func- tions, we can write the following in cell C1.
How do you make a date go up by one month? In C1, we write: This is not quite right. The problem arises because January has 31 days and, with this formula, we are asking Excel to give the date for something like February 31, We had no trouble with the first formula for January, because that month has the same number of days as December. Instead of trying to find the ending day of each month, we could look for the first day of the next month and then subtract one day.
Since the first day is always day 1, this is quite easy. So January 31 is really February 1 minus 1 day; February 28 is March 1 minus 1 day, and so on. But wait. Yes, in fact, it can. So now we have a solution for our dating problem. We add an extra month to the month interval we want to go up, but specify 0 for the DAY. In C1, to make the date go up by one month, we write: Yearly Dating Increasing dates by 1 year is fairly simple matter now.
Just add 1 to the YEAR number. In cell C1, we would use the formula: However, if we are working with a February year-end, this formula will not return the leap day of February 29 in the leap years, since the DAY will always be based on the count of 28 for or 29 if the starting year had been We could use the approach of using the 0 day of the next month, however: That will bring us to exactly a year later.
Non-Annual Intervals Using the MONTH part makes it easy to change the periodicity of your model from annual to quarterlies, or to some other non- annual interval.
Functions 89 Finding the Number of Days Between Dates Because of the serial value system, it is easy to find the number of days that have elapsed from one date to the next. For example, to find the number of days between August 17, and October 1, , we do the following: Finding the Number of Months Between Dates Using DAYS Finding the number of months between two dates is a little tricky, because of the different lengths of the months in the inter- val.
In the last example we could divide 18, by the average number of days in a month i. A better way is to use the DAYS function. With DAYS, Excel considers each year to be days by assuming that there are 12 months, each composed of 30 days. So Excel has a way of considering the ending days of each month so that everything falls into line properly.
Because each year is 5 days shorter than the actual year 6 in a leap year , the number of interval days is fewer than the previous calculation. This is not a problem if we are looking to get a sense of the portion of the month or the year using this method.
To find the number of months, we simply take the inter- val in days in a day year and divide it by Now, to get the number of months or the number of years: It does not in this case because Excel looks at the end of the month and tries to fit the , , , day endings into some order, and somehow the day ending is confusing it.
We can help Excel get unconfused by bringing the dates into the beginning of the month, where the interval algorithm is more straightforward. We do this by adding the number 1 to the cell references.
In this way, we also do not need to change the dates themselves: Looking up data is really pinpointing the location of the data point that you want, whether it is from a collection of alternatives or from its location as defined by rows and columns. Depending on how you write the formula, this function will return either the row or column number but not both within the range that you specified.
This can be a number, a text, or a reference to another cell that holds the LookUpValue. LookUpArray is a contiguous range of cells, or a range name. In this case, the items in the LookUpRange must be arranged in ascending order. The data must be in descending order. If it is 0, then it will find the exact match for LookUpValue. The data can be in any order. MATCH is very useful in this mode, and this is what we will illustrate below.
The range in the middle specifies the one-column block. If we made a slight change to the formula and made the target range start at row 1: There is an inter- esting point here. Being able to identify a row number of a data point can be useful in other instances, especially if we already know which column it is in. B6,0 4 Bob 5 George 6 Bob 7 To make this function useful to return a row number, the range used in LookupArray should start with row 1 in the column.
In fact, the range can be the whole column, which can be easily defined by leaving out the row numbers, thus: The RowNumber is an integer greater than 0 that specifies which row within the Array the data point is in.
RowNumber 1 means that the data point is on the first row of the Array. This can be omitted if the Array is a one-row range.
It specifies which column the data point is in. ColumnNumber 1 means that the data point is on the first column of the Array. You can omit this if the Array is a one-column range. D6,1,1 will return the value in the top left corner of the range i. Using this table, we will get the following results from these other variations: If they point to a location outside the range, you will get a REF error message. The following in cell E8 returns the stock price for the company: These functions are most useful when the answer does not depend on an exact match with your search parameters.
The TableArray contains the data for the lookup. The RowNumber is the row that contains the data to be returned by the function.
The LookUpType is optional. If omitted or TRUE, this means than an approximate match can be returned if there is no exact match. The approximate match will be based on the value that is less than the LookUpValue.
Tax rate: The four most common error messages that Excel will show are the following: Errors Excel will display this error when you attempt to divide a number by 0. It is easy to write a formula that inadvertently divides by zero because as you develop the formula, you may be using some test numbers. It may be that as the model starts to be used, there will be values coming in that will make these formulas calculate properly again. However, it is sometimes quite disconcerting for a new user unfamiliar with your model to see these error messages.
For this reason, for any formula you write that involves a division, you should take steps to do an error trap by using an IF statement. Another variation is this formula: This is fine, unless there is the chance that this formula will be read by another cell as part of the calculation in that cell.
Error The usual occurrence of this error message is when you have written a formula whose components include a text. Essentially, this is Excel saying: I want values only, please.
Error You will get this error if your formula uses a range name that does not exist. This can be because of a misspelled range name, or a range name that you have created and used before but which has now been deleted. A misspelled function name will also give you this error. The REF! Error This happens when the formula uses an invalid cell reference.
For example, start with a formula in cell A10 like this: In copying, Excel will try to keep the relative referencing, so as you go up one row, it changes the reference to A0. Since A0 does not exist, the error message shows up. You will also get this error if you are in a situation where a formula, looks like this: Copying and pasting will not cause a REF! Understanding what the error messages mean allows us to get an idea of what kind of error to look for and make the necessary corrections.
Functions 99 When we build a model, however, errors can be a little more troublesome. As you will see when we start developing the for- mulas in the model, we will be using circular references. A circular reference occurs when a formula refers to itself, whether directly or indirectly.
A10 in cell A10, you would get a circular reference because every time A10 is calculated, it must include itself in the SUM calculation, in a never-ending cycle. When you create such a circular reference, Excel will give you a warning message: One way is to correct the inadvertent circular references. This will set Excel to allow circular references. Why do we want iterative circular references? Simply put, it is a simple way to get the model to converge on its calculations of interest expense.
The model then has to recalculate or iterate to adjust for this increase, which in turn will create another, but incrementally smaller, increase. The iteration will go through several more cycles before there is convergence within the limits set in the model.
So, circular references can be used to good advantage. The dangerous thing is that as there is now a calculation loop in the model, if there is an error that gets inadvertently introduced into the calculations, this error message will continue to cycle around in the loop. Even after the source of the error has been removed, the error message continues to be caught in the loop!
In this case, there are two ways to correct the situation: Once this is done, restore the formula again.
Simply put, at a location in the calculation loop, we write a formula that returns a 0 when it encounters an error condition. If there is an error in the loop, this formula in C50 will revert to a 0, which is then read by C51 and the rest of the calculations.
The 0 breaks the circular loop and gives the error message a chance to be cleared out, so that when C50 calculates again, it does not see the error sign and automatically reconnects to C The loop is restored.
Functions return 1. Neat trick 1: You can put a negative number for the number of digits: Neat trick 2: To round to the nearest 5 cents in a price, double the number first before doing a ROUND to 1 digit, and then divide by 2. You can use other divisors for other results. The divisor can be any other number except 0. Working with Numbers in a Direction: The following functions work in identifying or shifting numbers on the numbers line.
For a positive number, this means it is rounded up. For a negative number, it is rounded down, so it becomes more negative.
Both the number and significance must have the same sign. If they do not, a NUM! FLOOR 1. The number of digits can be a positive or negative number, and works exactly the same way as in ROUND.
Other Information Functions: One example where this is useful is when you are writing an IF statement where you want the formula to read the entry in a cell, even if it is a zero.
Working with Text: In these functions, the space between words counts as one character. Functions u RIGHT Text, number of characters will return the rightmost portion of the text, consisting of the number of characters.
The portion will have the length of the number of characters. Excel is able to handle text strings and values together, but if you want the values to carry a particular format when you want to show the two together, you must use TEXT and define the format of how that value will appear. Amazingly, you can still apply an operation to this text string so that it still performs as a value, but the format does not work on the result.
Formulas and Functions with Microsoft Excel Financial Modeling with Crystal Ball and Excel. Microsoft Excel and Access Integration: With Microsoft Office Microsoft Excel Formulas. Easy Microsoft Excel Beginning Microsoft Excel Microsoft Office Excel Using Microsoft Excel Financial Analysis using Excel. Recommend Documents. You've been outbid. Don't let it get away - place another bid.
You've been outbid by an automatic bid placed earlier by another bidder. You're the highest bidder on this item! You're the first bidder on this item! You're the highest bidder on this item, but you're close to being outbid. This auction is almost over and you're currently the high bidder. You're the high bidder on this item, but the reserve price hasn't been met yet.