Financial Ratios

Can someone please put these answers listed on an excel worksheet with formulas included. I cannot figure out how to do this.

Granny's Cat farms, Inc.

A. Long-term debt ratio:
Long-term debts are debt liabilities due in one year or more
i.e. long-term debt $65,000 A
Other long-tern debt $25,000 B
Long-term debt $85,000

The LTDR is the rate of long-term debt to total capitalization however; it does not include short-term debt, therefore
i.e. Long-term debt $65,000 A
Other long-tern debt $25,000 B
Shareholders Equity $80,000 C
Capitalization $165,000

LTDR = Long-term Debt / (Long-term debt + Total Shareholders Equity)
= 85,000 / 165,000
= 0.5151

Excel Formula

Written as follows: =(A+B)/(A+B+C)
Where A - C will be the cell name on your spreadsheet eg. =(A1+B1)/(A1+B1+C1)

This rule will be the same for all the following answers

B. Total Debt Ratio:

TDR = (Total Assets - Total Equity) / Total Assets
= (242,000- 80,000) / 242,000
= 0.6694

C. Times Interest Earned:

TIE = Earnings Before Interest & Tax (EBIT) / Interest Expense
= 20800 / 5000
= 4.16

D. Cash Coverage Ratio:

CCR = (EBIT + Depreciation) / Interest Expense
= (20800 + (12,000+ 5,700) / 5,000
= 38,500 / 5,000
= 7.7

E. Current ratio:

CR = Current Assets / Current Liabilities
= 102,000 / 77,000
= 1.325

F. Quick Ratio:

QR = (Current Assets - Inventory) / Current Liabilities
= (102,000 -12,000) / 77,000
= 1.169

G. Operating Profit Margin:

OPM = Net Income / Sales
= 20,440 / 95,000
= 0.2155
= 21.55%

H. Inventory Turns:

IT = Cost of Goods Sold / Inventory
= 40,000 / 12,000
= 3.333

I. Days In Inventory:

DII = 365 / Inventory Turns (H. above)
= 365 / 3.333
= 109.511

J. Average Collection Period:

ACP = Sales / Accounts Receivable

NB: In this case, Accounts Receivable is taken to be the difference between the 'Other Current Assets' from the beginning to the end of year 200X
i.e. 15,000 - 12,000 = $3,000

= 95,000 / 3,000
= 31.667 days

K. Return on Equity:

ROE = Net Income / Total Equity
= 20,440 / 80,000
= 0.2555
= 25.55%

L. Return on Assets:

ROA = Net Income / Total Assets
= 20,440 / 242,000
= 0.0845
= 8.45%