Part 4 – Excel Spreadsheet: Analyzing the Impact of a Minimum Wage Rate Increase
FREE EXCEL SPREADSHEET – We have provided an Excel spreadsheet to help you compute the financial impact of the wage increase to your business.
Do you have the latest spreadsheet update?
Most current spreadsheet is dated: 1/14/2016
WARNING: The Excel spreadsheet is a tool to “estimate” the impact, and to help you understand the effectiveness (and challenges) associated with some of the more common business strategies. Be sure to review the information, assumptions and computation explanations in the related articles and in the discussion below. The spreadsheet uses the barest amount of input variables and assumes pretty much everything else is unchanged. As such, it paints the picture with broad strokes and it is not to be relied upon as definitive. Consult with your financial professional when making decisions relating to managing the impact of wage rate increases to your business.
USAGE AGREEMENT: By downloading and using the spreadsheet, you are acknowledging to its limitation and instructional purposes. There is no cost. We are providing this tool free. We do ask that you help is in return be spreading the message. Please “share” these posts on FaceBook and “Tweet” them on Twitter.
Here is the link to download the Excel spreadsheet… [ DOWNLOAD SPREADSHEET ]
INSTRUCTIONS & EXPLANATIONS IN USING THE SPREADSHEET –
SPREADSHEET & PRINT LAYOUT…
The spreadsheet itself is set up with three side-by-side analysis sections:
When printing these reports, each analysis section will report on a separate page:
The Excel spreadsheet has a minimum number of variables, making it easier to use. The estimate assumes that the only thing that changes over the period (years 2016 through 2019) is the labor wage rates. Remember, we are not generating a financial projection for this period. We are estimating the financial impact of this one financial variable.
The input variables needed are:
- Total Sales
- Materials & Supplies Costs (all variable costs)
- Labor Costs (assumed to be all variable costs)
- Payroll Taxes & Benefits (assumed to be a percentage of Labor Costs)
- Overhead & Administrative Expense (assumed to be all fixed costs)
- Total Labor Hours for the year at Minimum Wage Rate
- All Other Labor Hours for the year
Note – Any on-going expenses that will not vary with volume should be included in the Overhead & Administrative Expense variable. This would include set salaries and their related payroll taxes (e.g. owner’s salaries) .
COMPUTING THE NON-MINIMUM WAGE COMPONENT…
With this base year information, we need to compute and breakdown the payroll into the Minimum Wage and Non-Minimum Wage components. This is automatically calculated in the Excel spreadsheet, but is illustrated below for your review and understanding:
NON-MINIMUM WAGE TREATMENT CHOICE…
The next factor needed in computing the financial impact is your management choice on how you will treat Non-Minimum Wage employees during this adjustment period. We know what the minimum wage rate increase will be, but the spreadsheet needs to know what rate adjustments (if any) that you will be making for your non-minimum wage employees. This issue is discussed at more length in Part 1.
There are many options, but I’m simplifying it to three: A) Ignore non-minimum wage employees and don’t increase their rates (until you have to), B) Increase their rates by the same DOLLAR increase that the minimum wage rate changes, and C) Increase their rates by the same PERCENTAGE increase that the minimum wage rate changes. This is illustrated as follows:
You will need to choose between these options (A, B or C) so that the spreadsheet can compute the estimated financial impact of the wage rate change. “B” is set as default, but I encourage you to try each choice to better understand how non-minimum wage employees affect your analysis.
With the above minimal input, the Excel spreadsheet will have the necessary information to compute the first two analysis sections:
The third analysis section computes a combination of PRICING and SALES VOLUME. You have to input the amount of pricing increase you will make each year, and then the spreadsheet will compute the additional volume of sales that would be necessary to make up the difference. The variable input cells are located in row 2 above the Sales Volume analysis columns.
The Excel spreadsheet should provide you with the start you need to assess and analyze a wage rate increase. The amount of time and effort you invest into preparing for change will greatly improve your success rate.
If we can be of formal assistance to you and your business, please call our office to set up an appointment… 2o8.452.1234
- Part 1 – How will the Increased Minimum Wage Rate Impact Your Business?
- Part 2 – Managing a Minimum Wage Rate Increase – Increasing Prices, Sales Volume & Cutting Costs
- Part 3 – Increasing The Minimum Wage Rate: Solution or Problem?
- Part 4 – Excel Spreadsheet: Analyzing the Impact of a Minimum Wage Rate Increase