Accounting Excel Assignment

profilerosell
management_accounting_problem_2.xls
Home>Business & Finance homework help>Accounting homework help>Accounting Excel Assignment

P15-11

Student Name:
Class:
Problem 15-11
1.
MODERN BUILDING SUPPLY
Ratios
This Year Last Year
curret assets $ 2,060,000 $ 1,470,000
current liabilities $ 1,100,000 $ 600,000
Working capital $ 960,000 $ 870,000
Correct! Correct!
current assets $ 2,060,000 $ 1,470,000
current liabilities $ 1,100,000 $ 600,000
Current ratio 2.45
0 Correct!
c,ms,ar,s.t. note $ 740,000 $ 6,450,000
current liabilities $ 1,100,000 $ 600,000
Acid-test ratio
0 0
sales $ 7,000,000 $ 6,000,000
A.R. $ 525,000 $ 400,000
Accounts receivable turnover 13.3 15.0
Average collection period
0 0
cogs $ 5,400,000 $ 4,800,000
Inventory $ 1,050,000 $ 800,000
Inventory turnover ratio 5.1 6.0
Average sales period
0 0
total liabilites $ 1,850,000 $ 1,350,000
stockholders equity $ 2,150,000 $ 1,950,000
Debt-to-equity ratio 0.860
Correct! 0
earnings $ 630,000 $ 490,000
interest expense $ 90,000 $ 90,000
Times interest earned 7.0
Correct! 0
2a.
MODERN BUILDING SUPPLY
Common-Size Balance Sheets
This Year Last Year
Current assets:
Cash 55.0%
Marketable securities
Accounts receivable, net
Inventory
Prepaid expenses
Total current assets
Plant and equipment, net
Total assets
0 0
Liabilities:
Current liabilities
Bonds payable, 12%
Total liabilities
Stockholders' equity:
Preferred stock, $50 par, 8%
Common stock, $10 par
Retained earnings
Total stockholders' equity
Total liabilities and equity
0 0
2b.
MODERN BUILDING SUPPLY
Common-Size Income Statements
This Year Last Year
Sales
Cost of goods sold
Gross margin
Selling and administrative expenses
Net operating income
Interest expense
Net income before taxes
Income taxes
Net income
0 0
Enter appropriate data in yellow cells. Your answers for each of the ratios will be verified.
Enter appropriate data in yellow cells.
Enter appropriate data in yellow cells.

Given P15-11

Given Data P15-11:
Requested loan amount $ 300,000
MODERN BUILDING SUPPLY
Comparative Balance Sheet
This Year Last Year
Assets
Current assets:
Cash $ 90,000 $ 200,000
Marketable securities - 50,000
Accounts receivable, net 650,000 400,000
Inventory 1,300,000 800,000
Prepaid expenses 20,000 20,000
Total current assets 2,060,000 1,470,000
Plant and equipment, net 1,940,000 1,830,000
Total assets $ 4,000,000 $ 3,300,000
Liabilities and Stockholders' Equity
Liabilities:
Current liabilities $ 1,100,000 $ 600,000
Bonds payable, 12% 750,000 750,000
Total liabilities 1,850,000 1,350,000
Stockholders' equity:
Preferred stock, $50 par, 8% 200,000 200,000
Common stock, $10 par 500,000 500,000
Retained earnings 1,450,000 1,250,000
Total stockholders' equity 2,150,000 1,950,000
Total liabilities and stockholders' equity $ 4,000,000 $ 3,300,000
MODERN BUILDING SUPPLY
Comparative Income Statement and Reconciliation
This Year Last Year
Sales $ 7,000,000 $ 6,000,000
Cost of goods sold 5,400,000 4,800,000
Gross margin 1,600,000 1,200,000
Selling and administrative expenses 970,000 710,000
Net operating income 630,000 490,000
Interest expense 90,000 90,000
Net income before taxes 540,000 400,000
Income taxes (40%) 216,000 160,000
Net income 324,000 240,000
Dividends paid:
Preferred dividends 16,000 16,000
Common dividends 108,000 60,000
Total dividends paid 124,000 76,000
Net income retained 200,000 164,000
Retained earnings, beginning of year 1,250,000 1,086,000
Retained earnings, end of year $ 1,450,000 $ 1,250,000
Typical ratios:
Current ratio 2.5
Acid-test ratio 1.2
Average collection period 18 days
Average sale period 50 days
Debt-to-equity ratio 0.75
Times interest earned 6.0
Return on total assets 10%
Price-earnings ratio 9
Accounts receivable, beginning of last year $ 350,000
Inventory, beginning of last year $ 720,000

P15-16

Student Name:
Class:
Problem 15-16
HEDRICK COMPANY
Rates of Return
1a.
This Year Last Year
Net income
Add after-tax cost of interest:
Total
Average total assets
Return on total assets
0 0
1b.
Net income
Less preferred dividends
Net income remaining for common
0 0
Average total stockholders' equity
Less average preferred stock
Average common equity
Return on common equity
0 0
HEDRICK COMPANY
Stockholders' Well Being
2a.
Net income remaining for common
Avg. number of common shares outstanding
Earnings per share
0 0
2b.
Dividends per share
Market price per share
Dividend yield ratio
0 0
2c.
Dividends per share
Earnings per share
Dividend payout ratio
0 0
2d.
Market price per share
Earnings per share
Price-earnings ratio
0 0
2e.
Stockholders' equity
Less preferred stock
Common stockholders' equity
Number of common shares
Book value per share
0 0
2f.
Gross margin
Sales
Gross margin percentage
0 0
3.
HEDRICK COMPANY
Ratios
This Year Last Year
Working capital
0 0
Current ratio
0 0
Acid-test ratio
0 0
Average collection period
0 0
Average sales period
0 0
Debt-to-equity ratio
0 0
Times interest earned
0 0
Enter appropriate data in yellow cells. Your answers for each of the ratios will be verified.
Enter appropriate data in yellow cells. Your answers for each of the sections will be verified.
Enter appropriate data in yellow cells. Your answers for each of the ratios will be verified.
Enter appropriate data in yellow cells. Your answers for each of the ratios will be verified.

Given SP15-16

Given Data SP15-16:
Requested loan amount $ 1,000,000
HEDRICK COMPANY
Comparative Balance Sheet
This Year Last Year
Assets
Current assets:
Cash $320,000 $420,000
Marketable securities - 100,000
Accounts receivable, net 900,000 600,000
Inventory 1,300,000 800,000
Prepaid expenses 80,000 60,000
Total current assets 2,600,000 1,980,000
Plant and equipment, net 3,100,000 2,980,000
Total assets $ 5,700,000 $ 4,960,000
Liabilities and Stockholders' Equity
Liabilities:
Current liabilities $ 1,300,000 $ 920,000
Bonds payable, 10% 1,200,000 1,000,000
Total liabilities 2,500,000 1,920,000
Stockholders' equity:
Preferred stock, 8%, $30 par value 600,000 600,000
Common stock, $40 par value 2,000,000 2,000,000
Retained earnings 600,000 440,000
Total stockholders' equity 3,200,000 3,040,000
Total liabilities and stockholders' equity $ 5,700,000 $ 4,960,000
HEDRICK COMPANY
Comparative Income Statement and Reconciliation
This Year Last Year
Sales (all on account) $ 5,250,000 $ 4,160,000
Cost of goods sold 4,200,000 3,300,000
Gross margin 1,050,000 860,000
Selling and administrative expenses 530,000 520,000
Net operating income 520,000 340,000
Interest expense 120,000 100,000
Net income before taxes 400,000 240,000
Income taxes (30%) 120,000 72,000
Net income 280,000 168,000
Dividends paid:
Preferred stock 48,000 48,000
Common stock 72,000 36,000
Total dividends paid 120,000 84,000
Net income retained 160,000 84,000
Retained earnings, beginning of year 440,000 356,000
Retained earnings, end of year $ 600,000 $ 440,000
Tax rate 30%
Percentage increase in sales 25%
Common stock price, last year $ 20
Common stock price, this year $ 36
Typical ratios:
Current ratio 2.3
Acid-test ratio 1.2
Average collection period 31 days
Average sale period 60 days
Return on assets 9.5%
Debt-to-equity ratio 0.65
Times interest earned ratio 5.7
Price-earnings ratio 10
Total assets beginning last year $ 4,320,000
Stockholders' equity beginning last year $ 3,016,000
Accounts receivable, beginning of last year $ 520,000
Inventory, beginning of last year $ 640,000

P15-19

Student Name:
Class:
Problem 15-19
TANNER COMPANY
Computations
Interest expense
Times interest earned
Earnings before interest & taxes
0
Earnings before interest & taxes
Interest expense
Net income before taxes
0
Income tax expense
Net income
0
Sales on account
Average accounts receivable balance
Accounts receivable turnover
Ending accounts receivable balance
0
Quick assets
Current liabilities
Acid-test ratio
Cash
0
Current assets
Current liabilities
Current ratio
Inventory
0
Average inventory
Inventory turnover
Cost of goods sold
0
Gross margin
0
Gross margin
Net operating income
Operating expenses
0
Interest expense
Interest rate
Bonds payable
0
Current liabilities
Bonds payable
Total liabilities
0
Net income, less preferred dividends
Avg. number of common shares outstanding
Earnings per share
Total common stock
0
Total liabilities
Debt-to-equity ratio
Stockholders' equity
0
Total stockholders' equity
Common stock
Retained earnings
0
Total liabilities
Stockholders' equity
Total assets
0
Total assets
Current assets
Plant & equipment
0
TANNER COMPANY
Income Statement
For the Year Ended March 31
Sales
Cost of goods sold
Gross margin
Selling and administrative expenses
Net operating income
Interest expense
Net income before taxes
Income taxes
Net income
0
TANNER COMPANY
Balance Sheet
March 31
Current assets:
Cash
Accounts receivable, net
Inventory
Total current assets
Plant and equipment
Total assets
Current liabilities
Bonds payable, 10%
Total liabilities
Stockholders' equity:
Common stock, $2.50 par value
Retained earnings
Total stockholders' equity
Total liabilities and equity
0
Enter appropriate data in yellow cells. Use your computations from above to complete the missing amounts in the financial statements. "Net Income" and "Total liabilities and equity" will be verified.
Enter appropriate data in yellow cells. Use these computations to complete the missing amounts in the financial statements below.

Given P15-19

Given Data P15-19:
TANNER COMPANY
Income Statement
For the Year Ended December 31
Sales $ 2,700,000
Cost of goods sold ?
Gross margin ?
Selling and administrative expenses ?
Net operating income ?
Interest expense 45,000
Net income before taxes ?
Income taxes (40%) ?
Net income ?
TANNER COMPANY
Balance Sheet
December 31
Current assets:
Cash ?
Accounts receivable, net ?
Inventory ?
Total current assets ?
Plant and equipment, net ?
Total assets ?
Liabilities
Current liabilities $ 250,000
Bonds payable, 10% ?
Total liabilities ?
Stockholders' equity:
Common stock, $2.50 par value ?
Retained earnings ?
Total stockholders' equity ?
Total liabilities and equity ?
Selected financial ratios computed from above statements:
Current ratio 2.40
Acid-test ratio 1.12
Accounts receivable turnover 15.0
Inventory turnover 6.0
Debt-to-equity ratio 0.875
Times interest earned 7.0
Earnings per share $4.05
Return on total assets 14%
Selected balances at beginning of current year:
Accounts receivable $ 160,000
Inventory $ 280,000
Total assets $ 1,200,000