Excel Activity: Ratio Analysis

Start with the partial model in the file Ch$07$ P$15$ Build a Model.xlsx$.$ Joshua & White $($J&W$)$ Technology's financial statements are also shown here.

Joshua & White Technology: December $31$ Balance Sheets

$($Thousands of Dollars$)$

$20212020$

Assets

Cash $ $21,525$ $ $20,500$

Short$-$term investments $3,8543,440$

Accounts receivable $54,50051,000$

Inventories $79,00054,000$

Total current assets $ $158,879$ $ $128,940$

Net fixed assets $224,398200,000$

Total assets $ $383,277$ $ $328,940$

Liabilities & Equity

Accounts payable $ $30,975$ $ $29,500$

Accruals $10,50010,000$

Notes payable $19,1405,620$

Total current liabilities $ $60,615$ $ $45,120$

Long$-$term debt $69,48260,380$

Total liabilities $ $130,097$ $ $105,500$

Common stock $164,000164,000$

Retained earnings $89,18059,440$

Total common equity $ $253,180$ $ $223,440$

Total liabilities & equity $ $383,277$ $ $328,940$

Joshua & White Technology: Income Statements for Year

Ending on December $31($Thousands of Dollars$)$

$20212020$

Sales $ $407,400$ $ $388,000$

COGS excluding depreciation and amortization $293,000291,000$

Depreciation and amortization $19,70018,000$

Other operating expenses $26,74021,340$

EBIT $ $67,960$ $ $57,660$

Interest expense $5,7404,580$

EBT $ $62,220$ $ $53,080$

Taxes $(25\%)15,55513,270$

Net income $ $46,665$ $ $39,810$

Common dividends $ $16,925$ $ $15,924$

Additions to retained earnings $ $29,740$ $ $23,886$

Other Data $20212020$

Year$-$end stock price $ $80\mathrm{.}00$ $ $86\mathrm{.}00$

Number of shares $($Thousands$)4,0604,000$

Lease payment $($Thousands of Dollars$)$ $ $21,000$ $ $21,000$

Sinking fund payment $($Thousands of Dollars$)$ $ $4,000$ $ $4,000$

The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Round your answers to two decimal places. If your answer is zero, enter $"0".$

Download spreadsheet Ch$07$ P$15$ Build a Model$-$ac$81$ab$.$xlsx

Has J&W$\text{'}$s liquidity position improved or worsened? Explain.

$20212020$ Industry Average

Current ratio fill in the blank $2$

fill in the blank $3$

$2\mathrm{.}91$

Quick ratio fill in the blank $4$

fill in the blank $5$

$1\mathrm{.}76$

The firm's current ratio has

$,$ and the quick ratio has

$,$ indicating that J&W$\text{'}$s liquidity position has

$.$

Has J&W$\text{'}$s ability to manage its assets improved or worsened? Explain. Assume that there are $365$ days in a year.

$20212020$ Industry Average

Inventory turnover fill in the blank $9$

fill in the blank $10$

$7\mathrm{.}60$

Days sales outstanding $($days$)$ fill in the blank $11$

fill in the blank $12$

$50\mathrm{.}50$

Fixed assets turnover fill in the blank $13$

fill in the blank $14$

$1\mathrm{.}98$

Total assets turnover fill in the blank $15$

fill in the blank $16$

$1\mathrm{.}20$

The firm's inventory turnover has

$,$ the DSO has

$,$ the fixed assets turnover has

$,$ and the total assets turnover has

$,$ indicating that J&W$\text{'}$s ability to manage its assets has

$.$

How has J&W$\text{'}$s profitability changed during the last year?

$20212020$ Industry Average

Profit margin fill in the blank $22$

$\%$ fill in the blank $23$

$\%10\mathrm{.}64\%$

Basic earning power fill in the blank $24$

$\%$ fill in the blank $25$

$\%18\mathrm{.}12\%$

Return on assets fill in the blank $26$

$\%$ fill in the blank $27$

$\%12\mathrm{.}48\%$

Return on equity fill in the blank $28$

$\%$ fill in the blank $29$

$\%18\mathrm{.}14\%$

The firm's profit margin has

$,$ the basic earning power has

$,$ the return on assets has

$,$ and the return on equity has

$,$ indicating that J&W$\text{'}$s profitability has

$.$

Perform an extended DuPont analysis for J&W for $2020$ and $2021.$

Profit margin TA turnover Equity multiplier ROE

$2021$ fill in the blank $35$

$\%\backslash$times fill in the blank $36$

$\backslash$times fill in the blank $37$

$=$ fill in the blank $38$

$\%$

$2020$ fill in the blank $39$

$\%\backslash$times fill in the blank $40$

$\backslash$times fill in the blank $41$

$=$ fill in the blank $42$

$\%$

What do these results tell you?

The ROE has

because the profit margin has

$,$ and the equity multiplier has

$,$ despite the fact that the total asset turnover ratio has

$.$

Perform a common size analysis.

Joshua & White Technology: December $31$ Common Size Balance Sheets

$20212020$

Assets

Cash fill in the blank $47$

$\%$ fill in the blank $48$

$\%$

Short$-$term investments fill in the blank $49$

$\%$ fill in the blank $50$

$\%$

Accounts receivable fill in the blank $51$

$\%$ fill in the blank $52$

$\%$

Inventories fill in the blank $53$

$\%$ fill in the blank $54$

$\%$

Total current assets fill in the blank $55$

$\%$ fill in the blank $56$

$\%$

Net fixed assets fill in the blank $57$

$\%$ fill in the blank $58$

$\%$

Total assets fill in the blank $59$

$\%$ fill in the blank $60$

$\%$

Liabilities & Equity

Accounts payable fill in the blank $61$

$\%$ fill in the blank $62$

$\%$

Accruals fill in the blank $63$

$\%$ fill in the blank $64$

$\%$

Notes payable fill in the blank $65$

$\%$ fill in the blank $66$

$\%$

Total current liabilities fill in the blank $67$

$\%$ fill in the blank $6$