help in Excel
help-science1
20201023150119d4fc1d1de5309f7f4839b4376537f2bc1.xlsSheet1
| Pro-forma financial statements | Fall 20 | ANTIA | |||
| Create pro forma financial statements from the information provided below | |||||
| Income Statement | Year 1 | ||||
| Year 0 | Year 1 | Year 2 | Sales revenues increase 3.0% | ||
| Revenues | 17,000 | 17,510 | 17,247 | Gross margin is 48.5% | |
| Cost of goods sold | 9,200 | 9,018 | 8,624 | SG&A decreases by 2.5% | |
| Gross profit | 7,800 | 8,492 | 8,624 | $3000 of PP&E is purchased on January 1, | |
| SG&A | 4,790 | 4,670 | 4,740 | New PP&E is depreciated over 10 years | |
| Depreciation | 1,700 | 2,000 | 1,940 | Inventory grows at the same rate as the growth in COGS | |
| Operating Profit | 1,310 | 1,822 | 1,943 | Assume that all other asset accounts grow at the same rate as sales. | |
| Interest expense | 155 | 131 | 99 | Accounts Payable grow at the same rate as COGS | |
| Income before taxes | Accrued and deferred income taxes grows at the same rate as tax expense. | ||||
| Taxes @35% | Long-term debt increases by $1500 | ||||
| Net Income | Unless otherwise stated, liability accounts grow at the same rate as sales | ||||
| Treasury Stock purchases equal $300 | |||||
| Dividends | Average interest cost of all interest bearing debt is 2.5% | ||||
| Dividend payout ratio is 20% | |||||
| Addition to retained earnings | Tax rate is 35% | ||||
| Funding requirements should be financed with short-term debt | |||||
| Balance Sheet | |||||
| Assets | |||||
| Year 0 | Year 1 | Year 2 | Y2 | ||
| Cash and cash equivalents | Sales revenue decline by 1.5% | ||||
| Marketable securities | Gross margin increases to 50% | ||||
| Accounts Receivables | Inventory grows in line with COGS | ||||
| Inventory | 3,142 | 3,080 | 2,945 | SG&A increases by 1.5% | |
| Prepaid expen. & other assets | $800 of PP&E(net) is sold on January 1 for $800 cash. (Gross =$1000, Accumulated depreciation = $200) | ||||
| Total Current Assets | Annual depreciation expense declines by $ 60 | ||||
| Assume that all other asset accounts change at the same rate as sales. | |||||
| Plant property and equipment (gross) | 7,607 | 10,607 | 9,607 | Accounts Payable grow change at the same rate as COGS | |
| Accumulated Depreciation | 3,000 | 5,000 | 6,740 | Long-term debt declines by $150 | |
| PP&E (net) | 4,607 | 5,607 | 2,867 | Accrued and deferred income taxes change at the same rate as tax expense. | |
| Unless otherwise stated, liability accounts change at the same rate as sales. | |||||
| Total Assets | 17,940 | 19,183 | 19,690 | Treasury Stock purchase is $200. | |
| Average interest cost of all interest bearing debt is 2.1% | |||||
| Liabilities & Shareholders' Equity | Dividend payout ratio changes to 22% | ||||
| Year 0 | Year 1 | Year 2 | Tax rate is 35% | ||
| Accounts payable | 3,148 | 3,086 | 2,951 | 200 shares of $1 par value common stock is issued for $800. | |
| Loans & notes payable (plug) | Excess cash is used to retire short-term debt | ||||
| Accrued income taxes | 1,322 | 1,936 | 2,111 | Do not add significant amounts to cash unless Loans & notes payable is drawn down to zero. | |
| Total Current Liabilities | |||||
| Long-term debt | 2,800 | 4,300 | 4,150 | ||
| Defered income taxes | 195 | 286 | 311 | ||
| Shareholders' Equity | |||||
| Common Stock at par | |||||
| Capital Surplus | |||||
| Retained earnings | 6,429 | 7,308 | 8,244 | ||
| Less treasury stock | (100) | (400) | (600) | ||
| Total equity | 8,052 | 8,631 | 10,167 | ||
| Total liabilities & shareholder equity | 17,940 | 19,183 | 19,690 | ||
| Statement of Retained Earnings | Year 1 | Year 2 | |||
| Beginning retained earnings | |||||
| +Net Income | |||||
| -dividends | |||||
| Ending retained earnings | |||||
| Statement of Cash Flows | |||||
| Year 1 | Year 2 | ||||
| Net Income | |||||
| + Depreciation | |||||
| + (increase) decrease in A.R. | |||||
| + (increase) decrease in inventory | |||||
| + (increase) decrease in prepaid exp. | |||||
| + increase (decrease) in A.P. | |||||
| + increase (decrease) in accrued taxes | |||||
| + increase (decrease) in deferred taxes | |||||
| =Cash Flow from operations | |||||
| + (increase) decrease in marketable sec. | (1) | 0 | |||
| + (increase) decrease in PPE | (3,000) | 800 | |||
| =Cash Flow from investing | (3,001) | 800 | |||
| + increase (decrease) in loans and notes. | |||||
| + increase (decrease) in LTD | |||||
| + increase (decrease) in common stock | |||||
| - dividends | |||||
| - treasury stock | |||||
| =Cash flow from financing | |||||
| Beginning cash | 640 | 659 | |||
| +Change in cash | 19 | 3,529 | |||
| Ending cash | 659 | 4,188 |
MJA:
