Visualizing fiscal year impact

[tmckerahan]

Hi fellow PHD fans. I'm trying to create a user-friendly Excel sheet to aid my colleagues in visualizing the fiscal year impacts of new contracts with our vendors. The contracts are always written on a calendar year basis (i.e., "the vendor will receive a sum total of $X, payable in equal quarterly installments, during calendar year 2010") but of course internally we need to translate that in terms of our fiscal year (May 1-April 30). I'd like to have a worksheet that would allow the user to input different scenarios to see their effects on different FYs. For example, what is the expected impact on each of the next 3 FYs if I pay this vendor monthly versus paying semi-annually versus paying quarterly?


I'm having a hard time figuring out where to start on this without relying on some monstrously large IF statements. Would welcome any tips or ideas anyone has. The end product really needs to be quite simple -- ideally the user would just input the contract date range and annual amount due, and then can choose among different scenarios to see what happens -- but I'm game to set up sophisticated macros or analyses in the background if necessary.


Thanks in advance for your help!

tmckerahan

 

[Hui]

Tmckerahan


You will need a model of your busness which wil result in Monthly or Quarterly Cashflows and/or Profits

You will need to either duplicate or setup the model so that it can run a standard Cash Flow/Profit and the a revised Cash Flow/Profit based on a new payment terms.


If your base case model isn't too complex duplicate the entire model on another page and set it up for the revised payments and then just summarise the results somewhere for analysis.


A simple Column Chart or Line Chart showing Cash Flow or Profit vs Time with 2 sets of data (Base and Revised Payment terms) should get you where you want to be.