Formula or VBA to breakdown total headcount and keep a running total

[Pcianf]

Hi Everyone

Looking for some assistance with either a formula or VBA code to do the following:

For simplicity, Sheet 1 contains 2 columns. Column H is 'Implementation Date and Column O is 'Dev Headcount'

Sheet 2 contains a chart spanning over 20 months.

What I am trying to do is build a headcount forecast based on new projects added to sheet 1. Each project takes roughly 7 months to implement and the headcount effort breakdown for each month is below

Month 1 3.0%
Month 2 14.0%
Month 3 17.0%
Month 4 21.5%
Month 5 21.0%
Month 6 17.5%
Month 7 6.0%

I need Sheet 2 to recognize when there is a new entry on Sheet 1. Further to that, I would need the total headcount to be broken down based on the Implementation date and the 7 months leading up to it based on the % breakdown above. I would need this continuously summed on the same row 19 Sold Capacity - BAU on Sheet 2 as more entries are added on Sheet 1.

Sample pictures are attached.

Let me know if you need any more info.

Sheet 1

Sheet 2

 

[Eric W]

Welcome to the MrExcel forum!

Try this:

Book2
	H							O
1
2
3	target implementation date							dev Headcount
4	12/1/2022							27
5	12/1/2022							78
6	11/1/2022							34
7	1/1/2023							21
8
Sheet1

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023
3	Headcount and Capacity
4	Associates Per Department	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125
5	Business Days in the Month	23	21	22	22	21	23	22	21	22	22	22	20	23	20	23	22	21	23
6	Available Capacity in Days	2875	2625	2750	2750	2625	2875	2750	2625	2750	2750	2750	2500	2875	2500	2875	2750	2625	2875
7	Subtractions	0	0	1.02	7.91	21.11	28.1	33.285	32.515	24.825	9.975	1.26	0	0	0	0	0	0	0
8	Remaining Capacity
9	Over/Under Dev FTE Equiv
10	Sold Capacity - BAU
11
12
13
14	Monthly breakdown
15	3%
16	14%
17	17%
18	21.50%
19	21%
20	17.50%
21	6%
Sheet2

Cell Formulas
Range	Formula
B5:S5	B5	=NETWORKDAYS(B2,EOMONTH(B2,0))
B6:S6	B6	=B4*B5
B7:S7	B7	=SUM(IFERROR(INDEX($A$15:$A$21,8-MATCH(EOMONTH(Sheet1!$H$4:$H$20+0,0),EOMONTH(B$2,{0,1,2,3,4,5,6}),0))*Sheet1!$O$4:$O$20,0))

This only works in Excel 365, although I might be able to figure out something for earlier versions. Also, I have several questions about how the headcount is applied. Presumably the business days per month varies from your sheet due to holidays. But take a look and see if this is on the right track.

 

[Pcianf]

Fantastic, this worked! I appreciate the help.

Thank you

 

[Eric W]

That was fun to figure out. Here's a slightly shorter version:

=SUM(IFERROR(INDEX($A$15:$A$21,8-MATCH(Sheet1!$H$4:$H$20,EDATE(B$2,{0,1,2,3,4,5,6}),0))*Sheet1!$O$4:$O$20,0))

Both versions require the dates in both tables to be the first of the month, although you can format them to just show the month/year.

Glad I could help!