Hi,
I'm trying to create a Cash Flow forecast for different classes that start at certain times of the year with different amounts of billing amounts and installments.
For example: A class that costs $5,000 starts in January 2017 and is 10 months long, open enrollment is 3 months prior to course. There are two options to pay.
Option A: Someone signs up in October 2016, they pay $500 to secure a spot in class and could start paying their monthly payments of $375/month in November but they would pay for a longer period of time 12 months (November 2016 through October 2017).
OR
Option B: Someone would pay a $500 registration in October 2016 and then another $1,000 one month before class (December 2016) and then 10 payments of $350/month would start from January 2017 and end in October 2017.
I've been manually calculating using some basic formulas for when we would expect the money each month but am trying to find an easier way that is more plug and play friendly. Is there a way that I could list a course, begin date of billing, end date of billing, total cost, the monthly payment, how many installments, and then have a formula that automatically populates the amount of revenue generated each month depending on when a course started and the number of installments?
Here is an example of how I was starting the tab below.
Could someone either point me in the direction of formulas to utilize or the best way to arrange a forecast model like this given the information above?
<tbody>
</tbody>
I'm trying to create a Cash Flow forecast for different classes that start at certain times of the year with different amounts of billing amounts and installments.
For example: A class that costs $5,000 starts in January 2017 and is 10 months long, open enrollment is 3 months prior to course. There are two options to pay.
Option A: Someone signs up in October 2016, they pay $500 to secure a spot in class and could start paying their monthly payments of $375/month in November but they would pay for a longer period of time 12 months (November 2016 through October 2017).
OR
Option B: Someone would pay a $500 registration in October 2016 and then another $1,000 one month before class (December 2016) and then 10 payments of $350/month would start from January 2017 and end in October 2017.
I've been manually calculating using some basic formulas for when we would expect the money each month but am trying to find an easier way that is more plug and play friendly. Is there a way that I could list a course, begin date of billing, end date of billing, total cost, the monthly payment, how many installments, and then have a formula that automatically populates the amount of revenue generated each month depending on when a course started and the number of installments?
Here is an example of how I was starting the tab below.
Could someone either point me in the direction of formulas to utilize or the best way to arrange a forecast model like this given the information above?
| 2016 | 2017 | |||||||||||||||||||
| Class | Begin Date | End Date | Deposit | Installments | Monthly Payment | Total Cost | Actual Enrollement | Forecasted Enrollment | Oct-16 | Nov-16 | Dec-16 | Jan-17 | Feb-17 | Mar-17 | Apr-17 | May-17 | Jun-17 | Jul-17 | Aug-17 | |
| January Apprenticeship | 2017 | Jan-17 | Oct-17 | $ 500 | 12 | $ 375 | $ 5,000 | 3 | 60 | |||||||||||
| June Residency | 2017 | Jun-17 | Aug-17 | $ 333 | 2 | $ 333 | $ 999 | 15 | 18 | |||||||||||
| September Practicum | 2017 | Sep-17 | Dec-18 | $ 500 | 16 | $ 500 | $ 8,500 | 5 | 25 | |||||||||||
<tbody>
</tbody>
