Question

CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Homework for Chapter 2: Problem # 1 in the text (Chapter 2) Using the data presented below for Blue Sky Inc.: 2017 2016 S7,550,000 $6,150,000 5,750,000 Sales Cost of Goods Depreciation Selling and G&A Expenses Fixed Expenses Lease Expense Interest Expense 4,550,000 120,000 100,000 820,000 730,000 200,000 200,000 150,000 150,000 350,000 300,000 Tax Rate 40.00% 40.00% Shares Outstanding Cash Marketable Securities Accounts Receivable Inventory Prepaid Expenses Plant & Equipment Accumulated Depreciation Long Term Investments Accounts Payable Notes Payable Accrued Expenses Other Current Liabilities Long-term Debt Common Stock Additional Paid-in-Capital Retained Earnings 100,000 108,000 80,000 50,000 150,000 100,000 450,000 350,000 1,250,000 850,000 40,000 120,000 5,350.000 4,800,000 290,000 410,000 450,000 360,000 420,000 380.000 150,000 150,000 200.000 100,000 100,000 180,000 2.900,000 2,500,000 2,500,000 2,000,000 600,000 500,000 548,000 500,000 a) 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. b) On another worksheet, create a statement of cash flows for 2017. All formulas should be linked directly to the source on previous worksheets. c) Using Excel's outlining feature, create an outline on the balance sheet that, when collapsed, shows only the subtotals for each section.

please show all work in Excel

Answer 1

BLUE SKY INC.
FOR THE YEAR INDED DEC.31/2017 ($IN 000'S)
	2017	2016
	$	$
Sales	7550	6150
Cost of good sold	5750	4550
Gross profit	1800	1600
Selling and G&Aexpenses	820	730
Fixed expenses	200	200
Depreciation	120	100
Lease expense	150	150
EBIT	510	420
Interest expenses	350	300
Earning before taxes	160	120
Taxes	64	48
Net income	96	72
NOTE:
tax rate	40%	40%
share outstanding	100	80
earning per share	0.96	0.9
dividends per share	0.48
addition to RE per share	5.48	6.25
BLUE SKY INC.
BALANCE SHEET
AS OF DEC.31/2017($in 000's)
ASSETS	2017	2016
Cash	108	50
Marketable securities	150	100
Account receivable	450	350
Inventory	1250	850
Prepaid expenses	120	40
Total current assets	2078	1390
Plant & equipment	5350	4800
Accumulated depreciation	410	290
Net fixed assets	4940	4510
Long term investment	450	360
Total assets	7468	6260
Liabilities and Owners equity
Account payable	420	380
Notes payable	150	100
Accured Expenses	150	100
Other current liabilities	200	180
Total current liabilities	920	760
liong term debt	2900	2500
Total liabilities	3820	3260
Common stock($25 per)	2500	2000
Additional paid in capital	600	500
Retaining earnings	548	500
Total shareholders equity	3648	3000
Total liabilities and owners equity	7468	6260
BLUE SKY INC.
STATEMENT OF CASH FLOWS
FOR THE YEAR ENDED DEC.31/2017($ in 000's)
Cash flow from Operations
Net income	96
Depreciation expenses	120
Change in Marketable securities	-50
Change in account receiables	-100
Change in inventories	-400
Change in prepaid expenses	-80
Change in account payable	40
Change in accured expenses	50
Change in other current liabilities	20
Total Cash flow from Operations	-304
Cash flow from investing
Change in plant and equipment	-550
Change in long term investment	-90
Total Cash flow from investing	-640
Cash flows from financing
Change in short term note payable	50
Change in long term debt	400
Change in common stock	500
Change in paid in capital	100
Cash dividends paid to shareholders	-48
Total Cash flows from financing	1002
Net change in cash balance	58
Beginning cash from balance sheet	50
Ending cash from balance sheet	108
Net change in cash balance	58
Dividend paid during the year=opening retaing earning+net progit during the year-closing retaing earning
= 500000+96000-548000
=48000