### Method 1 – Input All Required Particulars

- Launch Microsoft Excel on your device and rename the sheet according to your desire. We entitled our sheet as
**Specification**. The modification of the sheet’s name is not mandatory. It makes your datasheet more convenient for others. - Entitle your dataset with a suitable title. Select the range of cells
**B2:C2**and click on the**Merge & Center**option from the**Alignment**group, located in the**Home**We keep our dataset title as**Ratio Analysis Data**.

- Title cells
**B4**and**C4**as**Specifications**and**Amounts**.

- Input all the particular values for your organization, denoted in the range of cells
**B5:B20**.

You have completed the first step of ratio analysis in Excel sheet format.

### Method 2 – Calculate All Profitability Ratios

- Calculate the
**Gross Margin**. For that, select cell**C5**. - Write down the following formula in the cell.

`=(Specification!$C$5-Specification!$C$6)/Specification!$C$5`

- Press
**Enter**to get the**Gross Margin**.

- Calculate the
**Operating Profit Margin**, select cell**C6**and write down the following formula:

`=Specification!$C$7/Specification!$C$5`

- Press the
**Enter**, and you will get the value.

- Determine the
**Net Margin**in cell**C7**. The calculation formula is shown below:

`=Specification!$C$10/Specification!$C$5`

- Press the
**Enter**to get the result.

- Estimate the
**Return on Assets**or**ROA**, and write the following formula into cell**C8**.

`=Specification!$C$7/Specification!$C$12`

- Press
**Enter**on the keyboard.

**Return on Equity**or**ROE**write down the following formula into cell**C9**.

`=Specification!$C$10/Specification!$C$14`

- Press
**Enter**to get the value of**ROE**.

Say that you have finished the second step of ratio analysis in Excel sheet format and calculated all the required elements of the **Profitability Ratio**.

### Method 3 – Evaluate Every Efficiency Ratio

- We will determine the
**Receivables Turn-over Ratio**. To estimate its value, select cell**F5**. - Write down the following formula in the cell.

`=Specification!$C$5/Specification!$C$17`

- Press
**Enter**to get the value of the**Receivables Turn-over Ratio**.

- Figure out the
**Inventory Turn-over Ratio**, select cell**F6**and write down the following formula:

`=Specification!$C$6/Specification!$C$19`

- Press the
**Enter**, and you will get the value.

- Determine the
**Payable Turn-over Ratio**in cell**F7**. The formula for calculation is shown below:

`=Specification!$C$6/Specification!$C$20`

- Press the
**Enter**to get the ratio result.

- Our next item is the
**Asset Turnover Ratio**. To estimate its value, write the following formula into cell**F8**.

`=Specification!$C$5/Specification!$C$12`

- Press
**Enter**on the keyboard.

- To evaluate the value of the
**Fixed Asset Turnover Ratio**, write down the following formula in cell**F9**.

`=Specification!$C$5/Specification!$C$13`

- Press
**Enter**to get the value of the**Fixed Asset Turnover Ratio**.

- Compute the
**Equity Turn-over Ratio,**write down the following formula in cell**F10**.

`=Specification!$C$5/Specification!$C$14`

- Press
**Enter**to get the value of the**Equity Turn-over Ratio**.

Say that you have completed the third step of ratio analysis in Excel sheet format and calculated all six required elements of the **Efficiency Ratio**.

### Method 4 – Determine Liquidity Ratios

- Calculate the
**Current Ratio**. For that, select cell**C15**. - Write down the following formula in the cell.

`=Specification!$C$15/Specification!$C$16`

- Press
**Enter**to get the ratio value.

- To figure out the
**Quick Ratio**, select cell**C16**and write down the following formula:

`=(Specification!$C$18+Specification!$C$17)/Specification!$C$16`

- Press the
**Enter**, and you will get the value of**Quick Ratio**.

- Determine the
**Cash Ratio**in cell**C17**. The formula to calculate the Cash Ratio is shown below:

`=Specification!$C$18/Specification!$C$16`

- Press the
**Enter**to get the ratio result.

### Method 5 – Estimate Solvency Ratios

- Find the
**Debt to Equal Ratio**. For that, select cell**F15**. - Write down the following formula into the cell.

`=Specification!$C$11/Specification!$C$14`

- Press
**Enter**and you will get the ratio’s value.

- Get the
**Debt Ratio**, select cell**F16**and write down the following formula:

`=Specification!$C$11/Specification!$C$12`

- Press the
**Enter**, and you will get the value.

- Determine the
**Interest Coverage Ratio**in cell**F17**. The formula to calculate the ratio is shown below:

`=Specification!$C$8/Specification!$C$9`

- Press the
**Enter**to get the**Interest Coverage Ratio**.

### Method 6 – Create Ratio Analysis Summary Report

- Select all four ratio calculation tables in the
**Ratio Estimation**sheet. - Press
**‘Ctrl+C’**to copy them.

- Create a new sheet. Rename it as
**Summary**, and press**‘Ctrl+V’**to paste them there. - Format them according to your desire. Our formatting for this report is shown in the image below.

- Select the range of cells
**B10:C14**. - In the
**Insert**tab, select the**drop-down arrow**of the**Insert Pie or Doughnut Chart**option and choose the**3-D Pie**option.

- The chart will appear in front of you. Set a suitable name for your chart. We set our chart title as
**Profitability Ratio**. - Modify your chart style and texts from the
**Design**and**Format**tabs. - We chose
**Style 8**for our chart. Select the**Style 8**option from the**Chart Styles**group.

- We keep all the
**Chart Elements**for the convenience of other users.

- The column chart, select the range of cells
**B6:C8**. - In the
**Insert**tab, select the**drop-down arrow**of the**Column or Bar Chart**from the**Charts**group. - Select the
**Clustered Column**option from the**2-D Column**section.

- After that, click on the
**Chart Element**icon and check on the**Axes, chart title,**and**Data Table**elements. - Set the chart title as
**Liquidity Ratio.**

- We choose the
**Style 6**option from the**Chart Styles**group for our column chart.

- For the bar chart, select the range of cells
**B10:C15**. - In the same process, select the drop-down arrow of
**Columns or Bar Chart**, and select the**Clustered Bar**option from the**2-D Bar**section.

- Modify the number of elements and the chart style according to your desire. We choose
**Style 3**and the**Axes, chart title,**and**Data Table**elements in this chart.

- Use the
**Resize**icon at the edge of the chart for better visualization and complete the task.

**Download Practice Workbook**

## Related Articles

- How to Graph Ratios in Excel
- How to Use Interest Coverage Ratio Formula in Excel
- Debt Service Coverage Ratio Formula in Excel
- How to Convert Percentage to Ratio in Excel

**<< Go Back to Ratio in Excel | Calculate in Excel | Learn Excel**