Estimating Available Award Dollars when awards have asynchronous start dates.

Alex T.

Background: We're a research organization that receives a variety of federal, state, and private awards to conduct research. Awards are received throughout the year with a variety of project periods ranging from 1-5 years.

Objective: Derive an estimate of how much "cash" we have in the bank at any one time. Averaging the awards across time (Split $5M equally over each month of the budget year). Similarly the challenge is our burn rates differ by project as well, so it is hard to figure that into it. Ideally, I'd like a quarterly estimate.

Basically all we need is a general of idea of our financial position a couple years out into the future.

Thoughts?

Thanks in advance for any help.

Sean Morgan

Hello @Alex T.

For calculating values based on a Time/Date period, you will want to use a Formula.

Although I'm unable to see your Sheet, you could use a Formula such as =[Total Cash] / 12 to find out the allocated budget for each month, and then add * 4 to find out the funds per quarter.

If possible, and whilst hiding any sensitive data, are you able to provide a screenshot of what your Sheet looks like, and describe where these fund values should be stored?

Regards

Sean

Alex T.

Hey Morgan, thanks helping me think on this. I worked out a, I think, reasonable discount function based on historical burn rates. My challenge now is getting the data presented in a meaningful way, without drawing on smartsheet labs, I think I'm stuck generating a figure in excel and uploading it to the dashboard periodically.