What If analysis to calculate the effect of new pricing

[veryokota]

Sheet 1 contains product, pricing, and quantity info for 4 products. Each product has a different price. Each customer ordered a different quantity. We want to do a What If analysis to determine the effect that changes in prices would have had. The tiered pricing schedule for the 4 products is in Sheet 2. There are 2 different price tiers depending on whether the company is a reseller or distributor.

What sort of formula can be used to determine this?

Example: In Sheet, Distributor 1 (row 6) purchased 375 units of Product 1 at $24.95 each, 100 units of Product 2 at $26.95 and 175 units of Product 3 at $29.
If the tiered pricing in Sheet 2 had been effect (Distributor prices, so the correct section – rows 6-10 must be matched to the organization type), what would have been the resulting sales for each product and in total?

If the distributor purchased multiple products, then the calculation needs to be made for each product using the prices in the quantity tiers for the type of company (distributor or reseller).

[AliGW]

Welcome to the forum.

Just to clarify, where have you manually mocked up the results you want to see for your what if analysis?

In essence, what you need is two sets of pricing scales as lookup tables: one for current prices and one for projected prices. Then you calculate the current price values using one and your projected prices per customer with the other.

Does this make sense?

[veryokota]

Sorry, I didn't include a results mock up.
Yes, that makes sense about 2 sets of pricing scales as lookup tables. Looking forward to someone implementing that in the example spreadsheet.
Thank you.

[AliGW]

Looking forward to someone implementing that in the example spreadsheet.

Are you really? Well, then, you'd better add that mock-up so we can see what we are aiming for, hadn't you?

Joking apart - don't leave us to guess what output you're after.

[veryokota]

Gotta love that dry UK wit. However, please note that you're doing battle with an unarmed Yank.
Enclosed is an updated workbook with desired result.

[AliGW]

However, please note that you're doing battle with an unarmed Yank.

I approve.

I will have a look soon, but the cats are demanding lunch NOW!

Back soon ...

[AliGW]

Just a quick question: your proposed layout does not show any comparison. Are you wanting that same layout on a different worksheet for the 'what if' bit, or do you want a direct side-by-side comparison somewhere?

I think you probably need to think a little more about how this is going to be used and the user experience. I am not convinced by your layout - I think there are better ways of doing it that could feed into a useful summary table on which all sorts of dashboard style gizmos (charts/graphs) could be based.

Let me know your response before I start trying to manipulate messy data (sorry, but I know you can take it ...).

[JohnTopley]

See attached U:Z.

I used named ranges ("Reseller_xxx" and "Distributor_xxx") xxx is "variable - for your tiered table

In V3

=IFERROR(IFERROR(INDEX(INDIRECT($U3&"_Tbl"),MATCH($J3,INDIRECT($U3 &"_Order"),1),MATCH(V$2,Sheet2!$D$1:$G$1,0)),"")*$J3,0)

and similar for other columns

As Ali suggested there maybe better table layouts but I have just done the comparison.

Last 2 distributors had insufficient quantities.

Hope this helps.

[veryokota]

Ali, a summary table with all sorts of dashboard gizmos would be terrific. The original spreadsheet has 4,800 rows of data. We want to examine the effect of pricing changes to all accounts (the 4,800 rows of data) to view the impact on customers and on our bottom line.

[veryokota]

Thanks, John. However, there is an error in the calculation in that Reseller 2 has a sales amount, yet there is no data given for price and quantity.
Enclosed is an updated example calculation with sufficient quantities for distributors 2 and 3. Please use this one.

Is your formula pulling the pricing from the Pricing Schedule spreadsheet based upon the quantity sold of each product for the respective type of organization? Thanks again.

[JohnTopley]

There is no error for Reseller 2 which only has quantities for Products 1 & 4.

And the Pricing is based on the organisation / price table in Sheet2 (hence the named ranges).

UPDATE: I endorse Ali's feedback (in the following post) that this is not an insignificant piece of work and requires reformatting of the data if it to apply to hundreds/thousands of products.

[AliGW]

Ali, a summary table with all sorts of dashboard gizmos would be terrific.

It would be a big job and involve a wholesale reformatting of your data and a change in the way you collated it. But you have John's solution now, anyway.

[JohnTopley]

I suggest format in Sheet3 is better as there considerably more products than organisations/tiers.

The named ranges/formula I provided need changing to reflect this table.

Example included

[veryokota]

Thanks again, John. However, there are many more organizations (4,800) than products (4) so that new table won't work.
Can you redo the formulas to display the organization name rather than Reseller or Distributor in your table? Please use the example calculation new attached with this message.

[AliGW]

You are drip feeding facts about the dataset, which means that your helpers have to keep rethinking their approach. Please bear in mind that this can become very frustrating, and increases their investment of time considerably, which they are already giving for free.

Is there anything else about the real data that we don't know???

[veryokota]

That is everything.

[JohnTopley]

My original response gave you the answer you require: replace "Reseller" etc with the organisations BUT unless you want 4000+ named ranges, you will need to modify the INDEX formula reference to the correct organisation by using a MATCH on Organisation Name.

And are there varying QTY tiers for different organisations?

As per Ali's reply this is not only frustrating but, to me, very annoying!

[JohnTopley]

... And are we talking about price table per organisation OR , given your first post, what are in Sales/Marketing are "Channels" i.e an organisation is a "Reseller" or "Distributor" (or any other channel).

If this is the case, then add Channel into the "Order" or have a separate table which relates Organisation to Channel so the Reseller/Distributor price table I provided will suffice,

See attached. ("Current Sales")

And (some) Prices in "Desired Output" are wrong:

In D3

Qty =210 for Product 3 so price is $31.95 not $49.95