How to sum a limited time only offer

[Help_Me_Rhonda]

Hi there and sorry for the title, found this difficult to describe.

Basically I am trying to work out the impact of a limited time offer (say 6 months) which offers customers a discount on their phone bill for a period of time (say 12 months).

I'm getting stuck on the formula (if there is one!?) which takes into account the fact the build up and then roll off of customers - ie in month 14 the customers that signed up in the first 2 months will no longer benefit from the discount. I have attached a spreddie which hopefully illustrates this a bit better!

[amit.wilson]

Hello H,

welcome to the forum. You may need to explain the problem a little more. In the spreadsheet, can you do a mock-up of the input and output? The formulas can be built to use that input and generate that output.

Cheers

[Help_Me_Rhonda]

Hi and thanks,

Sorry if I didn't explain that very well!

Basically I want to be able to model out the impact of a special offer on the cost line of the business. From the spreadsheet, the limited offer will be open to customers from 1 July 2014 - 1 July 2015, and then benefit of the offer lasts for 12 months. So a customer taking up the offer on 1 October 2014 will receive a $5 discount per month for the following 12 months, and a customer taking up the offer on 1 December 2014 will receive the discount through to 1 Dec 2015. The total number of customers per month is in line 17.

Hope that is clearer!

[amit.wilson]

I may be missing something. But try this.

Cheers

[Help_Me_Rhonda]

Marginal revenue 2.xlsx

Thanks Amit,

Doesn't quite seem to achieve what I'm trying to do... I've tried to tackle this a different way, by having a row (R52) that adds and subtracts according to where a contract is in its contract life. However... I'm really not all over excel like you guys are! Is there any way to put in a formula that I can copy and paste across so that I don't have to manually add each subtraction??

Thanks!

[amit.wilson]

R - am still fuzzy on what you wanted. Here is what I have done (in green):

I've worked only on the formula in row 52.

In D60, I have defined an "Initiative Taper Date" = Duration of offer (12 months) after initiative start date. As the special price contracts won't start ending till after that point.

In row 52, if the current month is after the "Initiative Taper Date", then subtract "Existing activations taking up offer" 12 months ago.

See if this does what you want.

If it does not, then upload a template without formulas but with manually calculated data (i.e. what should the sheet look like) and a short (perhaps colour coded) explanation of what is being added/subtracted. That may make it easier to build a formula.

Cheers