Accounting direct method for cash flow excel
Goodluck123
SCFProjectcopy.xlsxAssignment Information
| This assignment is worth a maximum of 25 Points. |
| Please use the information in the following tabs to complete a direct method and indirect method statement of cash flows. |
| Do not simply type in numbers into your statement of cash flows. Instead, use formulas, cell references, and etc. in order to create the statement of cash flows. |
| I will expect to see your SCF change if I change something in any of the relevant statements. I also expect to see the statement of cash flows articulate to the change in cash on the balance sheet. If it does not match, you have not done the SCF correct. |
| This assignment is due at midnight on Monday May 18. (Submission by e-mail. Can and should be done in groups of four or less students.) |
Income Statement
| Washington Irving, Inc. | ||
| Comparative Statements of Comprehensive Income | ||
| For the Year Ending January 31, 2018 | ||
| 2017 | 2016 | |
| Gross sales | $ 3,870,250.00 | $ 3,250,000.00 |
| Less: Sales returns (1) | $ (275,000.00) | $ (150,000.00) |
| Net sales | $ 3,595,250.00 | $ 3,100,000.00 |
| Cost of goods sold | $ (2,260,890.00) | $ (1,850,000.00) |
| Gross profit | $ 1,334,360.00 | $ 1,250,000.00 |
| Operating expenses: | ||
| Selling expenses | $ 526,800.00 | $ 425,000.00 |
| General and administrative expenses | $ 382,500.00 | $ 279,800.00 |
| Research and development expenses | $ 75,000.00 | $ 125,000.00 |
| Goodwill impairment | $ 60,000.00 | $ 200,000.00 |
| Depreciation expense | $ 425,000.00 | $ 365,000.00 |
| Amortization expense (leases) | $ 60,872.40 | $ - 0 |
| Bad debts expense | $ 24,890.00 | $ - 0 |
| Total operating expenses | $ 1,555,062.40 | $ 1,394,800.00 |
| Operating income | $ (220,702.40) | $ (144,800.00) |
| Other income (expense): | ||
| Interest and dividend revenue | $ 159,800.00 | $ 126,500.00 |
| Gain (loss) on sale of TS investments | $ (1,250.00) | $ - 0 |
| Gain (loss) on sale of property | $ (12,500.00) | $ 165,000.00 |
| Unrealized holding gain (loss) on TS investments | $ 12,590.00 | $ 18,750.00 |
| Interest expense | $ (101,287.60) | $ (90,650.00) |
| Total other income (expense), net | $ 57,352.40 | $ 219,600.00 |
| Income before income taxes | $ (163,350.00) | $ 74,800.00 |
| Income tax expense (benefit) | $ (3,972.50) | $ 26,180.00 |
| Net income | $ (159,377.50) | $ 48,620.00 |
| Other Comprehensive Income | ||
| Defined benefit plans, net | $ (118,900.00) | $ (135,900.00) |
| Unrealized holding gain (loss) on AFS investments | $ 78,650.00 | $ 32,500.00 |
| Comprehensive income (loss) | $ (199,627.50) | $ (54,780.00) |
Changes in Equity
| Washington Irving, Inc. | ||||
| Statements of Equity | ||||
| For the Year Ending January 31, 2018 | ||||
| Common Stock | Additional Paid-in Capital | Retained Earnings | Accumulated Other Comprehensive Income | |
| Balance at January 31, 2016 | $ 15,000.00 | $ 10,860,000.00 | $ (43,267.00) | $ (28,680.00) |
| Net income | $ - 0 | $ - 0 | $ 48,620.00 | |
| Other comprehensive income (loss) | $ - 0 | $ - 0 | $ - 0 | $ (103,400.00) |
| Issuance of common stock | $ 72.50 | $ 45,240.00 | $ - 0 | $ - 0 |
| Purchase of common stock | $ (175.00) | $ (100,450.00) | $ - 0 | |
| Stock-based compensation | $ - 0 | $ 25,000.00 | $ - 0 | $ - 0 |
| Cash dividends declared on common stock | $ - 0 | $ - 0 | $ (1,750.00) | $ - 0 |
| Balance at January 31, 2017 | $ 14,897.50 | $ 10,829,790.00 | $ 3,603.00 | $ (132,080.00) |
| Net income | $ - 0 | $ - 0 | $ (159,377.50) | $ - 0 |
| Other comprehensive income (loss) | $ - 0 | $ - 0 | $ (40,250.00) | |
| Issuance of common stock | $ 100.00 | $ 62,400.00 | $ - 0 | $ - 0 |
| Purchase of common stock | $ (25.00) | $ (10,600.00) | $ - 0 | $ - 0 |
| Stock-based compensation | $ 25,000.00 | $ - 0 | $ - 0 | |
| Cash dividends declared on common stock | $ - 0 | $ - 0 | $ (2,250.00) | $ - 0 |
| Balance at January 31, 2018 | $ 14,972.50 | $ 10,906,590.00 | $ (158,024.50) | $ (172,330.00) |
Balance Sheet
| Washington Irving, Inc. | ||
| Comparative Balance Sheets | ||
| As of January 31, 2018 | ||
| 2017 | 2016 | |
| Assets | ||
| Current Assets: | ||
| Cash and cash equivalents | $ 754,535.50 | $ 469,037.50 |
| Accounts receivable (Net) | $ 257,070.00 | $ 233,450.00 |
| Inventory | $ 1,797,510.00 | $ 1,488,600.00 |
| Notes receivable | $ 311,700.00 | $ 339,200.00 |
| Investments | $ 995,090.00 | $ 1,068,750.00 |
| Total current assets | $ 4,115,905.50 | $ 3,599,037.50 |
| Non-current Assets | ||
| Property, plant, and equipment (Net) | $ 12,193,000.00 | $ 12,700,000.00 |
| Right-of-use asset | $205,420.97 | $ - 0 |
| Investments | $ 3,709,800.00 | $ 3,631,150.00 |
| Goodwill and intangible assets | $ 2,300,000.00 | $ 2,360,000.00 |
| Deferred income taxes | $ 29,562.50 | $ - 0 |
| Total non-current assets | $ 18,437,783.47 | $ 18,691,150.00 |
| Total Assets | $ 22,553,688.97 | $ 22,290,187.50 |
| Liabilities and Equity | ||
| Current Liabilities: | ||
| Accounts payable | $ 242,100.00 | $ 230,100.00 |
| Notes payable | $ 258,300.00 | $ 280,900.00 |
| Income taxes payable | $ - 0 | $ 3,477.00 |
| Current lease payable | $ 72,500.00 | $ - 0 |
| Current maturity of long-term debt | $ 256,900.00 | $ - 0 |
| Total current liabilities | $ 829,800.00 | $ 514,477.00 |
| Non-current Liabilities | ||
| Long-term debt (net) | $ 4,574,310.00 | $ 4,861,100.00 |
| Pensions | $ 6,302,590.00 | $ 6,151,130.00 |
| Leases Payable | $ 132,920.97 | $ - 0 |
| Deferred income taxes | $ 51,860.00 | $ 47,270.00 |
| Liability - RSU Plan | $ 50,000.00 | $ - 0 |
| Liability - Potential Additional Tax | $ 21,000.00 | $ - 0 |
| Total noncurrent liabilities | $ 11,132,680.97 | $ 11,059,500.00 |
| Total Liabilities | $ 11,962,480.97 | $ 11,573,977.00 |
| Common Stock, $0.01 par value | $ 14,972.50 | $ 14,897.50 |
| Additional paid-in capital | $ 10,906,590.00 | $ 10,829,790.00 |
| Retained earnings | $ (158,024.50) | $ 3,603.00 |
| Accumulated other comprehensive income (loss) | $ (172,330.00) | $ (132,080.00) |
| Total Equity | $ 10,591,208.00 | $ 10,716,210.50 |
| Total Liabilities and Equity | $ 22,553,688.97 | $ 22,290,187.50 |
Notes to the FS
| Note: | ||||
| The loss on sale of TS investments is for securities originally purchased for $40,000 and sold for $85,000 | BV: | $ 40,000.00 | Proceeds: | $ 85,000.00 |
| $32,000 of interest revenue is nontaxable | Nontax Rev | $ 32,000.00 | ||
| Cash sales were $3,265,900, the rest were sales on account | Cash Sales | $ 3,265,900.00 | ||
| The deferred tax liability is due to differences in tax to book depreciation methods | ||||
| Included in accounts payable are all relevant current liabilities payable, excepting income taxes payable | ||||
| No new long-term debt was issued during the year. | ||||
| G&A includes pension expense of $85,410 | Pension Exp. | $ 85,410.00 | ||
| Contributions to pension plan assets of $52,850 were made this year | Contributions | $ 52,850.00 | ||
| G&A expenses include $50,000 in RSUs payable in either equity or cash and $25,000 payable only in equity | Liab. | $ 50,000.00 | Equity | $ 25,000.00 |
| $85,260 of the returns were for sales on account | AR Returns | $ 85,260.00 | ||
| Leases have a four year lease term with one payment due immediately and then due at Feb 1 of each year thereafter | ||||
| Assume that the goodwill on the balance sheet is NEVER tax deductible and ignore all other temporary differences | Nontax Impair | $ 60,000.00 | ||
| The loss on the sale of property is for PP&E which was sold for $289,500 | PPE Proceeds | $ 289,500.00 | ||
