Three partners currently under mediation from 1st Oct 2020 for the quarterly amount they received through contract since March 2011. Contract is expected to go till June 2030.
Outcome of this exercise is for partners understanding when they mediate, what impact they will have on their share / backpay based on different percentage scenarios. They should be able to easily understand $ value when changing % values. we will do as current percentages, however ability to amend percentages should be there, which will then show change in $$ values.
Currently Sam receives 30% of the contract value, Chris receives 69.5% and John receives 0.5%. Sam and Chris have a major dispute while John is not fussed but maybe wants little increase.
Based on Financial impact summary, we need to add total amount received by Sam till Sept 2020 (which is $30,377,520.30) as per 30% and then work out total amount received for Chris and John based on their percentages. Partners will choose whether they would prefer to pay lumpsum or pay from their future payments, so we need to account for those options in building formula and work out NPV if they prefer to pay lump rather then from future payments which will go on till June 2030.
For future payments assumption from Dec 2020 to June 2030, we will assume contract payment to remain same for 2 years, then to be 90% for other 2 years, 80% for 2 years after that, 70% for next two years and last year at 60%.
We are thinking total 4 tabs for this assignment.
First: Financial Summary (linking all tabs into this one including back and future payments)
Looking Back: summary table including NPV (2011 to Sept 2020). Partners will have a option to pay lumpsum to each other or deduct $$ from their future payments to compensate another partner. So, working our NPV is very important
Looking Forward: straight forward based on outcome of mediation. we will still need to create one with assumptions based on decrease % as mentioned above.
5 freelancers are bidding on average $124 for this job
Hi there, I am an expert in excel formulas, dashboards, macros. I have gone through the description on your project and will be able to finish in few hours. Thanks Arun