How to Calculate and Visualize the Expense Ratio in Excel

How to Calculate the Expense Ratio in Excel

This tutorial explains how to calculate your Expense Ratio by connecting acquisition, service, and overhead costs into one total operating expense and dividing that by written premium.

This KPI helps you understand how efficiently your operations are running by revealing how much of each premium dollar is consumed by operating costs.

1. Set Up Monthly Labels

• Type “January” in the first cell and drag down to “December” to autofill the year.
• If you’re working with multiple years, you can append the year using text functions (e.g., “January 2025”) to separate periods.

2. Calculate Total Acquisition Cost by Month

• Use the SUMIFS() function to sum your Acquisition Costs (Column T) by Month (Column D).
  Example:

=SUMIFS(AcquisitionCostRange, MonthRange, Month)

• Format the results as currency with Ctrl + Shift + 4 and center-align them using Alt + H + A + C.
  If you want to remove decimals, double-click the format handle and apply the change throughout the column.

3. Calculate Service Cost by Month

• Repeat the same SUMIFS() process for your Service Cost column.
• You can use Format Painter to quickly apply the same currency formatting from the previous step.
• This ensures all financial values appear consistent across categories.

4. Calculate Overhead Expense by Month

• Copy your previous SUMIFS() formula and replace the column reference with the Overhead Expense column (Column Y).
• All formatting (currency, alignment) should remain correct.
• You now have monthly totals for each cost type: acquisition, service, and overhead.

5. Calculate Total Operating Expense

• Add the three cost columns together to get your Operating Expense:

=Acquisition + Service + Overhead

• Shortcut: highlight the three columns and press Alt + = to insert a SUM formula automatically.
  This gives you total monthly operating expenses.

6. Bring in Written Premium

• Written Premium is found in Column N.
• Use SUMIFS() again to total premium per month.
• Make sure formatting matches your expense columns (currency and alignment).

7. Calculate the Expense Ratio

• Divide your Operating Expense by Written Premium:

=OperatingExpense ÷ WrittenPremium

• Format the result as a percentage using Ctrl + Shift + 5, and center it.Drag the formula down to calculate for all months.

8. Create a Stacked Column and Line Chart

1. Highlight your cost data and Expense Ratio column.
   
2. Press Ctrl and select the Expense Ratio column to include both ranges.
   
3. Go to Insert → All Charts → Combo Chart.
   
4. Choose Stacked Column for the three expense categories and Line (Secondary Axis) for the Expense Ratio.
   

This setup gives you:

• Stacked bars showing total expenses per month (Acquisition + Service + Overhead).
  
• A line graph showing the Expense Ratio percentage trend.
  

9. Format and Finalize the Chart

• Add a clear title like “Expense Ratio by Month”.
  
• Adjust colors for better visibility (e.g., blue for acquisition, orange for service, gray for overhead, green line for ratio).
  
• If needed, modify number formats on the y-axis to show decimals or add data labels for precision.
  

10. Interpret the Results

• This visualization helps you quickly see how much of your premium income is absorbed by operating costs each month.
• A lower Expense Ratio means better efficiency, more of your premium is contributing to profit.
• If the ratio spikes in a specific month, review your acquisition or service costs for unusual changes.

Result

• You now have a complete Expense Ratio dashboard in Excel, combining cost data and performance metrics into one chart.
• This KPI gives you a clear view of operational efficiency and highlights where cost improvements can have the greatest impact.