Love "PRORATE"

The PRORATE function does not exist in Excel (to my knowledge, reinventing it was cumbersome).

We use it to spread budget accross time.

Indeed, with the right columns and rows, Yearly (can be quarterly, monthly or whatever) budgets adjust as the dates change.

Then dedicated boxes consolidate to overview at a glimpse.

PRORATE is straight forward to use with 5 logical parameters. See http://help.smartsheet.com/customer/portal/articles/775363-using-formulas#advanced

I find it especially useful when I make up a new project plan, with a lot of date changes at this stage => Budget is always aligned :-)

Would you like me to issue a short template?

Find more posts tagged with

Formulas

Comments

Jeremy Michels

I would love to see at template. What industry are you working in?

Dean Krontz

Charles,

I was trying to find some time to do something just as you described. IF you have a template already created I would appreciate if you could share.

Thanks in advance.

Charles DESCOTES

Ok, I did one quick example with PRORATE budgeting to make it clearer.

Trevor helped me making it available to you (see bottom).

You will also see how I check Start and Finish dates thanks to Harvey balls (I love this gimmick).

For the latter and to verify the budget robustness, conditional formatting is the watchdog.

Your comments/questions are welcome.

Access the example:

Any paid user who clicks the following distribution link, will have a copy of the sheet added to their account (and they will be the owner of the copy). If they are not a paid user, they can give this link to a paid user who can get the sheet and share it with them.

Here is the link: https://app.smartsheet.com/b/launch?lx=m1loiQflaLU7r3jjLFtAy12F3tjZfBYMXSEruozjq1E

Issa M

Hi Charles, thanks for posting this. This is extremely helpful. I'm having trouble figuring out how to adapt this for monthly rather than yearly allocation though. In particular, I'm not sure how monthly allocation is compatible with the date format in the formula. How would the formula change from year, month, day if year is eliminated? Can you share either what the formula should look like or a template you've created?

Thanks for any help you can provide!

Issa

Screen Shot 2019-03-01 at 12.20.57 AM.png

Issa M

Never mind. I figured it out. Pretty straightforward actually. I guess I just needed to sleep on it. Thanks anyway!