1

My goal is to adjust my pricing to match that of the competition, be it by reducing or increasing my current pricing. My model is a bit complicated and messy because their pricing includes tax, while mine doesn't, which is why I have so many columns.

Another complexity is that some of the obtained competitor's pricing includes installation, but not all, which is stated in column G.

If anyone can suggest a way for me to simplify the model by merging columns H:K and or columns L:O I would be really grateful.

https://www.dropbox.com/s/nir0k3a8cw6a037/superuser.xlsx?dl=0

Here is what my spreadsheet looks like

  • oh no. @user3695778 your comment seems to have disappeared :( – ExcelIskillingme Nov 8 at 21:55
1

Move your pricing block (H-K) before your competitor's (E-F), and remove either H or C (as they're the same).

Remove I.

Change J to Installation Cost (Before Tax)

Change K to Total (With tax)

Then change calculations accordingly.

If you need to have a value for taxes (like a competitor only tells you the price of an item after tax, but not before) then have a cell with JUST the tax modifier (10%-11%, or whatever it is), for both part and installation (or a single value if the tax rate is the same for both) . The tax modifier probably isn't going to change per part number, so set it off to the side of your table and refer to it as needed. If you want to drag and copy formulas and still use a single cell for calculations, use an Absolute reference (which is a reference using dollar signs, you can look it up if you're not familiar. Not too complicated).


Change L and M to "Is mine lower than my competitor's?", and only use a single field that's either TRUE or FALSE. Do the same with N and O. True/Falses are better to use, as they work easily with If statements. For example, using a single True/False field, you can do something like this:

=IF(3F, do_stuff_for_increasing_price, do_stuff_for_lowering_price)

As the IF() statement can check the value of a cell without any extra calculations if it's already TRUE/FALSE. For example, this does the same thing as the above code:

=IF(3F=TRUE, do_stuff_for_increasing_price, do_stuff_for_lowering_price)

So save yourself some extra work in the future and use TRUE/FALSE entries when they're applicable.


Change the single N/O field to just compare installation costs (from the newly updated J column) to make it easier to track and compare in case you want to compare installation costs on their own later on down the line.

Use the same field for P and Q. Keeping it negative is good, as you can do things like =J*Q to find out much you have to increase or decrease your totals, which is a lot easier than reading how big the difference is THEN looking up whether that's an increase or decrease by referring to another cell.

You don't need S, as that's already E. You can probably get rid of T, as your competitors are only showing their after-tax costs, AND you should be isolating the tax variable (as mentioned before) so that you can refer to it later as needed.


After all that, you should have 7 less columns and a better optimized table.

To further refine things, highlight a block's columns (like your Competitor's pricing in E-F) and go into your Data tab, into the Outline group, and click on Group. It'll create a grouping on the outside of your worksheet, where the cell references are, that you can click to hide a grouping of columns/rows. Any cells hidden this way still calculate normally. But that way, you don't need to have your True/False Cost Comparison columns in the way when they're used more for internal calculations than for the user.

Once it's all said and done, it'll look something like this:

Snapshot of example table

  • Many thanks for your help @Daniel Zastoupil You've certainly helped me optimise my table. The thing I'm struggling with however, is that your model assumes that I have a clear breakdown as for the cost of installation for competitor's parts, which is not the case. – ExcelIskillingme Nov 9 at 4:30
  • When it comes to competitors' pricing, I don't know how much they charge for installation. I do however have the pricing for my parts, both with and without installation, and in order to work out our installation cost I subtract cost without installation from cost including installation. – ExcelIskillingme Nov 9 at 4:31
  • This is why I was using the Competitor's price includes installation Yes/No column. Because without it I don't know which pricing column to compare to. – ExcelIskillingme Nov 9 at 4:31
  • What I'm trying to say is that competitors' pricing doesn't always include installation, even if WE do offer an option to have the equivalent part installed. This is what the Yes/No column was used for. I hope I make some sense – ExcelIskillingme Nov 9 at 4:33
  • 1
    It definitely makes sense, I just didn't remember to include that in my example. I would definitely include it as you did before in that regard. It definitely sounds like you have a good eye for what it needs. However, simply reducing the size of the header name can be enough to reduce clutter. For example, after Competitor Price, you could have a column labeled "Install?" Which is enough to keep it fairly clean. – Daniel Zastoupil Nov 9 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.