# Excel Applications for Accounting Principles

## Quiz 3 :Merchandising Company Worksheet P2-work

Question Type
PROBLEM DATA The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, $450. b. Insurance premiums that expired during the year,$2,420. c. Depreciation on equipment during the year, $1,500. d. Included in the rent expense of$30,000 is $1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013,$1,440. f. Merchandise inventory on June 30, 2013, $68,864. REQUIREMENT As the accountant for Sports Connection, you have been asked to prepare adjusting entries and financial statements to complete the accounting cycle for the year. A worksheet called P2WORK has been provided to assist you in this assignment. As you review this worksheet, it should be noted that columns H and I will automatically change when you enter values in columns E and G. Free Essay Answer: Answer: Adjusted trial balance: Adjusted trial balance is a worksheet that include the balances of all the accounts that are reported to the financial statements. It is prepared after the trial balance and the adjusting entries are being made. It is used as a working paper for the preparation of financial statements but is not considered as a financial statement in itself. It is a type of worksheet which is prepared at the end of the accounting period. Complete the worksheet as follows: Thus, the total of adjusted trial balance is$444,232.
Prepare the above-mentioned worksheet with excel formulae as follows:

Tags
PROBLEM DATA The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, $450. b. Insurance premiums that expired during the year,$2,420. c. Depreciation on equipment during the year, $1,500. d. Included in the rent expense of$30,000 is $1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013,$1,440. f. Merchandise inventory on June 30, 2013, $68,864. REQUIREMENT Open the file P2WORK from the website for this book at cengagebrain.com. Enter the formulas in the appropriate cells on the worksheet. Then enter the adjusting amounts in columns E and G. Also, in column D or F, insert the letter corresponding to the adjusting entry (a-e). ( Note: Not all textbooks handle the change in inventory as an adjustment. Use the method for handling inventory that is prescribed in your textbook.) Column A is "frozen" on the screen to assist you in completing requirement 3. Free Essay Answer: Answer: In the worksheet, record the following transactions for adjusting entries: a. The store supplies on hand at the end of the fiscal year is$450. The balance in trial balance for store supplies is $2,370. This means that$1,920 in supplies was used and needs to be adjusted. Debit supplies expense and credit store supplies for $1,920. b. Insurance premiums used during the year are$2,420. Debit insurance expense and credit prepaid insurance.
c. Depreciation on the company's equipment is $1,500. Debit depreciation expense and credit accumulated depreciation. d. Prepaid rent of$1,200 is included in the rent expense for the first month in the next fiscal year. Debit prepaid rent and credit rent expense.
e. Wages earned by employees but not yet paid are $1,440. Debit salaries expense and credit salaries payable. f. The value of merchandise inventory at the end of the fiscal year is$68,864. First we will clear out merchandise inventory so we will debit income summary and credit inventory for the beginning balance of $47,964. Secondly, to reflect the ending balance in inventory, debit inventory and credit income summary for$68,864.
In adjusted trial balance, the total of debits should be equal to the total of credits. In order to determine this, a formula will be entered to add all the numbers in all columns. The spreadsheet after recording all the transactions and entering all the formulas is shown below:

Tags
PROBLEM DATA The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, $450. b. Insurance premiums that expired during the year,$2,420. c. Depreciation on equipment during the year, $1,500. d. Included in the rent expense of$30,000 is $1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013,$1,440. f. Merchandise inventory on June 30, 2013, $68,864. REQUIREMENT Complete the income statement and balance sheet by entering formulas in columns J, K, L, and M that reference the appropriate cells in column H or I. Net income will be automatically calculated at the bottom of the income statement and balance sheet columns. Check to be sure that these numbers are the same. Enter your name in cell A1. Save the completed file as P2WORK3. Print the worksheet. Also print your formulas using landscape orientation and fit-to-1 page scaling. Check figure: Net income (cell J34),$37,902.
Free
Essay

In the worksheet, the information will be transferred in the appropriate column of adjusted trial balance, income statement, or balance sheet using cell references. A formula will be used at the end of both columns to calculate the totals of each column. The complete spreadsheet is shown below:
Notice how debits equal credits and Company S has a net income of $37,902. Tags WHAT-IF ANALYSIS You discover that your boss has mistakenly provided you with the wrong information on two of the adjusting entries. Expired insurance premiums should have been$3,420, and unpaid salaries should have been \$2,440. Make the corrections on your worksheet and save the corrected file as P2WORK4. Reprint the worksheet.
Essay