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.
The name of the Average Collection Period ratio is a bit misleading. After all, how can an analyst accurately measure how long on average it takes a company to collect on its debts without having access to data on every transaction? The answer, of course, is that they can't, but you can discover useful information about a company's collection from its public data. The Average Collection Period ratio is calculated in two parts. In the first step, you divide Total Sales by Accounts Receivable, which is the amount yet to be paid for completed sales to calculate the Receivables Turnover ratio.
So on this worksheet, we have Total Sales, Accounts Receivable and Days in Year, which we'll use later. So to calculate the Receivables Turnover ratio, it's equal B5 divided by B6 and there you have that ratio and again, it tells you how many times in a year a company earns the money currently owed by its creditors. You can compare the Receivables Turnover ratio to the Times Interest Earned ratio, which I'll cover elsewhere in this course. That ratio calculates the number of times a year a company earns enough money to cover its interest payments on its debt.
In the second step, you divide 365, the number of days in a year by the result of the first step, the Receivables Turnover ratio. You treat the result as a number of days, which you interpret as the Average Collection Period. So here we have the days in the year, which are in cell B7, and you divide that by the Receivables Turnover ratio, B9. Press Enter and you have an Average Collection Period of just over 80 days. A company's Average Collection Period provides an indirect measure of its health by providing insight into that company's credit granting policies.
If a company's average collection period is higher than that of other companies in the same sector, it could indicate that its customers are running into financial difficulties and therefore pay more slowly. Finally, I would like to point out that analysts differ on how they calculate a company's Average Collection Period. So don't be surprised if you are asked to use a different formula. As long as you use the same formula consistently, your results will be useful.
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.