Start learning with our library of video tutorials taught by experts. Get started
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.
Like private individuals, companies can borrow money to finance their operations. Some mechanisms to borrow money include taking out loans, issuing stock, and selling bonds to raise capital. Also, like private individuals, companies can only borrow so much money before they start to become bad credit risks. One basic measure of a company's relative indebtedness is the debt ratio. To calculate the debt ratio, which is also some times called the total debt ratio, you subtract a company's total equity from total assets, and divide that result by total assets. So, I have a worksheet here, with Shareholders' Equity, Total Assets, and Total Liabilities, which we'll use in a moment.
Now, before I type in the debt ratio formula, I'd like to point out that I have a copy of the formula here in cell C9. You can see the formula up here in the formula bar. Now, the reason that Excel doesn't treat this formula as a formula, it displays it as text, is because I have an apostrophe here at the start of the entry. Anytime a cell's contents begin with an apostrophe, Excel treats the value as text, regardless of whether it's a number, a currency value, and date, whatever. It just treats it as a string of characters.
So, that's the formula I'll be entering in, without the apostrophe. So Excel treats it as a formula. We have =B6-B5, which is again assets minus equity, divided by B6. Now, because the two values that Excel was using in this formula, B6 and B5, are both currency values, it attempted to display the result as a currency value here, except it doesn't make any sense because it's a ratio. So, I'll change that cell's formatting to a number. Now, we see a debt ratio of 0.39, which is correct.
There are two useful ways you can extend the debt ratio analysis. The first is by dividing the company's total liabilities by its total equity, which gives you the debt-to-equity ratio. The debt-to-equity ratio is an extremely useful tool for analyzing a company's health. The rule of thumb is that manufacturing firms should maintain a debt-to-equity ratio of between 0.5 and 1.5. If a company borrows too much money, it can run into problems servicing its debt. On the other hand, a company that doesn't borrow any money or not enough money might be missing out on opportunities because it's being too conservative.
So, to calculate the debt-to-equity ratio, we divide the company's total liabilities, which are in B7 by its shareholders' equity in B5, press Return, and there is the result. You can also calculate a measure called the Equity Multiplier, which is total assets divided by total equity. The equity multiplier shows a company's total assets per dollar of shareholders' equity. A higher equity multiplier indicates higher financial leverage, which means the company is relying more on debt to finance its assets. So, to calculate the equity multiplier, we divide total assets by shareholders' equity, and there's the result.
The debt ratio, debt to equity ratio, and equity multiplier are all based on the same factors. If you can calculate any one of those ratios, you can calculate the other two.
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.