The Lake Lucerne Company uses the allowance method of estimating bad debts expense. An aging schedule is prepared in order to calculate the balance in the allowance account.

Instructions for worksheet:

Please show the formulas needed for answers if possible, not just the numbers typed into the blanks

Chapter 9

Using Excel

P9-42 Using Excel to prepare depreciation schedules

The Fraser River Corporation has purchased a new piece of factory equipment on January 1, 2018, and wishes to compare three depreciation methods: straight-line, double-declining-balance, and units-of-production.

The equipment costs $400,000 and has an estimated useful life of four years, or 8,000 machine hours. At the end of four years, the equipment is estimated to have a residual value of $20,000.

Requirements

1.

Use Excel to prepare depreciation schedules for straight-line, double-declining-balance, and units-of-production methods.

2.

Prepare a second depreciation schedule for double-declining-balance method, using the Excel function DDB. The DDB function cannot be used in the last year of the asset's useful life.

3.

At December 31, 2018, Fraser River is trying to determine if it should sell the factory equipment. Fraser River will only sell the factory equipment if the company earns a gain of at least $6,000. For each of the depreciation methods, what is the minimum amount that Fraser River will sell the factory equipment for in order to have a gain of $6,000?

Excel Skills

1.

Create formulas with absolute and relative cell references.

2.

Format the cells as accounting number format.

3.

Use the Excel function DDB to calculate double-declining-balance depreciation.

Worksheet:

Aging of Accounts Receivable
		Minimum Days	Maximum Days	% Uncollectible
		1	30	1%
		31	60	2%
		61	90	5%
		91	365	50%
	Requirement 1: Calculate the number of days each receivable is outstanding.
	DATA
	Customer Name	Amount	Today's Date	Invoice Date	Days Outstanding	Uncollectible %
	Bonneville	$ 900	7/31/18	6/28/2018
	Chinook	$ 1,100	7/31/18	7/28/2018
	Duwamish	$ 500	7/31/18	5/15/2018
	Foss	$ 350	7/31/18	6/5/2018
	Nisqually	$ 3,245	7/31/18	7/15/2018
	Worden	$ 250	7/31/18	4/12/2018
	Requirement 2: Complete the Schedule of Accounts Receivable.
	Schedule of Accounts Receivable
	Customer Name	1-30 Days	31-60 Days	61-90 Days	91-365 Days	Total Balance
	Bonneville	1
	Chinook
	Duwamish
	Foss
	Nisqually
	Worden
	Totals
	Estimated % uncollectible
	Estimated total uncollectible
		Balances on July 31, 2018
		Accounts Receivable			Allowance for Bad Debts
		6,345				65
	Requirement 3: Journalize the adjusting entry for Bad Debts Expense.
		Account / Explanation			Debit	Credit