Forcasting

[Elmoo1984]

Hi

I am hope you can help me please. I am trying to calculate predicted costs and profit margins, for a course I am running.
So to explain...
We charge £6,000 per course, the course runs for 9 months, it costs me £4,250 to run the cost in total at a cost of £472 per month, giving me a profit of £1,750.00. ( this sounds like a school Question)
I want to plan additional starts in for each month going forward but only want to count them for 9 months accumulative, I can do it manually but I want to able to amend the the number of starts and the table to update

Its straight forward until I get to month 10 when I want one learner to course to finish.

I have done it manually here but would like a formula to do it.

 

[tico_ocit]

Can you please share via XL2BB? It's very easy to work with!

 

[Elmoo1984]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Costings				Planned Starts
2	Course Charge	£6,000			August	1
3	Course Length (Months)	9			September	2
4	Cost per Month	£472			October	2
5	Course cost	£4,250			November	2
6	Profit	£1,750			December	0
7					January	3
8
9
10		Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21
11	Accumulative	1	3	5	7	7	10	10	10	10	9	7
12	Starts per month	1	2	2	2	0	3	0	0	0	0	0
13	Cost per Month	£ 472	£ 1,417	£ 2,361	£ 3,306	£ 3,306	£ 4,722	£ 4,722	£ 4,722	£ 4,722	£ 4,250	£ 3,306
14
15
16
17
Sheet1

Cell Formulas
Range	Formula
B4	B4	=B5/B3
B6	B6	=B2-B5
B12	B12	=F2
C12	C12	=F3
D12	D12	=F4
E12	E12	=F5
F12	F12	=F6
G12	G12	=F7
B13:L13	B13	=B11*$B$4

 

[tico_ocit]

I added the final month, where the course supposes to end. Although be aware that I don't know if you count the starting month as on of the 9 months, ok? So change at your own need.
I hope that is what you want.
I took the liberty to add some lines, such your profit. So change the number of starting courses and see if it works as you wanted.

help_course.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Costings				Planned Starts		Ends
2	Course Charge	6000			ago-20	1	mai-21
3	Course Length (Months)	9			set-20	0	jun-21
4	Cost per Month	472,22			out-20	0	jul-21
5	Course cost	4250			nov-20	0	ago-21
6	Profit	1750			dez-20	0	set-21
7					jan-21	0	out-21
8
9
10		ago-20	set-20	out-20	nov-20	dez-20	jan-21	fev-21	mar-21	abr-21	mai-21	jun-21
11	Accumulative	1	1	2	3	4	5	6	6	6	5	4
12	Starts per month	1	0	0	0	0	0	0	0	0	0	0
13	Cost per Month	£ 472,22	£ 472,22	£ 944,44	£ 1 416,67	£ 1 888,89	£ 2 361,11	£ 2 833,33	£ 2 833,33	£ 2 833,33	£ 2 361,11	£ 1 888,89
14	Earn per Month	£ 666,7	£ 666,7	£ 1 333,3	£ 2 000,0	£ 2 666,7	£ 3 333,3	£ 4 000,0	£ 4 000,0	£ 4 000,0	£ 3 333,3	£ 2 666,7
15	Profit	£ 194,44	£ 194,44	£ 388,89	£ 583,33	£ 777,78	£ 972,22	£1 166,67	£1 166,67	£1 166,67	£ 972,22	£ 777,78
Folha1

Cell Formulas
Range	Formula
B4	B4	=B5/B3
B6	B6	=B2-B5
G2:G7	G2	=EDATE(E2,9)
B11	B11	=B12
C11:L11	C11	=SUM(IF($G$2:$G$7>C10,IF($E$2:$E$7<C10,1,0)))+C12
B12:L12	B12	=IFERROR(VLOOKUP(B10,$E$2:$F$7,2,FALSE),0)
B13:L13	B13	=B11*$B$4
B14:L14	B14	=($B$2/9)*B11
B15:L15	B15	=B14-B13
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tico_ocit]

I found an error!!! C11 - =SUM(IF($G$2:$G$7>C10,IF($E$2:$E$7<C10,1,0)))+C12 . Shouldn't be 1 but instead the number of starts on that specific month!
So...

help_course.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Costings				Planned Starts		Ends
2	Course Charge	6000			ago-20	1	mai-21
3	Course Length (Months)	9			set-20	2	jun-21
4	Cost per Month	472,22			out-20	0	jul-21
5	Course cost	4250			nov-20	0	ago-21
6	Profit	1750			dez-20	0	set-21
7					jan-21	0	out-21
8
9
10		ago-20	set-20	out-20	nov-20	dez-20	jan-21	fev-21	mar-21	abr-21	mai-21	jun-21
11	Accumulative	1	3	3	3	3	3	3	3	3	2	0
12	Starts per month	1	2	0	0	0	0	0	0	0	0	0
13	Cost per Month	£ 472,22	£ 1 416,67	£ 1 416,67	£ 1 416,67	£ 1 416,67	£ 1 416,67	£ 1 416,67	£ 1 416,67	£ 1 416,67	£ 944,44	£ -
14	Earn per Month	£ 666,7	£ 2 000,0	£ 2 000,0	£ 2 000,0	£ 2 000,0	£ 2 000,0	£ 2 000,0	£ 2 000,0	£ 2 000,0	£ 1 333,3	£ -
15	Profit	£ 194,44	£ 583,33	£ 583,33	£ 583,33	£ 583,33	£ 583,33	£ 583,33	£ 583,33	£ 583,33	£ 388,89	£ -
Folha1

Cell Formulas
Range	Formula
B4	B4	=B5/B3
B6	B6	=B2-B5
G2:G7	G2	=EDATE(E2,9)
B11	B11	=B12
C11:L11	C11	=SUM(IF($G$2:$G$7>C10,IF($E$2:$E$7<C10,$F$2:$F$7,0)))+C12
B12:L12	B12	=IFERROR(VLOOKUP(B10,$E$2:$F$7,2,FALSE),0)
B13:L13	B13	=B11*$B$4
B14:L14	B14	=($B$2/9)*B11
B15:L15	B15	=B14-B13
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Elmoo1984]

Amazing thank you so much!

 

[tico_ocit]

Amazing thank you so much!

You're welcome!