Returning calculated values in multiple scenarios using formulas

[sriramdh]

Hello everyone,

I have been given a complex task. I need to calculate the training effort for multiple scenarios. For each of the scenarios, I have a duration for which the company will be absorbing the cost of training and anything additional to this duration will be transferred to the customer.

The three scenarios that I'm working with for calculating the training costs are below:

1. Initial Ramp-up - no cost to the customer during ramp-up period (induction training), the additional cost/effort of training to be apportioned depending on the paid training weeks
2. Ramp-up in intermediate months - there could be an addition of resources during any intervening months, this would mean that the delta from any resource addition would be treated in the same way as point 1
3. Ramp down in intermediate months - there could be a reduction of resources during any intervening months, this would mean that any leftover paid training needs to be adjusted accordingly.

I was able to create scenario 1 using formulas, but scenarios 2 & 3 are too complicated. Would be great if someone could help me arrive at the formula. My working file is attached.

Calculation Training Revenue.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
25						FOR HEADCOUNT RAMP-UP IN BETWEEN (Issue in Paid training getting paused for previous batch of people when new headcount is added)
26
27	4	No. of weeks in month	4
28	P	Paid Training (weeks)	4
29	I	Induction Training (weeks)	4
30
31
32				Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
33		Operations	Remaining	0.00	0.00	0.50	0.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
34
35		Training (Paid)	Remaining	0.00	1.00	1.00	0.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
36			Current	0.00	1.00	0.50	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
37			Spillover	1.50	0.50	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
38
39		Training (Induction)	Current	1.00	0.00	0.00	1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
40			Spillover	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
41
42			Headcounts	10	10	10	12	10	10	10	10	10	10	10	10
43				10	0	0	2	-2	0	0	0	0	0	0	0
44
45			To be picked up for revenue calculation. The number denotes the amount of
46
47
Training Cost

Cell Formulas
Range	Formula
G33:R33	G33	=G35-G36
G35:R35	G35	=1-G39
G36	G36	=IF($F$4/$F$3>G35,G35,$F$4/$F$3)
H36:R36	H36	=IF(H42-G42>0,IF(($F$4/$F$3)+G37>H35,H35,($F$4/$F$3)+G37),IF(G37>H35,H35,G37))
G37	G37	=IF(G36=G35,($F$4/$F$3)-G36,IF(G35=0,$F$4/$F$3,0))
H37:R37	H37	=IF(H42-G42>0,IF(H36=1,(($F$4/$F$3)+G37)-1,0),IF(AND(H35=H36,H35<>0),G37-H36,IF(AND(H35=0,G37<>0),G37,0)))
G39	G39	=IF($F$5/$F$3>1,1,$F$5/$F$3)
H39:R39	H39	=IF(H42-G42>0,IF(($F$5/$F$3)+G40>1,1,($F$5/$F$3)+G40),IF(G40>1,1,G40))
G40	G40	=IF(G39=1,$F$5/$F$3-1,0)
H40:R40	H40	=IF(H42-G42>0,IF(H39=1,(($F$5/$F$3)+G40)-1,0),IF(H39=1,(G40)-1,0))
H43:R43	H43	=H42-G42