Returning calculated values in multiple scenarios using formulas

sriramdh

New Member
Joined
Sep 19, 2014
Messages
4
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
DEFGHIJKLMNOPQRS
25FOR HEADCOUNT RAMP-UP IN BETWEEN (Issue in Paid training getting paused for previous batch of people when new headcount is added)
26
274No. of weeks in month4
28PPaid Training (weeks)4
29IInduction Training (weeks)4
30
31
32Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12
33OperationsRemaining0.000.000.500.001.001.001.001.001.001.001.001.00
34
35Training (Paid)Remaining0.001.001.000.001.001.001.001.001.001.001.001.00
36Current0.001.000.500.000.000.000.000.000.000.000.000.00
37Spillover1.500.500.000.000.000.000.000.000.000.000.000.00
38
39Training (Induction)Current1.000.000.001.000.000.000.000.000.000.000.000.00
40Spillover0.000.000.000.000.000.000.000.000.000.000.000.00
41
42Headcounts101010121010101010101010
4310002-20000000
44
45To be picked up for revenue calculation. The number denotes the amount of
46
47
Training Cost
Cell Formulas
RangeFormula
G33:R33G33=G35-G36
G35:R35G35=1-G39
G36G36=IF($F$4/$F$3>G35,G35,$F$4/$F$3)
H36:R36H36=IF(H42-G42>0,IF(($F$4/$F$3)+G37>H35,H35,($F$4/$F$3)+G37),IF(G37>H35,H35,G37))
G37G37=IF(G36=G35,($F$4/$F$3)-G36,IF(G35=0,$F$4/$F$3,0))
H37:R37H37=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)))
G39G39=IF($F$5/$F$3>1,1,$F$5/$F$3)
H39:R39H39=IF(H42-G42>0,IF(($F$5/$F$3)+G40>1,1,($F$5/$F$3)+G40),IF(G40>1,1,G40))
G40G40=IF(G39=1,$F$5/$F$3-1,0)
H40:R40H40=IF(H42-G42>0,IF(H39=1,(($F$5/$F$3)+G40)-1,0),IF(H39=1,(G40)-1,0))
H43:R43H43=H42-G42
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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