Estimating Available Award Dollars when awards have asynchronous start dates.
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.
Answers
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!