Using the Budgeting Tool

Using the SCi³ Budgeting Tool

The SCi³ Budgeting Tool merges a dealer’s past results with the results of highly-effective, similar dealers into one tool, automatically providing a number of pro forma projections and enabling dealers to develop, challenge, and rationalize budgets relatively quickly.

Provided three targets – revenue, installed margin, and operating profit – the tool provides an interactive means of achieving those targets by adjusting budgets on a metric-by-metric basis, simultaneously showing what is:

reasonable
best practice
recent past experience

Getting Started

Prerequisites

To use the SCi³ Budgeting Tool you’ll need the following:

an active subscription to the Solomon Coyle Interactive Intelligence and Insights (SCi³) service
an active user account at our business intelligence portal: https://bi.solomoncoyle.com
participation in the annual Solomon Coyle Financial Benchmarking program in at least one of the two most recent years.

The greater the participation in the trailing five+ years, the more effective the tool will be. If you are missing any of these but are interested in using the tool, contact support@solomoncoyle.com for assistance in fulfilling these prerequisites.

Downloading the Tool

When you’re ready to start (or start fresh) using the SCi³ Budgeting Tool, get the newest version:

Important: Make sure to download the tool to a known location on your hard drive or network, rather than attempting to use the tool in Excel Online.

Why Won’t the Tool’s Buttons and Links Work?

If you find that some or all of the tool’s buttons and links don’t work, it is possible that the workbook’s macros were disabled by Windows out of an abundance of caution. This will frequently be the case if you attempted to open up the file while it was still in your Downloads folder (which Windows always worries about). Getting around this is simple. Once you’ve moved the file to a known location on your hard drive, right-click on it in a Windows Explorer window, choose Properties, and “check” the Unblock box pictured below.

If you are still having trouble using the tool, contact support@solomoncoyle.com for assistance.

Setting Up the Tool

Once you’ve opened the Budgeting Tool in Excel 2013 or later, on a PC using Windows 7 or later, you’re ready to configure the tool.

Because the tool interacts directly and frequently with the online Solomon Coyle Business Intelligence portal, make sure that you have Internet access and login:

If you have any issues logging in, browse to the Business Intelligence Portal and follow the prompts to reset your password or setup your account.

Configuring the Budgeting Tool

Step 1 – Selecting a Dealer

The tool’s Dealer Selector in Step 1 will be populated with all dealers you have permission to view. Typically, this may be solely your primary dealership, but if you have uploaded financial benchmarking data for branch locations or other business units (as a growing number of dealers who subscribe to the Solomon Coyle Advanced Analytics service have), these will be listed here, as well. Select the dealership or business unit for which you will be building a budget:

Step 2 – Setting the Best Practice Index Year

The Best Practice Index year setting auto-fills with the year of the most recent Solomon Coyle Financial Benchmark results for which you have a valid submission. You can override this default by entering an earlier year.

Step 3 – Setting the Dealer Base Year

This auto-fills with the latest year for which you have uploaded benchmarking results. You can override this default with any other year for which you participated in financial benchmarking.

The years available to you for Step 3 are listed here:

Finally, let the tool know the kinds of dealers you’d like to compare yours to when budgeting. We recommend that you endeavor to budget towards best practice by focusing on the top 25% (by Operating Profit) of dealers that have something significant in common with you. The tool makes that easy – just click Load Dealer Defaults and the tool will set up your best practice indexes with recommended defaults:

If you wish to override one or more of the defaults, each cell has a pulldown from which you can select different options:

For example, you may be contemplating a sharp drop or rise in revenue, so you may wish to budget towards best practice from dealers in the targeted revenue group if it is different than the default revenue group, which is determined by the revenue your dealership had during the Dealer Base Year.

Step 4 – Define Best Practice

The system will provide default Best Practice criteria. When you click “Load Dealer Defaults” the system will set the criteria to match key characteristics (size, installed margin range, market population, and services sales mix) to your dealer’s characteristics during the year you entered in Step 3. Later, when you trigger the initial build of your budget in Step 5, the system will calculate Best Practice metrics from the top 25% (by operating profit) of dealers that matched these characteristics during the Best Practice Index year you selected in Step 2. You can tweak these before proceeding to Step 5 if, for example, you are contemplating a large change in sales or installed margin.

Step 5 – Load the Tool

When ready, click Go – the tool needs about a minute to fetch, configure, and load the data you’ll use to create your budget.

Switch to the BudgetTool tab to see and begin editing the starter pro forma budgets the tool built using the configuration settings on the BudgetToolBuilder tab.

Using the Tool

Getting to Know the Layout

The High-Level Map

The BudgetTool tab is big and can be overwhelming until you grow familiar with it. Start by looking around and getting to know this tab. There is much more information than can be displayed on one screen, so switch to the High-level Map tab whenever you need to get your bearings. This tab will help you build a basic understanding of the layout of the BudgetTool tab.

Figure 1: A thumbnail view of the high-level map

The next sections in this guide refer to each color-coded area on the High-level Map tab.

Title and Key

This section is a simple recap of the Dealer Base Year and Best Practice Index Year selected when generating the budget, as well as a key:

The formulas in any cell with a pale blue background can be overridden with hard values
Once overridden, those cells turn yellow
Restore the formulas in those cells by selecting them and hitting Control-R on your keyboard
Cells to the right of a (V)ariable indicator can be designated as “fixed” so that the recommended values in the Budget column will not float with changes to projected revenue, instead remaining pegged to the results from the Dealer Base Year.

Budget Tool Columns

There are nine types of columns, some of which are discussed in greater detail later in this document but are summarized briefly below, from left-to-right in their appearance:

Prior Years – up to four years preceding the Dealer Base Year.
Dealer Base Year – the year that you indicated in the configuration options on the first tab. In addition to forming the foundation of the “Base Year Pro Forma,” it also let’s the system know which year it should count backwards from when collecting up to four previous years’ history.
History Average – this is an average of your Dealer Base Year and Prior Years.
History Pro Forma – a pro forma budget based on the targets you entered above and the History Average.
Base Year Pro Forma – a pro forma budget based on the targets you entered above and the Dealer Base Year.
Current Budget – you will be interacting with the Budget Tool primarily in this column, overriding suggested values until you have closed any gaps between target Operating Profit and Budgeted Operating profit. Note that it is in the center of the tool, to the right of historical data and to the left of best practice data.
Best Practice Pro Forma – a pro forma based on the average Overall Best Practice Index and the targets you entered above.
Overall Best Practice Index – the average of the (maximum of) four best practice slices you selected in the configuration step.
Best Practice Index Source Slices – the individual best practice slices you selected in the configuration step.
Trend – the small line chart in this column provides a quick look at how each metric is changing over time.
Fixed Flags – placing an “F” in this column causes the tool to default the budget value to whatever is in the dealer base year for that metric.

Pro Forma Guidance

The Budget Tool provides you as many as many as 15 different perspectives on your current budget. This is too many to comfortably view on one screen, so the majority are collapsed, by default, but you can expand and collapse most of those perspectives by clicking the corresponding link.

Click Show History by Year to see all of your historical results going back as many as four years prior to the Dealer Base Year you selected.
The Dealer Base Year’s Results and Pro Forma based on those results and the targets you have entered are always visible and cannot be collapsed.

Click Show Historical Indexes to see two more columns:
- Historical Averages: averages of your historical results (including the dealer base year’s results)
- Pro Forma Based on Historical Averages: a pro forma budget based on those averages and the targets you entered in the Targeting area

Current Budget – always visible. Note that this budget starts out named <Optional Budget Name>, but you can rename this if you’d like.

Best Practice Index and a Pro Forma Based on Best Practice Index are next. These are always visible and cannot be collapsed.
Finally, you can expand and collapse the Best Practice Detail slices you configured on the BudgetToolBuilder tab (up to four).

Targeting Your Goals

The first order of business is to set a target revenue, target installed margin, and target operating profit. They default to your Base Year results.

Once you have identified target revenue, installed margin and operating profit, use the tool to fine-tune specific cost and profit centers until you have eliminated the gap between your targets and the outcomes predicted by the tool.

Target revenue, installed margin, and operating profit default to your results from the Dealer Base Year. When you change each of these values, you’ll notice that all pro forma values, with the exception of any hard values you’ve entered, or designated as “fixed,” will immediately reflect those changes.

You may notice that the projected Operating Profit under Current Budget will be different than the Operating Profit in Base Year, even when you’ve made no changes to the three default target values. This happens because the tool defaults to best practice values, rather than dealer base year values, for pro forma SG&A (Core Expense) projections.

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 Budget Tool starts at your current product mix and margins, so Target Installed Margin will equal your existing installed margin.
Important: Default Budget SG&A Expenses come from the Best Practice Index Percent-of-Revenue (as opposed to your actuals in the Dealer Base Year).
Operating Profit is impacted by every section of the Budget Tool. With that in mind:
- Replace the default value in Target Operating Profit (which the Budget Tool initializes with your current Operating Profit) with your goal.
- The Budget Tool initializes Sales Expense and G&A Operating Expenses using the Best Practice Index rather than your actuals. Note, therefore, that the Budget 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 Budget Gap between your targets and what the Budget Tool projects for these values. You will use the Budget Tool to make line-item adjustments until those gaps are eliminated.

Identifying Fixed Items

By default, the tool allows budget values (headcounts and dollar amounts) to float with changes in target revenue. While this is one reason the tool is so effective, the reality is that many of your dollar amounts and headcounts are sticky and difficult to “variablize” with changes in sales. The tool allows you to “tag” specific line items as Fixed, which causes the budget column – the column that holds your budget values – to default to the values in your base year. Just put an F (for Fixed) to the left of any item you want to Fix.

The Budget Tool helps you evaluate the adjustments needed to core expense for achieving those target values.

Budget Adjustments

Adjust Your Revenue Mix and Margins to Eliminate the Installed Margin Gap

The Budget Tool initializes the budget with the same revenue mix, margins, and COGS percents-of-revenue as your Actuals. Make line-item changes to the corresponding light-blue cells to eliminate any gap between Target Installed Margin and Budget Installed Margin.

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

Adjust Your SG&A (Core Expense) to Close the Operating Profit Gap

As previously stated, the Budget Tool bases initial Sales Expenses and G&A Operating Expenses on the Target Revenue you entered multiplied by the percent-of-revenue values in the Best Practice Index. Again, this projection method differs from how the Budget Tool projects Revenue Mix and Margins, which are based on your base year actuals.

Adjust the values that have light-blue backgrounds to close the gap between target and budget Operating Profit. As always, you can undo any overwritten cell and revert to the formula by selecting that cell and hitting Ctrl-R on your keyboard.

Note that you have the option to flag some values as Fixed by changing the V in section to an F. This will force the tool to use the value from your base year actuals rather than a value that was allowed to float in response to target revenue and best practice.

Note, also, that pro forma headcount (headcount adjusted for your target revenue) from the Staffing section of the Best Practice Index is repeated up in this section in column .

Review Your Current and Budget Staffing Levels

As with SG&A, the Budget Tool initializes your budget with staffing levels based on Best Practice Index productivity. The Productivity and Staffing area of the Budget Tool is a good place to challenge your employee head count against that index. Even though it’s not tied mathematically to the Budget Tool’s financial region, this section enables you to “surface” differences in productivity and staffing between your actuals and the Best Practice Index – whether variable or fixed in nature. The headcount you set here will be repeated in SG&A in order to remind you of changes to those headcounts as you adjust dollar amounts in that section.

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

Using the SCi3 Budgeting Tool