Problem: I want assistance to create the income statement, balance sheet, statement of retained earnings, statement of cash flows, ratio analysis and vertical and horizontal analysis. Please complete the spreadsheet using formulas so I can see how everything was calculated.
Additional information:
- Land with the land improvements were sold at book value (no gain or loss) in 2014. (Note: To evaluate the sale, use the following accounts: land, building and land improvements, and Other Noncurrent Assets)
- New equipment purchased with cash for $2,739,067 in 2014
- New storage building purchased with cash for $135,000 in 2015
- No investments have been sold or purchased in 2014 or 2015
There are currently 8,275,000 shares of common stock outstanding. No additional common stock has been sold or repurchased in any of the aforementioned years.
Trial Balances for years ending December 31, 2013, 2014, and 2015
| Account Description |
2013 |
2014 |
2015 |
| Cash on Hand |
$ 2,484 |
$ 2,548 |
$ 2,599 |
| Regular Checking Account |
247,646 |
253,960 |
243,802 |
| Savings Account |
3,806,198 |
4,083,071 |
4,205,563 |
| Accounts Receivable |
20,513,628 |
57,697,091 |
49,042,528 |
| Other Receivables |
- |
1,400,000 |
1,200,000 |
| Allowance for Doubtful Accounts |
(1,578,525) |
(1,387,691) |
(2,942,552) |
| Inventory |
23,531,507 |
75,851,471 |
65,990,780 |
| Reserve for Inventory Obsolescence |
(3,765,000) |
(12,136,103) |
(10,558,525) |
| Prepaid Insurance |
929,143 |
2,830,474 |
2,667,722 |
| Prepaid Rent |
250,000 |
- |
- |
| Office Supplies |
9,259 |
9,565 |
9,182 |
| Land |
146,250 |
131,040 |
131,040 |
| Buildings and Land Improvements |
779,882 |
698,775 |
833,775 |
| Machinery, Equipment, Office Furniture |
541,521 |
3,280,589 |
3,280,589 |
| Accum. Depreciation |
(205,000) |
(764,692) |
(1,381,847) |
| Investments |
715,864 |
2,238,634 |
2,070,736 |
| Other Noncurrent Assets |
67,301 |
- |
- |
| Taxes Receivable/payable |
- |
(3,205,440) |
(6,011,540) |
| Accounts Payable |
(5,791,398) |
(22,488,866) |
(13,850,648) |
| Wages Payable |
(36,838) |
(264,513) |
(198,384) |
| FICA Employee Withholding |
(1,648) |
(9,452) |
(7,089) |
| Medicare Withholding |
(730) |
(12,785) |
(9,589) |
| Federal Payroll Taxes Payable |
(7,541) |
(132,256) |
(99,192) |
| State Payroll Taxes Payable |
(3,519) |
(61,720) |
(46,290) |
| FICA Employer Withholding |
(1,648) |
(9,452) |
(7,089) |
| Medicare Employer Withholding |
(730) |
(12,785) |
(9,589) |
| Line of Credit |
(12,500,000) |
(49,731,360) |
(44,177,211) |
| Current Portion Long-Term Debt |
- |
(13,440,000) |
(12,084,720) |
| Interest payable |
- |
(470,311) |
(568,429) |
| Bonuses payable |
- |
(504,000) |
(459,000) |
| Dividend payable |
(6,000,000) |
(15,250,000) |
(15,000,000) |
| Common Stock |
(10,131,250) |
(10,131,250) |
(10,131,250) |
| Paid-in Capital |
(9,278,750) |
(9,278,750) |
(9,278,750) |
| Retained Earnings |
(2,773,901) |
(2,238,105) |
(9,185,791) |
| Dividends |
6,000,000 |
15,250,000 |
15,000,000 |
| Sales |
(307,716,148) |
(271,839,067) |
(288,876,206) |
| Sales Returns |
5,621,979 |
12,432,247 |
23,110,096 |
| Warranty Expense |
1,375,352 |
1,297,104 |
1,444,381 |
| Income from Investments |
- |
(1,227,199) |
(1,138,905) |
| Interest Income |
(255,379) |
(147,707) |
(142,168) |
|
|
|
|
| Cost of Goods Sold |
176,961,527 |
161,029,981 |
179,103,248 |
| Freight |
5,378,689 |
4,749,095 |
4,325,068 |
| Advertising Expense |
1,121,425 |
1,161,276 |
1,057,591 |
| Auto Expenses |
261,218 |
235,763 |
214,713 |
| Research and Development |
39,015,418 |
592,335 |
3,080,313 |
| Depreciation Expense |
166,250 |
581,012 |
617,155 |
| Warehouse Salaries |
5,791,730 |
5,348,208 |
4,870,689 |
| Property Tax Expense |
100,619 |
111,252 |
101,319 |
| Legal and Professional Expense |
4,506,417 |
10,435,113 |
9,503,406 |
| Bad Debt Expense |
2,028,032 |
5,875,403 |
13,963,800 |
| Insurance Expense |
1,067,428 |
1,045,085 |
951,774 |
| Maintenance Expense |
76,420 |
96,220 |
87,629 |
| Utilities |
169,554 |
170,855 |
155,600 |
| Phone |
95,467 |
58,911 |
53,651 |
| Postal |
160,042 |
87,140 |
79,360 |
| Miscellaneous Office Expense |
21,279 |
27,879 |
25,390 |
| Payroll Tax Exp |
1,938,736 |
1,767,149 |
1,609,368 |
| Pension/Profit-Sharing Plan Ex |
3,750,000 |
3,696,000 |
3,366,000 |
| Rent or Lease Expense |
3,254,357 |
1,351,363 |
2,230,705 |
| Administrative Wages Expense |
21,094,132 |
18,344,399 |
19,706,506 |
| Bonus expense |
- |
504,000 |
459,000 |
| Interest Expense |
1,093,750 |
3,373,056 |
2,842,147 |
| Income Tax Expense - Federal |
2,956,250 |
14,142,240 |
7,269,540 |
| Income Tax Expense - State |
536,250 |
2,503,200 |
1,258,000 |
| Loss on Legal Settlement |
23,965,000 |
- |
- |