Spreadsheet Analysis Using ExcelAnnette’s Accessories had the following stock issued and o...

Spreadsheet Analysis Using Excel

Annette’s Accessories had the following stock issued and outstanding at January 1, 2011

.150,000 shares of $1 par common stock

10,000 shares of $50 par, 8%, cumulative preferred stock

On March 5, 2011, Annette’s declared a $100,000 cash dividend to be paid March 31 to shareholders of record on March 21.

Required

Set up a spreadsheet to calculate the total amount of dividends to be paid to preferred and common shareholders under the following alternative situations:

a. No dividends are in arrears for preferred shareholders.

b. One year’s worth of dividends is in arrears for preferred shareholders.

c. Two years' worth of dividends is in arrears for preferred shareholders.

d. Instead of a $100,000 dividend, Annette’s paid a $70,000 dividend and one year of dividends was in arrears.

Spreadsheet Tips

The following spreadsheet provides one method of setting up formulas for all possible alternatives. The spreadsheet also reflects the results of Requirement a

Notice the use of the IF function. The IF function looks like =IF(condition, true, false). To use the IF function, first describe a certain condition to Excel. Next indicate the desired result if that condition is found to be true. Finally, indicate the desired result if that condition is found to be false. Notice in cell C4 of the spreadsheet (dividends in arrears distributed to preferred shareholders) that the condition provided is B4 is B3, which is asking whether the dividends in arrears arc less than the total dividend. If this condition is true, the formula indicates to display B4, which is the amount of the dividends in arrears. If the condition is false, the formula indicates that B3 should be displayed, which is the total amount of the dividend.

The IF function can also be used to determine the amount of the current dividend distributed to preferred shareholders, the amount available for common shareholders, and the dividends in arrears after the dividend.