Task 5

[John Bashunov]

[John Bashunov]

I think this is the hardest problem in the set, so any help will be useful.

I am struggling with the approach for the first and last bullet. 

For the first bullet, I'm thinking that a new discount rate for reserving will need to be used (to add some conservatism). Also, since upfront expenses will increase, the remaining CF stream would become a lot less volatile. And lastly, I think that margin will be added in for padding purposes and insuring that unexpected expenses don't break the bank.

For the 2nd bullet, I think its best to do a forfeiture/redemption table and commentary on how the magnitude of forfeitures in year 2 will decrease,but it will appear. Also, i think redemption will increase in the first year due to fear that their points will expire the next year.

For the 3rd bullet, I think that the biggest impact will be the renewal assumption (i.e. how many new miles will be coming in the door? Since this is conditional on the success of the program in the first year, a sensitivity table can go a long way here).

I'm really interested to see what you guys think as this will probably be my toughest question before writing up the formal memo. 

On Thursday, July 25, 2013 7:28:24 PM UTC-7, John Bashunov wrote:

[ancl...@gmail.com]

The first change: would you also consider amortizing the set up fee (that's how I interpreted the question)...but not sure.
I started this task but have not finished it yet..probably will do tomorrow.

On Thursday, July 25, 2013 9:28:24 PM UTC-5, John Bashunov wrote:

[ancl...@gmail.com]

After rereading he question I realize they are clearly referring to future expenses, so ignore my previous questions.

[ancl...@gmail.com]

OK...so here are my thoughts:

First Change: this would limit first year's profit and also limit the losses in subsequent years. This seems like a good change to me since the majority of the revenue is in the first year...

Second change: I agree with your reasoning, this impacts the forfeiture and the redemption assumptions.

Third change: this would include a lot more uncertainty (and hence risk) about basically all assumptions. Yes, the sensitivity analysis on the purchased miles might be a good idea here (thanks!). The risk with this change is that you cannot change (correct) prices after one year...

[Dana T]

I am in the process of refining my answer to this 5 but initial intent was to stick with something relatively simple so I created a table listing the 3 changes, how the model could be adjusted, the risks/rewards of the change, and my recommendation. I only recommended the first change, holding the reserve, for the other two the risk seemed to outweight the reward.

[Dana T]

to this *task, not 5

[John Bashunov]

This is still my toughest problem. Does anyone have other other approaches to this? 

I am having a hard time making suggested changes to the model based on the upfront reserve. Do you just remove the revenue portions from the formula in C20 on the Single Scenario - Example tab and or modify the NPV formula in the Multi-Scenario tab to calculate the reserve for you. 

Then use that reserve as a 1st year fee, take expenses out of the new calculation, just assuming that your reserve + margin should cover it? 

Or do you use the reserve as a 1st year fee, then convert the reserve to an asset and adjust it based on the difference in expected expenses that year?

[AInul Shafiqah Shafie]

This is somewhat how I did mine. 

1st bullet: I set up a reserve calculation and made an example of how the profits would be if we had a reserve that amortizes every year. 

2nd bullet: I did a sensitivity test for 3 different scenarios here (redemption rates increases significantly in year 1 and 2, redemption rates increases just by a little, forfeiture rates went up slightly + redemption rates increases a little)

3rd bullet: seems like this is riskier than the benefits. so I did not recommend this. 

[AInul Shafiqah Shafie]

John, 

I actually set up a separate reserve calculation on the side. Just took the average of projected expenses (from the 1000 scenarios) for each of the 10 years, calculated the PV and added a contingency margin, and called that my reserve. And then I amortized my reserve across the 10 years, so there would be the same amount of cash flow coming out of the reserve every year. 

[Allan Costa]

I am having difficulty with the amortization portion of the reserve.  I am calculating my reserve as the PV of future expenses (annual program, labor, fuel, equipment).

so profit = baggage revenue - reserve - annual program expenses - labor - fuel - equipment and maintenance?

This doesn't seem right because all you are doing is making your profit less each year.

or would it be that initial profit = price revenue - reserve - start up expenses
and ongoing profit = baggage revenue + payment from reserve - annual program expenses - labor - fuel - equipment and maintenance

Also in the second proposal would the payment from the reserve be the same each year?

[John Bashunov]

I think the problem becomes this:

- Initially you calculate the PV of the Reserve using the projections you already have and add margin  

-Then you amortize this over 10 years and this becomes your new "expense" line

- Therefore profit = (Revenues - Amortized Reserve) each year

- So you see since there are no Adjustments to Reserve that occur each successive year (since reserve doesn't get re-calculated based on the actual expenses that came in), your upfront reserve (your initial guess) better be damn spot on or you'll run in solvency issues or misuse of capital.

That's why I'm not really recommending this one. Instead, I'm going to recommend changing the upfront reserve to a traditional reserve (where adjustments can be made retroactively based on recent experience. 

[AInul Shafiqah Shafie]

I was seeing the reserve as money we would set aside in the beginning, and amortize it throughout the length of the projected expenses. 

So in the initial year, profits would be lower than the original model because profit = price revenue - reserve - start up expenses but in the years after
 profit = baggage revenue + payment from reserve - annual program expenses - labor - fuel - equipment and maintenance, like Allan noted. 

This way, what the reserves does is smoothen out the profits, instead of having a big positive sum in the beginning and losses thereafter, I have a smaller profit initially but level profits for the remainder years. 

I think you can set up a reserve that follows the actual experience. I just used amortization for simplicity sake and to prove my point about the volatility. Theoretically, you could set up a reserve model that matches your future expenses and have it grow at a certain investment rate. 

[Allan Costa]

When discussing how the model would need to be updated, are you giving specific instructions regarding formulas to be entered into excel, and actual new assumptions values?

[John Bashunov]

Might be overkill on my part, but for the reserve question, I have specific instructions on how to reformat the model (not as specific as =vlookup( or anything, but pretty specific. Here's my first point:

In the “Model – Multi Scenario” tab, convert the “BOY Cash Flows” and “MOY Cash Flows” into Revenues and Expenses much like the “Single Scenario – Example” tab

For 2 it's simple assumption changes.

For 3, it's conditional on the performance of the program in the first year. This can be a scenario-based example as well. 

[Christie Ritten]

Thanks to all you guys for your thoughts! This really is a tough question.

1st bullet: Okay, this amortization is making sense to me, I think. Take the present value (at 5%) of future expenses. (I'm taking future to mean everything that comes after time 0, so it would not include the one-time set-up fee, but it would include ongoing program costs, labor, fuel, and equip  for years 1-10.) Then you can calculate the 10-year payment amount. I don't see a problem with using 5% for this problem, since we are using it for other purposes. My instinct is not to add any margin in, because the question says "best estimate."

I agree that this reserving will help smooth out profits and losses. (But the overall profitability over the 10-year model will still be the same, right?) The payment amount won't be exactly right in each year, which I think is okay: if it's a little too much, BBA keeps the difference as profits, and if it's not quite enough, BBA finds the money to cover the difference. This is definitely better than just winging it, trying to come up with $80k or something each year.

I think I'll point out (if not here, then in the Task 6 memo) that it might be tempting to take the big profit in the first year, since BBA's general profitability has been below target, but it is probably more prudent to smooth the profits out.

I won't get too specific about how to change the model. Just explain how it would work, then say, "So in the model we would add an reserve stream and change the profit formula to incorporate it."

2nd bullet: I also plan on playing with the redemption and forfeiture assumptions and showing the results in a table. I think in this case, we would know the plan provisions in advance, so we should goal-seek the price that meets our risk metrics. No sense in keeping our price from Task 2, because that was based on different provisions. I'll leave all the other baseline assumptions alone... although maybe not number of miles purchased. If CDL customers know that it's only 24 months, they might shy away from earning air miles.

For this change, the model itself doesn't need to be altered, I'm thinking, just some inputs.

3rd bullet: Overall, not a good idea because BBA is restrained and might be locked in a bad deal, if miles purchased ends up being lower than expected. (Of course, the upside is also possible.) Management might want to see more stringent risk metrics met before committing. But as we know, miles purchased and redemption/forfeiture rates are a big question mark.

As for model changes, we would want it to handle miles purchased revenue coming in in year 2-3 as well as in year 1. Other than that, no model changes, maybe just input changes to the miles purchased and the redemption/forfeiture rates...

I've been wrestling with how to think about these rates. My first thought: forfeiture rates will go down because more activity possible, with miles now earned in three years instead of one. (I assume earning miles counts as account activity, for purposes of the forfeiture rules, along with redeeming them.) But these baseline rates were developed from BBA frequent flyers, who have the opportunity to earn in every year... so maybe the rates are more appropriate now than they were in previous tasks. Because of the forfeiture pattern, the baseline rates are clearly for new members. But I doubt they were developed by looking only at new members who earned in the first year, and then never again until all their miles were redeemed or forfeited. I mean, that would have been a good idea (appropriate to the original project), but I have a feeling that's not what we're supposed to assume when it says "calculated using BBA's current frequent flyer program" in Task 2. Anyone else have thoughts on this???

[John Bashunov]

Christie,

Like the thinking on the first 2 bullets, but looks like you started to over-think the last one. There's nothing in the model that specifically says that this is a one year contract, so technically it should be fine for a 1,2,3, or even a 10 year contract for all we care. All we need to change, like you said, are the newly injected BOY Miles for each of the additional years and maybe add an assumption for the % of folks electing the program (to give this 100mil a bit more credibility).

You def should mention that we need to re-visit these assumptions. For instance, is the initial setup fee only the same for an 1 year vs a 3 year contract? Can any of these fees be negotiated down now that we have some leverage (business relations, etc.). I was thinking more stuff like this.

Also, don't get bogged down with forfeiture and redemption. The length of the contract has nothing to do with consumer behavior, so we should leave that part alone. All the customers care about is that they have a benefit. They don't care about the inner doings among their credit card company and some airline.

But I know where you're coming from...its a really long question so by the end you're like "I'm all out of creative ideas damn it"

Hopefully that helps :)

[Christie Ritten]

John, thanks for the reality check. :-) I would argue that, technically, forfeiture isn't purely customer behavior. It depends on the circumstances. If new miles are added to your account each year, you will not forfeit any, even if your behavior (e.g. never redeem any miles) remains the same as when miles were only added in year 1.

I will think about it some more. I'll do my best to simplify, but I think some of these thoughts are worth mentioning. Thanks for the advice.

[Salman Shah]

Task2:

The model is not to be changed for this Task  And at the end of the Task it is written to provide graphs and Tables. If we are not supposed to change the Model then how can we add Tables and graphs.

 

When it is written in the Task not to change the Model then there is no need to make changes in the forfeiture and redemption assumption. And change will be random and like aiming in the dark.

No idea, I have how the experience will emerge and so cannot change the assumption based on that.

 

But yes the customers will not appreciate this change and will prefer cash pay back option which will have negative impact on the profitability.