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

System

This discussion was created from comments split from: Summary Sheet Symbols and Projection Question.

Amy Arnold

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

saurabhmaheshwari

Hi @Amy Arnold

Can you please share some more details?

1. Weekly Turnover rate is 3% of what? 3% of overall goal to reach?
2. Intake size means intake per week to reach goal?
3. How is intake size related to turnover rate?
4. When we calculate weeks to reach goal, should we consider where a particular site stands as of today? Which column represents that value?
5. 
   

Amy Arnold

Hi @saurabhmaheshwari,

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

Leibel S

@Amy Arnold

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

saurabhmaheshwari

@Amy Arnold

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

1. 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
2. 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

Amy Arnold

@saurabhmaheshwari,

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

saurabhmaheshwari

Glad it helped !

Leibel S

@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 (12-9 = 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.