Question

Using the data presented below for Blue Sky Inc.:

	2017	2016
Sales	$7,550,000	$6,150,000
Cost of Goods	5,750,000	4,550,000
Depreciation	120,000	100,000
Selling and G&A Expenses	820,000	730,000
Fixed Expenses	200,000	200,000
Lease Expense	150,000	150,000
Interest Expense	350,000	300,000
Tax Rate	40.00%	40.00%
Shares Outstanding	100,000	80,000
Cash	108,000	50,000
Marketable Securities	150,000	100,000
Accounts Receivable	450,000	350,000
Inventory	1,250,000	850,000
Prepaid Expenses	120,000	40,000
Plant & Equipment	5,350,000	4,800,000
Accumulated Depreciation	410,000	290,000
Long Term Investments	450,000	360,000
Accounts Payable	420,000	380,000
Notes Payable	150,000	100,000
Accrued Expenses	150,000	100,000
Other Current Liabilities	200,000	180,000
Long-term Debt	2,900,000	2,500,000
Common Stock	2,500,000	2,000,000
Additional Paid-in-Capital	600,000	500,000
Retained Earnings	548,000	500,000

1 Create Blue Sky’s income statement and balance sheet using formulas wherever possible. Each statement should be on a separate worksheet. Improve the readability of the data by using the format explained on page 49, so that Excel will display the numbers as if they had been divided by 1,000. Make the appropriate note on the heading of each financial statement.

2 Create a common-size income statement and balance sheet for 2017 and 2016. These statements should be created on a separate worksheet with all formulas linked directly to the income statement and balance sheet.

Please show all work.

Answer 1

Income statement
		2017
	2016
Sales	$6,150,000	$7,550,000
Cost of Goods	4,550,000	5,750,000
gross profit	1,600,000	1,800,000
Depreciation	100,000	120,000
Selling and G&A Expenses	730,000	820,000
Fixed Expenses	200,000	200,000
Lease Expense	150,000	150,000
Interest Expense	300,000	350,000
profit befor tax	120,000	160,000
Tax Rate -40%	48000	64000
net profit	72,000	96,000
Balance Sheet	2016	2017
Assets
Cash	50,000	108,000
Marketable Securities	100,000	150,000
Accounts Receivable	350,000	450,000
Inventory	850,000	1,250,000
Prepaid Expenses	40,000	120,000
total of current assets	1,390,000	2,078,000
Plant & Equipment less accumulated depreciation	4,510,000	4,940,000
Long Term Investments	360,000	450,000
total of assets	6,260,000	2,078,000
Liabilities and shareholders equity
Accounts Payable	380,000	420,000
Notes Payable	100,000	150,000
Accrued Expenses	100,000	150,000
Other Current Liabilities	180,000	200,000
total of current liabilities	760,000	920,000
Long-term Debt	2,500,000	2,900,000
total of liabilities	3,260,000	3,820,000
Common Stock	2,000,000	2,500,000
Additional Paid-in-Capital	500,000	600,000
Retained Earnings	500,000	548,000
total of shareholders equity	3,000,000	3,648,000
total of liabilities and shareholders equity	6,260,000	7,468,000
Common Size income statement			2017
	2016	Individual item as % of sales =( individual item/amount of sales)*100		Individual item as % of sales =( individual item/amount of sales)*100
Sales	$6,150,000	100%	$7,550,000	100%
Cost of Goods	4,550,000	73.98%	5,750,000	76.16%
gross profit	1,600,000	26.02%	1,800,000	23.84%
Depreciation	100,000	1.63%	120,000	1.59%
Selling and G&A Expenses	730,000	11.87%	820,000	10.86%
Fixed Expenses	200,000	3.25%	200,000	2.65%
Lease Expense	150,000	2.44%	150,000	1.99%
Interest Expense	300,000	4.88%	350,000	4.64%
profit befor tax	120,000	1.95%	160,000	2.12%
Tax Rate -40%	48000	0.78%	64000	0.85%
net profit	72,000	1.17%	96,000	1.27%
common size Balance Sheet			2017
Assets	2016	Individual item as % of total assets =( individual item/amount of total assets)*100	2017	Individual item as % of total assets =( individual item/amount of total assets)*100
Cash	50,000	0.80%	108,000	1.45%
Marketable Securities	100,000	1.60%	150,000	2.01%
Accounts Receivable	350,000	5.59%	450,000	6.03%
Inventory	850,000	13.58%	1,250,000	16.74%
Prepaid Expenses	40,000	0.64%	120,000	1.61%
total of current assets	1,390,000	22.20%	2,078,000	27.83%
Plant & Equipment less accumulated depreciation	4,510,000	72.04%	4,940,000	66.15%
		0.00%
Long Term Investments	360,000	5.75%	450,000	6.03%
total of assets	6,260,000	100.00%	7,468,000	100.00%
Liabilities and shareholders equity
Accounts Payable	380,000	6.07%	420,000	5.62%
Notes Payable	100,000	1.60%	150,000	2.01%
Accrued Expenses	100,000	1.60%	150,000	2.01%
Other Current Liabilities	180,000	2.88%	200,000	2.68%
total of current liabilities	760,000	12.14%	920,000	12.32%
Long-term Debt	2,500,000	39.94%	2,900,000	38.83%
total of liabilities	3,260,000	52.08%	3,820,000	51.15%
Common Stock	2,000,000	31.95%	2,500,000	33.48%
Additional Paid-in-Capital	500,000	7.99%	600,000	8.03%
Retained Earnings	500,000	7.99%	548,000	7.34%
total of shareholders equity	3,000,000	47.92%	3,648,000	48.85%
total of liabilities and shareholders equity	6,260,000	100.00%	7,468,000	100.00%