PrintWhy 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.
Overwriting 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.
Restore a Formula (Ctrl-R)
You can restore the formula of an editable cell by selecting it and hitting Ctrl-R on your keyboard.
Quick Jump
You can navigate quickly to a specific area of the SPT using the Quick Jump list.
Clone 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:
- Log into the Solomon Coyle Business Intelligence Portal (bi.SolomonCoyle.com).
- Choose Financial Benchmarking | Dealer Benchmark Report in the menu at the top of the page.
- Make sure that the Reporting Group for column one is showing your dealership.
- 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 , then enter your three targets:
- Revenue
- Installed Margin
- Operating Profit
You can change these target values whenever you like.
Things to consider when setting your targets
Values 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:
- Replace the default value in Target Operating Profit with your goal. (The SPT initially displays your current Operating Profit).
- 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 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.
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.
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.
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
Fine tuning your projections is an iterative process. Here are some tips for supporting your thinking as you go along.
Use the Clone Scenario feature to preserve your thinking as you explore alternative paths to your targets. Scenario clones are automatically displayed in new worksheets.
To keep track of your iterations, you can enter helpful Scenario Names.
You can also assign different names to the worksheets.