Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
Financial statements provide vital information to potential investors. But most industries have competitors of various sizes and sales levels. The computer software industry, for example, has competitors including multi-billion dollar corporations, all the way down to smaller shops that produce specially products for limited markets. Comparing sales figures, income and other financial aspects of these diverse companies directly isn't always helpful. But you can perform meaningful analysis on the financial statements of two separate companies using a mathematical technique known as Common-Sizing. Common sizing is the practice of treating financial performance in terms of another factor such as company size or market share.
You can common-size a company sales figures, for example, by dividing components of those sales figures by the cost of good sold or buying that sales. As an example, I have a worksheet with information from two companies, just hypothetical companies of different sizes but within the same industry. As you can see, Company A with Operating Revenues of over 4 billion is significantly larger than Company B which is still impressive but below a billion at 685 million dollars of operating revenue per year.
So the question is, how do you compare the two companies performance? And once again, the idea is the common-size. So in this example, we'll take these operating cost and expenses such as general operations, general administrative, depreciation and so on, and express them as a percentage of operating revenues. So the way that you create that formula, type an equal sign and then divide the value, in this case B8, the operating cost by the value in B6, which are the operating revenues.
Now I haven't pressed Enter yet, and that's because I don't want the reference to change when I copy the formula to other cells in this column. So I'll press F4, which makes the reference into an absolute reference, and then press Enter. And there you have it. This is about 45% of this value, so it looks like the calculation is correct. And I'll copy the formula down to the remaining cells in the column and you see the values. Now we can do the same thing for Company B.
So we have cell D8 that are the operating expenses for Company B, divided by the operating revenues, which are in D6. Again pressing F4 to make D6 into an absolute reference, so it won't change when we copy the formula and press Enter. And we can see the Company B's operating expenses are a little bit more but very comparable to those of Company A . So to calculate the percentage of revenue for the other expenses, we just copy the formula down the column and there you have it.
We are getting a value error in cell E12, because there is no value or a zero value in cell D12. That's simply just a function of the data in the worksheet and it's not an error in the formula. So it's nothing to worry about. You can just treat it as a null value. As we can see, Company B is significantly more efficient in terms of its selling the general and administrative cost, those are only 27% of their operating revenue. And the two companies are pretty similar, within a percentage point on amortization of intangible assets.
But it looks like Company B has significantly more depreciation of property plans and equipment, and that could be due to a couple of factors. The equipment could be newer and the sum total of the depreciation of all of that equipment is adding up and allows for a significant expense this year. You do need to be careful when you perform a common-sizing operation to ensure that your analysis make sense within the context of an industry. For the software industry, for example, you have relatively low production cost, so the cost of good sold might not be the best to use in your analysis.
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.