Excel for Finance Lesson 8: Financial Ratios From the P&L and Balance Sheet

Excel for Finance Lesson 8: Financial Ratios From the P&L and Balance Sheet featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 2 min read

πŸ“˜ Excel for Finance Course Β· Lesson 8 of 8

Advertisement
In this article
  1. Profitability
  2. Liquidity
  3. Leverage
  4. Efficiency (working capital)
  5. Reading the practice company
  6. Where people go wrong
  7. Practice
  8. Continue learning

Ratios turn financial statements into answers: is the business profitable, can it pay its bills, how much does it owe, how fast does cash come back? Banks look at the same ratios when you apply for a loan.

Profitability

Gross margin %:  =(Revenue - COGS) / Revenue
Net profit:      =Revenue - COGS - Opex - Interest - Tax
Net margin %:    =Net profit / Revenue
ROE %:           =Net profit / Equity

Liquidity

Current ratio:  =(Inventory + Receivables + Cash) / Current liabilities
Quick ratio:    =(Receivables + Cash) / Current liabilities

A current ratio around 1.3-2 is common for trading businesses; below 1 means current liabilities exceed current assets. Context matters by industry.

Leverage

Debt-equity:        =Total debt / Equity
Interest coverage:  =EBIT / Interest            ' EBIT = Revenue - COGS - Opex

Efficiency (working capital)

Debtor days:     =Receivables / Revenue * 365
Inventory days:  =Inventory / COGS * 365
Creditor days:   =Payables / COGS * 365
Cash cycle:      =Debtor days + Inventory days - Creditor days
πŸ’‘ Show each ratio for both years side by side with a change column and an arrow. A ratio means little alone; the trend and the comparison with peers tell the story.

Reading the practice company

Revenue grew 15%, margins held, and interest coverage is comfortable, but debtor and inventory days rose: growth is tying up more cash, which matches the lower cash balance. That’s the kind of one-paragraph conclusion a ratio sheet should lead to.

Advertisement

Where people go wrong

Mistake Effect
Year-end balances for seasonal businesses Misleading days; use averages where possible
Mixing revenue and COGS bases Inventory days on revenue understates them
Ratios without commentary Nobody acts on them

That’s the Excel for Finance course. Next: loan schedules and the Charts & dashboards course.

Practice

Download the workbook below. Build each figure in the yellow column of the Practice sheet; the Check column turns green when it matches, and the Answers sheet has a working formula for every task.

Advertisement

πŸ“Ž Practice files for this article

  • πŸ“—
    Practice workbookTwo years of summarised P&L and balance-sheet figures for an example company.
    ⬇ XLSX Β· 13 KB

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong