Reference no: EM131106755

Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file.
[Hint: I recommend you copy this problem to a Word or Excel document and print it out so that you can work with it in a reasonable manner.]

A company published the following information in its financial statements for its 2015 annual report:

Sales		$76,000
- Cost of goods sold		49,000
Gross profit		27,000
- Cash Operating expenses	$9,000
- Depreciation	2,000
Total Operating Expenses		11,000
EBIT		16,000
- Interest expense		840
EBT		15,160
- Tax expense		5,306
Net Income		$9,854
- Dividends		3,942
Addition to retained earnings		$5,912

Cash	$9,000
Marketable securities	2,000
Accounts receivable	11,000
Inventories	7,000
Fixed Assets, net	24,000
Total Assets	$53,000
Accounts payable	$8,000
Accrued payables	3,000
Bonds payable	12,000
Common stock	16,000
Retained earnings	14,000
Total Liabilities and Equity	$53,000

Sales in 2016 are estimated to be $90,000. Cost of goods sold and $5,000 of the cash operating expenses are considered variable costs.

• Depreciation and the remainder of cash operating expenses are considered to be fixed costs.
• Cash, accounts receivable, inventories, accounts payable, and accrued payables are considered to be spontaneous items.
• Marketable securities, net fixed assets, bonds payable, and common stock are discretionary.
• $5,000 of bonds payable at the end of 2015 are considered "current liabilities," and will be repaid in early 2016.
• The company purchased fixed assets of $3,600 in 2016, but depreciation for 2016 will remain the same dollar amount as it was for 2015.
• The firm will maintain its 2015 dividend payout ratio in 2016.
• The income tax rate for 2016 is expected to be the same as it was in 2015.

Required:.

Prepare the pro-forma 2016 income statement, balance sheet, and statement of cash flows