Quantity Pricing Question

[NewGuy123]

I have a schedule that lists on the top columns of months and another row that has the number of widgets sold for each month. I'm trying to calculate revenue given different price points depending on how many widgets are sold.

My examplie is that I have Widget pricing of $2 for first 5, $1.50 for second 5 purchased, and $1 for the last 3 purchased. I'm having difficulty creating a formula that is able to account for the different pricing. I want a formula so I can then do an analysis for different scenarios. I will input exactly how many widgets are shipped in each of the months and want the schedule to automatically calculate revenue based on my price points. What creates a big problem is that if there is a month that may have a shipment of widgets that has more than one pricing.

Within the spreadsheet, I have the following pricing setup for this example:
Widget Pricing - 1st Tier / $2.00 / 5
Widget Pricing - 2nd Tier / $1.50 / 5
Widget Prcing - 3rd Tier / $1.00 / 3

Using my example, customer will buy 13 widgets in total.

In Month 1, 4 widgets are at the pricing setup in 1st tier cell ($2.00).

In Month 2, 5 widgets with revenue calculated as 1 widget multiplied by the 1st tier cell ($2) and 4 widgets multiplied by the 2nd tier price ($1.50).

In Month 3, 3 widgets with revenue calculated as 1 widget multiplied by the 2nd tier cel ($1.50) and 2 widgets multiplied by the 3rd tier cell ($1.00).

In Month 4, 1 widget will be sold and revenue calculated at 1 widget multiplied by the 3rd tier cell ($1.00).

Can any one help?

I've considered using a cumulative shipment row for each of the months to help with a calculation but unsure if this will help.

 

[Peter_SSs]

NewGuy123

Welcome to the MrExcel board!

Is this something along the correct lines? If not, please advise further details. Also consider that small samples of a sheet can be shown on the board (like I have below) using Colo’s HTML Maker:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
or Excel jeanie:
http://www.excel-jeanie-html.de/index.php?f=1

Formulas in N2 and O2 are just copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	No Sold	Revenue
2	Widgets Sold	4	5	3	1									13	20.50
3	Widgets Sold	2												2	4.00
4	Widgets Sold	1	1	1	3	1	1							8	14.50
5	Widgets Sold	20	20	20										60	67.50
6
7	Pricing Table
8	Units	Base Amount	Extra Per Unit
9	0		2.00
10	5	10	1.50
11	10	17.5	1.00
12
Widgets

 

[NewGuy123]

Trying to Digest

Peter,

Thanks for your help. I'm still trying to digest/understand your recommendation given that I'm not an expert on vlookups.

One thing I forgot to add was that I was trying to also come up with monthly revenues in the monthly columns which would mean another row that calculated this.

Your Row 2 was exactly what my example is. (I'm assuming that your other rows below row 2 (excluding the pricing table) doesn't apply to your answer.)

And, from what I can tell, your pricing table is correct from the standpoint that 1-5 widgets = $2/per, 6-10 widgets =$1.50/per, and 11-13 widgets =$1.00/per.

Anything that you can help me understand would be great.

Thanks.

 

[Peter_SSs]

Peter,

Thanks for your help. I'm still trying to digest/understand your recommendation given that I'm not an expert on vlookups.

One thing I forgot to add was that I was trying to also come up with monthly revenues in the monthly columns which would mean another row that calculated this.

Your Row 2 was exactly what my example is. (I'm assuming that your other rows below row 2 (excluding the pricing table) doesn't apply to your answer.)

And, from what I can tell, your pricing table is correct from the standpoint that 1-5 widgets = $2/per, 6-10 widgets =$1.50/per, and 11-13 widgets =$1.00/per.

Anything that you can help me understand would be great.

Thanks.

Well, I'm just guessing at exactly what you want because I can't see your sheet and you haven't shown it or described in detail the layout and requirements. If you only have one row of data, then maybe something like this?

B4 and B5 formulas copied across to the right.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	No Sold	Revenue
2	Widgets Sold	4	5	3	1									13	20.50
3
4	Cumulative Total	4	9	12	13
5	Monthly Revenue	8	8	3.5	1
6
7	Pricing Table
8	Units	Base Amount	Extra Per Unit
9	0		2.00
10	5	10	1.50
11	10	17.5	1.00
12
Widgets

 

[NewGuy123]

Starting to Understand

Peter,

I'm starting to understand further. Is there anyway to eliminate from the pricing table column B (Base Amt) since it may be difficult to explain to non-Excel experts? I see that it assists with the total revenue calc. since it looks at the total shipments to determine the total revenue. Knowing the total shipments will determine what the total revenue will be based on your column B in the pricing table area.

I was hoping to have a monthly revenue calculations based on widgets sold in row 2 as you have. This monthly revenue calc would be specific to a customer with the uniques price points as I have explained (i.e. 1-5=$2, 6-10=$1.50, 11+=$1.00).

Thanks.

 

[Peter_SSs]

Re: Starting to Understand

Is there anyway to eliminate from the pricing table column B (Base Amt) since it may be difficult to explain to non-Excel experts?

The easiest way would be to move the pricing table to the right of the other data, leave the "Base Amt" in the table, but hide that column (select any cell in the "Base Amt" column then Format|Column|Hide). That way the formulas can still use the base Amount but the user does not see it in the table.

I was hoping to have a monthly revenue calculations based on widgets sold in row 2 as you have. This monthly revenue calc would be specific to a customer with the uniques price points as I have explained (i.e. 1-5=$2, 6-10=$1.50, 11+=$1.00).

What would the layout look like? So far, I have no "Customers" in my sheet. How would it be arranged?

Have you investigated Colo's HTML maker or Excel jeanie yet? If you could show me what the sheet is to look like it would make things a lot easier!

 

[NewGuy123]

Peter,

Your last spreadsheet's results were exactly what I was looking for. I'm still trying to understand the formulas.

If I have further questions, I wanted to to add to this topic but it wouldn't be until tomorrow sometime. If you're available, I was hopeful you can try to help me out further.

If not, thanks for all of your help.

I wasn't able to download that software since I don't have administrator privileges on my computer.

By the way, I was trying to replace the numbers you had in the pricing table to be consistent with the unit figures that I had but the calcs didn't work. I tried to replace your "0" with "1", your "5" with "6", and your "10" with "11". I wanted these replacements to illustrate that the first unit pricing ("1") would be $2, sixth purchased unit ("6") would be $1.50, etc.

Thanks.

 

[Peter_SSs]

By the way, I was trying to replace the numbers you had in the pricing table to be consistent with the unit figures that I had but the calcs didn't work. I tried to replace your "0" with "1", your "5" with "6", and your "10" with "11". I wanted these replacements to illustrate that the first unit pricing ("1") would be $2, sixth purchased unit ("6") would be $1.50, etc.

Once again, I would suggest fixing this by hiding columns. I have moved the existing table over to columns R:T and added some extra descriptive info in column Q. To show you what I have done, I have left all columns visible, but you could hide columns R and S.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	No Sold	Revenue		Pricing Table
2	Widgets Sold	4	5	3	1									13	20.50		Units	Units	Base Amount	$ Per Unit
3																	1 to 5	0		2.00
4	Cumulative Total	4	9	12	13												6 to 10	5	10	1.50
5	Monthly Revenue	8	8	3.5	1												> 10	10	17.5	1.00
Widgets