Fluent Essays

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 the 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 the company anticipates 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 the financial reports (balance sheet and income statement) for the last 2 fiscal years. See the Excel template for these reports. Using this Excel template, provide the following information. Be sure to include formulas for the cells that are labeled formulas. Be sure all amounts are linked to the proper cells: Compute the following items for Bradburn Corporation: Current ratio for fiscal years 2014 and 2015. Acid-test (quick) ratio for fiscal years 2014 and 2015. Inventory turnover for fiscal year 2015. Return on assets for fiscal years 2014 and 2015. (Assume total assets were $1,688,500 at 3/31/13.) Percentage change in sales, cost of goods sold, gross margin, and net income after taxes from fiscal year 2014 to 2015. 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. Assume that the percentage changes experienced in fiscal year 2015 as compared with fiscal year 2014 for sales and cost of goods sold will be repeated in each of the next 2 years. Is Bradburn’s desire to finance the plant expansion from internally generated funds realistic? Discuss. Should Topeka National Bank grant the extension on Bradburn’s notes considering Daniel Brown’s statement about financing the plant expansion through internally generated funds? Discuss. Instructions 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 companys 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. Solution (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 1,300,000 Retained earningsa 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 Formula Total Assets = Mar 31, 2013 Formula Total Assets = Mar 31, 2014 Formula Total Assets = Mar 31, 2015 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 2014 Amount Inventories = EOY 2015 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 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 Formula to 1 2015 Quick ratio = Current assets - Inventories ----------------------- = Current liabilities Formula ---------------- = Formula Formula to 1 (3) Inventory turnover for fiscal year 2015. 2015 Inventory Turnover = Cost of goods sold ------------------------------------ = Average inventory Amount ---------------- = Formula ERROR:#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 Formula 2015 Return on assets = Net income ----------------------- = Average total assets Formula ---------------- = 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 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’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 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 2015 as compared with fiscal year 2014 for sales and cost of goods sold will be repeated in each of the next 2 years. Is Bradburn’s desire to finance the plant expansion from internally generated funds realistic? Discuss. Enter text answer as appropriate. 2015 2016 2017 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 2012 to 2013; 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’s notes considering Daniel Brown’s statement about financing the plant expansion through internally generated funds? Discuss. Enter text answer here. &F, &A, Page &P of &N, &D, &T