Accounting Tutorial: Worksheet Chapter 11
Previous
Next
Worksheet
General Journal
Worksheet Tutorial
To assist in the preparation of financial statements, a worksheet is usually prepared. Some computer programs by-pass this step, but the software internallly does these computations. It is necessary to understand that from the trial balance and any adjustments you need to make, you have all the information needed to prepare a worksheet and eventually the financial statements. Some accounts like merchandise inventory, prepaid insurance, depreciation, and supplies must be adjusted. A good place to make these adjustments is using an eight-column worksheet. The worksheet looks like an expanded trial balance. In addition to the two columns for the trial balance, there are debit and credit columns where we we put the adjustments. The next two columns are to used to break out the income statement accounts, the revenue and expenses. The last two columns are used to break out the balance sheet accounts, asset, liabilities and stockholders' equity. Click on the worksheet link above to view a partially completed worksheet. The numbers in the trial balance columns are the ones from the last lesson. To make the worksheet a template that we can use over and over again. we are going to put in some simple formulas to extend trial balance amounts into either the income statement or balance sheet columns. Remember that income statement accounts are Sales - Credits and Expenses- Debits. Balance sheet accounts are Assets - Debits, Liabilities and Stockholders' Equity - Credits. We' re going to extend the number in the trial balane columns into either the income statement debit, income statement credit or balance sheet debit and balance sheet credit columns. The accounts we are going to work on are the ones that have no adjustents that need to made to them. More on adjustments later. Cash is a debit in the trial balance. We know that it also is an asset account and therefore, belongs on the balance sheet. If the account balance is a debit and an asset on the trial balance, then it will be a debit on the balance sheet. If the account is a liability and a credit on the trial balance, then it will be a credit on the balance sheet. If the account is a stockholders' equity account and a credit on the trial balance, then it it a credit on the balance sheet. If an account is a revenue account and a credit balance on the trial balance, then it will be a credit on the income statement. If the account is an expense and a debit on the trial balance, then it will be a debit on the income statement. Let's start with our first account, Cash. If you put the cursor on the amount of cash, you will see that the amount is located in cell C4. We know that this very same number is to appear on the worksheet in the balance sheet debit column. Scroll across to that column, it should be I4. Instead of keying in the actual amount of cash , all we really need to do is type "= c4" in cell I4. The amount of cash should then be transferred. Why we would go to all this trouble? Try changing the amount of cash in the trial balance. After keying in a new amount, you will see that same, new amount appear in the balance sheet debit column as well. By utilizing this method, you have less of a chance to make an error when carying over the amounts, and you can use this worksheet as a template for future assignments. Now let's put in the formulas for the following accounts. Account name Cell Copy To Cash C4 I4 A/R C5 I5 Furniture C6 I6 Off Equip C10 I10 Notes Payable D14 J14 Accts Pay D15 J15 Work Comp D16 J16 Fed Inc tax D17 J17 FICA D18 J18 Medicare D19 J19 FUTA D20 J20 SUTA D21 J21 Sales Tax D22 J22 SDI D23 J23 State Inc Tax D24 J24 State Emp Train D25 J25 Common Stock D26 J26 Retained Earn D27 J27 Sales D29 H29 Sales Returns C30 G30 Other Income D31 H31 Purchases C32 G32 Pur Returns D33 H33 Salary Exp C34 G34 Rent C35 G35 Repairs C36 G36 Advertising C37 G37 Misc Exp C41 G41 Payroll Taxes C42 G42 Legal Exp C43 G43 Utilities C44 G44 Alterations C45 G45 Interest C46 G46 Delivery C47 G47 FORMULAS TO TOTAL THE WORKSHEET Let's put in some formulas to total each column of the worksheet. What we want to do is get the sum of all number in a column, starting from the first number and ending with the last number in the column. If you scroll to the top of the trial balance column, placing the cursor on the amount of cash, you will see that this is cell C4. If you scroll down to the bottom of the column next to delivery expense, you will see that this is cell C47. Move your cursor down to cell C50 and type in the following formula = sum(c4:c47). This should give you the totals of the debit column on the trial balance. Now we need to extend this formula accross the worksheet for all of the other columns. We could enter a new formula for each column, (= sum D4:D47), = sum(e4:e47), etc; or we could copy the existing formula to the other columns of the worksheet. To copy the formula, place the cursor on the cell containing the formula, click Edit on the menu bar, click on copy. Move the cursor to cell C49. Hold the shift key down and move the cursor all the way to the end of the worksheet: the balance sheet credit column .Click edit again and then click paste. The formula will be copied across the bottom of the worksheet. FORMATING THE WORKSHEET Since we want all of the numbers on the worksheet to have two decimal places, and have commas placed in the right place, we need to format the worksheet for our purposes. To begin, click on the first cell which contains money, drag the cursor to the right and down to cover the whole worksheet. Click Format on the menu bar, click on cells, specify 2 decimals, accounting format and use the dollar sign. Now every number entered into the worksheet will be formated the way we want. ADJUSTMENTS - Office Supplies At any specific time, some general ledger acounts do not reflect a true, up-to-date balance. One of these accounts is office supplies. Each time we buy office supplies we debit the asset account office supplies. However, each time we use a new toner cartridge, paper for the copy machine, etc, it would be impractical to credit that account. The balance in the office supplies account needs to be brought up-to-date at the end of each month. Changes in general ledger accounts that are recorded at the end of the fiscal period are called adjustments. Each adjustment affects two accounts. One is debited and the other credited. In the example of office supplies, the asset account is credited and the expense acccount, supplies is debited. Here is how the number is determined. A physical inventory of our office supplies is taken. this number is referred to as the ending inventory of office supplies. This amount is subtracted from the trial balance number showing the amount of office supplies on hand and purchased during the accounting period. The difference shows the amount of office supplies used during the fiscal period. After taking our physical inventory of supplies, we have determined that it is $350.00 If we subtract it from the $635.00 we get $285.00 This is the amount of supplies expense for the fiscal period. Now to show this on the worksheet do the following. 1. In the adjsutments credit column, cell F8, enter 285.00 2. In the balance sheet debit column, Cell I8, enter =c8-f8. Ending Inventory appears in Cell I8 3. Scroll down to supplies expense. Go to cell E38, type =F8 to put the 285.00 in the adjustments credit column 4. Goto Cell G38, type = F8 to put the amount in the debit column of the income statement. ADJUSTMENTS - Prepaid Insurance When we purchase an insurance policy it is usually for one year. If we charged off the whole year policy to insurance expense in one month we would misstate the amount of insurance expense on our income statement. What we want to do is to spread out the epense of the policy, month by month. To do this we look at the total prepium paid. Cell C12 shows prepaid insurance. We divide this out by 12 to get the monthly expense for insurance. We arrive at $100 per month. This is the amount of insurance expense that we want to show on the income statement. To do this follow these steps. 1. Find the amount of prepaid insurane, 1200.00 and divide it by 12 2 Go to Cell F12, in the adjustment credit column and type in 100.00 3. Scroll down to insurance expense, the adjustments debit column, Cell E40 and type =F12 4. Now to get the proper amount of insurance expense on the income statment, go to Cell G40 and type = F12 ADJUSTMENTS - Depreciation Depreciation expense which lowers the value of certain asset accounts each month is a legitimate expense on the income statement. We need to expense out a part of the value of the asset each fiscal period. The accounts we are looking at for Bromley's Formal Wear are Office Equipment and Furniture and Fixtures. We mentioned depreciation expense back in the lesson where we were coming up with the projected income statement. Depreciation is a method of charging off to an expense the wear and tear on equipment and other assets. There are a number of different methods of depreciating an asset. We will use the simplest one, called straight-line. We first determine the value of the asset. Next we determine the useful life of it. We divide by the number of years and then by 12 to determine the monthly amount that can be charged to depreciation expense. To see the value of the assets we need to look back at lesson two to see what the assets are and their value. Proceed as follows. FURNITURE & FIXTURES Display cases cost $25,000. Assume a life expectancy of 10 years. Yearly depreciation for this asset is $2,500 per year. Monthly depreciation is $2,500/12 = 208.33. Display racks cost $20,000. Life of the asset is equal to 10 years, therefore, yearly depreciation is $2,000 and monthly depreciation is $166.67. If we add $208.33 + $166.67 we get $375.00 If we go to Cell F7 in the adjustments credit column. Key in 375.00 OFFICE EQUIPMENT Our $2,000 cash register has a useful life for 5 years, therfore yearly depreciation is $400 and the monthly depreciation is $33.33 per month. Our computer cost $1,500. Since computers and operating systems change regularly we will assume a 3-year life value for the asset. The monthly depreciation for the computer is $41.67. The software used to operate the computer is also and asset. Years of useful service for the software is also 3 years. Montlhy depreciation is $27.78 The filing cabinet cost $300. Life equal 5 years. Yearly depreciation is $60 and monthly is $5.00. Our desk cost $400 and we calculate that this one will do for the next 5 years. Montly depreciation is $6.67. Our desk chair cost $100 when purchased. It will last for 5 years. Yearly depreciation is $20 and monthly is $1.67. Now let's add up these amounts 33.33 + 41.67 + 27.78 + 5.00 + 6.67 + 1.67 = 116.12. Goto Cell F11 and enter 116.12. Got to cell E39 and enter = f7 + f11 to get the total of the two depreciation amounts for furniture fixtures and office equipment. Now the last step in the figuring depreciation is to extend this total to the income statment debit column where all of the expenses are listed. Go to Cell G39 and key in = E39
Journalizing Adjustments
Once the worksheet has been completed, we need to journalize and then post these adjustment to our general ledger. Click on the link above for the general ledger and journalize the adjustments Date PR Account Name Debit Credit 11-30 Supplies 285.00 Supples Expense 285.00 11-30 Depreciation Expense 491.42 Accum Dep Furniture 375.00 Accum Dep Off Equip 116.42 11-30 Insurance Expense 100.00 Prepaid Insurance 100.00 11-30 Income Summary 100,000 Mdse Inventory 100,000 11-30 Mdse Inventory 47,754.94 Income Summary 47,754.94 Print this journal out for your instructor Go back one lesson and click on the link for Bromley's Formal general ledger. Post these amounts to the general ledger. Print out the trial balance for your instructor.
Inventory Evaluation Methods
Inventory appears on both the income statement and balance sheet. In the balance sheet, inventory is often the largest current asset. In the income statement, inventory is subtracted from the cost of goods available for sale to determine the cost of goods sold. The basis for valuation of the inventory is cost. How do we determine the ending inventory? What methods are available to us? There are two basic ways to determine ending inventory: Perpetual and Periodic methods. Companies that sell products of a high unit value like automobiles, TV sets usually maintain a perpetual system that show at all times the amount of inventory at hand. Periodic system of inventory accounting requires that purchase of merchandise be recorded as debits to the purchases account. That is method we have been using in this simulation. Periodic inventory systems are used for companies that sells a variety of merchandise with low unit prices. At the end of the accounting period, a physical inventory is taken, this means making a systematic count of merchandise on hand. The inventory must then be priced at cost. Taking a physical inventory is a time-consuming process that interrupts the business and inconveniences the customer. It is normally taken when the store is closed. What if it is impractical or we cannot take a physical inventory? What methods are available to use to determine the amount of ending inventory? There a number of methods. Some of these methods include: average cost, first in, first out, last in, first out, lower-of-cost or-market, gross profit and retail method. We will focus on the gross profit method since we are dealing with virtual goods. ADJUSTMENTS - Merchandise Inventory To find the ending inventory for Bromley's Formal Wear using the Gross Profit Method of Inventory Evaluation proceed as follows. Sales = 274,549.36 Beginning Inventory 100,000,00 Purchases +108,915.41 Mdse Avail for Sale 208,915.41 To determine cost of goods sold multiply .587 times sales (274,549.36) Remember we found this percentage at Biz state.com. The answer of 161,160.47 represents cost of goods sold. Subtract cost of goods sold from total merchandise available for sale to get the ending inventory 208,915.41 minus COGS 161,160.47 Ending Inv 47,754.94 Now that we have the ending inventory, we can enter these numbers on the worksheet. 1. First we are going to enter our beginning inventory into cell F13. By doing this when we calculate the adjustments in with the trial balance, the 100,000 will be erased. 2. Enter 47,754.94 into cell E13. This is where we put in the ending inventory amount. The account called income summary is used to calculate the cost of goods sold section of the income statement. It is also used to balance out the entries you just did above. 3. In cell F28 type =E13. In cell E28 type =F13 Income statement columns - Carry over the amounts for income summary to the income statement columns 4. In cell G28 type =E28, in cell H28 type =F28 Balance sheet. We now need to get the correct ending inventory into the debit column of the balance sheet, to show that we now have a different ending inventory. 5. In cell IB type = C13+E13-F13. This formula will subtract, zero out the beginning inventory of 100,000, and put the new ending inventory as an asset on the balance sheet. CALCULATING NET INCOME To see if our worksheet is in balance we need to find out what the difference is between the income statement columns and balance sheet columns. This difference must be the same. 6. Go to cell A50 and type in "Net income" 7. Go to cell G50 and type = H49-G49. This will give you the difference between the income statement debit and credit columns. This number also represents the net income for the fiscal period. 8. Now to see if the worksheet is in balance, goto cell J50 and type = I49-J49. This answer must be the same as in step 7. This represents the increase in stockholders' equity after the fiscal period and is the same number as the net income.
1. What accounts or account need to be adjusted on the worksheet?
Office Supplies
Inventory
All of the above
2. How many columns does the worksheet have?
2
4
8
3. If the acccount is an asset on the trial balance, which other column on the worksheet should it also be on?
Income statement - debit
Income statement - credit
Balance sheet - debit
4. An expense that appears on the trial balance should be also on the?
Income statement debit column
Balance sheet debit column
Income statement credit column