• Home
  • Blog
  • Reconciled Balance and Omitted Check Excel Calculations

Reconciled Balance and Omitted Check Excel Calculations

0 comments

Bug-Off Exterminators provides pest control services and sells extermination products manufactured by other companies. The following six-column table contains the company’s unadjusted trial balance as of December 31, 2018.

BUG-OFF EXTERMINATORSDecember 31, 2018 Unadjusted
Trial BalanceCash$17,200     Accounts receivable 2,500     Allowance for doubtful accounts    $821 Merchandise inventory 10,600     Trucks 31,600     Accum. depreciation—Trucks     0 Equipment 51,000     Accum. depreciation—Equipment     12,700 Accounts payable     5,200 Estimated warranty liability     1,300 Unearned services revenue     0 Interest payable     0 Long-term notes payable     15,500 Common stock     11,000 Retained earnings     49,500 Dividends 12,000     Extermination services revenue     54,000 Interest revenue     868 Sales (of merchandise)     77,822 Cost of goods sold 44,000     Depreciation expense—Trucks 0     Depreciation expense—Equipment 0     Wages expense 34,000     Interest expense 0     Rent expense 9,000     Bad debts expense 0     Miscellaneous expense 1,201     Repairs expense 8,900     Utilities expense 6,710     Warranty expense 0     Totals$228,711  $228,711 

The following information in a through h applies to the company at the end of the current year.

a. The bank reconciliation as of December 31, 2018, includes the following facts.

 Cash balance per bank$15,100Cash balance per books 17,000Outstanding checks 1,800Deposit in transit 2,450Interest earned (on bank account) 52Bank service charges (miscellaneous expense) 15

Reported on the bank statement is a canceled check that the company failed to record. (Information from the bank reconciliation allows you to determine the amount of this check, which is a payment on an account payable.)

b. An examination of customers’ accounts shows that accounts totaling $679 should be written off as uncollectible. Using an aging of receivables, the company determines that the ending balance of the Allowance for Doubtful Accounts should be $700. 

c. A truck is purchased and placed in service on January 1, 2018. Its cost is being depreciated with the straight-line method using the following facts and estimates.

  Original cost$32,000Expected salvage value 8,000Useful life (years) 4

d. Two items of equipment (a sprayer and an injector) were purchased and put into service in early January 2016. They are being depreciated with the straight-line method using these facts and estimates.

SprayerInjectorOriginal cost$27,000 $18,000Expected salvage value 3,000  2,500Useful life (years) 8  5

e. On August 1, 2018, the company is paid $3,840 cash in advance to provide monthly service for an apartment complex for one year. The company began providing the services in August. When the cash was received, the full amount was credited to the Extermination Services Revenue account.

f. The company offers a warranty for the services it sells. The expected cost of providing warranty service is 2.5% of the extermination services revenue of $51,760 for 2018. No warranty expense has been recorded for 2018. All costs of servicing warranties in 2018 were properly debited to the Estimated Warranty Liability account.

g. The $15,000 long-term note is an 8%, five-year, interest-bearing note with interest payable annually on December 31. The note was signed with First National Bank on December 31, 2018.

h. The ending inventory of merchandise is counted and determined to have a cost of $11,700. Bug-Off uses a perpetual inventory system.

Required: 

1. Use the preceding information to determine amounts for the following items.

a. Correct (reconciled) ending balance of Cash, and the amount of the omitted check.

b. Adjustment needed to obtain the correct ending balance of the Allowance for Doubtful Accounts.

c. Depreciation expense for the truck used during year 2018.

d. Depreciation expense for the two items of equipment used during year 2018.

e. The adjusted 2018 ending balances of the Extermination Services Revenue and Unearned Services Revenue accounts.

f. The adjusted 2018 ending balances of the Warranty Expense and the Estimated Warranty Liability accounts.

g. The adjusted 2018 ending balances of the Interest Expense and the Interest Payable accounts.

2. Use the results of part 1 to complete the six-column table by first entering the appropriate adjustments for items a through g and then completing the Adjusted Trial Balance columns. (Hint: Item b requires two adjustments.)

3. Prepare journal entries to record the adjustments entered 4n the six-column table. Assume Bug-Off’s adjusted balance for Merchandise Inventory matches the year-end physical count.

4-a. Prepare a single-step income statement for year 2018.

4-b. Prepare a statement of retained earnings (cash dividends during 2018 were $10,000) for year 2018.

4-c. Prepare a classified balance sheet as at 2018.

About the Author

Follow me


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}