FORMULA HELP: Forecasting Cash Flow for classes

D

DTMAT

New Member
Joined
Jul 7, 2016
Messages
2
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?
[TABLE="width: 1759"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class[/TD]
[TD][/TD]
[TD]Begin Date[/TD]
[TD]End Date[/TD]
[TD]Deposit[/TD]
[TD]Installments[/TD]
[TD]Monthly Payment[/TD]
[TD]Total Cost[/TD]
[TD]Actual Enrollement[/TD]
[TD]Forecasted Enrollment[/TD]
[TD]Oct-16[/TD]
[TD]Nov-16[/TD]
[TD]Dec-16[/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[TD]Jul-17[/TD]
[TD]Aug-17[/TD]
[/TR]
[TR]
[TD]January Apprenticeship[/TD]
[TD]2017[/TD]
[TD]Jan-17[/TD]
[TD]Oct-17[/TD]
[TD] $ 500[/TD]
[TD]12[/TD]
[TD] $ 375[/TD]
[TD] $ 5,000[/TD]
[TD]3[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]June Residency[/TD]
[TD]2017[/TD]
[TD]Jun-17[/TD]
[TD]Aug-17[/TD]
[TD] $ 333[/TD]
[TD]2[/TD]
[TD] $ 333[/TD]
[TD] $ 999[/TD]
[TD]15[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]September Practicum[/TD]
[TD="align: right"]2017[/TD]
[TD] Sep-17[/TD]
[TD]Dec-18[/TD]
[TD] $ 500[/TD]
[TD]16[/TD]
[TD] $ 500[/TD]
[TD] $ 8,500[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

ricafonyat
  • Solved
Loans Payments Consolidated Cash Flow
Replies
1
Views
381
DRSteele
DRSteele
B
  • Question Question
cash flow through automatic months
Replies
0
Views
603
bj70
B
W
  • Question Question
Dynamic XIRR for sum of CF's with Offset
Replies
3
Views
4K
DRSteele
DRSteele
G
  • Question Question
Payment term and cash flow (formula linked with costs) - weekly cash flow
Replies
1
Views
2K
jorismoerings
J
N
  • Question Question
Formula for a single list of Dates from two payment cycles and a list of fix dates
Replies
0
Views
1K
narendra
N

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top