How to Eliminate Pennies by Rounding Sales Price Plus Tax to the Nearest Nickel

My son, Ted, owns Rosemont Beverage Center here in Pennsylvania. He wanted to raise some of his prices, and wanted to make giving change easier by setting the sales prices, such that when tax was added, the total amount ended with a zero or a 5 in the cents column. He was doing this by trial and error, but with hundreds of SKUs, I thought that a spreadsheet formula to do this for all of the items would be the way to go. We need to start first with the suggested sales price- I suggest that you make that one column. Then have a second column that is the sales price and tax. You set up a simple formula that the second column equals the first, plus 6% or whatever your sales tax is. Next, there is the excel formula that can help you do this, MROUND, which rounds to the nearest multiple. This will be the third column.

• To round such that the price of the item, plus tax ends with a zero or a five in the cents column (we will call this the Convenient Amount), use the MROUND function and for the first entry in the parenthesis, put the cell number you wish to round. The second entry will be .05, which means round to the nearest nickel.
• To back into the sales price itself, we need to take the Convenient Amount, and solve for the sales price. For that, we multiply it by 100 divided by 100 plus the percentage of the sales tax. In our state, for example, sales tax is 6%. That means that we multiply the convenient number by 100/106 to obtain the sales price.
• For example, you determine that you want to charge about $15.53 for an item. If the tax is 6%, that will make the total including sales tax $16.47. You are not happy charging this odd amount. You therefore use the MROUND function.
• This tells you to charge $16.45. (including tax)
• We need to know the sales price to charge such that when tax is added, we arrive at our convenient total. To find that, we multiply $16.45 by 100/106 = $15.52. You might also want to test check your calculation by adding tax to the $ 15.52 to be sure that you arrive at $16.45. That can be a fourth column.
• We can set up a spreadsheet to do this for all of the SKUs that we wish to adjust. By the way, did you know that Canada has phased out the penny? The government suggests that transactions be rounded off to the nearest nickel. Here is the way you can get ahead of the curve when it happens in the US.

TABASFUNDING provides entrepreneurs with funding to acquire or expand businesses in the form of flexible loans from $100,000 to $750,000, or more. We supplement bank and other funds, and consider most types of businesses in Pennsylvania, New Jersey, and Delaware.   Please call 610-896-2400 or email Lee@Tabasfunding.com for further information.

________________________________

LEE TABAS is available as a consultant or an expert witness.  Areas of expertise include:  general business, management, problem asset resolution, marketing, acquisition or sale of businesses, relationship development, loan policies, high performance banking, and business funding.  Please call 610-896-2400 or email Lee@Tabasfunding.com for further information.