Numbers and financial data drives today's business world and Excel 2007: Financial Analysis can help decode this information. The proper understanding of these numbers, and the formulas behind them, can be the gateway to corporate and personal success. Microsoft MVP (Most Valuable Professional) Curt Frye teaches basic fluency in corporate finance, enabling users to see the meaning behind essential financial calculations. Curt explains how to review formulas to ensure they have the proper inputs, and shows how to interpret formula output. He also covers how to calculate leverage ratios and amortization and depreciation schedules, as well as forecast future growth. Exercise files accompany this course.
Sometimes you'll look over a formula, verify that it uses the proper inputs, but still find that it returns an incorrect result. If that's the case, you can move to the formula, with one calculation at a time to identify the problem. Some of the tactics you can use are setting up the formula with inputs that you know will generate a specific result. If you don't get the result you expect, then you know there is a problem with the formula. Then you can step through the formula with one calculation at a time, I'll show you how in a moment, attempt to identify at which point the formula generates an incorrect result while you are stepping through it and once you've identified the problem, edit the formula and step through it again to ensure that you're getting the right result for the right reasons.
As an example of a formula that you might evaluate, take a look at the formula in cell B7, which is Monthly Payment. Now we have a $245,000 loan with a 6.9% interest rate being paid off over three years. There is no way that you would have a Monthly Payment of $0. It just doesn't happen. So, how do you find the error in the formula? To do that, you can go to the Evaluate Formula dialog box. So, I click the cell that contains the formula I want to evaluate and then on the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
The Evaluate Formula dialog box displays the formula and allows you to go through and determine the value step by step. So, we'll start out by clicking Evaluate and it pulled the value from B4, which is 6.9% or 0.069. Next step is to divide that by 12, with the result you see here and when I click Evaluate again, it will display the number of years, which is in cell B5. Click Evaluate again. You get 36, which is the number of payments.
You made 12 payments per year, over three years, that's 36. Then you have the Loan Principal and here's where we see the error. Instead of a Loan Principal here in cell B3, I have $245,000, it's 0. So, I have already found the error but just to show you how it works, when you evaluate the rest of the formula, I'll click Evaluate again and you get a value of $0. So, I'll click Close and edit this formula so that the Principal is pulled from cell B3 as opposed to cell B2. Press Return and we get the right result.
I won't go through all the steps of the formula again, but you could use the Evaluate Formula dialog box to determine that you were getting the proper result for the right reasons. Evaluating Excel formulas is a powerful technique you can use to verify that a formula produces the correct result. You can also use formula evaluation to understand how a formula's logic works. If you encounter a formula that uses a function you haven't used before, take the time to look at the function in the Excel Help files and step through the formula so you'll have a better idea of why the formula is in the workbook and how it derives its result.
There are currently no FAQs about Excel 2007: Financial Analysis.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.