How to Do Ratio Analysis in Excel Sheet Format: 6 Methods

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.

• Press Enter to get the Gross Margin.

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

• Press the Enter, and you will get the value.

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

• Press the Enter to get the result.

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

• Press Enter on the keyboard.

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

• 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.

• 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:

• 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:

• 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.

• Press Enter on the keyboard.

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

• 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.

• 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.

• Press Enter to get the ratio value.

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

• 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:

• 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.

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

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

• 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:

• 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