Financial Modeling in Excel For Dummies book cover

Financial Modeling in Excel For Dummies

Overview

Turn your financial data into insightful decisions with this straightforward guide to financial modeling with Excel 

Interested in learning how to build practical financial models and forecasts but concerned that you don’t have the math skills or technical know-how? We’ve got you covered! Financial decision-making has never been easier than with Financial Modeling in Excel For Dummies. Whether you work at a mom-and-pop retail store or a multinational corporation, you can learn how to build budgets, project your profits into the future, model capital depreciation, value your assets, and more. 

You’ll learn by doing as this book walks you through practical, hands-on exercises to help you build powerful models using just a regular version of Excel, which you’ve probably already got on your PC. You’ll also: 

  • Master the tools and strategies that help you draw insights from numbers and data you’ve already got 
  • Build a successful financial model from scratch, or work with and modify an existing one to your liking 
  • Create new and unexpected business strategies with the ideas and conclusions you generate with scenario analysis 

Don’t go buying specialized software or hiring that expensive consultant when you don’t need either one. If you’ve got this book and a working version of Microsoft Excel, you’ve got all the tools you need to build sophisticated and useful financial models in no time! 

Turn your financial data into insightful decisions with this straightforward guide to financial modeling with Excel 

Interested in learning how to build practical financial models and forecasts but concerned that you don’t have the math skills or technical know-how? We’ve got you covered! Financial decision-making has never been easier than with Financial Modeling in Excel For Dummies. Whether you work at a mom-and-pop retail store or a multinational corporation, you can learn how to build budgets, project your profits into the future, model capital depreciation, value your assets, and more. 

You’ll learn by doing as this book walks you through practical, hands-on exercises to help you build powerful models

using just a regular version of Excel, which you’ve probably already got on your PC. You’ll also: 

  • Master the tools and strategies that help you draw insights from numbers and data you’ve already got 
  • Build a successful financial model from scratch, or work with and modify an existing one to your liking 
  • Create new and unexpected business strategies with the ideas and conclusions you generate with scenario analysis 

Don’t go buying specialized software or hiring that expensive consultant when you don’t need either one. If you’ve got this book and a working version of Microsoft Excel, you’ve got all the tools you need to build sophisticated and useful financial models in no time! 

Financial Modeling in Excel For Dummies Cheat Sheet

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.

Articles From The Book

38 results

Excel Articles

How to Calculate Revenue in Your Financial Model

It is always important to document your assumptions when preparing a financial model. Once you’re happy with your assumptions, you can use them to calculate the revenue of the business for the next year.

Projecting sales volume

You know that your base case assumption is that the cafe will sell 120 cups of coffee per day, so you need to multiply this assumption by the monthly seasonality to arrive at the number of cups sold per day in each month. Follow these steps:
  1. Go to the Revenue worksheet and select cell B5.In this cell, you’re going to enter a formula to calculate the total number of cups of coffee.
  2. Type =.
  3. Go to the Assumptions worksheet and select cell B9.
  4. Press F4 to lock the reference.You need to anchor this reference because as you copy the formula across, you don’t want B9 to change to another cell.
  5. Stay on the Assumptions worksheet and multiply this reference by the monthly seasonality assumption by typing * and selecting cell B34.There is no need to anchor the seasonality reference because you want the reference to change as you copy it along the row.
  6. Press Enter to finish the formula.Your formula will look like this: =Assumptions!$B$9*Assumptions!B34. The calculated result is 96.
  7. Copy this formula across the row by selecting cell B5, pressing Ctrl+C, selecting cells C5 through M5, and pressing Ctrl+V or Enter.
You have the total number of cups sold per day. Now you need to project how many of these cups are large and how many are small based on your assumptions. You’re going to use the calculated value of 96 and split it into large and small cups, based on your assumed split between large and small on the Assumptions worksheet. Follow these steps:
  1. On the Revenue worksheet, select cell B6 and type =.
  2. Go to the Assumptions worksheet, select cell B12, and press F4 to lock the reference.
  3. Multiply this value by typing *.
  4. Go back to the Revenue worksheet and select cell B5.
  5. Press Enter to finish the formula.Your formula will look like this: =Assumptions!$B$12*Revenue!B5. The calculated result is 3.
  6. Copy this formula across the row to calculate this for the entire year.You’re going to repeat this process to find the number of small cups.
  7. On the Revenue worksheet, select cell B7 and type =.
  8. Go to the Assumptions worksheet, select cell B13, and press F4 to lock the reference.
  9. Multiply this value by typing *.
  10. Go back to the Revenue worksheet and select cell B5.Your formula will look like this: =Assumptions!$B$13*Revenue!B5. The calculated result is 58.
  11. Copy this formula across the row to calculate this for the entire year.
  12. On the Revenue worksheet, select cell B8 and enter the formula =SUM(B6:B7).If you prefer, you can use the AutoSum function or the shortcut Alt+=. The calculated result is 96.
  13. Copy this formula across the row to calculate this for the entire year.
  14. Perform a sense-check by highlighting both cells B6 and B7.If you look at the status bar, the SUM will equal 96, the total number of cups sold per day. Go one step further than sense-checking and add an error check in row 9.
  15. In cell B9, enter the formula =B8-B5 and copy it across the row.

Always sense-check your numbers as you build a model. Don’t leave it to the end to check your numbers. Never take the number given for granted. Work it out in your head and use a calculator to make sure your numbers look right. This will help you make sure the numbers you’ve calculated are correct. When you’re sure the numbers are right, add in an error check if you can just like you did in row 9. A good financial modeler is always looking for opportunities to put error checks into their models.

Projecting dollar sales

Now that you’ve projected how many cups and sizes you’ll sell per day in each month, it’s time to translate this into actual dollar sales figures. Follow these steps:
  1. On the Revenue worksheet, select cell B12 and multiply B6 (the number of large cups sold per day in January) by the price per large cup with the formula =B6*Assumptions!$C$12.The calculated result is $154.
  2. Copy this formula across the row to calculate this for the entire year.
  3. On the Revenue worksheet, select cell B13 and multiply B7 (the number of small cups sold per day in January) by the price per small cup with the formula =B7*Assumptions!$C$13.The calculated result is $202.
  4. Copy this formula across the row to calculate this for the entire year.
  5. Add the sum total in cell B14 with the formula =SUM(B12:B13)The calculated result is $355.
  6. Copy this formula across the row to calculate this for the entire year.You now have sales per day figures for large cups, small cups, and total cups for each given month. In order to find each month’s total sales, you must multiply daily sales by the number of business days per month.
  7. On the Revenue worksheet, select cell B15 and multiply the daily sales figure in cell B14 by the number of business days in January with the formula =B14*Assumptions!B32.Note that you don’t need to press F4 to add any cell referencing because you want the cell references to copy across. The calculated result is $7,814.
  8. Copy this formula across the row to calculate this for the entire year.You’ve now projected your monthly sales for the year! Check your totals against this model.

Excel Articles

Discounting Cash Flows and Valuation in Your Financial Model

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. Select cell B28 and enter 5,000.
  7. 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.

Excel Articles

Building Scenarios in Your Financial Model

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:
  1. Go to the IS Cash Flow worksheet and select cell B1.
  2. Select Data Validation in the Data Tools section of the Data Ribbon. The Data Validation dialog box appears.
  3. 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.
  4. 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.
  5. Click OK.
  6. 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.
  7. 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:
  1. Select cell B9 and press the Insert Function button on the Formulas tab or next to the formula bar.
  2. Search for HLOOKUP, press Go, and click OK. The HLOOKUP dialog box appears.
  3. 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.
  4. 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.
  5. 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.
  6. In the Row_index_num field, enter the row number, 2.
  7. In the Range_lookup field, enter a zero or false, because you’re looking for an exact match.
  8. Check that your dialog box looks the same as the image below.
  9. 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.
  10. 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.

  11. 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!
  12. Go back to the Balance Sheet worksheet and make sure that your error check is still zero.
  13. 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.