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.
A company's income statement summarizes the company's operating expenses, total income and deducts the provision for income taxes. You can compare two companies results by dividing the component parts of Operating expenses, Cost of revenue, Research and development, Sales and marketing, and General and administrative, by the Total operating income from the year. In the worksheet that you see in front of you, I pulled some results from the Microsoft 2008 Corporate Annual Report and specifically, I have the Operating expenses and what I would like to do is compare them to Operating Income.
So I want to point out that unlike the analysis that I did in the previous lesson, these factors here, these items do not add up to the total on Operating income. Basically operating income is the net profit or loss that a company makes based on its operations. It's a function of its total revenue, so don't be thrown off by the fact that these number don't add up. So to calculate the percentage of Operating Income, again we're using it as a ratio. Type =B6, which contains the value for the Cost of revenue divided by B11, the Operating Income.
And again, when I copy the formula further down the column, I want the cell reverence B11 to remain constant, so I press F4 to make it an absolute reference. Press Enter and we see that Cost of revenue is 51.57% of Operating Income. And I'll just copy that down. You'll see that in this case, Total operating expenses were higher than the Operating Income. But that's not unusual. Say a company has an Operating revenue of $100 million in a year and it has total operating expenses of 80 million.
They still have made $20 million on their operations. So they're in the black. The only question is whether they can reduce their Operating expenses or apply the spending more effectively so that Operating Income goes up. If you wanted to compare two companies, one like Microsoft over here which has an extremely high operating revenue and cost associated with that revenue, you would type in the formula = and the value is in F6 divided by the value in F11 and again, we don't want this all referenced to F11 to change when we copy the formula, so you press F4, to turn it into an absolute reference and press Enter and there your have your result.
And I'll copy the formula down here and now you're able to compare the two companies performance in terms of Operating Income, based on their expenses for Cost of revenue, Research and development, Sales and marketing, and so on. Common-sizing a company's income statement enables you to determine what portion of the company's expenses go to which components of its cost. If one company has a much larger percentage of general and administrative cost for example, you might want to hold off investing in that company, until it finds efficiencies in that area, freeing up more resources for product development and growth.
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.