complete the project using Excel spreadsheet.

Bob Builder Construction Company (BBCC) is a large firm for kitchen appliance manufacturers. Peral, the finance manager of BBCC, submitted a justification to support the application for a short-term loan from the Queensville Interstate Bank (QIB) to finance increased sales. The consolidated income statement and balance sheet of BBCC are as follows.

BBCC Income Statement for 2018 and 2019 (thousand dollars)

2018

2019

Sales

$40,909

$45,000

Cost of Goods Sold

$20,909

$23,000

Gross Profit

$20,000

$22,000

Selling and Administrative Expenses

$11,818

$13,000

Depreciation Expense

$2,000

$3,000

Operating Income (EBIT)

$6,182

$6,000

Interest Expense

$400

$412

Earnings before Taxes (EBT)

$5,782

$5,588

Income Taxes (@ 40%)

$2,313

$2,235

Net Income (NI)

$3,469

$3,353

Dividends Paid (@ 21.86%)

$758

$733

BBCC Balance Sheet as of End of 2018 and 2019 (thousand dollars)

2018

2019

Assets:

Cash

$2,000

$1,800

Accounts Receivable (net)

$6,000

$7,600

Inventory

$5,000

$5,220

Plant and Equipment (gross)

$26,000

$31,000

Less: Accumulated Depreciation

$10,000

$13,000

Plant and Equipment (net)

$16,000

$18,000

Land

$1,000

$1,000

Liabilities:

Accounts Payable

$2,000

$2,600

Notes Payable

$3,000

$3,300

Accrued Expenses

$3,000

$3,100

Bonds Payable

$4,000

$4,000

Stockholders' Equity:

Common Stock

$4,000

$4,000

Retained Earnings

$14,000

$16,620

You are the loan officer at QIB responsible for determining whether BBCC's business is strong enough to be able to repay the loan. To do so, accomplish the following:

1. Calculate the following profitability ratios for 2018 and 2019, compare with the industry averages shown in parentheses, and indicate if the company is doing better or worse than the industry and whether the performance is improving or deteriorating in 2019 as compared to 2018.

a. Gross profit margin (50 percent)

b. Operating profit margin (15 percent)

c. Net profit margin (8 percent)

d. Return on assets (10 percent)

e. Return on equity (20 percent)

f. Current ratio (1.5)

g. Quick ratio (1.0)

h. Debt to total asset ratio (0.5)

i. Times interest earned (25)

j. Average collection period (45 days)

k. Inventory turnover (8)

l. Total asset turnover (1.6)

2. Calculate the EVA and MVA for BBCC, assuming that the firm's income tax rate is 40 percent, the weighted average rate of return expected by the suppliers of the firm's capital is 10 percent, and the market price of the firm's stock is $20. There are 1.2 million shares outstanding.

3. Determine the sources and uses of funds and prepare statement of cash flows for 2019.

4. Discuss the financial strengths and weaknesses of BBCC based on the financial condition as evident from the ratio analysis and the cash flow statement.

5. Which ratios should you analyze more critically before recommending granting of the loan and what is your recommendation?

ii

Ceramic Products, Inc. reports these account balances at January 1, 2018 (shown in alphabetical order). eldsys9 Accounts Payable $ 28,000 Accounts Receivable $ 20,000 Buildings $ 153,000 Capital Stock $ 185,000 Cash oldsvisosh $ 13,000 Equipment 20,000 Land $ 80,000 Notes Payable $ 24,000 Retained Earnings 49.000 On January 5, Ceramic Products collected $ 12,000 of its accounts receivable and paid $ 11,000 on its note payable. In a trial balance prepared by Ceramic Products on January 1, 2018, the total of the credit column is: a. $ 182,000 b. $ 196,000 C. $ 166,000 d. $ 286,000The following information is available: Sales $ 600,000 Net Income 30,600 Retained Earnings $ 60,000 Average Stockholders' Equity $ 200,000 Dividends $ 10,000 What is the return on equity? (Round your answer to the nearest whole number.) Multiple Choice O 5% O 15% O 20% O 25%