AC 302 Chapter 24 Question Problem 3

AC 302 Chapter 24 Question Problem 3
Name				Date			
Instructor				Course			
Intermediate Accounting 14th Edition by Kieso Weygandt and Warfield							
Primer on Using Excel in Accounting by Rex A Schildhouse							
							
P24-3 (Ratio Computations and Additional Analysis) Bradburn Corporation was formed 5 years ago 							
through a public subscription of common stock. Daniel Brown, who owns						15%	of the 
common stock, was one of the organizers of Bradburn and is its current president. The company has been successful, but it currently is experiencing a shortage of funds. On June 10, Daniel Brown approached the 							
							
Topeka National Bank, asking for a 24-month extension on two					$35,000 	notes, which are due on 	
June 30, 2013, and September 30, 2013. Another note of				$6,000 	is due on March 31, 2014, but he 		
expects no difficulty in paying this note on its due date. Brown explained that Bradburn- cash flow problems 							
are due primarily to the company- desire to finance a				$300,000 	plant expansion over the next 		
2 fiscal years through internally generated funds.							
							
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 	
Retained earningsa					388,000 	282,000 	
Total liabilities and owners' equity					$1,852,000 	$1,740,500 	
aCash dividends were paid at the rate of $1.00 per share in fiscal year 2012 and $2.00 per share in fiscal year 2013.							
							
							
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		
							
(a) Compute the following items for Bradburn Corporation:							
(1) Current ratio for fiscal years 2012 and 2013.							
							
	2012 Current ratio = 		"Current assets
  ----------------------- =
Current liabilities"		Amount		
					---------------- =	Formula	to 1
					Amount		
							
	2013 Current ratio = 		"Current assets
  ----------------------- =
Current liabilities"		Formula		
					---------------- =	Formula	to 1
					Formula		
							
(2) Acid-test (quick) ratio for fiscal years 2012 and 2013.							
							
	2012 Quick ratio = 		"Current assets - Inventories
  ----------------------- =
Current liabilities"			Formula	
						---------------- =	Formula
						Formula	to 1
							
	2013 Quick ratio = 		"Current assets - Inventories
  ----------------------- =
Current liabilities"			Formula	
						---------------- =	Formula
						Formula	to 1
							
(3) Inventory turnover for fiscal year 2013.							
							
	2013 Inventory Turnover = 		"Cost of goods sold
  ------------------------------------ =
Average inventory"			Amount	
						---------------- =	Formula
						#N/A	to 1
							
(4) Return on assets for fiscal years 2012 and 2013. (Assume total assets were						$1,688,500 	
  at March 31, 2011.)							
							
	2012 Return on assets = 		"Net income
  ----------------------- =
Average total assets"		Formula		
					---------------- =	Formula	
					Formula		
							
	2013 Return on assets  = 		"Current assets
  ----------------------- =
Current liabilities"		Formula		
					---------------- =	Formula	
					Formula		
							
"(5) Percentage change in sales, cost of goods sold, gross margin, and net income after taxes from fiscal year
     2012 to 2013. Omit ""000"" from the values."							
							
							
		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.							
"(b) Identify and explain what other financial reports and/or financial analyses might be helpful to the
     commercial loan officer of Topeka National Bank in evaluating Daniel Brown- request for a time extension
     on Bradburn- notes."							
							
							
							
Other financial reports and financial analyses which might be helpful to the commercial loan officer of Spokane National Bank include:							
							
							
1	Enter text answer as appropriate.						
							
							
							
							
							
2	Enter text answer as appropriate.						
							
							
							
							
							
3	Enter text answer as appropriate.						
							
							
							
							
							
4	Enter text answer as appropriate.						
							
							
							
							
							
"(c) Assume that the percentage changes experienced in fiscal year 2013 as compared with fiscal year 2012
     for sales and cost of goods sold will be repeated in each of the next 2 years. Is Bradburn- desire to
     finance the plant expansion from internally generated funds realistic? Discuss."							
							
							
							
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.						
	Other operating expenses increase at the same rate experienced from 2010 to 2011; i.e., at						
							
	Depreciation remains constant at						
	Dividends remain at 			per share.			
	Plant expansion is financed equally over the two years(					 each year).	
	Loan extension is granted.						
							
"(d) Should Topeka National Bank grant the extension on Bradburn- notes considering Daniel Brown- statement
     about financing the plant expansion through internally generated funds? Discuss."							
							
							
Enter text answer here.