Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
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.
Just as you can compare the income statements of two differently sized companies, you can common size entries on their balance sheets to allow for meaningful analysis between those companies. One useful balance sheet element to use as a basis for common sizing is the current liabilities account. The Current Liabilities Account includes account payable, income taxes, short-term unearned revenue, securities lending payable and the even popular, other. Accounts payable is a measure of how much money is owed to creditors for products and services and securities lending payable shows how much money is owed to creditors who purchase bonds, and other debt instruments issued by the company.
In this case, I'm going to use the Total current liabilities as my Common-sizing factor and I'll be comparing the companies' assets. So first we have the Total cash, cash equivalents, and short-term investments. We have Total current assets which includes the cash, but also includes other items that can be converted to cash within a year and then Total assets which includes things such as buildings and other assets that can't be converted readily into cash. So, to create the formula, I'll divide the value in B9 by the value of the Total current liabilities in B7 and again, when I copy the formula, I don't want the cell reference to B7 to change.
I want it to remain absolute, so I'll press F4 and that makes the cell reference an absolute reference. Press Enter and I'll express these as percentage with two decimal points and I'll copy the formula down, so that we've the Total current assets and Total assets also expressed as a percentage of the Total current liabilities. Here we have Company B, again, it's significantly smaller.
But when we perform the analysis the common sizing, we take F9, divide that by the Total current liabilities for the company, which are in F7. Again, pressing F4, say the cell reference doesn't change when we copy the formula to another cell. Press Enter and again, I'll format these as percentages adding two decimal points and copy the formula down. So in this case, these two companies have a lot of cash on hand and also lot of assets in relation to their total current liabilities.
The higher the ratio between a company's assets and its liabilities the better shape it's in, because the better off it is in terms of what it owes its creditors. Common-sizing a balance sheet enables you to determine how well a company is managing its debt. Both the members of its supply chain and the creditors who bought bonds and notes. That said, you should always check a company's guidance and explanations in its quarterly or annual report, so you have a context for the raw numbers.
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.