Prepare, using Excel:
1. A Trial Balance for both years (each one on a separate tab) Refer to financial accounting text for order of accounts on a trial balance. Submit for feedback after this step. If your trial balance is not correct, the rest of the requirements will be difficult to complete. You should enter the accounts names and amounts in cells on this sheet ONLY. Other sheets should reference the information on this sheet (3D formulas) Name the sheets Trial Balance 2015, Trial Balance 2014.
2. An Income Statement (including simple EPS calculation) for both years on the same worksheet tab. Name the sheet Income Statement.
3. Statement of Retained Earnings for both years on the same worksheet tab. Name the sheet Retained Earnings.
4. Balance Sheet for the end of each year on the same worksheet tab. Name the sheet Balance Sheet.
5. A Statement of Cash Flows for 2015 only. Name the sheet Cash Flows.
6. Statement of Stockholders' Equity for 2015 only. Name the sheet Stockholders’ Equity.
7. Be sure to include a heading on each financial statement in good form.
Format the statements correctly. Refer to a financial accounting text or online resources for help.
8. A Solvency Ratio Analysis for 2015 including Working Capital, Current Ratio, Quick Ratio, Accounts Receivable Turnover, Number of days' sales in receivables, Inventory Turnover, and Number of days' sales in Inventory (Refer to an Introductory Accounting Text (preferably Warren Reeves & Duchac) for formulas.
You should have a total of 8 worksheet tabs.
9. Use global (3D) formulas - in other words, refer to cells on other worksheets in your formulas.
YOU MUST USE 3-D formulas throughout each worksheet. The ONLY worksheets that should have the hard-coded or typed financial information or words is the trial balance. Every other sheet MUST have ONLY text or formulas/functions. (Few Exceptions like names of subtotals and totals and the Cash Flow Statement)
10. Use a different tab for each financial statement/presentation and name them appropriately. (see above)
December 31, 2015 December 31, 2014
Accounts receivable (net) $288,000 $ 185,000
Accumulated Depreciation 200,000 100,000
Additional Paid in Capital-Common 600,000 400,000
Administrative expenses 471,000 475,000
Bond payable,10%, due 2017 500,000 500,000
Cash 201,000 120,000
Common stock, $20 par 200,000 100,000
Cost of goods sold 1,580,000 1,420,500
Current liabilities 280,000 300,000
Dividends on common stock 40,000 40,000
Dividends on preferred stock 12,500 12,500
Gain on Sale of Land 125,000 ---
Income tax expense 168,000 118,750
Inventories 417,000 169,000
Long-term investments 299,500 250,000
3-Month Marketable securities 387,000 67,500
Mortgage note payable, 8%, due 2015 650,000 ----
Other expense (interest) 98,000 50,000
Other income 48,000 47,500
Preferred $2.50 stock, $100 par 500,000 500,000
Prepaid expenses 21,500 50,000
Property, plant, and equipment 2,775,000 2,100,000
Retained earnings, 1/1 1,041,500 781,500
Sales 3,415,000 3,075,500
Sales returns and allowances 35,000 22,500
Selling expenses 726,000 723,750
Treasury Stock 40,000 ---
Additional information to be used for completing some of the financial statements:
Included in the Administration expenses for 2015 is Depreciation Expense 100,000.00
The company purchased 1000 shares of treasury stock for $40 per share in 2015 40,000.00
The company issued 5000 shares of common stock in 2015 for a price of $60 per share 300,000.00
The company purchased Property Plant and Equipment in 2015 for cash at a cost of
(A loan was acquired for $600,000 to get additional cash for the purchase) 1,000,000.00
The company sold Land in 2015 that had cost $325,000 for cash of 450,000.00
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
This is only a preview of the solution. Please use the purchase button to see the entire solution