Accounting spreadsheets

Square One Consulting Accounting Spreadsheets For the past several years, Abby Brown has operated a part-time consulting business from her home. As of June 1, of this year, Abby decided to move to rented quarters and to operate the business, which was to be known as Square One Consulting, on a full-time basis. The chart of accounts for the business is as follows: 11 Cash 31 Abby Brown, Capital 12 Accounts Receivable 32 Abby Brown, Withdrawals 14 Supplies 33 Income Summary 15 Prepaid Rent 41 Fees Earned 16 Prepaid Insurance 51 Salary Expense 18 Office Equipment 52 Supplies Expense 19 Accum. Deprec.—Office Equipment 53 Rent Expense 21 Accounts Payable 54 Deprec. Expense—Office Equipment 22 Salaries Payable 55 Insurance Expense 23 Unearned Fees 59 Miscellaneous Expense 31 Abby Brown, Capital 32 Abby Brown, Withdrawals 33 Income Summary 41 Fees Earned 51 Salary Expense 52 Supplies Expense 53 Rent Expense 54 Deprec. Expense—Office Equipment 55 Insurance Expense 59 Miscellaneous Expense The following transactions occurred during the month of June: June 1 Abby Brown brought the following assets into the new business: cash, $30,000; accounts receivable, $7,500; supplies, $2,000; and office equipment, $15,000. No liabilities were brought into the new business. 2 Paid for three months’ rent on a leased property, $6,000. 2 Paid the 18-month premiums on business insurances, $3,600. 4 Received cash from clients as an advance payment for services to be provided over the next few months, $5,000. 5 Purchased additional office equipment, on account, for $6,000 from Office Depot Company. 6 Received cash from clients on account, $4,000. 10 Paid cash for ads placed in the newspaper, $200. 12 Paid Office Depot for part of the June 5 debt, $1,200. 12 Recorded services provided on account for the period June 1-12, $13,000 14 Paid part-time receptionist for two weeks’ salary, $1,500 RECORD THE FOLLOWING TRANSACTIONS ON PAGE 2 OF THE JOURNAL. 17 Recorded cash from cash clients for fees earned during the period June 1 – 16, $9,000. 18 Paid cash for supplies, $1,400. 20 Recorded services provided on account for the period June 13-20, $8,500. 24 Recorded cash from clients for fees earned for the period June 17-24, $6,300. 26 Received cash from clients on account, $12,100. 27 Paid the part-time time receptionist for two weeks’ salary, $1,500. 29 Paid telephone bill, $150. 30 Paid electricity bill, $400. 30 Recorded cash from cash clients for fees earned for the period June 25 – 30, $3,900. 30 Recorded services provided on account for the remainder of June, $2,500. 30 Abby withdrew $10,000 for personal use. Required: In a workbook, complete the following: 1. Create and open the accounts in a general ledger. 2. Create and then journalize the entries in a two-column journal, beginning with page 1. 3. Post (by linking – Chapter 5) the entries in the ledger. 4. Create a 10-column worksheet. Prepare the trial balance (linked from the ledgers), listing all the accounts, in order, from the Chart of Accounts. Complete the worksheet, using the following adjustment information: a. Insurance expired during the month, (you calculate). b. Supplies on hand on June 30, $600. c. Depreciation of the office equipment for the month, $250. d. Accrued receptionist salary on June 30, $350. e. Rent expired during the month, $2,500. f. Unearned fees on June 30, $3,200. In addition, the owner has decided to give the receptionist a bonus if the net income exceeds $10,000. The bonus would be 5% of the amount of net income that exceeds the first $10,000 of net income. If applicable, the bonus would be paid on July 15. After you calculate the preliminary net income on the worksheet, create an IF statement to calculate the bonus by making an additional adjustment to record the salary payable. This adjustment can be placed under the income statement and balance sheet columns and then calculate the final net income amount. Include some type of warning message (conditional formatting) within the worksheet. If it is not obvious, please note on your worksheet where it can be found. Reference page 243 and 363 if you have forgotten how to do this. 5. Prepare in good form a multi-step income statement that includes an embedded chart in good form; a statement of owner’s equity; and a classified, report-form, balance sheet. All statements should be appropriately linked from the worksheet and/or a previous statement.   BONUS PROBLEM A company is considering the use of a spreadsheet in completion of its weekly payroll. Ms. Brown has asked you to create the federal withholding tax calculation using =IF statements. The tax tables are as follows: SINGLE person (head of household)-- If the amount of wages (after subtracting withholding allowances) is: The amount of income tax to withhold is: Not over $138… $0 Over-- But not over-- of excess over-- $138 $200 10% $138 $200 $696 $6.20 plus 15% $200 $696 $1,279 $80.60 plus 25% $696 $1,279 $3,338 $226.35 plus 28% $1,279 $3,338 $7,212 $802.87 plus 33% $3,338 $7,212 $2,081.29 plus 35% $7,212 MARRIED person-- If the amount of wages (after subtracting withholding allowances) is: The amount of income tax to withhold is: Not over $303… $0 Over-- But not over-- of excess over-- $303 $470 10% $303 $470 $1,455 $16.70 plus 15% $470 $1,455 $2,272 $164.45 plus 25% $1,455 $2,272 $4,165 $368.70 plus 28% $2,272 $4,165 $7,321 $898.74 plus 33% $4,165 $7,321 $1,904.22 plus 35% $7,321 The weekly withholding allowance amount is $70.19. Create the necessary =IF statement for the federal income withholding using the following employees to test your calculations: Name Status # Withholding Allowances Earnings J. Rel S 0 $750.00 M. Hansen S 2 $1,025.00 K. Bunne M 5 $1,050.00 L. Miller S 4 $635.00 J. Belling M 8 $1,450.50 G. Spencer M 1 $930.32