The commercial loan officer of Topeka National Bank requested financial reports for the last 2 fiscal years.
BRADBURN CORPORATION
Statement of Financial Position
March 31
Assets
2013
2012
Cash
$18,200
$12,500
Notes receivable
148,000
132,000
Accounts receivable (net)
131,800
125,500
Inventories (at cost)
105,000
50,000
Plant & equipment (net of depreciation)
1,449,000
1,420,500
Total assets
$1,852,000
$1,740,500
Liabilities and Owners' Equity
Accounts payable
$79,000
$91,000
Notes payable
76,000
61,500
Accrued liabilities
9,000
6,000
Common stock (130,000 shares, $10 par)
1,300,000
1,300,000
388,000
282,000
Total liabilities and owners' equity
$1,852,000
$1,740,500
SANDBURG CORPORATION
Income Statement
For The Fiscal Year Ended March 31
2013
2012
Sales
$3,000,000
$2,700,000
Cost of goods sold
1,530,000
1,425,000
Gross margin
1,470,000
1,275,000
Operating expenses
860,000
780,000
Income before income taxes
610,000
495,000
Income taxes
244,000
198,000
Net income after income taxes
$366,000
$297,000
Depreciation charges on the plant and equipment of
$100,000
and
$102,500
for the fiscal years ended March 31, 2012, and 2013, respectively, are included in cost of goods sold.
Instructions:
Fill in the provided matrix and utilize it as the matrix for "VLOOKUP" formulas within the cells below.
Column 4
Column 5
2013
2012
Average inventory - 2011
Formula
Average total assets
Formula
Formula
Total Assets = Mar 31, 2009
Formula
Total Assets = Mar 31, 2010
Formula
Total Assets = Mar 31, 2011
Amount
Cost of goods sold
Amount
Amount
Current assets
Amount
Amount
Current liabilities
Amount
Amount
Dividends
Amount
Amount
Depreciation
Amount
Amount
Gross margin
Amount
Amount
Income before taxes
Amount
Amount
Income taxes (40%)
Amount
Amount
Inventories = EOY 2010
Amount
Inventories = EOY 2011
Amount
Net income after taxes
Amount
Amount
Operating expenses
Amount
Amount
Sales
Amount
Amount
2012 Current ratio =
Amount
---------------- =
Formula
to 1
Amount
2013 Current ratio =
Formula
---------------- =
Formula
to 1
Formula
2012 Quick ratio =
Formula
---------------- =
Formula
Formula
to 1
2013 Quick ratio =
Formula
---------------- =
Formula
Formula
to 1
2013 Inventory Turnover =
Amount
---------------- =
Formula
#N/A
to 1
$1,688,500
at March 31, 2011.)
2012 Return on assets =
Formula
---------------- =
Formula
Formula
2013 Return on assets
=
Formula
---------------- =
Formula
Formula
2012
2013
Change
Percent Change
Sales
Formula
Formula
Formula
Formula
Cost of goods sold
Formula
Formula
Formula
Formula
Gross margin
Formula
Formula
Formula
Formula
Net income after taxes
Formula
Formula
Formula
Formula
Note: The formulas in some cell formulas are "live" and need values placed in their source cells.
1
Enter text answer as appropriate.
2
Enter text answer as appropriate.
3
Enter text answer as appropriate.
4
Enter text answer as appropriate.
Enter text answer as appropriate.
2013
2014
2015
Sales
Formula
Formula
Formula
Title
Formula
Formula
Formula
Gross margin
Formula
Formula
Formula
Title
Formula
Formula
Formula
Income before taxes
Formula
Formula
Formula
Title
Formula
Formula
Formula
Net income
Formula
Formula
Formula
Add: Title
Amount
Amount
Deduct: Title
Amount
Amount
Note repayment
Amount
Funds available for plant expansion
Formula
Formula
Plant expansion
Amount
Amount
Excess funds
Formula
Formula
Assumptions:
Sales increase at a rate of
Cost of goods sold increases at rate of
despite depreciation remaining constant.