When done, close browser tab or window.
Start video.
The video opens to show an Excel spreadsheet that displays a table for Given Information and a blank space for Answers.
Narrator: "In this example, we are provided the earnings and dividends of a company during its first four years of operations. Our goal is to calculate the balance of retained earnings at the end of each year."
The Excel spreadsheet shows columns from A to H and rows from 1 to 12. The table starts from column B and row 4. The table is labeled "Given Information: super asterisk." Super asterisk indicates insert a different amount in any of the blue cells in the given information to see alternative calculations.
The table has the column headings Net Income and Dividends in columns C and D. Four rows for Years 1, 2, 3, and 4 are shown. The cells for each column and row are blank and highlighted in blue.
The blank space for Answers to find Retained Earnings is given in column G and rows 4 to 8.
Narrator: "The balance of Retained Earnings is the total of net income earned over the life of the company that has not been distributed as dividends. For the first year of operations, the ending balance of Retained Earnings is simply the difference between net income and dividends in Year 1. In Excel, this is calculated as cell C5 minus cell D5, which results in a Retained Earnings balance of $8,000. For the second year of operations, the year-end balance of Retained Earnings is calculated as the sum of net income in Years 1 and 2, minus dividends paid in Years 1 and 2. To calculate this amount in Excel, take the sum of cells C5 and C6, and deduct the sum of cells D5 and D6. This results in a Retained Earnings balance of $3,000. The ending balance of Retained Earnings in Year 3 is the sum of net income earned during Years 1 to 3, minus the sum of dividends paid during the same period of time. In Excel, we can take the sum of cells C5 through C7, and subtract the sum of cells D5 through D7. The resulting value is $18,000. Similarly, at the end of Year 4, the balance of Retained Earnings is the sum of net income for Years 1 to 4, minus all dividends paid during the same four-year period. In Excel, this is calculated as the sum of cells C5 through C8, minus the sum of cells D5 through D8. The resulting difference is $58,000."
The visual shows the values for the Given Information. The table is as follows:
| Net Income | Dividends | |
|---|---|---|
| Year 1 | $10,000 | $2,000 |
| Year 2 | negative 5,000 | 0 |
| Year 3 | 20,000 | 5,000 |
| Year 4 | 50,000 | 10,000 |
The value of Retained Earnings for Year 2 is calculated by selecting the corresponding values for Net Income and Dividends. The value 3,000 is displayed in G6. Similarly, the value of Retained Earnings for Year 3 and Year 4 are calculated. The value 18,000 is displayed in G7 and 58,000 in G8.
Narrator: "Now, let's consider what would happen if, in Year 4, the company instead reports a net loss of $50,000 and distributes no dividends. Excel makes it quick and easy to analyze the impact of this change. To do so, let's enter the revised values of a $50,000 net loss, and $0 in dividends paid. Using the formulas that we entered earlier, Excel automatically calculates that, as of the end of Year 4, the company's Retained Earnings account has a negative balance of $32,000."
The visual continues to show the table for Given Information and Answers as discussed in Description 3. The following text is shown below the table: What is the balance of Retained Earnings if the company instead reports a net loss of $50,000 and distributes no dividends in Year 4?
The value of Net Income for Year 4 is changed to negative 50,000, and the value of Retained Earnings for Year 4 changes to negative 42,000. Then, the value of Dividends for Year 4 is changed to 0, and the value of Retained Earnings for Year 4 changes to negative 32,000. The value negative 32,000 is highlighted.
Video has ended.
Described transcript ©2024 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.