Enter the data in Excel. 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 (000’s) format (#, ##0.00).

1. 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
Dividends per Share	$0.48	$0.45
Retained Earnings	548,000	500,000

a) Enter the data in Excel. 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 (000’s) format (#, ##0.00). Make the appropriate note on the heading of each financial statement.

b) Below Income Statement, find Earnings per Share (Hint: EPS deals with Net Income) and Addition to RE per Share (Hint: EPS less Dividends) for 2017 & 2016.

c) On another worksheet, create a statement of cash flows for 2017. All formulas should be linked directly to the source on previous worksheets.

d) Using Excel’s outlining feature, create an outline or group the balance sheet that, when collapsed, shows only the subtotals for each section.

e) Visualize 2016 & 2017 Sales, Gross Profit, and Net Income using appropriate graph and formatting.

2. Using the data from the previous problem:

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

b) Using the common-size income statement for 2017, create a forecasted income statement for 2018 assuming that each item is expected to remain in the same proportion as in 2017. The forecasted sales for 2018 are $8,500,000. Use data validation technique.

Expert Solution

Income Statement of Blue Sky
for the year ending
Perticulers	2017		2016
Perticulers	Amounts ($)	Amounts ($)	Amounts ($)	Amounts ($)
Income
Sales	7,550,000.00		6,150,000.00
Total of Income A		7,550,000.00		6,150,000.00

Less: cost of good sold	5,750,000.00		4,550,000.00
		5,750,000.00		4,550,000.00

Gross Profit ( Sale - Cost of good sold)		1,800,000.00		1,600,000.00

Expenses
Deprication	120,000.00		100,000.00
Selling & G & A Expenses	820,000.00		730,000.00
Fixed Expenses	200,000.00		200,000.00
Lease Expenses	150,000.00		150,000.00
Interest Expenses	350,000.00		300,000.00

Total Expenses B		1,640,000.00		1,480,000.00

Net Income before income taxes ( A-B)		160,000.00		120,000.00

Income Tax @ 40%	160000*40%	64,000.00	120000*40%	48,000.00

Net Income after taxes		96,000.00		72,000.00

EPS = Net Income/ Number of Outstanding Share
Share Outstanding		100,000.00		80,000.00

EPS	(96000/100000)	0.96	(72000/80000)	0.90

Addition to Retained Earing Per share		0.96		0.90



Balance of Blue Sky
As on
Perticulers	2017		2016
Perticulers	Amounts ($)	Amounts ($)	Amounts ($)	Amounts ($)
Assets
Fixed assets
Plant & Equipment	5,350,000.00		4,800,000.00
Less Accumlated Depriciation	(410,000.00)		(290,000.00)
	4,940,000.00		4,510,000.00
Long term Investment	450,000.00		360,000.00
		5,390,000.00		4,870,000.00

Current Assets
Account Receivable	450,000.00		350,000.00
Cash	108,000.00		50,000.00
Marketable Securities	150,000.00		100,000.00
Inventory	1,250,000.00		850,000.00
Prepaid Exp	120,000.00		40,000.00
Total of Current Assets		2,078,000.00		1,390,000.00

Total Assets		7,468,000.00		6,260,000.00

Liabilities
Non Current Liability
Long Term Debt	2,900,000.00	2,900,000.00	2,500,000.00	2,500,000.00
Share holder Funds
Common Stock	2,500,000.00		2,000,000.00
Additional Paid in capital	600,000.00		500,000.00
Retained Earnings	548,000.00		500,000.00
Total Equity Funds		3,648,000.00		3,000,000.00

Current Liabilities
Accounts Payable	420,000.00		380,000.00
Notes Payable	150,000.00		100,000.00
Accured Exp	150,000.00		100,000.00
Other Current Liabilities	200,000.00		180,000.00
Total Current Liabilities		920,000.00		760,000.00

Total of Liabilities		7,468,000.00		6,260,000.00




Statement of Cash Flows
© www.excel-skills.com		2017
Cash flows from operating activities
Profit before taxation		160,000.00
Adjustments for:
Depreciation		120,000.00
Interest expense		350,000.00

Working capital changes:
(Increase) / Decrease in trade and other receivables		(100,000.00)
(Increase) / (Decrease) in inventories		(400,000.00)
(Increase) / (Decrease) in Prepaid Exp		(80,000.00)
(Increase) / (Decrease) in Marketable Securities		(50,000.00)
Increase / (Decrease) in Accounts payables		40,000.00
Increase / (Decrease) in Notes payables		50,000.00
Increase / (Decrease) in Accured Exp		50,000.00
Increase / (Decrease) in Other Current Liab		20,000.00
Cash generated from operations		160,000.00
Interest paid		(350,000.00)
Income taxes paid		(64,000.00)
Dividends paid
Net cash from operating activities		(254,000.00)

Cash flows from investing activities

Purchase of property, plant and equipment		(550,000.00)

Acquisition of Longs investments		(90,000.00)

Net cash used in investing activities		(640,000.00)

Cash flows from financing activities
Proceeds from issue of share capital		100,000.00
Proceeds from issue of Common Stock		500,000.00
Proceeds from long-term borrowings		400,000.00
Increase in Retained Earning		(48,000.00)
Net cash used in financing activities		952,000.00

Net increase in cash and cash equivalents		58,000.00

Cash and cash equivalents at beginning of period		50,000.00

Cash and cash equivalents at end of period		108,000.00

Cash Balance Control Total		-




Commom Income Statement of Blue Sky
for the year ending
Perticulers	2017		2016
Perticulers	Amounts ($)	Amounts ($)	Amounts ($)	Amounts ($)
Income
Sales	7,550,000.00		6,150,000.00
Total of Income A		7,550,000.00		6,150,000.00

Less: cost of good sold	5,750,000.00		4,550,000.00
		5,750,000.00		4,550,000.00

Gross Profit ( Sale - Cost of good sold)		1,800,000.00		1,600,000.00

Expenses
Deprication	120,000.00		100,000.00
Selling & G & A Expenses	820,000.00		730,000.00
Fixed Expenses	200,000.00		200,000.00
Lease Expenses	150,000.00		150,000.00
Interest Expenses	350,000.00		300,000.00

Total Expenses B		1,640,000.00		1,480,000.00

Net Income before income taxes ( A-B)		160,000.00		120,000.00

Income Tax @ 40%	160000*40%	64,000.00	120000*40%	48,000.00

Net Income after taxes		96,000.00		72,000.00



Forcasted Income Statement of Blue Sky
for the year ending 2018
Perticulers	2017- Base Year		2018
Perticulers	Amounts ($)	Amounts ($)	Amounts ($)	Amounts ($)
Income
Sales	7,550,000.00		8,500,000.00
Total of Income A		7,550,000.00		8,500,000.00

Less: cost of good sold ( 5750000/7550000*8500000)	5,750,000.00		6,473,509.93
		5,750,000.00		6,473,509.93

Gross Profit ( Sale - Cost of good sold)		1,800,000.00		2,026,490.07

Expenses
Deprication Assume same	120,000.00		120,000.00
Selling & G & A Expenses	820,000.00		923,178.81
Fixed Expenses	200,000.00		200,000.00
Lease Expenses	150,000.00		150,000.00
Interest Expenses Assume Same	350,000.00		350,000.00

Total Expenses B		1,640,000.00		1,743,178.81

Net Income before income taxes ( A-B)		160,000.00		283,311.26

Income Tax @ 40%	160000*40%	64,000.00	120000*40%	113,324.50

Net Income after taxes		96,000.00		169,986.75

ekkarill92 answered 2 years ago

Using Microsoft Excel, create a balance sheet and an income statement using the relevant information below...

Using Microsoft Excel, create a balance sheet and an income statement using the relevant information below Calculate the Net Working Capital and the Net Operating Working Capital for both years Perform vertical analysis on both statements, for both years Perform Horizontal analysis on both statements for the most current year What was the Net Operating Profit After Taxes for both years? 2016 Accounts Receivable 400 Sales 5000 Accounts Payable 650 Inventory 1400 COGS 3000 Common Stock 1500 Cash 1600 Oper....

Create an income statement and balance sheet using the data below. Important: double the balance of...

Create an income statement and balance sheet using the data below. Important: double the balance of the Net Sales and make the necessary corrections to balance. Inputs Accounts Payable 12,000 Accounts Receivable 10,000 Accruals 10,000 Accumulated Depreciation 100,000 Beginning of year Inventory 50,000 Beginning of year Retained Earnings 120,000 Cash 7,000 Common Stock 121,500 Cost of Goods Sold 200,000 Current Portion - Long Term Debt 1,500 Depreciation Expense 25,000 Dividends 40,000 Gross Property, Plant, and Equipment 400,000 Interest Expense 15,000...

*Create a General Journal, Worksheet, Income Statement, Changes in Retained Earnings, Balance Sheet, Data for General...

*Create a General Journal, Worksheet, Income Statement, Changes in Retained Earnings, Balance Sheet, Data for General Ledger and also make a Ledger.* Byte of Accounting, Inc. Transaction Description of transaction 01. June 1: Byte of Accounting, Inc. issued 2,610 shares of its common stock to Jeremy after $29,160 in cash and computer equipment with a fair market value of $41,310 were received. 02. June 1: Byte of Accounting, Inc. issued 2,645 shares of its common stock after acquiring from Courtney...

Using the formulas provided for the exam, and the balance sheet and income statement shown below,...

Using the formulas provided for the exam, and the balance sheet and income statement shown below, calculate the ratios (1 point each): CORRIGAN CORPORATION: BALANCE SHEET AS OF DECEMBER 31: Cash 72,000 Accounts Receivable 439,000 Inventory 894,000 Total Current Assets 1,405,000 Land and Building 238,000 Machinery 132,000 Other Fixed Assets 61,000 Total Assets 1,836,000 ========== Accounts Payable 80,000 Accrued Liabilities 45,010 Notes Payable 476,990 Total Current Liabilities 602,000 Long-term Debt 404,290 Total Liabilities 1,006,290 Common Stock ...

Create a classified balance sheet. MUST BE CLASSIFIED, using these relevant accounts. SHOW EXCEL FORMULAS!

Create a classified balance sheet. MUST BE CLASSIFIED, using these relevant accounts. SHOW EXCEL FORMULAS! Cash $10,000.00 Patient receivable $50,000.00 Patient revenue $60,000.00 Prepaid Insurance $30,000.00 Insurance expense $40,000.00 Inventory (asset) $55,000.00 Labor $65,000.00 Plant and Equipment $100,000.00 Accounts payable $11,000.00 Wages payable $12,000.00 wage expense $13,000.00 Mortgage Payable $14,000.00 Net asset without donor restrictions $50,000.00 Net asset with donor restrictions ????????

Prepare the worksheet, income statement, statement of owner's equity and balance sheet using the following information....

Prepare the worksheet, income statement, statement of owner's equity and balance sheet using the following information. Account Balances of Cross Lumber Account No. 110 Cash 1300 111 Accounts Receivable 1280 112 Merchandise Inventory 4300 113 Lumber Supplies 267 114 Prepaid Insurance 209 121 Lumber Equipment 3300 122 Acc. Dep. Lumber Equipment 530 220 Accounts Payable 1200 221 Wages Payable - 330 J. Cross, Capital 5761 331 J. Cross, Withdrawls 2700 332 Income Summary - 440 Sales 23200 441 Sales Returns...

Create an income statement and balance sheet using the data below Inputs Accounts Payable 12,000 Accounts...

Create an income statement and balance sheet using the data below Inputs Accounts Payable 12,000 Accounts Receivable 10,000 Accruals 10,000 Accumulated Depreciation 100,000 Beginning of year Inventory 50,000 Beginning of year Retained Earnings 120,000 Cash 7,000 Common Stock 121,500 Cost of Goods Sold 200,000 Current Portion - Long Term Debt 1,500 Depreciation Expense 25,000 Dividends 40,000 Gross Property, Plant, and Equipment 400,000 Interest Expense 15,000 Long Term Debt (excluding current portion) 120,000 Net Sales 400,000 Operating Expenses (excluding depreciation) 40,000...

Using this Company's information, please create a Balance Sheet and Income Statement Note: Please create these...

Using this Company's information, please create a Balance Sheet and Income Statement Note: Please create these statements for the year 2018 only 2017 2018 Accounts Payable $81,000 $66,000 Accounts Receivable $110,000 $128,000 Accumulated Depreciation, PP&E $290,000 $356,000 Cash and Cash Equivalents $62,000 $54,000 Common Stock $415,000 $425,000 Cost of Goods Sold $359,000 $368,000 Depreciation Expense $62,000 $66,000 Dividends $20,000 $25,000 Income Tax Expense $18,000 $23,000 Interest Expense $44,000 $44,000 Inventory $48,000 $55,000 Long-Term Notes Payable $850,000 $810,000 Patents $678,000 $712,000...

Create a balance sheet and income statement using the following information: WIth proper financial statement formatting...

Create a balance sheet and income statement using the following information: WIth proper financial statement formatting Any missing information, please fill in. Total Shareholder Equity Long Term Debt: 3,650,000 Inventories: 350,000 Income Taxes: 85,000 Total Non-Operating Expenses, Net Common stock: 100,000 Total Assets Retained Earnings: 3,000,000 Cost of Goods Sold: 2,000,000 Interest Income: 20,000 Accounts Receivable: 1,500,000 General and Administrative Costs: 1,000,000 Accruals: 800,000 Preferred Stock Net Earnings Goodwill & Other Intangibles: 1,800,000 Net Revenue: 4,500,000 Earnings Before Income Taxes...

Using Excel and WorldCom's income statement and balance sheet for 2001, provided on pages F-2 and...

Using Excel and WorldCom's income statement and balance sheet for 2001, provided on pages F-2 and F-3 of Form 10-K, prepare a common-size balance sheet and income statement for the years 2000 and 2001. Using formulas, compute the following ratios: gross margin percent, return on sales, return on assets, return on equity, total asset turnover, accounts receivable turnover, accounts receivable days, debt to assets, equity to assets, debt to equity, equity multiplier, current ratio, acid test, net working capital, book...

Question