calculating cost prices for a product with multiple variables

**cbs** · 12-01-2010, 07:23 PM

Would appreciate some help with the following problem.

I am putting together a formula that will calculate the cost of a software sale.

There are two variables of the sale, the number of users (USER), and then the level of support (SUPP) required for the software.

The cost of sale for each of the variables of the sale is based on a discount to RRP.
The discount to RRP varies by product and also varies between the two variables, eg.a different discount for software users (say 50%) and software support (say 25%).

USER = a non linear relationship of between selling price and number of users, eg. 1 user = 100, 2 user = 190, 3 user = 270 etc.
SUPP = choice of three levels of support (eg.bronze, silver, gold) and also a non linear relationship between users and selling price, eg.1 user bronze = 25, 2 user bronze = 40, 1 user silver = 50, 2 user silver = 80.

I'm trying to produce a formula which will enable me to input into three fields eg. product, number of users, and level of support - and out will pop a cost price for that sale.

Each product also has a different max number of users.

My current attempts at the formula are cumbersome and will be difficult to maintain when sales price lists are producted.

Would much appreciate your input, thanks

**Maistrye** · 12-01-2010, 07:45 PM

You should probably do up an excel file that contains an example. I'd recommend that you show:

at least two or three dummy products,
each of which has different USER/SUPP values with all the levels,
and then provide 5-10 dummy customers which shows how you would do the calculations for each (try to make each somewhat distinct).

S

**Leith Ross** · 12-01-2010, 07:48 PM

Hello cbs,

Welcome to the Forum!

So we can propose solutions that fit your needs, you should post your workbook. Seeing the layout, formatting, formulae, and any VBA code, reduces the number of questions and answers. This will provide you with a faster turnaround and more accurate results.

**cbs** · 12-02-2010, 09:17 AM

Thanks for input thus far, worksheet attached now should give a clearer idea of the issue.
2 issues.

First, how to limit the selection of users, companies and support choices (B4-6) based on the initial software choice (B3).

Second, with these choices, how to calculate the selling and cost price into C8 and C9.

thanks in advance for any further input.

**Maistrye** · 12-02-2010, 12:14 PM

Hi,

I've attached an example of what you can do. I've added a tab called Steps Taken that describes what was done to achieve the results.

Please note that I make rather liberal use of named ranges. If you're not too familiar with them, you should probably read up a bit on them. (Here's one place: http://www.contextures.com/xlNames01.html)

There is at least one area you might have a bit of difficulty when you expand it -- the number of users. The layout you have currently isn't all that great if you go to (say) 100 or 1000 users for some software. There are ways to deal with this, not included in the below spreadsheet. Just mentioning it so you're aware.

Also, you'll note that the formulas in cells C8/C9 will return errors if the selections made are not valid (ie. not in your list on the side). This can be corrected by adding a wrapper function to the cells. The details are on the Steps Taken sheet at the bottom.

S