Loading...

Operational Performance and Financial Benchmark Report

Using the Scenario Planning Tool

Print this PagePrint

Why Use the Scenario Planning Tool?

Scenario development requires three targets: revenue, installed margin, and operating profit.

Once you’ve identified those targets, the difference between Installed Margin and Operating Profit is what is available to spend on Core Expense (sales expense plus G&A expense).

Our Scenario Planning Tool (SPT) helps you evaluate adjustments to core expense needed for achieving those target values.

Let us hear from you
Please email our Research Support team if you have comments or questions.

Usage Conventions

The features described below make it easy to use the SPT and will enhance your workflow.

Numeral 1 callout for the graphic aboveOverwriting Initial Scenario Values

The SPT uses your target Revenue number to initialize scenario values. You overwrite those values as you fine tune the Scenario. Values that you can overwrite have a light-blue background.  Once changed, its background turns yellow.

Numeral 2 callout for the graphic aboveRestore a Formula (Ctrl-R)

You can restore the formula of an editable cell by selecting it and hitting Ctrl-R on your keyboard.

Numeral 3 callout for the graphic aboveQuick Jump

You can navigate quickly to a specific area of the SPT using the Quick Jump list.

Numeral 4 callout for the graphic aboveClone Scenario

Do you want to preserve a scenario – and all the changes you made to it – while continuing to explore the impact of additional adjustments? Click the Clone Scenario link to make a new tab based on the current scenario, which you can then tweak without affecting the original scenario.

Getting Started

Configure and Download the SPT

The SPT is a macro-enabled Excel workbook that requires the full desktop version of Excel, running on a Windows PC.

Configure and download the tool as follows:

  1. Log into the Solomon Coyle Business Intelligence Portal (bi.SolomonCoyle.com).
  2. Choose Financial Benchmarking | Dealer Benchmark Report in the menu at the top of the page.
  3. Make sure that the Reporting Group for column one is showing your dealership.
  4. Make any desired changes to the next four columns which – by default – will be configured to build indexes that “look” like your dealership for the selected year.

In configuring your columns, Solomon Coyle recommends that you accept the default configuration, which uses the Top 25% of dealers by Operating Profit.

Tip: When considering a large change to projected sales, you may want to change the Sales Volume column to select dealers in a different sales range than your current level.

When you’re ready, click Launch Scenario Planning Tool.

Your customized SPT will now download. To get started, open it in the full desktop version of Excel with macros enabled.

There is no limit to how many configurations you can create and download.

Set Your Targets

Next, review your Index Source Numeral 1 callout for the graphic below, then enter your three targets:

  1. Revenue
  2. Installed Margin
  3. Operating Profit

You can change these target values whenever you like.

Setting targets

Things to consider when setting your targets

Numeral 2 callout for the graphic aboveValues with light blue backgrounds are driven by Target Revenue and either your actuals or the Index. The target revenue is initialized with your existing revenue.

Installed Margin changes as a result of changes to Revenue Mix, Product Margins, and WD&I Service COGS.  The SPT starts at your current product mix and margins, so Scenario Installed Margin will equal your existing installed margin.

Scenario SG&A Expenses come from the Index Percent-of-Revenue (as opposed to your actuals).

Operating Profit is impacted by every section of the SPT.  With that in mind:

  1. Replace the default value in Target Operating Profit with your goal. (The SPT initially displays your current Operating Profit).
  2. The SPT initializes scenario Sales Expense and G&A Operating Expenses using the Benchmark Index rather than your actuals. Note, therefore, that the Scenario Operating Profit will likely differ from your actual Operating Profit, even if you project no change in sales.

Now that you have set your targets, look at the Gaps Numeral 3 callout for the graphic above between your target values and what the SPT projects for these values.

You will make line item adjustments in the SPT until those gaps are eliminated.

Scenario Planning

Adjust Revenue Mix and Margins to Eliminate Installed Margin Gap

The SPT initializes the Scenario with the same revenue mix and margins as your actuals. Make line item changes to eliminate any gap between Target Installed Margin and the Scenario Installed Margin.

Note: This projection method differs from how the SPT projects Sales Expense and G&A Operating Expense, which are based on the average of the indexes you configured at bi.SolomonCoyle.com.

adjust-mix-and-margins

Any change to your projected revenue mix will likely result in a net change to Scenario Revenue, creating a gap between Target Revenue and Scenario Revenue. To eliminate such a gap, you could make additional revenue mix changes or update your Target Revenue number.

Once you’re satisfied with your revenue mix and product margins, any additional adjustments to balance the Scenario Installed Margin to your target require changes to WD&I COGS.

Review Your Current and Scenario Staffing Levels

The SPT initializes your scenario with staffing levels based on index productivity. The Productivity and Staffing area of the SPT is a good place to challenge your employee head count against that index. Even though it’s not tied mathematically to the SPT’s financial region, this section enables you to “surface” differences in productivity and staffing between your actuals and the index – both variable and fixed in nature.

review-staffing

Staff counts from this section are referenced in the SG&A Expenses region, guiding changes to those line item expenses.

Tip: Like Sales Expense and G&A Operating Expense, the SPT initializes scenario staffing levels using your target revenue and the Index productivity values. If you based your index on Top 25% best practice, this is a good opportunity to challenge your staffing levels against high-performing dealers.

Adjust Your Sales Expenses and G&A Operating Expenses

The SPT bases initial scenario Sales Expenses and G&A Operating Expenses on the Target Revenue you entered multiplied by the percent-of-revenue values in the Index.  This projection method differs from how the SPT projects Revenue Mix and Margins, which are based on your actuals.

adjusting-expenses

Eliminate the gap between Target Operating Profit % and Scenario Operating Profit % by changing the initial scenario values.

Identify and adjust fixed expenses based on actual.

Creating Additional Scenarios

Creating additional scenarios

Fine tuning your projections is an iterative process. Here are some tips for supporting your thinking as you go along.

Numeral 1 callout for the graphic aboveUse the Clone Scenario feature to preserve your thinking as you explore alternative paths to your targets. Scenario clones are automatically displayed in new worksheets.

Numeral 2 callout for the graphic aboveTo keep track of your iterations, you can enter helpful Scenario Names.

Numeral 3 callout for the graphic aboveYou can also assign different names to the worksheets.

Video: Introduction to the Scenario Planning Tool