Quantity Pricing Question

NewGuy123

New Member
Joined
Jul 28, 2007
Messages
4
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1JanFebMarAprMayJunJulAugSepOctNovDecNo SoldRevenue
2Widgets Sold45311320.50
3Widgets Sold224.00
4Widgets Sold111311814.50
5Widgets Sold2020206067.50
6
7Pricing Table
8UnitsBase AmountExtra Per Unit
902.00
105101.50
111017.51.00
12
Widgets
 

NewGuy123

New Member
Joined
Jul 28, 2007
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1JanFebMarAprMayJunJulAugSepOctNovDecNo SoldRevenue
2Widgets Sold45311320.50
3
4Cumulative Total491213
5Monthly Revenue883.51
6
7Pricing Table
8UnitsBase AmountExtra Per Unit
902.00
105101.50
111017.51.00
12
Widgets
 

NewGuy123

New Member
Joined
Jul 28, 2007
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 28, 2007
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRST
1JanFebMarAprMayJunJulAugSepOctNovDecNo SoldRevenuePricing Table
2Widgets Sold45311320.50UnitsUnitsBase Amount$ Per Unit
31 to 502.00
4Cumulative Total4912136 to 105101.50
5Monthly Revenue883.51> 101017.51.00
Widgets
 

Forum statistics

Threads
1,181,722
Messages
5,931,673
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top