Birju Shah
New Member
I have a project document recording annualized charges for various services covering a contract which runs from 1/6/13 through to 31/5/18 (5 Yearly columns of charges)
I need to accurately report on annual revenues by a certain line of business. The raw data tab essentially has on each row, a start date, an end date, and 5 columns with yearly £ values.
The problem I’m having started because I needed to reflect mid-year price changes to a yearly document. If a certain charge ran from Y1 to Y2 of the contract, I populated the year 2 column with the year 1 values. However, for example, in Y2M2 the price changed, so I had to add end dates to the initial row of charges, and add a new row for the new charge with applicable dates.
The problem this is causing is that a reporting tab I have created now counts the two sets of charges which is inflating revenues for a given year. I.e. the when reporting the LOB on the year 2 column of the raw data, the formulas pull back both the old pricing and the new pricing, as both charges were applicable for a period during the contract year.
Please could someone assist? I would like to accurately report the data in a time apportioned manner. I’m not sure if I’ve explained this accurately so please let me know if you need more information.
I need to accurately report on annual revenues by a certain line of business. The raw data tab essentially has on each row, a start date, an end date, and 5 columns with yearly £ values.
The problem I’m having started because I needed to reflect mid-year price changes to a yearly document. If a certain charge ran from Y1 to Y2 of the contract, I populated the year 2 column with the year 1 values. However, for example, in Y2M2 the price changed, so I had to add end dates to the initial row of charges, and add a new row for the new charge with applicable dates.
The problem this is causing is that a reporting tab I have created now counts the two sets of charges which is inflating revenues for a given year. I.e. the when reporting the LOB on the year 2 column of the raw data, the formulas pull back both the old pricing and the new pricing, as both charges were applicable for a period during the contract year.
Please could someone assist? I would like to accurately report the data in a time apportioned manner. I’m not sure if I’ve explained this accurately so please let me know if you need more information.