Building a Pricing Model

Hi

I want to build a pricing model for a web application. I need a formula for cell that will calculate the cost of the product given a variable. The price of the product is dependent on the number of people that sign up for it - this is the variable. The price per person diminishes as the number of people rises.

Example Pricing Structure:

0-50 People - $50 per person
51 - 100 People - $40 per person
101 - 150 People - $30 per person
151 - 200 People - $20 per person
201 - 250 People - $10 per person
251 - 300 People - $5 per person
301+ People - $2.5 per person

So, if 152 people sign up, the client will be charged (50*50) + (50*40) + (50*30) + (2*20) or $6040.

Other Example Outcomes:

50 People = $2,500
75 People = $3,500
125 People = $5,250
200 People = $7,000

My goal is to create a pricing sheet in xls that will produce a price given the variable - a number of people that will be in the application.

Many thanks in advance and apologies if this is answered elsewhere.

A

It does indeed - thank you very much!!!

If your issue is resolved, please edit your first post to mark it as "Solved". This will help others who may have a similar issue to find the solution.

Also, If you like (or dislike) my post, then please rate it. Thanks.

Hello.

I am in need of some assistance building a pricing calculator for my sales team to utilize when meeting with customers. We are apparel decorator with a wide range of customers, from national retail chains to individual customers. There are number of different variables that need to be addressed in order to come up with the correct pricing.

My hope is that I can explain with enough clarity for you all! I have attached a table for reference below the examples.

Factors
- Quantity of items
- Number of locations of decoration (I am only using one location for the examples)
- Number of colors per location

Example 1: A customer wants to order 24 shirts, one location with one color.
Example 2: A customer wants to order shirts, one location with four colors.

Colors
QTY 1 2 3 4 5 6
12 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
13 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
14 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
15 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
16 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
17 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
18 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
19 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
20 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
21 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
22 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
23 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
24 $8.00 $8.00 $8.00 $9.00 $10.00 $11.50
25 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
26 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
27 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
28 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
29 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
30 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
31 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
32 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
33 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
34 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
35 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50
36 $6.50 $6.50 $6.50 $7.50 $8.50 $10.50