Annettes Accessories had the following stock issued and outstanding at January 1, 2013: 150,000 shares of $1

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

150,000 shares of $1 par common stock

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

On March 5, 2013, 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

Transcribed Image Text:

Alternatel- Formulas Distribution to Shareholders Preferred Common Total dividend declared Preferred Arrearage Current Preferred Dividend Available & Distributed to Common IF(B3-C4-C5 0,B3-C4-C5,0 Total 100000 IFB4 B3B4 B3) =IF(B5