Hi -

I'm trying to build an output model similar to the one shown below, please see the following explanations to help answer:

Sample Source Data Explanation:

(I) The service date represents each transaction date for each customer
(II) The Unique ID represents a particular customer
(III) The product code input, (in this example 2 but can be 1, 2, or both) indicates whether or not the 1st threshold input (in this example $300) should be deducted from the total cost when calculating net cost to me.
(IV) The total cost represents the total cost of each transaction

Other important considerations: The source data is sorted by service date and unique ID, so that each customers transactions are listed in the order in which they were incurred.

Sample Output Explanation:

* For each unique ID I need to calculate the net cost to me on each day of the year based on the inputs shown below: Explanation of Inputs:

(I) Product code- the product code can be either 1, 2, or both. In this example it is 2 and denotes when a customer transaction has a threshold amount that needs to be applied before subsidies are factored.

(II) 1st threshold- given the product code input of (1, 2, or both) the total cost of the transactions for the customer should be reduced by this amount until the total cost with the associated product code input has been exhausted. (Example: Unique ID 1x1's first transaction has product code 2 listed and a total cost of $2,944.47 (far exceeding the first threshold, however there could be multiple transactions before the threshold is met, see Unique ID 3x3 from the sample output). The $2,944.47 should be reduced by the 1st threshold amount of $300 and the remainder should be further reduced by the subsidy amount (in this example is 25%) to show a net cost to me on 1/2/2015 of $1,983.35. Further, the threshold input can be $0 to $400.

(III) Subsidy- this is the amount that should be used to reduce the total cost of each transaction. In this example when product code input equals 1 or when total cost of product code 2 has surpassed the 1st threshold the subsidy amount should be applied to reduce total cost. IMPORTANT: Subsidy can be expressed as either a percentage of total cost or as dollar value reduction of total cost.

Main Issues:
(I) When a unique ID has multiple transaction with a selected product code input before meeting the selected threshold amount.
(II) Once threshold is met further reducing the total cost by the subsidy.

Please note from the sample output, unique ID example 3x3 which encounters its first transaction on 1/1/2015 with a product code of 1 for $5, because the product code is 1 only the subsidy is used to reduce the total cost of this first transaction. The second transaction occurs on 1/7/2015 with a product code of 2 for $235, because this product code is equal to our input we must first reduce this claim by the threshold amount of $300 (Note: this does not cause a negative balance instead it should be treated as the 1st step in surpassing our 1st threshold). The third transaction on 1/9/2015 with a product code of 2 for $1,363.20 should be reduced by the remaining amount of our 1 threshold balance after the prior transaction on 1/7/2015 of $65 ($300 - $235) and then further reduced by the subsidy input.

Attached Files

Example.xlsx (13.8 KB, 9 views)