Calculate "the number of weeks" before the goal is reached
Answers

I still need help with if possible.
How to calculate "the number of weeks" before the goal is reached based on the following criteria:
 Using the weekly "Intake Size" (i.e. Site A = 12)
 How long would it take to meet the "Goal to Reach" (i.e. Site A = 345)
 If the weekly turnover rate of 3%
Thank you in advance 🙌
Amy

Hi @Amy Arnold
Can you please share some more details?
 Weekly Turnover rate is 3% of what? 3% of overall goal to reach?
 Intake size means intake per week to reach goal?
 How is intake size related to turnover rate?
 When we calculate weeks to reach goal, should we consider where a particular site stands as of today? Which column represents that value?
Regards,
Saurabh Maheshwari
Smartsheet Superstar
Portfolio Lead, TCS

Thanks for starting this conversation.
I would like to setup a projection to give us an idea of when we might meet our recruitment goals (Goal to Reach).
 The idea is to stay below a 3% Weekly Turnover Rate (WTR). In reality, this number fluctuates above and below 3%.
 The weekly Intake Size and the WTR directly impacts the Goal to Reach column.
Here's a scenario:
 The Current # of students on campus is 145.
 We'll lose 3% of the Current # of students each week for different reasons.
 We'll intake 12 new students weekly.
 Considering this, how long might it take to have 345 students on campus (Goal to Reach)?
Link to Smartsheet: https://app.smartsheet.com/b/publish?EQBCT=573c191bfe014a77898ac0eebadb2a83
Thank you

Don't think there is a function in Smartsheet that can accomplish this

I think below formula should give you desired number of weeks to reach goal
=([Goal to Reach]@row  [Current #]@row) / [Intake Size]@row + ((([Goal to Reach]@row  [Current #]@row) / [Intake Size]@row) * 0.03 * [Current #]@row) / [Intake Size]@row
 First part of formula i.e. ([Goal to Reach]@row  [Current #]@row) / [Intake Size]@row  This will provide number of weeks to reach goal if we constantly intake students and if we do not have any turnover
 Second part of formula adds additional weeks needed if we have WTR of 3% on current count  ((([Goal to Reach]@row  [Current #]@row) / [Intake Size]@row) * 0.03 * [Current #]@row) / [Intake Size]@row
I tried this with sample data provided by you and here is the outcome
Regards,
Saurabh Maheshwari
Smartsheet Superstar
Portfolio Lead, TCS

The formula works perfectly! Thank you for explaining how it works too. That helps not to savvy people like me.
Have a nice weekend!
Amy

Glad it helped !
Regards,
Saurabh Maheshwari
Smartsheet Superstar
Portfolio Lead, TCS

@Amy Arnold @saurabhmaheshwari
Not exactly sure what the above formula is doing, but it definitely would not solve for this question.
Considering that your are constantly adding only 12 per week but losing 3%. That would mean that from when you hit 300 students you are losing at least 9 students per week (300 x .03 = 9) which means you are only adding 3 per week (129 = 3).
Then when you reach 333 students you start losing 10 per week, and only adding 2.
All in all your number of weeks to reach goal would be about 50 weeks.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!