Need 10% off and 20% off to match same set of prices

[shepaeo]

I fear that this is basic math, but I am struggling and cannot figure out the formula to calculate my data set in excel.

I have an issue. At our store, we have a membership that gives customers 20% off of normal price. Members pay an annual fee, but if they make a few purchases per year, they will easily save more than our membership fee. We have another location where we have more competition and customers are more price sensitive, therefore we need to have lower prices. We want the 2nd location to have a 10% discount off their lower prices, but have membership prices across the board.

It does not seem as simple as taking 10% off of the higher prices to get the price for 2nd location, because then 10% off the 2nd location prices is coming up different from 20% off of 1st location.

I think that I need to start from the uniform membership price and add a percentage from there. However, I am unable to determine what percentage increase for each location to get different full fees at each office with different percentages off to get the same membership price.

Another hurdle that we have is the software we have to use to process customer accounts makes us set dollar amounts for all prices, and we cannot set the membership to simply calculate the 20% off at one location, and 10% off at the other. We have to set 3 separate prices for each SKU: the 1st location, 2nd location, and Membership price. We would like as much consistency as possible.

Please help.

Thank you.

[Richard Buttrey]

Hi, and welcome to the forum.

Can I suggest you upload an example workbook that contains a scenario for each of your different situations. Manually add the results you expect to see and indicate which are results and which original data.

[shepaeo]

Thank you for the prompt reply Richard. I can supply our current membership prices. the $0 amount are inactive SKUs that show up on every report for me.

[Syrkrasi]

Whoa

Please attach a sample Excel workbook.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.)
4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[shepaeo]

sheet is now attached

[Richard Buttrey]

Thanks for the attachment but you haven't added the result you expect to see as requested.
At the moment I've no idea what the results should look like. Please add them along with explanatory notes as to how you've calculated them.

[Aardigspook]

Maybe I'm missing something, but it seems to me that what you want is something like this:

A2 = Base price from location 1 (for example $100)
B2 = Membership price at location 1, 20% off = A2*0.8 ($80)
C2 = Membership price at location 2 = membership price at location 1 so = B2 ($80)
D2 = Non-member price at location 2 = C2 * 10/9 ($88.89)

(C2 isn't really needed, obviously, but I include it for clarity.)

If instead you want to start with the membership price, then this:

A2 = Membership price (e.g. $80)
B2 = Price at location 1 = A2 * 10/8 ($100)
C2 = Price at location 2 = A2 * 10/9 ($88.89)

Does that help at all?