Hi Everyone:
My first post so please forgive me. I'm under a deadline and what I thought would prove to be a simple exercise is turning into a not so simple exercise. I work for large insurance company.
I'm trying to lay these rules out in a few simple steps (see below italicized). I've desensitized the information. I'm trying to cook up a small risk sharing/re-calibration (to operating margin) model.
I've attached a file ... in column c the grey cells with blue font are inputs. Everything to the right of column D is the rule based framework.
We were trying to cook up just one formula, or use goal seek, but I think I need to lay out a series of steps so the end user can follow the logic.
Thanks in advance.
Big Meat
– If the Risk Amount is:
– Within 5% +/- of the Target, there shall be no prospective adjustments.
– If more than 5% +/- of the Target, there shall be a prospective adjustment calculated based on the factors below.
– A positive amount more than 5% above the Target, and if KMART Operating Margin is <=0%, then first, such amount as is necessary to bring KMART to 0% shall be allocated 100% to KMART, KMART shall receive 75% of the remainder until KMART Operating Margin is 2.5%, and shall receive 50% of the remainder until KMART achieves 5% margin, and 75% to ESPIRIT thereafter.
– A negative amount more 5% of the Target, if KMART operating margin is >= 5%, 100% of the negative risk amount shall be allocated to KMART until KMART operating margin is 5%, then 75% to KMART until KMART operating margin is 2.5%, then 50% to KMART until KMART operating margin is 0%, and 25% of the remainder to KMART. Provided, however, that if ESPIRIT EBIDA margin would not have been reduced margin below 5%.
My first post so please forgive me. I'm under a deadline and what I thought would prove to be a simple exercise is turning into a not so simple exercise. I work for large insurance company.
I'm trying to lay these rules out in a few simple steps (see below italicized). I've desensitized the information. I'm trying to cook up a small risk sharing/re-calibration (to operating margin) model.
I've attached a file ... in column c the grey cells with blue font are inputs. Everything to the right of column D is the rule based framework.
We were trying to cook up just one formula, or use goal seek, but I think I need to lay out a series of steps so the end user can follow the logic.
Thanks in advance.
Big Meat
– If the Risk Amount is:
– Within 5% +/- of the Target, there shall be no prospective adjustments.
– If more than 5% +/- of the Target, there shall be a prospective adjustment calculated based on the factors below.
– A positive amount more than 5% above the Target, and if KMART Operating Margin is <=0%, then first, such amount as is necessary to bring KMART to 0% shall be allocated 100% to KMART, KMART shall receive 75% of the remainder until KMART Operating Margin is 2.5%, and shall receive 50% of the remainder until KMART achieves 5% margin, and 75% to ESPIRIT thereafter.
– A negative amount more 5% of the Target, if KMART operating margin is >= 5%, 100% of the negative risk amount shall be allocated to KMART until KMART operating margin is 5%, then 75% to KMART until KMART operating margin is 2.5%, then 50% to KMART until KMART operating margin is 0%, and 25% of the remainder to KMART. Provided, however, that if ESPIRIT EBIDA margin would not have been reduced margin below 5%.
Last edited:
