Bulk Discount Pricing

[cjms85]

Hi all,

I’m currently working on a generic pricing template, where the End user is able to specify the unit cost of an item, the number of units that qualify for a bulk purchase discount and the discount rate at each of these bulk purchase intervals.

Example

This is an example of the table the End User would fill in. Items B1 and B2 have different costs, different intervals for the bulk purchase discount and different discounts themselves. Obviously in reality I would have more than 2 or 3 items

[TABLE="width: 100%"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]Cost
[/TD]
[TD]Interval 1
[/TD]
[TD]Interval 2
[/TD]
[TD]Interval 3
[/TD]
[TD]Interval 4
[/TD]
[TD]Interval 5
[/TD]
[TD]% of original cost 1
[/TD]
[TD]% of original cost 1
[/TD]
[TD]% of original cost 1
[/TD]
[TD]% of original cost 1
[/TD]
[TD]% of original cost 1
[/TD]
[/TR]
[TR]
[TD]B1
[/TD]
[TD]£1000
[/TD]
[TD]0-100
[/TD]
[TD]101-200
[/TD]
[TD]201-300
[/TD]
[TD]301-400
[/TD]
[TD]>401
[/TD]
[TD]100%
[/TD]
[TD]98%
[/TD]
[TD]96%
[/TD]
[TD]94%
[/TD]
[TD]92%
[/TD]
[/TR]
[TR]
[TD]B2
[/TD]
[TD]£900
[/TD]
[TD]0-150
[/TD]
[TD]151-250
[/TD]
[TD]251-350
[/TD]
[TD]351-450
[/TD]
[TD]>451
[/TD]
[TD]100
[/TD]
[TD]99%
[/TD]
[TD]98%
[/TD]
[TD]97%
[/TD]
[TD]96%
[/TD]
[/TR]
[TR]
[TD]B3
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[/TR]
</tbody>[/TABLE]

The End User would then specify how many purchases are made in a given month.

[TABLE="width: 49%"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]Month 1 no. of units
[/TD]
[TD]Month 2 no. of units
[/TD]
[TD]Month 3 no. of units
[/TD]
[TD]Month 4 no. of units
[/TD]
[TD]Month 5 no. of units
[/TD]
[/TR]
[TR]
[TD]B1
[/TD]
[TD]95
[/TD]
[TD]134
[/TD]
[TD]207
[/TD]
[TD]389
[/TD]
[TD]621
[/TD]
[/TR]
[TR]
[TD]B2
[/TD]
[TD]37
[/TD]
[TD]156
[/TD]
[TD]273
[/TD]
[TD]373
[/TD]
[TD]521
[/TD]
[/TR]
[TR]
[TD]B3
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[TD]Etc.
[/TD]
[/TR]
</tbody>[/TABLE]

I would then have the worksheet calculate the appropriate cost for the total units purchased each month, based on the discount that they qualified for.

In the linked spreadsheet, I have 3 worksheets:
· BoM (Bill of Materials)
· Volumetric Arrays
· Profiling

https://drive.google.com/file/d/0B__bxAaMPpZZMng0V0RpUUpOc2s/view?usp=sharing

BoM

In the BoM, the End User specifies the cost of the item (this is currently made up of 4 components), with the total item cost given in Column F. Then the upper threshold, or upper limit of the pricing interval is specified in Columns H:Q. I decided to have 10 different intervals, but this is arbitrary. Similarly, the discount rates are set in Columns S:AB. Here the discount is input as a percentage of the original price.

Volumetric Arrays
In the Volumetric Arrays sheet, Cells A4:P4 represent the first entry in the BoM. They give sheet and cell references for the BoM ID, Upper Threshold 1, Upper Threshold 10, % Price 1 and % Price 10, located on the BoM sheet.

Column R uses a indirect and concatenate to calculate the BoM ID from Columns B:D.

Column S calculates the Lower Threshold values.

Column T uses a transpose array, indirect and concatenate to import the correct Upper Threshold values from the BoM.

Column U does the same as above to import the discount rate.

Columns W:AK use a series of direct text entries and formulas to create part of a Index Match formula that will be used on the Profiling sheet.

Column AM and AO concatenate the two separate text strings that will go to make up the index match formula in the following way: “=INDEX(array,MATCH(“ and “,lookup array,1),3)”. Note that the lookup value has been not been assigned in either of these text strings.

By selecting Rows 4-15 and dragging down, this Volumetric Arrays sheet can be populated further to accommodate any additional items added to the BoM. This is why there are 11 rows of blank space (shaded grey), between each entry on the Volumetric Array sheet.

Named ranges:
Column R = VA_BoMID
Column AM = VA_IndexID
ColumnAO = VA_MatchID

Profiling

Column A, the End User can select an Item from the BoM in the drop down lists in Rows 3-12.

Columns B:Y, the End User can enter the number of units purchased per month.

Column Z, formula to give the total number of units purchased.

Columns AB:AY, formula yielding the total cost of units per month based on the number purchased and discount rate. Uses Index Match for find the BoM ID unit cost, multiplied by the % Price from columns CA:CX, multiplied by the number of items purchased from B:Y

Columns BB:BY, formula concatenating several index matches. Looks at the row’s BoM ID in Column A and matches to the correct formula strings on the Volumetric Arrays using VA_BoMID, VA_IndexID and VA_MatchID, to put together a text string of the formula to determine the correct discount rate. The lookup value is determined using the CELL function to the appropriate column in the number of units purchased table.

Colums CA:CX, uses a VBA module Eval(), which evaluates a text string as a formula. This points to the corresponding cells in the columns BB;BY, to yield the % Price value for a given month.

Code for VBA Eval() module:

Option Explicit
 
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

As you can probably tell, this is rather clunky workaround to do what I need. Am I missing an in-built function in Excel that could do what I want, or can anyone think of a simpler method to achieve the same result?

Many thanks for your help!
Chris

 

[cjms85]

After some playing around and a good night's sleep, I've managed to come up with a far less clunky solution to my problem.

Part of the frustration came from the the fact that I didn't realise you could used Index Match to match different Column arrays.

Anyway, here's a copy of my solution spreadsheet in case anyone is interested: https://drive.google.com/file/d/0B__bxAaMPpZZMng0V0RpUUpOc2s/view?usp=sharing

The tables below provide a bit of context about what is going on in each of the columns, as well as listing the named ranges etc.

Bill of Materials (BoM) sheet

Column Reference	Description
Column A	Bill of Materials (BoM) ID number. Takes form of B1, B2, etc. for each new entry. Named Range: Column A:A = BoM_ID Column A2:A1048576 = BoM_IDList
Column B	BoM unit cost. Named Range: Column B:B = BoM_Cost
Column D	Upper threshold for purchasing quantity. Specifies the highest value that will qualify for the corresponding % Price. Normally the End User would specify the Threshold for each BoM item, but this has already been populated for this example. Named Range: Column D:D = BoM_Threshold1
Column E	Upper threshold for purchasing quantity. Named Range: Column E:E = BoM_Threshold2
Column F	Upper threshold for purchasing quantity. Named Range: Column F:F = BoM_Threshold3
Column G	Upper threshold for purchasing quantity. Named Range: Column G:G = BoM_Threshold4
Column H	Upper threshold for purchasing quantity. As you cannot give a cell an infinite value in excel, this number should be sufficiently large that you would never purchase more than this number. Named Range: Column H:H = BoM_Threshold5
Column J	% of the unit price cost to be paid. Normally the End User would specify the %Price for each BoM item, but this has already been populated for this example. Named Range: Column J:J = BoM_Price1
Column K	% of the unit price cost to be paid. Named Range: Column K:K = BoM_Price2
Column L	% of the unit price cost to be paid. Named Range: Column L:L = BoM_Price3
Column M	% of the unit price cost to be paid. Named Range: Column M:M = BoM_Price4
Column N	% of the unit price cost to be paid. Named Range: Column N:N = BoM_Price5

<tbody>
</tbody>

Profiling (Pro) sheet

Column Reference	Description
Column A	Yellow cells are a drop down-list. Click on cells A2-A11 to select an entry from the BoM. Yellow cells can be dragged down, as the number of entries in the BoM increases.
Columns B:M	Number of BoM Units purchased per month. Normally the End User would specify the number of items purchased here, but for the time being these are randomly generated numbers.
Column O	Index Match function to automatically look up the BoM Unit cost. This will update to whatever BoM item is selected in Column A of the Profiling sheet.
Columns Q:U	Lower threshold price, Column Q is set to 1 for every row, as you cannot purchase less than 1 unit. Columns R:U are the corresponding Upper Threshold values in Columns W:AA +1.
Columns W:AA	Index Match function to automatically look up the corresponding Upper Threshold limit for whatever BoM Item is given in Column A.
Columns AC:AG	Index Match function to automatically look up the corresponding % Price value for whatever BoM Item is given in Column A.
Columns AI:AT	Array Index Match function to determine whether the values in Columns B:M qualify for a particular discount.
Columns AV:BG	Formula to calculate the unit price discount for a given month multiplied by the number of BoM items purchased that month.
Column BH	Total purchasing cost.

<tbody>
</tbody>

If anyone has any questions, I'm more than happy to answer them where I can.

Best,
Chris

 

[cjms85]

Sorry, I think I've linked to the old URL in the last post. This should be the correct file:
https://drive.google.com/file/d/0B__bxAaMPpZZZG9YNEpIUTlnY0k/view?usp=sharing