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.
One of the most basic measures of profitability is how much money a company makes on each item it sells. You can analyze a company's profit by subtracting the cost of goods sold from total revenue and then dividing that result by total sales revenue. The result is the gross profit ratio. A company's gross revenue ignores all expenses except the cost of goods sold. In industry lingo, gross revenue is Earnings Before Interest, Taxes, Depreciation, and Amortization or EBITDA. So here in the worksheet, you can see that we have the Sales Revenue and the Cost of Goods Sold.
So again what we're going to do is subtract cost of goods sold from sales revenue and then divide it by sales revenue to get the gross profit margin. So = (B5-B6/B5), press Enter, and there is our ratio, 0.56. You can calculate a company's gross profit margin for all of its products, but you can also break the numbers down by product to get an idea of how efficiently a product contributes to the company's bottom line. Some companies follow what's called a Loss Leader Strategy, where they sell one item such as a razor at a loss, because the ancillary products such as razor blades generate substantial profits.
Another example of this Loss Leader Strategy comes from the video game console market. Companies often sell their consoles at a loss and make up the difference in game sales. In those cases it might make sense to consider the products as a group and not individually. Calculating a company's gross profit margin tells us how effectively the company earns money with its products, but it ignores expenses such as interest, depreciation and taxes. Because investors need to know more about a company's finances than simply the amount of money it makes on each sale, you can calculate the Net Profit Margin, which takes interest, tax, depreciation and amortization expenses into account.
Because of that, it's a true measure of a company's ability to generate cash in excess of its expenses. To calculate a company's net profit margin, you subtract all interest, tax, depreciation and amortization expenses from the company's sales revenue and then divide the result by sales revenue. That also includes cost of goods sold. So on this worksheet, we have the sales revenue and then we have all of the cost, not just cost of goods sold. So to calculate the Net Profit Margin, we have a formula that subtracts all of the cost from B5, the sales revenue.
So we have sum of B6-B10, and these are the costs, and divide that result by the sales revenue, which is in cell B5. The result is the Net Profit Margin, which reflects a company's true ability to generate profits. Calculating the Net Profit Margin for individual products can generate different results based on how the company assigns its overhead cost. For example, a company might need to take out a loan so they can manufacture a product, in which case the company can assign that facility's interest, depreciation and amortization expenses to the product's account.
In many cases though, the company will spread the burden of taxes, interest, depreciation and amortization across all of its product lines. Also, it's not unusual for a company's gross and net profit margins to vary by season. Many retail businesses rely on brisk holiday sales to turn their profit for the year. Companies with consistently high profit margins relative to other companies in their market sector sell products efficiently and keep their operating expenses in check. The cash they generate can be distributed to shareholders as dividends making the company more attractive to investors or be used for research and development to enhance their products.
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.