Question

In: Accounting

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.

Solutions

Expert Solution

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

Related Solutions

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...
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 ????????
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                                       ...
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...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT