Introduction

Smith Company manufactures and sells widgets to the baggers industry. The unadjusted trial balance as of December 31, 2017, is shown below. The company operates under a calendar fiscal year and uses the perpetual inventory system.

Smith Company accountants have not completed the bank reconciliation for the year ended December 31, 2017. As of that date, the statement from Bank of the South showed a balance of $19,631.00. Additional information regarding bank account activity includes the following:

1. Outstanding checks were $2,837.00;
2. Deposit recorded on the books as of December 31, 2017 of $428.00 did not clear the bank records until January 2, 2018;
3. One check in payment of insurance was correctly recorded by the bank in the amount of $487.00, but was recorded by Smith Company, and charged to Insurance Expense properly, as $478.00;
4. The bank recorded a withdrawal, previously authorized, for a loan payment in the amount of $875.00, which included interest of $186.00. Smith Company has not made any entry for this as of December 31^st;
5. The bank statement includes a charge for a NSF check in the amount of $687.00. The check has been returned by the bank and Smith Company will seek re-payment from the customer.
6. The bank recorded the service charge for the latest period of $37.00.
7. A deposit of $965.00 was recorded by the bank on December 12^th, but the deposit did not belong to Smith Company. The deposit should have been made to the account of another bank customer, Smithe Inc.

Required:

1. Prepare a bank reconciliation in good form for December 31, 2017. (4 pts)
2. Prepare any necessary adjusting entries as needed (do not combine entries). Note: These will be used below in the preparation of the financial statements. (8 pts)

In addition to the above trial balance information and the information from the bank reconciliation, the following information is used to prepare the year-end adjusting entries: (12 pts)

1. The equipment, appearing in the above trial balance, was purchased four years earlier and is being depreciation using the straight-line method over a 10-year life.
2. Wages that have not been paid as of December 31^st and should be accrued at year-end amount to $5,680.
3. The company borrowed $28,000 on September 1, 2017. The principal is due to be repaid in 8 years and interest is due to be paid twice a year, at March 1^st and September 1^st, at an annual rate of 6%.
4. When the company purchases supplies on account the supplies expense is debited. Supplies on hand at year-end are valued at $625.
5. Rent expense for the annual period ending January 31^, 2018 has been charged to rent expense.
6. An insurance policy for two years was signed on July 1, 2017, with the first installment, representing 12 months, paid at that time.

Required:

1. Prepare all necessary adjusting journal entries.
2. Using the unadjusted trial balance, create a 10-column worksheet (form provided) & post the amounts from the trial balance and the adjusting entries (including entries as a result of the bank reconciliation). (14 pts)
3. Complete the 10-column worksheet.
4. From the data in the worksheet, prepare a multi-step Statement of Income and a classified Balance Sheet. (8 pts)
5. Prepare closing entries. (6 pts)
6. Present a post-closing trial balance. (3 pts)

All the above required data is to be prepared in one Excel file, using correct cell referencing techniques so that a value is only entered into the worksheet one time. All totals, subtotals, etc., are to be completed using Excel techniques.

Required continued:

Once the final statements are prepared, you are to complete the following analytic ratios, using the year-end balances instead of average balances: (10 pts)

1. Inventory turnover ratio;
2. Average collection period;
3. Profit margin on sales;
4. Return on assets;
5. Return on equity.

The final solution will be an Excel worksheet uploaded to the Blackboard assignment, with all supporting information included and the worksheet linking properly to the appropriate calculated values. For example, when you prepare the adjusting entries those values will be linked properly to the worksheet and for the calculation of the ratios, using Excel cell references correctly. Note: All journal entries are to be prepared as separate transactions do not combine entries.

Lastly, representing the remaining 10 points available, I expect you to provide a biblical application, in a Word file, applied to your answer to the following question:

Why is it important to ensure that the financial statements fairly represent the financial position of the company?

This Word document is to be uploaded to the same assignment link. Make sure to complete both the Excel file and the Word document prior to attempting to upload to the assignment in Blackboard. You must browse and access both files prior to hitting the submit button in Blackboard.

Summary of Requirements:

1. An Excel file with separate worksheets for the following items (labeled as such):
   1. Unadjusted trial balance;
   2. Bank reconciliation and the adjusting entries as a result of the reconciliation;
   3. Adjusting entries from data given with appropriate calculations using linked data from Unadjusted trial balance;
   4. The 10-column worksheet, properly referencing the trial balance and adjusting entry data from the other worksheets;
   5. Income Statement and Balance Sheet;
   6. Closing entries, and post-closing trial balance;
   7. A final worksheet with the resulting calculations for the ratios as required, referencing the data in the statements from the appropriate worksheet.
2. A Word file containing the biblical application, in proper form, in response to the question presented.

The following check figures should be used to validate your work:

Worksheet:

Total debits from adjusting entries - $29,064.00; ensure that all entries are combined on the worksheet (ex: if debit and credit to same account, net amounts)

Adjusted trial balance debits - $604,087.00

Statements:

Operating income - $25,054.00

Total liabilities - $61,337.00

Post-closing Trial Balance:

Total debits - $254,059.00

Excel file:

Word file:

Account Debits Credits $ 17,865.00 47,520.00 42,370.00 140,000.00 $ 42.000.00 27,700.00 Cash Accounts receivable Supplies Prepaid rent Prepaid insurance Inventory Equipment Accumulated depreciation Accounts payable Wages payable Interest payable Note payable Common stock Retained earnings Sales revenue Cost of goods sold Interest expense Wage expense Rent expense Insurance expense Supplies expense Utility expense Bank charges Depreciation expense 28,000.00 65,000.00 47,500.00 374,250.00 193,540.00 87,505.00 36,000.00 7,200.00 5,185.00 7,265.00 Totals $584,450.00 $584,450.00 Account Debits Credits $ 17,865.00 47,520.00 42,370.00 140,000.00 $ 42.000.00 27,700.00 Cash Accounts receivable Supplies Prepaid rent Prepaid insurance Inventory Equipment Accumulated depreciation Accounts payable Wages payable Interest payable Note payable Common stock Retained earnings Sales revenue Cost of goods sold Interest expense Wage expense Rent expense Insurance expense Supplies expense Utility expense Bank charges Depreciation expense 28,000.00 65,000.00 47,500.00 374,250.00 193,540.00 87,505.00 36,000.00 7,200.00 5,185.00 7,265.00 Totals $584,450.00 $584,450.00