To assist in the preparation of financial statements, a worksheet is usually prepared. Some computer programs by-pass this step, but the software internally 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 balance 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 adjustments 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 carrying 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||Trial Bal Cell Location||WS Cell Location|
|Acc Dep Furniture||D7||J7|
|Acc Dep Office Eq||D11||J11|
|Fed Inc Tax||D17||J17|
|State Inc Tax||D24||J24|
|State Train Tax||D25||J25|
|Payroll Tax Exp.||C42||G42|
|Delivery Tax Exp.||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 across 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.
- Format Cells
- Specify 2 decimals, accounting format and use the dollar sign.
- Now every number entered into the worksheet will be formatted the way we want.
- You can also right click the mouse after selecting cells to format
- Format Cells
- Two decimals
- Symbol is $
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 cars, expensive jewelry, TV sets, usually maintain a perpetual system that show at all times the amount of inventory at hand.
- Periodic systems of inventory accounting require that purchase of merchandise be recorded as debits to the purchases account.
- That is the method we have been using in this simulation.
- Periodic inventory systems are used for companies that sell 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.
We will focus on the gross profit method since we are dealing with virtual goods.
- Some of these methods include: average cost
- First in, first out
- Last in, first out
- Lower-of-cost or-market
- Gross profit
- Retail method.