Employee head count

[SPVM]

Hi

I am busy with a 5 year financial forecast. My intention is to appoint two new employees each year. To keep it simple for now, each employees sales income and cost to company is equal. eg Sales = $50k, CTC $30k.
The variable is when the employees starts. My assumption is that once the employee starts he will continue until the end of the 5 year forecast.

January 2021= 2 Employees. Entire year 2 employees. Sales=$100k, CTC=$60k. They will continue for the entire forecast. 5x12=60 months.

January 2022+1. Therefore 3 employees until June. $150k, CTC $90k and continue for the rest of the 5 year forecast.
Then July 2022+ 1. Therefore end of year 2022. Employee head count 4. Sales=$200k, CTC=$120k. They will continue until the completion of the 5 year forecast.

January 2023+1. Therefore 5 employees until June. $250k, CTC $150k
Then July 2023+ 1. Therefore 6 employees to the end of year 2023. Head count 6. Sales=$300k, CTC=$180k. They will continue until the completion of the 5 year forecast.

Same assumptions for 2024 and 2025.

Where it becomes complicated. I want to be able to change when the employee starts. Not a fixed January and July, for each year. Maybe January and August, or February and September. Etc, Ect.

I have a table, of 5 rows representing the years and 12 columns representing the months. I complete the forecast by inserting a X, 0 or 1 in the relevant cell corresponding to his start date.

Then I want to be able to have a formula that will tell me. Employee head count, Sales total and CTC for any given month in the total of 60 months.

Eg. Cell E4 is April 2023. The the cumulative figures from the start January 2021 (B2) is Head count x, sales xy and CTC is xyz.

Then I can check June 2024 which is cell G5.

I need to be able to instantly see my Employee head count, sales and CTC for any given month throughout the 5 year forecast, as per my initial data input of when employees would be appointed.

Is the beyond excels capabilities or am I able to do this.

Please help.

Kind Regards
David

 

[GraH]

Following your assumptions it could be something like (notice this is not "flexible")

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
1		Sales Income	Cost To Company
2		k$ 50	k$ 30		Start Year	Sales Income	Mid Year Starter	2021	2022	2023	2024	2025
3					2021	FTE 1		k$ 50	k$ 50	k$ 50	k$ 50	k$ 50
4	Year	Sales Income	Cost To Company		2021	FTE 2		k$ 50	k$ 50	k$ 50	k$ 50	k$ 50
5	2021	k$ 100	k$ 60		2022	FTE 3		$ -	k$ 50	k$ 50	k$ 50	k$ 50
6	2022	k$ 175	k$ 105		2022	FTE 4	Y	$ -	k$ 25	k$ 50	k$ 50	k$ 50
7	2023	k$ 275	k$ 165		2023	FTE 5		$ -	$ -	k$ 50	k$ 50	k$ 50
8	2024	k$ 375	k$ 225		2023	FTE 6	Y	$ -	$ -	k$ 25	k$ 50	k$ 50
9	2025	k$ 475	k$ 285		2024	FTE 7		$ -	$ -	$ -	k$ 50	k$ 50
10					2024	FTE 8	Y	$ -	$ -	$ -	k$ 25	k$ 50
11					2025	FTE 9		$ -	$ -	$ -	$ -	k$ 50
12					2025	FTE 10	Y	$ -	$ -	$ -	$ -	k$ 25
13							Total	k$ 100	k$ 175	k$ 275	k$ 375	k$ 475
Sheet1

Cell Formulas
Range	Formula
I3:M12	I3	=IF(N(H3)>0,$C$2,((I$2>=$F3)*MAX(1/2,$H3=""))*$C$2)
I13:M13	I13	=SUM(I3:I12)
C5:D9	C5	=C$2*(2*(ROW($B5)-ROW($B$4))-IF(N($B4)=0,0,1))+(C$2/2*IF(N($B4)=0,0,1))

 

[JGordon11]

Model inputs are in Bold Red

MrE20210123-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Sales/yr	$ 50,000
3	CtC/yr	$ 30,000
4
5
6			1	2	3	4	5	6	7	8	9	10	11	12		1st Hire	2nd Hire	Total Sales	Cumulative	CtC	Cumulative
7		Starting #	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Month	Month	for year	Sales	for year	CtC
8	2021	0	1	1	1	1	1	1	2	2	2	2	2	2		1	7	$ 75,000	$ 75,000	$ 45,000	$ 45,000
9	2022	2	2	3	3	3	3	3	3	4	4	4	4	4		2	8	$ 166,667	$ 241,667	$ 100,000	$ 145,000
10	2023	4	4	4	5	5	5	6	6	6	6	6	6	6		3	6	$ 270,833	$ 512,500	$ 162,500	$ 307,500
11	2024	6	6	7	7	7	7	8	8	8	8	8	8	8		2	6	$ 375,000	$ 887,500	$ 225,000	$ 532,500
12	2025	8	9	9	9	9	9	9	9	9	10	10	10	10		1	9	$ 466,667	$ 1,354,167	$ 280,000	$ 812,500
Sheet3

Cell Formulas
Range	Formula
C8:N12	C8	=B8+($P8=C$6)+($Q8=C$6)
R8:R12	R8	=SUMPRODUCT(C8:N8*$B$2/12)
S8:S12	S8	=SUM($R$8:R8)
T8:T12	T8	=SUMPRODUCT(C8:N8*$B$3/12)
U8:U12	U8	=SUM($T$8:T8)
B9:B12	B9	=N8

 

[SPVM]

H

Model inputs are in Bold Red

MrE20210123-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Sales/yr	$ 50,000
3	CtC/yr	$ 30,000
4
5
6			1	2	3	4	5	6	7	8	9	10	11	12		1st Hire	2nd Hire	Total Sales	Cumulative	CtC	Cumulative
7		Starting #	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Month	Month	for year	Sales	for year	CtC
8	2021	0	1	1	1	1	1	1	2	2	2	2	2	2		1	7	$ 75,000	$ 75,000	$ 45,000	$ 45,000
9	2022	2	2	3	3	3	3	3	3	4	4	4	4	4		2	8	$ 166,667	$ 241,667	$ 100,000	$ 145,000
10	2023	4	4	4	5	5	5	6	6	6	6	6	6	6		3	6	$ 270,833	$ 512,500	$ 162,500	$ 307,500
11	2024	6	6	7	7	7	7	8	8	8	8	8	8	8		2	6	$ 375,000	$ 887,500	$ 225,000	$ 532,500
12	2025	8	9	9	9	9	9	9	9	9	10	10	10	10		1	9	$ 466,667	$ 1,354,167	$ 280,000	$ 812,500
Sheet3

Cell Formulas
Range	Formula
C8:N12	C8	=B8+($P8=C$6)+($Q8=C$6)
R8:R12	R8	=SUMPRODUCT(C8:N8*$B$2/12)
S8:S12	S8	=SUM($R$8:R8)
T8:T12	T8	=SUMPRODUCT(C8:N8*$B$3/12)
U8:U12	U8	=SUM($T$8:T8)
B9:B12	B9	=N8

Hi

Thank you for this, its perfect, just what I needed.
An addition in a separate spread sheet is to know which month the 1st and 2nd hire took place. As the assumption is when I hire another employee it is because I have acquired another client and this results in a once off admin fee $2000. So for that specific month I need to show the extra income/sales.


As per you example above. Info range P8:Q12. The value represent a month. (3=March, 5=May) The answer must then appear in the corresponding cell for that month in that year.
eg 2023. 1st hire March (3) = $2000 in column E row 13/14? 2nd hire June (6) = $2000 in column H row 13/14.

Jan Feb March April May June July August Sep Oct Nov Dec
2021 $2000 $2000
2022 $2000 $2000
2023 $2000 $2000

Vlookup wont work?
What function do you use for the answer to appear in a possibility of cells within a column range C - N. (Jan-Dec)

Thank you for your help.

 

[JGordon11]

Something like this:

MrE20210123-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Fee	$ 2,000
2
3		1	2	3	4	5	6	7	8	9	10	11	12
4		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
5	2021	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -
6	2022	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -
7	2023	$ -	$ -	$ 2,000	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -
8	2024	$ -	$ 2,000	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -
9	2025	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -
Admin Fees

Cell Formulas
Range	Formula
B5:M9	B5	=((B$3=Forecast!$P8)+(B$3=Forecast!$Q8))*$C$1

 

[SPVM]

Something like this:

MrE20210123-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Fee	$ 2,000
2
3		1	2	3	4	5	6	7	8	9	10	11	12
4		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
5	2021	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -
6	2022	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -
7	2023	$ -	$ -	$ 2,000	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -
8	2024	$ -	$ 2,000	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -
9	2025	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -
Admin Fees

Cell Formulas
Range	Formula
B5:M9	B5	=((B$3=Forecast!$P8)+(B$3=Forecast!$Q8))*$C$1

That was quick. I see my example shifted the info under each other, sorry but you understood my query. I will have a look at your solution thank you.

 

[SPVM]

That was quick. I see my example shifted the info under each other, sorry but you understood my query. I will have a look at your solution thank you.

Something like this:

MrE20210123-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Fee	$ 2,000
2
3		1	2	3	4	5	6	7	8	9	10	11	12
4		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
5	2021	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -
6	2022	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -
7	2023	$ -	$ -	$ 2,000	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -
8	2024	$ -	$ 2,000	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -
9	2025	$ 2,000	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 2,000	$ -	$ -	$ -
Admin Fees

Cell Formulas
Range	Formula
B5:M9	B5	=((B$3=Forecast!$P8)+(B$3=Forecast!$Q8))*$C$1

Hi

I could not get the forecast function to work.

I used IF/OR
=--(IF(OR(C$6=$P$8,C$6=$Q$8),$B$3))

Thanks for your help

 

[JGordon11]

Forecast is the name of the worksheet I was referencing since you asked how to do the calc from a different worksheet. So Forecast!$P8 is referencing cell P8 on the sheet called Forecast. Change it to the name of the sheet where the original data table is located (whatever you named it, or if you didn't name it it might just be Sheet1). If you're doing the calc on the same sheet as the original data table then you don't need the worksheet identifier.