Problem 4Worksheet for a Merchandising Entity (WORKSHEET)

Learning Objectives

Prepare a worksheet for a sole proprietorship merchandising firm

Answer What If Scenarios

Prepare financial statements from a worksheet.

Create a Chart

Problem Data The trial balance of Sports Haven at June 30, 2017, the end of the current fiscal year is as follows: Dr. Sports Haven Work Sheet For Year Ended June 30, 2017 Trial Balance Account Title Cr. Cash 17,150 Accts receivable 127,010 Notes Receivable 15,000 Allowance for Bad Debt 1,270 Merchandise inv. 47,964 Prepaid insurance 7,450 Store Supplies 2,370 Store equipment 30,000 Accum. depreciation 3,000 Accounts payable 12,420 Joe Athlete, Capital 105,572 Joe Athlete, Drawing 14,400 Income From Sales 455,400 Purchases 192,900 Advertising exp. 7,950 Rent expense 30,000 Salaries exp. 79,840 Utilities exp. 5,628 577,662 577,662 Adjustment information is as follows: a. Supplies on hand as of 6-30-17, $350. b. The insurance premium was paid on 5/1/17 as was for a six month period. Record the amount that has expired. (Round to the nearest whole dollar.) C. The accounts receivable clerk needs to make the year-end adjustment for the expected bad debt. The company uses the percentage of Sales to compute the adjustment. All sales were on account and the rate used is 1.5%. d. The Note Receivable was signed on 6/1/2017. (It was used to extend a past due accounts receivable.) The term of the note was 8% interest. The note is due within 6 months. Record the accrued interest. (Round to the nearest dollar.) e. A count of the merchandise inventory was made and totaled $68,800. f. The store equipment has not been depreciated for 2017. It was purchased several years ago and is being depreciated using the straight line method. The cost of the Store Equipment was $30,000. The salvage value was estimated at $5,000. The life at the time if was purchased was 5 years. No depreciation has been recorded for this year. g. Salaries accrued but not paid at June 30, 2017, $4,000. (Ignore any payroll taxes.) h. It was discovered that the outside labor firm Account Temps had not been paid for the time incurred at year end. An amount of $2,000 is owed to them for services performed prior to 6-30-17. i. Included in the rent expense of $30,000 is $1500 that has been prepaid for July, 2017 REQUIREMENTS: 1. As accountant for Sports Haven, you have been asked to prepare adjusting entries and financial statements to complete the accounting cycle for the year. A worksheet (File WORKSHEET) has been provided to assist you in the assignment. As you review the template, it should be noted that columns H and I will automatically update and change when you enter values in columns E and G. 2. Open the File WORKSHEET. Enter the formulas in the appropriate cells on the worksheet. Enter the adjustment data is columns E and G. In column D or F, insert the letter corresponding to the adjustments (a - i). 3. Complete the Income statement and balance sheet columns by entering formulas in columns J, K, L, M that reference the appropriate cells in column Hor I. NOTE: DO NOT ENTER ANY NUMBERS IN THESE CELLS. Enter cell references so that if any changes are made to the adjustment data, your income statement and balance sheet columns will automatically update. Net income (or loss) will be automatically computed at the bottom of the income statement and balance sheet columns. Check to make sure that these numbers are the SAME. Put the appropriate header on your worksheet. Save the file as WORKSHEET3. Print the worksheet so it fits to one page. Also print the formulas using Control. The Check figure for net income is $139,184. 4. What If Analysis: a. You discovered that your boss provided some bad information regarding the adjustments. The insurance premiums were actually for four months - not six months. Also, the bill from Account Temps was for $3,000 (not $2,000). Make these corrections on your worksheet and save the corrected file at WORKSHEET4. (HINT: All you should have to change are the adjustment columns. If you followed directions, and used CELL REFERENCES, then EVERYTHING should be updated. Check this. Re-save the file. Re-print the worksheet. 5. Prepare Financial Statements: Open up the revised file you called WORKSHEET 4. Within the same workbook or file, create another "tab" or Worksheet called "Financials". Within this new worksheet prepare a formal set of financial statements including an income statement (for a merchandiser - i.e. compute Cost of Goods Sold), a statement of owner's equity, and a classified balance sheet. ALL AMOUNTS ON YOUR FINANCIAL STATEMENTS SHOULD BE CELL REFERENCED TO YOUR WORKSHEET so that if any amounts change on the worksheet, the financial statements will be automatically updated. Note: There were no additional investments made by the owner over the year. Save the file at WORKSHEETS. Set up the file so that your formal financial statements will print out appropriately. You can use Page Break Preview to do so. (They should NOT SPLIT in the middle of a statement....but they do not have to all fit on one page. Split the statements in an appropriate way.) Save your new file as WORKSHEETS