Creating a Payback period model

[Blondek]

Hi team, 

I'm trying to create a model to analyse the payback period on certain products. I have earnings per product per year (presented with the product in the rows and earnings separated out by year - with the year across the columns). I also have costs in a separate file I intend to join by product SKU.

I want to analyse the payback period - i.e. how many years it has taken per product to recoup the costs. Can anybody please help?

Thanks, 

Matt

[christine_assaad]

Hi @Blondek 

It will be helpful to share a sample of how your data is structured... but generally speaking you can use Alteryx DateTimediff formula and configure it to run the difference between two dates in a specified units such as year.

Hope this helps. Cheers!

[Blondek]

Hi, 

Thanks a lot - Sample data attached. Of course the actual data is much larger, but I'd like a column which is 'Payback period', which determines how many years it took to recoup the costs. If it has not yet recouped, I'd like it so say how long it would take to recoup under the current trend. 

Thanks, 

Matt

[Raj_Singh1]

hi @Blondek 

if you could pleas share some data sample or excel file. and some more instructions, would be useful for community to reply back with the workflow or solution

[Blondek]

Hi, 

Thanks, sample data above. 

Matt

[Raj_Singh1]

Hi @Blondek 

Please see attached solution. If only gives the year and product where payback period is reached.

Let me know if you have any more questions 🙂

[Blondek]

Hi @Raj_Singh1  

Thanks for this - this isn't a packaged workbook, so I can't see the detail. 

However, there is a bit of a difference between what you've done and what I'm trying to achieve: I'm trying to create a model which tells the actual OR forecast payback: 

- If it has paid back, I want to see how long it took to payback in years 

- If it hasn't paid back, I want to see how long it will take in years if current revenue levels continue 

Is there a way of achieving this? 

Thanks a lot, 

Matt

[Raj_Singh1]

Hi @Blondek 

Please see attached my updated solution. it calculates the years to achieve forecast. 

It calculates on simple method, however would make more sense when you attach real data.

There are better ways of calculating forecast, those take long time and approach can be improved and so can accuracy be. but this one should serve your purpose. pLease let me if you have any more questions or would like improvements to be made.