In: Accounting
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.
Income Statement of Blue Sky | ||||
for the year ending | ||||
Perticulers | 2017 | 2016 | ||
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 | ||
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 | ||
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 | ||
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 |