Hello,
I have been going crazy trying to work out the logic for this problem!
I am creating a spreadsheet showing 12 months of the year and the payment associated with a quantity of goods. We don't know the quantity of goods delivered yet, so the spreadsheet has to calculate when we know the numbers.
The first 0 to 35,000 units delivered cost £10 per unit, therefore this tranche will max out at a payment of 35,000 * 10 (£350,000) for the year and we typically manage to delivery 35,000 unit within the first few months of the year.
This element is straightforward, I am probably not using the most efficient formula, but it works (see below)
=IF(SUM(C4:D4)>$D$13,($D$13*$B$6)-C6,IF(D4<=$D$13,D4*$B$6,$B$6*$D$13))
The element I am really struggling on is the next tranche of pricing which is >35,000 and <=100,000 and this is priced at £5. So the spreadsheet needs to workout when year to date we have delivered more than 35,000 and price the next tranche of 65,000 units at £5 and cap this calculation at a year to date of 100,000 cumulative units.
Then all volumes year to date above 100,000 is priced at £4, with is a simpler formula that I can probably work out.
For the avoidance of doubt, each tranche must be fully completed before the next one prices, i.e. if we deliver 10,000 units in January, then the Jan invoice is 10,000 * 10 and if by April we have sold year to date 35,001, then we would have invoiced 35,000 units at £10 and one unit at £5 and then each subsequent unit will be priced at £5 up to 100,000 cumulative units year to date (65,000 in total) would price at £5, before any excess units above 100,000 price at £4. Hope that makes sense!
I hope you can see from the screen shot that the green line works and has stopped invoicing from within month 7 as the cumulative bill for 35,000 units is hit, so on that invoicing line, it is showing zeros for the end of the year.
Thanks in advance!
Sdm
I have been going crazy trying to work out the logic for this problem!
I am creating a spreadsheet showing 12 months of the year and the payment associated with a quantity of goods. We don't know the quantity of goods delivered yet, so the spreadsheet has to calculate when we know the numbers.
The first 0 to 35,000 units delivered cost £10 per unit, therefore this tranche will max out at a payment of 35,000 * 10 (£350,000) for the year and we typically manage to delivery 35,000 unit within the first few months of the year.
This element is straightforward, I am probably not using the most efficient formula, but it works (see below)
=IF(SUM(C4:D4)>$D$13,($D$13*$B$6)-C6,IF(D4<=$D$13,D4*$B$6,$B$6*$D$13))
The element I am really struggling on is the next tranche of pricing which is >35,000 and <=100,000 and this is priced at £5. So the spreadsheet needs to workout when year to date we have delivered more than 35,000 and price the next tranche of 65,000 units at £5 and cap this calculation at a year to date of 100,000 cumulative units.
Then all volumes year to date above 100,000 is priced at £4, with is a simpler formula that I can probably work out.
For the avoidance of doubt, each tranche must be fully completed before the next one prices, i.e. if we deliver 10,000 units in January, then the Jan invoice is 10,000 * 10 and if by April we have sold year to date 35,001, then we would have invoiced 35,000 units at £10 and one unit at £5 and then each subsequent unit will be priced at £5 up to 100,000 cumulative units year to date (65,000 in total) would price at £5, before any excess units above 100,000 price at £4. Hope that makes sense!
I hope you can see from the screen shot that the green line works and has stopped invoicing from within month 7 as the cumulative bill for 35,000 units is hit, so on that invoicing line, it is showing zeros for the end of the year.
Thanks in advance!
Sdm
