Reference no: EM132260158
Assignment - Company Spreadsheet Project
Instructions: This has to be done on the excel template and the question at the end of the survey has to be answered.
Mr. Wayne is in the second month of his new Accounting Company that performs accounting services for small to mid-size businesses in his area. The following are a list of transactions that occurred in the second month of operations for Batman Accounting Services, Inc. a subchapter S corporation:
|
June
|
|
|
1
|
The Corporation accepted a 30 day, 12% note from Robin for $20,000 cash (round to the nearest whole dollar)
|
|
1
|
The Corporation issued for cash 15,000 shares of $1 par common stock at $5
|
|
1
|
Issued $2,000,000 of 10-year 12% callable bonds of $1,988,000, interest is payable monthly at the end of every month.
|
|
1
|
The Corporation incurs another month's rent for $1,200 per month that was already prepaid.
|
|
2
|
Purchased 1,000 shares of Superman Corporation stock as an available-for-sale security at $50 per share.
|
|
The Corporation paid the invoice for miscellaneous office supplies for $425
|
|
3
|
The Corporation collected $9,500 from miscellaneous customers that had an outstanding balance due on account.
|
|
4
|
The Corporation paid $1500 to advertise on the city billboard.
|
|
4
|
The Corporation reimbursed Mr. Wayne $650 for meals spent entertaining clients.
|
|
6
|
Paid monthly insurance payment of $1,200.
|
|
10
|
Made $900 worth of repairs on the building. (repairs are not enough to capitalize)
|
|
15
|
Paid the Payroll tax liabilities for the month of May
|
|
15
|
The Corporation paid $200 for utilities
|
|
15
|
The Corporation paid $125 for the telephone service
|
|
20
|
The Corporation accepted a credit payment from Mr. Joker for $450, but incurred a $5 credit service fee and received $445 in cash
|
|
20
|
The Corporation bought $850 worth of miscellaneous office supplies such as pens, pencils and paper
|
|
30
|
The Corporation paid wages to Mr. Freeze for 160 hours worked at $25 an hour. According to the W-4, $450 is withheld for federal income tax withholding and 6.2% is withheld for FICA tax and 1.45% is withheld for Medicare tax and 5% is withheld for state income tax.
|
|
30
|
The Corporation invoiced $35,250 to multiple miscellaneous clients
|
|
30
|
The Corporation collected the first payment from Robin
|
|
30
|
The Corporation billed miscellaneous clients in the total amount of $12,500.
|
|
30
|
Robin paid off the note with interest.
|
|
30
|
Paid the monthly interest on the bonds. The bond discount is amortized monthly on a straight line bases.
|
|
30
|
The Corporation determined that 10% of their Accounts receivables will be uncollectible, the company used the allowance method.
|
|
30
|
At the end of the month, Superman Corporation stock was valued at $55 per share on the market.
|
Note: Remember your adjusting entries.
The company elects to use the straight line depreciation method for all assets. The amount of depreciation for the month is $122.
Assuming the Corporation is on an Accrual Basis of accounting, prepare a 5 column trial balance. This should include the unadjusted trial balance, adjustments, adjusted trial balance, balance sheet and income statement. Then prepare, in good form (i.e. correct heading titles), a comparative balance sheet & Income Statement with prior month and current month with a horizontal analysis included with the amount and percentage. I have included the prior month's trial balance on the template. Also, prepare a statement of cash flows using the indirect method.
All of these should be done in excel with formulas. Included an excel template you can use. The first tab are the T-accounts that you can enter in the numbers, then you should sum up the balances and link them to the trial balance on tab 2. Then the totals on the trial balance should carry over to the balance sheet, income statement and statement of owners' equity tabs. You should not hard key any amounts on tab 2, they should all be linked to the second tab. I have done the first transaction and linked the cash account for your reference.
Finally, write a short evaluation about the company and where you think the company is heading. Include in your evaluation if you think the company is profitable and where do you think the company can save money or ways the company can improve revenue.
Attachment:- Assignment Files.rar