Recent years have been very difficult for Donald Frump Painters, Inc., who has taken up house painting on the side in an attempt to improve his financial situation (after all, paying alimony to so many ex-wives gets very expensive.) Donald, who uses the calendar year accounting period, reports the following account balances for Frumps Professional Painters as of December 31, 2019:

Cash $65,500

Accounts Receivable 62,300

Painting Supplies 13,800

Prepaid Insurance 3,000

Painting Ladders 10,000

Accum. Deprec., Ladders 2,400

Accounts Payable 38,300

Salaries Payable 0

Unearned Painting Fees 24,600

Common Stock 30,700

Retained Earnings 60,000

Dividends 41,000

Painting Income 96,000

Interest Income 9,000

Salary Expense 29,500

Rent Expense 17,200

Insurance Expense 12,000

Supplies Expense 0

Depreciation Expense 0

Uniform Expense 6,700

Donald also reports the following information that may be helpful for making year-end adjustments (when you enter your adjustments, you may omit keying the adjustments with the letters a, b, etc., which shows which debit goes with which credit. But dont add any new accounts for adjustments use those on the above list only!)

• $1,695 of salaries have been earned by Donalds employees but not yet paid or recorded.

• Painting supplies remaining on hand as of year-end total $895.

• The Prepaid Insurance balance represents a 2-year policy effective October 1, 2019 (hint: count the months on your fingers!!)

• The Painting Ladders have an estimated life of 8 years and an estimated residual value of $400. Use straight-line depreciation (hint: this is covered in the chapter on Fixed Assets in accounting principles.)

• $8,800 of the Unearned Painting Fees has now been earned as of year-end.

Donald asks your help in preparing a worksheet to assist him with his required year-end activities. You remember that the major columns on the worksheet are:

Acc Title Trial Bal Adjustments Adjusted TB Income State. Balance Sheet

Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr.

Color all the cells in these two sets of columns (Trial Balance and Adjustments) a bright yellow to bring them to the attention of users. However, the Adjusted Trial Balance, Income Statement, and Balance Sheet columns must consist of FORMULAS ONLY - i.e., they are your Calculations (Answer) Section!

Format all the numbers on the worksheet with commas, no decimal places or dollar signs. You may find it helpful to use Freeze Panes as you are developing your workbook.

All of the number columns will need to have totals be sure that all of these total formulas add from the very top of the column down to the very bottom of the column.

The formulas needed in the Adjusted Trial Balance columns are complex; I am going to give you the formulas you need for these two columns only.

• Here is the Adjusted Trial Balance DEBIT column formula (for row 6, assuming Trial Balance debits are column B, Trial Balance credits are column C, Adjustments debits are column D, and Adjustments credits are column E.)

=IF(B6-C6+D6-E6>0, B6-C6+D6-E6,0)

• You will need to have this formula in the Adjusted Trial Balance debit column for EVERY account on the worksheet (even those that normally have credit balances, since sometimes out of the ordinary situations can occur.)

• Here is the Adjusted Trial Balance CREDIT column formula (for row 6, assuming Trial Balance debits are column B, Trial Balance credits are column C, Adjustments debits are column D, and Adjustments credits are column E.)

=IF(C6-B6+E6-D6>0, C6-B6+E6-D6,0)

• You will need to have this formula in the Adjusted Trial Balance credit column for EVERY account on the worksheet (even those that normally have debit balances, since sometimes out of the ordinary situations can occur.)