Lane Company Spreadsheet Assignment Number 1 Given below is an unadjusted trial balance for Lane Company at the end of the accounting period. Account Debits Credits Cash 20,950 Accounts receivable 60,000 Allowance for doubtful accounts 800 Merchandise inventory 43,756 Prepaid insurance 8,000 Land 40,000 Building 200,000 Accumulated depreciation building 12,000 Office equipment 120,000 Accumulated depreciation office equipment 6,000 Accounts payable 38,405 Interest payable Long-term note payable 72,000 Common stock, $2 par 50,000 Paid-in capital in excess of par value 65,000 Retained earnings 124,445 Sales 537,000 Cost of goods sold 208,000 Sales salaries expense 78,000 Advertising expense 24,000 Doubtful accounts expense Miscellaneous selling expense 5,200 Administrative salaries expense 76,000 Utilities expense 8,244 Interest expense 2,000 Insurance expense 6,000 Depreciation expense building Depreciation expense office equipment Miscellaneous administrative expenses 5,500 Totals 905,650 905,650 Additional Information: 1. A perpetual inventory system is used. 2. For simplicity, there will be only 5 adjustments: Doubtful accounts, insurance, the two depreciation items, and interest. 3. Additional accounts (not included on the trial balance) will be Income tax expense and Income tax payable. (It is not necessary to add these accounts to the trial balance.) For simplicity, the assumption will be that income tax payable equals income tax expense. REQUIRED: 1. Construct a workbook of 3 sheets. Sheet 1 will be the input section. Sheet 2 will automatically calculate a 10-column worksheet. Sheet 3 will automatically calculate a multiple-step income statement (including EPS). Name your file Lane1 and save your work periodically. 2. SHEET 1: Design an input section containing all of the accounts shown in the unadjusted trial balance. The input section should be able to accept changes in balances for all the listed accounts. Even if the unadjusted amount in an account to be adjusted is zero, the spreadsheet should add the zero amount to the adjustment. (Although the unadjusted amount may be zero for this period, it may be a non-zero amount in subsequent periods.) Additionally, the input section should facilitate easy input of the 5 adjustments. Only one input of each adjustment should be used. (That is, do not require the entry of both a debit and credit.) Label the sheet 1 tab as Input. DO NOT REQUIRE THE ADJUSTMENTS TO BE ENTERED IN THE BODY OF THE TRIAL BALANCE. 3. SHEET 2: Label the tab for sheet 2 as Worksheet. When an input section is used, it is desirable to have only cell references and formulas in the output sections (worksheet and income statement). An exception might be made for the calculation of income tax expense since the tax rates are not likely to change often. HOWEVER, FOR THIS ASSIGNMENT, PUT THE INCOME LEVELS AND TAX RATES IN THE INPUT SECTION. Thus, only cell references and formulas will be in the output section. Examples of a 10-column worksheet are attached. Place the income tax calculation in the cell which calculates the income tax expense in the Adjustments column. For simplicity, assume an income tax rate of 20% for income up to $50,000 and a rate of 30% for income over $50,000. For example, the income tax amount for pretax income of $70,000 is $16,000. 4. SHEET 3: Sheet 3 should automatically calculate and display a multiple-step income statement in good form based on the worksheet section. (All numbers displayed on sheet 3 will come from cell references and formulas.) A sample income statement is attached. 5. FORMATTING and CENTERING: Format all numbers with a comma at zero places past the decimal point. (Exceptions to zero places past the decimal point will be the income tax rates and EPS.) For Sales Revenue, Net Income, and Earnings Per Share, only, format with a $ sign. For Earnings Per Share only, format at two places past the decimal point. Some captions should centered within their column. Others (such as the Income Statement heading) should be centered across all columns. Hint: Use the merge and center icon on the tool bar. Put in underlines and double underlines where appropriate. Change the font type, size and color for headings, etc., if you wish. Save your work in the Lane1 file.