Using the data presented below for Blue Sky Inc.: 2017 2016 Sales $7,550,000 $6,
ID: 2815994 • Letter: U
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.
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
Explanation / Answer
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%
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%
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.