Danielle Stein Fairhurst

Cheat Sheet / Updated 11-12-2021

When you're building financial models in Microsoft Excel, functions are the name of the game. You also need to check your work — and check it again — to make sure no errors slip through the cracks. Finally, to make your work quick and easy, keyboard shortcuts are a lifesaver.

Article / Updated 09-11-2017

Once you’ve found the business’s FCF, terminal value, and discount rate, it’s time to value the business in your financial model. Follow these steps: Select cell C21 and enter the formula =SUM(C19:C20); copy this formula across the row. This formula sums cells C19:C20 to arrive at the total cash flows to discount. Select cell B23 and enter the formula =NPV(B6,C21:I21). This uses the NPV function to discount the cash flows, telling you what the series of cash flows over the seven-year future period is worth today, based on the assumed WACC. The first reference of the NPV (in B6) is your discount rate or WACC, and the second part of the formula is the total cash flows to discount. By discounting all the FCFF and terminal value, you have arrived at enterprise value, or the value of the whole business disregarding the capital structure. This value is $215,460. In order to find the value of the equity, you must add the cash the business currently has and subtract the debt the business currently owes to lenders. To add the cash, select cell B24 and link it to Year 0 Cash at Bank in the Balance Sheet tab with the formula =‘Bal Sheet’!B6. To add the debt, select cell B25 and enter =-’Bal Sheet’!B28. You want to show this as a negative value, so preface the formula with a minus sign. Compare your values to those below. Select cell B26 and enter the formula =SUM(B23:B25). You should have now arrived at an equity value of $192,960. In order to find a target share price, you must divide the equity value by the number of shares outstanding. The business has 5,000 shares outstanding. Select cell B28 and enter 5,000. Select cell B29 and enter the formula =B26/B28. The calculated value is $38.59. Check your totals against the model asbove. You’ve now found the fair value of the business (enterprise value), its equity (equity value), and its stock price! You can download a copy of the completed model called File 1102.xlsx.

Article / Updated 09-11-2017

Now that you’ve determined your base case assumptions that reflect how you believe the business will perform, you also want to run worst-case and best-case scenarios in your financial model. Not only do you want to see how you believe the business will do, but you also want to see how the business will perform if it does worse than expectations or better than expectations. Running multiple scenarios is a very important part of financial modeling — some would say it’s the whole point of financial modeling — because it allows the user to gauge the different outcomes if certain assumptions end up being different. Because no one can see into the future and assumptions invariably end up being wrong, being able to see what happens to the outputs when the main assumption drivers are changed is important. Because you’ve built this integrated financial model such that all the calculations are linked either to input assumption cells, or to other parts of the financial statements, any changes in assumptions should flow nicely throughout the model. The proof is in the pudding, however. Entering your scenario assumptions Going back now to the Assumptions worksheet, you believe that the main drivers of profitability for your cafe will be the average number of cups you sell per day and the rent you’ll pay. You believe that reducing cups sold per day by 20 cups and increasing rent by 10 percent is a reasonable worst-case scenario, and increasing cups sold per day by 20 cups and reducing rent by 10 percent is a reasonable best-case scenario. At the very top of the Assumptions worksheet, enter the scenario input assumptions. Building a drop-down box You’ve decided on your scenario assumptions, so now you need to build a drop-down box, which is going to drive your scenario analysis. You have a full, working financial model, so you want the ability to easily switch between your scenarios to see how the outputs change in real time. You can put the scenario drop-down box on either of the financial statements, but for this example you’ll put it at the top of the income statement. Follow these steps: Go to the IS Cash Flow worksheet and select cell B1. Select Data Validation in the Data Tools section of the Data Ribbon. The Data Validation dialog box appears. From the Allow drop-down list, select List. You could type the words Best, Base, and Worst directly into the field, but it’s best to link it to the source in case you misspell a value. In the Source field, type = and then click the Assumptions worksheet, and highlight the scenario names Worst, Base, Best. Your formula in the Source field should now be =Assumptions!$B$2:$D$2. Click OK. Go back to cell B1 on the IS Cash Flow worksheet, and test that the drop-down box is working as expected and gives the options Best, Base, and Worst. Set the drop-down box to Base for now. Building the scenario functionality You need to edit your input assumptions for number of cups sold per day and monthly rent so that as the drop-down box on the IS Cash Flow worksheet changes, the input assumptions change to the corresponding scenario. For example, when Best has been selected on the IS Cash Flow worksheet, the value in cell B9 on the Assumptions worksheet should be 140, and the value in cell B23 should be $1,080. This should be done using a formula. Often, many different functions will achieve the same or similar results. Which function you use is up to you as the financial modeler, but the best solution will be the one that performs the required functionality in the cleanest and simplest way, so that others can understand what you’ve done and why. In this case, there are several options you could use: a HLOOKUP, a SUMIF, or an IF statement. The IF statement, being a nested function, is the most difficult to build and is less scalable. If the number of scenario options increase, the IF statement option is more difficult to expand. In this instance, I have chosen to use the HLOOKUP with these steps. Follow these steps: Select cell B9 and press the Insert Function button on the Formulas tab or next to the formula bar. Search for HLOOKUP, press Go, and click OK. The HLOOKUP dialog box appears. Click the Lookup_value field, and select the drop-down box on the IS Cash Flow worksheet. This is the criteria that drives the HLOOKUP. Press F4 to lock the cell reference. In the Table_array field, you need to enter the array you’re using for the HLOOKUP. Note that your criteria must appear at the top of the range. Select the range that is the scenario table at the top — in other words, B2:D4 — and the press F4 to lock the cell references. The cell references will change to $B$2:$D$4. In the Row_index_num field, enter the row number, 2. In the Range_lookup field, enter a zero or false, because you’re looking for an exact match. Check that your dialog box looks the same as the image below. Click OK. The formula in cell B9 is =HLOOKUP(‘IS Cash Flow’!B1,$B$2:$D$4,2,0) with the calculated result of 120. Perform the same action in cell B23 with the formula =HLOOKUP(‘IS Cash Flow’!$B$1,$B$2:$D$4,3,0). Instead of re-creating the entire formula again, simply copy the formula from cell B9 to cell B23 and change the row reference from 2 to 3. Copying the cell will change the formatting of the number, so you’ll need to change the currency symbol back to $ again. Go back to the IS Cash Flow worksheet and change the drop-down to Best. Check that your assumptions for average number of cups sold per day and monthly rent on the Assumptions worksheet have changed accordingly. Cups will have changed to 140 and rent to $1,080. Now, the important test is to see if the balance sheet still balances! Go back to the Balance Sheet worksheet and make sure that your error check is still zero. Test the drop-down again by changing it to Worst. Cups will have changed to 100 and rent will be $1,320. Check the error check on the Balance Sheet worksheet again. Congratulations! Your entirely integrated financial model, together with scenario analysis, is now complete! You can download a copy of the completed model in File 1002.xlsx.

Article / Updated 09-11-2017

Now that you’ve figured out how to fund your business, you need to make future assumptions in your financial model and project how the business will perform. You need to build a separate Assumptions worksheet with your business projections that will drive the rest of the financial model. The Assumptions worksheet already has places for your key business drivers — it’s up to you to input accurate and reasonable assumptions for the business. In modeling, all input variables should be formatted consistently (usually with blue font or using Input Style, which can be found on the Home tab in the Styles section of the Ribbon). Then anyone using this model knows that she can make changes to any of the cells formatted in that way. Revenue assumptions Based on your study of other cafes in the area (you’ve been drinking a lot of coffee the past few weeks, haven’t you?), you expect the following assumptions for your business’s revenue: You’ll sell an average of 120 cups of coffee per day throughout the year. Forty percent of coffees sold will be in large cups; 60 percent will be in small cups. You’ll charge $4 for a large cup of coffee and $3.50 for a small cup of coffee. These are your expectations for the business’s sales; they represent your base case revenue assumptions. You aren’t really sure whether you have the daily number of sales right — you’re just estimating — so you’ll adjust this number when you run the scenarios. You’ll address the best- and worst-case assumptions later on, after the base case is complete, so you can leave the cells in rows 3 and 4 blank for now. Go to the Assumptions page, and enter the business sales. The model has already been formatted for you, but when you make your own models, be careful to format correctly, expressing percentage values as percentages and using dollar signs to show dollar values. One of the most common errors in financial modeling is confusing units, such as treating a number of units as a price. Proper formatting makes any report or model clearer and easier to read for the user. Pay particular attention to formatting when using percentages. If a cell is formatted as a percentage, any figure entered will automatically be converted to a fraction. For example, if you enter the number 5 in any normal cell, the value is 5. But if the cell is formatted as a percentage, the number 5 will automatically be converted to a value of 0.05 (or 5 percent). This could potentially lead to incorrect calculations. Expense assumptions In your analysis, you’ve also researched the operating costs of running a cafe, which are the following: You think the rent expense will most likely be $1,200 per month. This is just an estimate, though — you’ll enter some potential fluctuations into the scenario analysis later on. Consumables — including coffee beans, cups, filters, and so on — will cost you $0.45 per cup. This amount has been averaged over both large and small cups, so you won’t need to distinguish between size for the purpose of this model. The barista’s salary is $50,000 per year, plus 25% in other staff costs and benefits. Monthly utilities, such as electricity, heat, and water, will cost $100 per month. The company income tax rate is 30 percent. These are your expectations for the business’s costs; they represent your base case expense assumptions. Scroll down to the Expense Assumptions section on the Assumptions worksheet, and enter the assumptions. Other assumptions Finally, you also have some other assumptions regarding the number of business days you’ll be open per month and how busy your cafe will be throughout the year, so you need to apply some seasonality because this will affect your cash flow. Follow these steps: Scroll down to the Other Assumptions section on the Assumptions worksheet, and based on next year’s calendar, complete row 32 for the number of business days per month. Based on historical seasonal and weather patterns, complete row 33 for the seasonality variance. Models must be well documented and assumptions must be clearly stated. A properly documented model will not only help you and others follow it, but also help users if you’re no longer around when a change needs to be made to the model. Including sources is handy for when you or someone else wants to go back and check the validity of your assumptions. Document assumptions and sources as you build the model. Documenting as you go is much easier than having to go back and do it at the end. After all, a model is only as good as its assumptions!

Article / Updated 09-11-2017

When you’re creating charts in financial models or reports, you should still follow best practice and try to make your models as flexible and dynamic as you can. You should always link as much as possible in your models, and this goes for charts as well. It makes sense that when you change one of the inputs to your model, this should be reflected in the chart data, as well as the titles and labels. Building the chart on formula-driven data Download File 0901.xlsx. Open it and select the tab labeled 9-23 to try it out for yourself. If you hide data in your source sheet, it won’t show on the chart. Test this by hiding one of the columns on the Financials sheet and check that the month has disappeared on the chart. You can change the options under Select Data Source so that it displays hidden cells. Linking the chart titles to formulas Because all the data is linked to the drop-down box, you can easily create a dynamic title in the chart by creating a formula for the title and then linking that title to the chart. Follow these steps: In cell A1 of this model, change the title to the following: =“Five Year Strategic Forecast Costs for Call Center - “&F1. The ampersand (&) serves as a connector that will string text and values from formulas together. Instead of the ampersand, you can also use the CONCATENATE function, which works very similarly by joining singular cells together, or the TEXTJOIN function is a new addition to Excel 2016, which will join together large quantities of data. When you have the formula in cell A1 working, you need to link the title in the chart to cell A1. Click the title of the chart. This part can be tricky. Make sure you’ve only selected the chart title. Click the formula bar. Type = and then click cell A1. Press Enter. The chart title changes to show what is in cell A1. You can’t insert any formulas into a chart. You can only link a single cell to it. All calculations need to be done in one cell and then linked to the title as shown. Creating dynamic text Take a look at the monthly budget report. We’ve already built formulas in columns F and G, which will automatically update as the data changes, and display how we’re going compared to budget. Now you’ll create a chart based on this data, and every time the numbers change, you’ll like to be able to see how many line items are over budget. Follow these steps: Highlight the data showing the account, actual, and budget values in columns B, C, and D, respectively. Select the first 2-D Column option from the Charts section of the Insert tab on the Ribbon to create a clustered column chart. In cell A1, create a heading with a dynamic date. Link the title of the chart to the formula in cell A1. Edit the chart so that the titles are horizontally aligned and change the colors. This chart will look much better if it’s sorted so that the larger bars are on the left side. Highlight all the data including the headings, and click the Sort button (in the Sort & Filter section of the Data tab in the Ribbon). The Sort dialog box appears. Sort by Actual from largest to smallest. It’s very easy to mess up formulas when sorting, so be sure that you highlight all the columns from columns A to G before applying the sort. Now, add some text commentary to the chart. You can do this by adding commentary in a single cell, which is dynamically linked to values in the model and link the cell to a text box to show the commentary on the chart. In cell A15, create a formula that will automatically calculate how many line items are over budget. You can do this with the formula =COUNTA(G3:G12)-COUNT(G3:G12), which calculates how many non-blank cells are in column G. You can see that two line items are over budget, so convert this to dynamic text with the formula =COUNTA(G3:G12)-COUNT(G3:G12)&” Items over Budget.” Insert a text box into the chart by pressing the Text Box button in the Text group on the Insert tab in the Ribbon. Click the chart once. The text box appears. Carefully select the outside of the text box with the mouse, just as you did in the last section when linking the chart titles. Now go to the formula bar and type =. Click cell A15 and click Enter. Resize and reposition the text box as necessary. Test the model by changing the numbers so that more items are over budget, and make sure that the commentary in the text box changes.

Article / Updated 09-11-2017

The major limitation of drop-down scenarios in financial modeling is that you can’t see multiple scenarios side-by-side. To show the cost per customer under different scenarios, you need to change the scenario drop-down box in cell F1 — but you’ll only be able to look at one scenario at a time. To add a data table that will allow you to see the cost per customer of all three scenarios side by side, follow these steps: Add the three scenario names — “Best Case,” “Base Case,” and “Worst Case” — below the Cost per Customer. Make sure that you spell the names correctly, and don’t add trailing spaces or the data table won’t work. Select cells C2:E2 and press Ctrl+C. Select cell A25, right-click, and select Paste Special → Transpose to paste the names in cells A25:A17 with exactly the same spelling. Highlight cells A24:F2. On the Data tab of the Ribbon, in the Forecast group, select Data Table under the What-If Analysis button to display the Data Table dialog box. Because the variable you’re changing is arranged in column A, you need to tell the Data Table dialog box where the original input is for the column, which is the Scenario cell in F1. Under the Column input cell field, select cell F1. You can only show one output in a data table, so you chose to show the cost per customer only. If you want to show other values, you need to create additional data tables. Now that you have the scenario results, they can be displayed in a line chart. The key message from this model can be seen in this chart. You can see that the cost per customer varies depending on the scenario, and the best case scenario doesn’t necessarily mean that you’ll experience a lower cost per customer. Because the data table needs to be arranged in a single block, you can’t insert a row above the scenario outputs to show that these are the results of the scenario analysis. You can change the formatting of row 24 and add the title “Scenario Analysis” in row 23 for clarity.

Article / Updated 09-11-2017

Scenario Manager is grouped together with Goal Seek and Data Tables in the What-If Analysis section of the Data tab. Being grouped with other tools that are so useful would lead you to believe that Scenario Manager is also a critical tool to know. However, despite its useful-sounding name and the good company it keeps, Scenario Manager is quite limited in its functionality and is as helpful as the name suggests! It’s therefore not frequently used by expert financial modelers. Setting up the model To demonstrate how to use Scenario Manager, let’s apply it to a simple loan calculation model. The theory behind loan calculations is quite complex, but fortunately, Excel handles loans quite easily. In the example shown below, you’ll see an interest rate calculator upon which you can test the sensitivity of monthly repayments to changes in interest rates and loan terms. Follow these steps: Download File 0801.xlsx, open it, and select the tab labeled 8-14, or simply set up the model with hard-coded input assumptions. In cell B11, type =PMT( and press Ctrl+A. The Function Arguments dialog box appears. The PMT function requires the following inputs: Rate: The interest rate. Nper: The number of periods over the life of the loan. Pv: The present value of the loan (the amount borrowed). Fv: The amount left at the end of the loan period. (In most cases, you want to pay the entire amount back during the loan period, so you can leave this blank.) Type: Whether you want the payments to occur at the beginning or the end of the period. (You can leave this blank for the purposes of this exercise.) Link the fields in the Function Arguments dialog box to the inputs in your model. The PMT function returns the annual repayment amount. Because you want to calculate the monthly repayment amount, you could simply divide the entire formula by 12, but because the interest is compounding, it’s more accurate to divide each field by 12 within the formula. So, the rate in the first field is converted to a monthly rate, and the number of periods in the second field is also converted to a monthly rate. Click OK. The formula is =PMT(B7/12,B9*12,B5). This function returns a negative value because this is an expense. For your purposes here, change it to a positive by preceding the function with the minus sign. Applying Scenario Manager Now you can use Scenario Manager to add some scenarios. You want to know the impact of changes in inputs on your monthly repayments. Follow these steps: On the Data tab, in the Forecast section of the Ribbon, click the What-if Analysis icon, and select Scenario Manager from the drop-down list. The Scenario Manager dialog box appears. Click the Add button to create a new scenario. The Add Scenario dialog box appears. Enter a name for the first scenario in the Scenario Name box (for example, Scenario One). Enter the cell references for the variable cells in the Changing Cells box. Separate each reference with a comma (if there is more than one), but don’t use spaces. You can also hold down the Ctrl key and click each cell in the spreadsheet to insert the references into the box. Click OK. The Scenario Values dialog box appears with the existing values (0.045 for the interest rate and 25 for the years). Click OK to accept these values as Scenario One. Click Add to add another scenario. The Add Scenario dialog box appears again. Enter a name for the second scenario in the Scenario Name box (for example, Scenario Two). Click OK. The Scenario Values dialog box appears again. Enter the variables’ values for this scenario (for example, 0.05 for the interest rate and 30 for the years). Click OK. You’re returned to the Scenario Manager dialog box. Follow Steps 7 through 9 again to create additional scenarios. After you’ve created all the scenarios, you can use the Scenario Manager to view each scenario by clicking the Show button at the bottom. The inputs are automatically changed to show the scenarios. Scenarios are sheet-specific, meaning they only exist in the sheet where you created them. So when you’re looking for the scenarios you’ve created, you have to select the correct sheet in the model.

Article / Updated 09-11-2017

The most commonly used method of building scenarios is to use a combination of formulas and drop-down boxes. In the financial model, you create a table of possible scenarios and their inputs and link the scenario names to an input cell drop-down box. The inputs of the model are linked to the scenario table. If the model has been built properly with all the inputs flowing through to the outputs, then the results of the model will change as the user selects different options from the drop-down box. Data validation drop-down boxes are used for a number of different purposes in financial modeling, including scenario analysis. Using data validations to model profitability scenarios Download File 0801.xlsx. Open it and select the tab labeled 8-1-start. The way this has been modeled, the inputs are lined up in column B. You could perform sensitivity analysis simply by changing one of the inputs — for example, change the customers per call operator in cell B3 from 40 to 45, and you’ll see all the dependent numbers change. This would be a sensitivity analysis, because you’re changing only one variable. Instead, you’re going to change multiple variables at once in this full scenario analysis exercise, so you’ll need to do more than tweak a few numbers manually. To perform a scenario analysis using data validation drop-down boxes, follow these steps: Take the downloaded model and cut and paste the descriptions from column C to column F. You can do this by highlighting cells C6:C8, pressing Ctrl+X, selecting cell F6, and pressing Enter. The inputs in cells B3 to B8 are the active range that drives the model and will remain so. However, they need to become formulas that change depending on the drop-down box that you’ll create. Copy the range in column B across to columns C, D, and E. You can do this by highlighting B3:B8, pressing Ctrl+C, selecting cells C3:E3, and pressing Enter. These amounts will be the same for each scenario until you change them. In row 2 enter the titles Best Case, Base Case, and Worst Case. Note that the formulas still link to the inputs in column B, as you can see by selecting cell C12 and pressing the F2 shortcut key. Edit the inputs underneath each scenario. You can put whatever you think is likely, but in order to match the numbers to those in this example, enter the values. Ignore column B for now. Now you need to add the drop-down box at the top, which is going to drive your scenarios. It doesn’t really matter where exactly you put the drop-down box, but it should be in a location that’s easy to find, usually at the top of the page. In cell E1, enter the title Scenario. Select cell F1, and change the formatting to input so that the user can see that this cell is editable. The easiest way to do this is to follow these steps: Click one of the cells that are already formatted as an input, such as cell E3. Press the Format Painter icon in the Clipboard section on the left-hand side of the Home tab. Your cursor will change to a paintbrush. Select cell F1 to paste the formatting. Format Painter is normally for single use. After you’ve selected the cell, the paintbrush will disappear from the cursor. If you want the Format Painter to become “sticky” and apply to multiple cells, double-click the icon when you select it from the Home tab. Now, in cell F1, select Data Validation from the Data Tools section of the Data tab. The Data Validation dialog box appears. On the Settings tab, change the Allow drop-down to List, use the mouse to select the range =$C$2:$E$2, and click OK. Click the drop-down box, which now appears next to cell F1, and select one of the scenarios (for example, Base Case). Applying formulas to scenarios The cells in column B are still driving the model, and these need to be replaced by formulas. Before you add the formulas, however, you should change the formatting of the cells in the range to show that they contain formulas, instead of hard-coded numbers. Follow these steps: Select cells B3:B8, and select the Fill Color from the Font group on the Home tab. Change the Fill Color to a white background. It’s very important to distinguish between formulas and input cells in a model. You need to make it clear to any user opening the model that the cells in this range contain formulas and should not be overridden. Now you need to replace the hard-coded values in column B with formulas that will change as the drop-down box changes. You can do this using a number of different functions; an HLOOKUP, a nested IF statement, an IFS, and a SUMIF will all do the trick. Add the formulas by following these steps: Select cell B3, and add a formula that will change the value depending on what is in cell F1. Here is what the formula will be under the different options: =HLOOKUP($F$1,$C$2:$E$8,2,0) Note that with this solution, you need to change the row index number from 2 to 3 and so on as you copy the formula down. Instead, you could use a ROW function in the third field like this: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0) =IF($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3)) =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3) =SUMIF($C$2:$E$2,$F$1,C3:E3) As always, there are several different options to choose from and the best solution is the one that is the simplest and easiest to understand. Any of these functions will produce exactly the same result, but having to change the row index number in the HLOOKUP is not robust, and adding the ROW may be confusing for a user. The nested IF statement is tricky to build and follow, and although the new IFS function is designed to make a nested IF function simpler, it’s still rather unwieldy. The SUMIF is quite simple to build and follow, and it’s easy to expand if you need to add extra scenarios in the future. Note that IFS is a new function that is only available with Office 365 and Excel 2016 or later installed. If you use this function and someone opens this model in a previous version of Excel, she can view the formula, but she won’t be able to edit it. Copy the formula in cell B3 down the column. By using an ordinary copy and paste, you’ll lose all your formatting. It’s important to retain the formatting of the model so that you can see at a glance which inputs are in dollar values, percentages, or customer numbers. Use Paste Formulas to retain the formatting. You can access it by copying the cell onto the clipboard, highlighting the destination range, right-clicking, and selecting the Paste Formulas icon to paste formulas only, and leave the formatting intact. Now for the fun part! It’s time to test the scenario functionality in the model. Click cell F1, change the drop-down box, and watch the model outputs change as you toggle between the different scenarios.

Article / Updated 09-11-2017

The COUNT function, as the name suggests, counts. Although this sounds pretty straightforward, it’s actually not as simple as it seems and, for this reason, the COUNT function is not as commonly used as the very closely related COUNTA function. The COUNT function only counts the number of cells that contain numerical values in a range. It will completely ignore blank cells and any cells within the range that don’t contain numerical values, such as text. For this reason, the COUNT function is used only if you specifically want to count the numbers only. Calculating a full-year projection using COUNT functions Let’s try calculating a full-year projection using the COUNT and COUNTA functions. For example, say you have only ten months of data, and you want to do a full-year projection for your monthly budget meeting. You can calculate how many months’ worth of data you have by using the formula =COUNT(B2:B13), which will give you the correct number of elapsed months (10). To insert the function, you can either type out the formula in cell B15, or select Count Numbers from the drop-down list next AutoSum button on the Home tab or the Formulas tab. Note that the COUNTA function would work just as well in this case, but you particularly want to count only numbers, so you should stick with the COUNT function this time. Try adding a number in for November, and notice that the elapsed months changes to 11. This is exactly what you want to happen because it will automatically update whenever you add new data. In cell B16, calculate the average monthly amount of inventory for the months that have already elapsed. You can do this using the formula =B14/B15. Then you can convert this number to an annual amount by multiplying it by 12. So, the entire formula is =B14/B15*12, which yields the result of 81,520. You can achieve exactly the same result using the formula =AVERAGE(B2:B13)*12. Which function you choose to use in your model is up to you, but the AVERAGE function does not require you to calculate the elapsed number of months as shown in row 15 in the image above. It’s a good idea to see the number of months shown on the page so you can make sure the formula is working correctly. Calculating headcount costs with the COUNT function Let’s take a look at another example where the COUNT function can be useful. I often use the COUNT function to calculate headcount in a budget as it’s entered. For a practical example of how to use the COUNT function as part of a financial model, follow these steps: Download File 0701.xlsx, open it, and select the tab labeled 7-15 or enter and format the data. In cell B17, enter the formula =COUNT(B3:B14) to count the number of staff in the budget. You get the result of 9. Again, the COUNTA function would’ve worked in this situation, but I specifically wanted to add up only the number of staff for which I have a budget. Try entering TBD in one of the blank cells in the range. What happens? The COUNT function doesn’t change its value because it only counts cells with numerical values. Try using the COUNTA function instead (with TBD still in place in one of the formerly blank cells). The result changes from 9 to 10, which may or may not be what you want to happen. After you’ve calculated the headcount, you can incorporate this information into your technology budget. Each of the costs in the budget is a variable cost driven by headcount. Follow these steps: In cell F3, enter the formula =E3*B17. This formula automatically calculates the total cost of all laptops based on the headcount numbers. Because you want to copy this formula down, you need to anchor the cell reference to the headcount. Change the formula to =E3*$B$17 by using the F4 shortcut key or typing in the dollar signs manually. Copy the formula down the range, and add a total at the bottom.

Article / Updated 09-08-2017

Knowing how the discounted cash flow (DCF) valuation works is good to know in financial modeling. The core concept of the DCF is that of the basic finance concept of the time value of money, which states that money is worth more in the present than the same amount in the future. In other words, a dollar today is worth more than a dollar tomorrow. The idea behind the time value of money is that if you have to wait to receive your funds, you’re missing out on other potential investment opportunities, not to mention the risk of not receiving the money at all. If you have to wait to get your money, you’d expect some compensation — hence, the concept of interest. For example, if you invest $100 at a 10 percent annual interest rate today, it will be worth $110 in one year. Conversely, $110 in one year would only be worth $100 today. In this example, the 10 percent is referred to as the discount rate. As the name suggests, the discount rate is a key input you need to calculate the DCF. A DCF valuation uses a modeler’s projections of future cash flow for a business, project, or asset and discounts this cash flow by the discount rate to find what it’s worth today. This amount is called the present value (PV). Excel has a built-in function that automatically calculates PV. If you want to know the math behind the function, here’s how to calculate PV, where CF is the cash flow for the projected year, r is the discount rate, and n is the number of years in the future: In the case of a project or asset with finite cash flows, the modeler would forecast all the cash flows and discount them to find the present value. For example, if you were purchasing an asset such as a large piece of machinery that has an expected life of ten years, you would model the entire ten years. When purchasing a business, however, which is expected to be a going concern and the cash flows are expected to continue into perpetuity, a DCF analysis must find a terminal value at the end of a certain forecast period instead of forecasting cash flows into eternity. The terminal value represents the projected value of the company or asset at the end of the forecast period. Forecast periods are typically projected to the point at which cash flows are expected to grow at a stable and predictable rate. When the cash flows become stable, you can then reasonably estimate a fair value for the steady cash flows that are earned after that point in time. There are variations of the DCF analysis in which the cash flows, discount rates, and terminal values can differ, but the most common method is to project free cash flow to firm, find a terminal value using the perpetuity growth method, and discount these values by the business’s weighted average cost of capital. Download the file File 1101.xlsx, open it, and spend a few minutes reviewing and familiarizing yourself with the financial statements. To perform a DCF analysis, you need several years of financial statements, which are provided in this new model.