Question

In: Accounting

Be advised, the template workbooks and worksheets are not protected. Overtyping any data may remove it....

Be advised, the template workbooks and worksheets are not protected. Overtyping any data may remove it.

All of the cells have been correctly formatted for presentation and should not require any adjustment. For example, if the text requires one, two, or three significant digits in a presentation, the template has been set for that presentation in the appropriate cells.

	In general, the highlighted cells are the cells which work and effort should be presented. These entries may include date(s), account title(s), values, memorandum appropriate to the entry, or text answers to questions.

And information or data which may be required by the solution will be entered in cells with borders to help identify them.

Where a highlighted cell shows "Date" enter the appropriate date for that step of the challenge. This may be any date format that Microsoft Excel accepts. Some of these formats include "1/1/12", "01/01/12", and "01/01/2012." All of these will return January 01, 2012, in the format set in the template.

	Where a highlighted cell shows "Acct Nbr" enter the appropriate account number, provided in the template and in the text for that step of the challenge. This is entry may be a "Look to" formula to another cell where that information has been provided or previously entered.

Where a highlighted cell shows "Account Title" enter the appropriate account title for that step of the challenge. This is a text entry and most of those cells are set for the proper indentation for that step. Frequently the chart of accounts appropriate to the challenge is provided and you can use the "look to" formula to reference the appropriate account title without typing it.

	Where a highlighted cell shows titles such as "Values," "Amounts," or "Quantities" enter the appropriate numerical value for that step of the challenge. The cell is formatted for proper presentation of the entered information. If a dollar sign is appropriate, it should not be entered, Microsoft Excel will place it there through formatting. Commas and significant digits (decimals) are also set through formatting for common presentation. Since the formatting of the templates is not protected by any password, you may change any of the formatting found in the templates to meet your desires.

Where a highlighted cell shows titles such as "Formula" you may enter the appropriate formula or enter a numerical value appropriate for that step of the challenge. Most of the values necessary for the appropriate formula are located on the template in cells with borders or in other yellow highlighted cells. The formula may be a simple "Look to" formula, an equal sign and a cell reference, "=E27" or more complex as "=E27*5," or something similar to the time-value-of-money formula. These are addressed in the tutorial text provided for Microsoft Excel.

		Where a highlighted cell shows "Text" enter the appropriate text for that step of the challenge. This may be a memorandum entry for a journal entry or a lengthy text answer discussing the results of an analysis of a company's financials. These titles can simply be typed over.

	Where a highlighted cell shows titles such as "Journal Number" or "Journ #" you should enter the appropriate number provided in the template and in the text for that step of the challenge. In general this will appear in instances such as "Record the following events in General Journal number six."

When negative values are required, enter them by starting with a minus sign, "-". Negative values may be shown as ($400) or -$400. Negative values in formulas can be created by putting a minus sign in front of the cell reference - "=E10*-E11" will return a negative value if both cells E10 and E11 contain positive values.

(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, 2014 Daniel Brown approached the

Topeka National Bank, asking for a 24-month extension on two

$35,000

notes, which are due on

June 30, 2015, and September 30, 2015. Another note of

$6,000

is due on March 31, 2016, but he

expects no difficulty in paying this note on its due date. Brown explained that Bradburn’s cash flow problems

are due primarily to the company’s 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

2015

2014

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 Stockholders' 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

Retained earnings^a

388,000

282,000

Total liabilities and stockholders' equity

$1,852,000

$1,740,500

^aCash dividends were paid at the rate of $1 per share in fiscal year 2014 and $2 per share in fiscal year 2015.

BRADBURN CORPORATION

Income Statement

For The Fiscal Year Ended March 31

2015

2014

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, 2014, and 2015, 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

2015

2014

Average inventory - 2015

Formula

Average total assets

Formula

Total Assets = Mar 31, 2013

Formula

Total Assets = Mar 31, 2014

Formula

Total Assets = Mar 31, 2015

Amount

Cost of goods sold

Amount

Current assets

Amount

Current liabilities

Amount

Dividends

Amount

Depreciation

Amount

Gross margin

Amount

Income before taxes

Amount

Income taxes (40%)

Amount

Inventories = EOY 2014

Amount

Inventories = EOY 2015

Amount

Net income after taxes

Amount

Operating expenses

Amount

Sales

Amount

(a) Compute the following items for Bradburn Corporation:

(1) Current ratio for fiscal years 2014 and 2015.

2014 Current ratio =

Current assets
----------------------- =
Current liabilities

Amount

---------------- =

Formula

to 1

Amount

2015 Current ratio =

Current assets
----------------------- =
Current liabilities

Formula

---------------- =

Formula

to 1

Formula

(2) Acid-test (quick) ratio for fiscal years 2014 and 2015.

2014 Quick ratio =

Current assets - Inventories
----------------------- =
Current liabilities

Formula

---------------- =

Formula

to 1

2015 Quick ratio =

Current assets - Inventories
----------------------- =
Current liabilities

Formula

---------------- =

Formula

to 1

(3) Inventory turnover for fiscal year 2015.

2015 Inventory Turnover =

Cost of goods sold
------------------------------------ =
Average inventory

Amount

---------------- =

Formula

#N/A

to 1

(4) Return on assets for fiscal years 2014 and 2015. (Assume total assets were

$1,688,500

at March 31, 2013.)

2014 Return on assets =

Net income
----------------------- =
Average total assets

Formula

---------------- =

Formula

2015 Return on assets =

Net income
----------------------- =
Average total assets

Formula

---------------- =

Formula

(5) Percentage change in sales, cost of goods sold, gross margin, and net income after taxes from fiscal year
2014 to 2015. Omit "000" from the values.

2014

2015

Change

Percent Change

Sales

Formula

Cost of goods sold

Formula

Gross margin

Formula

Net income after taxes

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’s request for a time extension
on Bradburn’s notes.

Other financial reports and financial analyses which might be helpful to the commercial loan officer of Spokane National Bank include:

1